Restructuring a vertical file into a horisontal one without renaming the variables manually

[Staffan Lindberg]

Dear list!

 

I have an often recurring problem and have sneaking suspicion that there must be a more elegant solution than my clumsy one. As a typical example I have a vertical file with some background variables (i.e. ID, VAR1 to VAR10). As an additional variable I have ADMINORDER, which is the admission number sorted by date. A patient can have between 1 to 5 admissions, so ADMINORDER varies between 1 to 5 within the same ID.

 

I want to restructure this file into a horizontal one, (i.e. ID, VAR1(1) to VAR10(1), VAR1(2) to VAR10(2),…………….VAR1(5) to VAR10(5)) in order to make same some correlations and crosstabulations. I usually do this by creating 5 subfiles and then before merging rename the variables in file 2-5 as no two variables can have the same name in a file. This is a rather tedious process when you have large number of background variables. Is there a more elegant and efficient solution to this ? I’ve been experimenting with RESTRUCTURE FILES but can’t get it to work properly and probably miss something there. Can anybody help or give a hint?

 

best

 

Staffan Lindberg

Sweden

[Mario Giesel]

Staffan,

 

CASESTOVARS

helps in your case.

 

Good luck,

 Mario

---

Von: Staffan Lindberg <[hidden email]>
An: [hidden email]
Gesendet: Montag, den 14. Februar 2011, 16:31:40 Uhr
Betreff: Restructuring a vertical file into a horisontal one without renaming the variables manually

Dear list!

 

I have an often recurring problem and have sneaking suspicion that there must be a more elegant solution than my clumsy one. As a typical example I have a vertical file with some background variables (i.e. ID, VAR1 to VAR10). As an additional variable I have ADMINORDER, which is the admission number sorted by date. A patient can have between 1 to 5 admissions, so ADMINORDER varies between 1 to 5 within the same ID.

 

I want to restructure this file into a horizontal one, (i.e. ID, VAR1(1) to VAR10(1), VAR1(2) to VAR10(2),…………….VAR1(5) to VAR10(5)) in order to make same some correlations and crosstabulations. I usually do this by creating 5 subfiles and then before merging rename the variables in file 2-5 as no two variables can have the same name in a file. This is a rather tedious process when you have large number of background variables. Is there a more elegant and efficient solution to this ? I’ve been experimenting with RESTRUCTURE FILES but can’t get it to work properly and probably miss something there. Can anybody help or give a hint?

 

best

 

Staffan Lindberg

Sweden

[Bruce Weaver]

For examples of how to use CASESTOVARS, see this tutorial from the UCLA Computing website.

   http://www.ats.ucla.edu/stat/Spss/modules/reshapew115.htm

For other data management tutorials, see:

   http://www.ats.ucla.edu/stat/Spss/topics/data_management.htm

HTH.

Mario Giesel wrote

Staffan,

CASESTOVARS
helps in your case.

Good luck,
 Mario




________________________________
Von: Staffan Lindberg <[e-mail]>
An: [e-mail]
Gesendet: Montag, den 14. Februar 2011, 16:31:40 Uhr
Betreff: Restructuring a vertical file into a horisontal one without renaming
the variables manually


Dear list!
 
I have an often recurring problem and have sneaking suspicion that there must be
a more elegant solution than my clumsy one. As a typical example I have a
vertical file with some background variables (i.e. ID, VAR1 to VAR10). As an
additional variable I have ADMINORDER, which is the admission number sorted by
date. A patient can have between 1 to 5 admissions, so ADMINORDER varies between
1 to 5 within the same ID.
 
I want to restructure this file into a horizontal one, (i.e. ID, VAR1(1) to
VAR10(1), VAR1(2) to VAR10(2),…………….VAR1(5) to VAR10(5)) in order to make same
some correlations and crosstabulations. I usually do this by creating 5 subfiles
and then before merging rename the variables in file 2-5 as no two variables can
have the same name in a file. This is a rather tedious process when you have
large number of background variables. Is there a more elegant and efficient
solution to this ? I’ve been experimenting with RESTRUCTURE FILES but can’t get
it to work properly and probably miss something there. Can anybody help or give
a hint?
 
best
 
Staffan Lindberg
Sweden

[lucameyer]

Hi Staffan,

See if this example can help:

DATASET CLOSE ALL.
NEW FILE.
/* FIRST SOME SAMPLE DATA */
DATA LIST LIST /ID VAR1 TO VAR3 ADMINORDER (5F2).
BEGIN DATA.
1 0 1 0 1
1 0 0 0 2
1 0 1 0 3
1 0 1 1 4
1 1 1 0 5
2 0 0 0 1
3 0 1 0 1
3 0 0 0 2
END DATA.
EXECUTE.

/* NOW LET'S RESTRUCTURE THE FILE */
SORT CASES BY ID ADMINORDER.
CASESTOVARS
  /ID=ID
  /INDEX=ADMINORDER
  /GROUPBY=INDEX.

/* AND THIS IS THE FILE RESTRUCTURED */
LIST.

Cheers,
Luca