cases to variables, index variable needed?

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

cases to variables, index variable needed?

Scott Dixon
It has been some years since I have worked with SPSS, so I need to apologize in advance if my question resembles a newbie question.
I am using SPSS 18 for Windows.
I have approximately 4800 cases in the raw file.

I was able to download a long list of names, cities and states in the following structure:

John Doe
Portland
Oregon
Mary Sanchez
San Francisco
California
Sid Smith
Seattle
Washington

I need to get the data into the following structure:

name                    city                      state
John Doe               Portland              Oregon
Mary Sanchez        San Francisco      California
Sid Smith              Seattle                Washington

I have looked into "cases to variables" and it appears I need an index variable.  With the index variable, the original data might look like:

1 John Doe
1 Portland
1 Oregon
2 Mary Sanchez
2 San Francisco
2 California
3 Sid Smith
3 Seattle
3 Washington

Can someone please give me some pointers on how to create that kind of repeating pattern for an index variable?

Thank you very much,

Scott

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD
Reply | Threaded
Open this post in threaded view
|

Re: cases to variables, index variable needed?

Adrian Barnett-2
Hi Scott
You could do this using the DATA LIST command when you define the data layout, telling it there are 3 records per case. Use RECORDS = 3.
 
Regards,

Adrian
--
Adrian Barnett        | "It's always the trombone player"
                      | (Faye Dunaway in 'The Arrangement')
Email: [hidden email]



From: Scott Dixon <[hidden email]>
To: [hidden email]
Sent: Saturday, 22 October 2016, 16:30
Subject: cases to variables, index variable needed?

It has been some years since I have worked with SPSS, so I need to apologize in advance if my question resembles a newbie question.
I am using SPSS 18 for Windows.
I have approximately 4800 cases in the raw file.

I was able to download a long list of names, cities and states in the following structure:

John Doe
Portland
Oregon
Mary Sanchez
San Francisco
California
Sid Smith
Seattle
Washington

I need to get the data into the following structure:

name                    city                      state
John Doe              Portland              Oregon
Mary Sanchez        San Francisco      California
Sid Smith              Seattle                Washington

I have looked into "cases to variables" and it appears I need an index variable.  With the index variable, the original data might look like:

1 John Doe
1 Portland
1 Oregon
2 Mary Sanchez
2 San Francisco
2 California
3 Sid Smith
3 Seattle
3 Washington

Can someone please give me some pointers on how to create that kind of repeating pattern for an index variable?

Thank you very much,

Scott

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

===================== To manage your subscription to SPSSX-L, send a message to [hidden email] (not to SPSSX-L), with no body text except the command. To leave the list, send the command SIGNOFF SPSSX-L For a list of commands to manage subscriptions, send the command INFO REFCARD
Reply | Threaded
Open this post in threaded view
|

Re: cases to variables, index variable needed?

John F Hall

Scott

 

If it’s a while since you used SPSS, you might also have a look at the tutorials on my page Block 1: From questionnaire to SPSS saved file

http://surveyresearch.weebly.com/block-1-from-questionnaire-to-spss-saved-file.html

 

John F Hall (Mr)

[Retired academic survey researcher]

 

Email:   [hidden email] 

Website: www.surveyresearch.weebly.com

SPSS start page:  www.surveyresearch.weebly.com/1-survey-analysis-workshop

 

 

 

From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Adrian Barnett
Sent: 22 October 2016 09:12
To: [hidden email]
Subject: Re: cases to variables, index variable needed?

 

Hi Scott

You could do this using the DATA LIST command when you define the data layout, telling it there are 3 records per case. Use RECORDS = 3.

 

Regards,

Adrian
--
Adrian Barnett        | "It's always the trombone player"
                      | (Faye Dunaway in 'The Arrangement')
Email: [hidden email]

 


From: Scott Dixon <[hidden email]>
To: [hidden email]
Sent: Saturday, 22 October 2016, 16:30
Subject: cases to variables, index variable needed?


It has been some years since I have worked with SPSS, so I need to apologize in advance if my question resembles a newbie question.
I am using SPSS 18 for Windows.
I have approximately 4800 cases in the raw file.

I was able to download a long list of names, cities and states in the following structure:

John Doe
Portland
Oregon
Mary Sanchez
San Francisco
California
Sid Smith
Seattle
Washington

I need to get the data into the following structure:

name                    city                      state
John Doe              Portland              Oregon
Mary Sanchez        San Francisco      California
Sid Smith              Seattle                Washington

I have looked into "cases to variables" and it appears I need an index variable.  With the index variable, the original data might look like:

1 John Doe
1 Portland
1 Oregon
2 Mary Sanchez
2 San Francisco
2 California
3 Sid Smith
3 Seattle
3 Washington

Can someone please give me some pointers on how to create that kind of repeating pattern for an index variable?

Thank you very much,

Scott

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

===================== To manage your subscription to SPSSX-L, send a message to [hidden email] (not to SPSSX-L), with no body text except the command. To leave the list, send the command SIGNOFF SPSSX-L For a list of commands to manage subscriptions, send the command INFO REFCARD

===================== To manage your subscription to SPSSX-L, send a message to [hidden email] (not to SPSSX-L), with no body text except the command. To leave the list, send the command SIGNOFF SPSSX-L For a list of commands to manage subscriptions, send the command INFO REFCARD
Reply | Threaded
Open this post in threaded view
|

Re: cases to variables, index variable needed?

Bruce Weaver
Administrator
In reply to this post by Scott Dixon
I don't have SPSS on this machine, so cannot test right now.  But I think that the following will work:

COMPUTE Index = SUM(LAG(Index),MOD($CASENUM,3) EQ 1).
FORMATS Index (F5.0).
DESCRIPTIVES Index.

This assumes you always have exactly 3 rows of data per person with the data sorted appropriately.

HTH.

Scott Dixon wrote
It has been some years since I have worked with SPSS, so I need to apologize in advance if my question resembles a newbie question.
I am using SPSS 18 for Windows.
I have approximately 4800 cases in the raw file.

I was able to download a long list of names, cities and states in the following structure:

John Doe
Portland
Oregon
Mary Sanchez
San Francisco
California
Sid Smith
Seattle
Washington

I need to get the data into the following structure:

name                    city                      state
John Doe               Portland              Oregon
Mary Sanchez        San Francisco      California
Sid Smith              Seattle                Washington

I have looked into "cases to variables" and it appears I need an index variable.  With the index variable, the original data might look like:

1 John Doe
1 Portland
1 Oregon
2 Mary Sanchez
2 San Francisco
2 California
3 Sid Smith
3 Seattle
3 Washington

Can someone please give me some pointers on how to create that kind of repeating pattern for an index variable?

Thank you very much,

Scott

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD
--
Bruce Weaver
bweaver@lakeheadu.ca
http://sites.google.com/a/lakeheadu.ca/bweaver/

"When all else fails, RTFM."

NOTE: My Hotmail account is not monitored regularly.
To send me an e-mail, please use the address shown above.
Reply | Threaded
Open this post in threaded view
|

Re: cases to variables, index variable needed?

Art Kendall
Was your data stored in such a way that you could just use DATA LIST TO read 3 lines (RECORDS for a case?
Art Kendall
Social Research Consultants
Reply | Threaded
Open this post in threaded view
|

Re: cases to variables, index variable needed?

Bruce Weaver
Administrator
Adrian made the same suggestion earlier in the thread.  But nowadays, if one uses the GUI to generate a draft of the command syntax, they'll get a GET DATA command rather than DATA LIST.  But it also allows one to read multiple lines of data per case.  E.g., assuming the OP's sample data are in a file called rawdata.txt:

PRESERVE.
SET DECIMAL DOT.
GET DATA  /TYPE=TXT
  /FILE="C:\Temp\rawdata.txt"
  /ENCODING='Locale'
  /FIXCASE=3
  /ARRANGEMENT=FIXED
  /FIRSTCASE=1
  /VARIABLES=
  /1 Name 0-24 A25
  /2 City 0-24 A25
  /3 State 0-24 A25.
RESTORE.
ALTER TYPE ALL (A=AMIN).
LIST.


Alternatively, if the data are already in a .SAV file, and if it is guaranteed that there are 3 rows per ID, with the values ordered as Name, City, State, then one could do something simple like this:

STRING Name City State (A50).
COMPUTE Rec3 = MOD($CASENUM,3) EQ 0.
EXECUTE.
DO IF Rec3.
- COMPUTE Name = LAG(StringVar,2).
- COMPUTE City = LAG(StringVar).
- COMPUTE State = StringVar.
END IF.
ALTER TYPE ALL (A=AMIN).
************************.
SELECT IF Rec3.
************************.
LIST Name to State.

But be careful with that SELECT IF line, as it will get rid of 2/3 of the cases.  

Using this code on the OP's sample data gives the following output:

Name         City          State
 
John Doe     Portland      Oregon
Mary Sanchez San Francisco California
Sid Smith    Seattle       Washington

HTH.


Art Kendall wrote
Was your data stored in such a way that you could just use DATA LIST TO read 3 lines (RECORDS for a case?
--
Bruce Weaver
bweaver@lakeheadu.ca
http://sites.google.com/a/lakeheadu.ca/bweaver/

"When all else fails, RTFM."

NOTE: My Hotmail account is not monitored regularly.
To send me an e-mail, please use the address shown above.