This module illustrates how to reshape data
files in SPSS without using the casestovars command. These examples take long data files and reshape them into
wide form. These
show common examples of reshaping data but do not exhaustively demonstrate the
different kinds of data reshaping that you could encounter.
Example #1: One variable
Let’s use the kidslw
file for our first example.
get file 'c:kidslw.sav'.
list famid birth age.
FAMID BIRTH AGE
1.00 1.00 9.00
1.00 2.00 6.00
1.00 3.00 3.00
2.00 1.00 8.00
2.00 2.00 6.00
2.00 3.00 2.00
3.00 1.00 6.00
3.00 2.00 4.00
3.00 3.00 2.00
Number of cases read: 9 Number of cases listed: 9
Let’s make age in this file wide, making one record per family which would contain
age1, age2
and age3, the ages of the kids in the family
(age2 would be missing if there is only one kid, and age3 would be missing if there are only
two kids). As our first step, we will create a vector with three elements, calling
it age. Next, we will create a variable also called age and
insert the values from the vector. We then list the data in the file to
show how they have changed. In this and all of the following examples, we will
list the data at intermediate steps of the process of making the file
wide. This is useful in case the process does not go correctly, because
it is easier to see where the error occurred.
vector age(3).
compute age(birth) = age.
list id famid age age1 age2 age3.
ID FAMID AGE AGE1 AGE2 AGE3
1.00 1.00 9.00 9.00 . .
2.00 1.00 6.00 . 6.00 .
3.00 1.00 3.00 . . 3.00
4.00 2.00 8.00 8.00 . .
5.00 2.00 6.00 . 6.00 .
6.00 2.00 2.00 . . 2.00
7.00 3.00 6.00 6.00 . .
8.00 3.00 4.00 . 4.00 .
9.00 3.00 2.00 . . 2.00
Number of cases read: 9 Number of cases listed: 9
The next step is to aggregate the
data. We use the max function to capture the largest value in the
variables age1 age2 and age3. Note that while we use the
max
function, you could also use the min function. Both functions work
because SPSS does not consider a missing value to be either infinitely large
nor infinitely small (as SAS and Stata do). Note that the variables not
directly referenced in the aggregate command are dropped (i.e., all
variables aside from famid age1, age2 and age3 are
dropped).
aggregate outfile 'c:kids1.sav'
/break famid
/age1 to age3 = max(age1 to age3).
get file 'c:kids1.sav'.
list.
FAMID AGE1 AGE2 AGE3
1.00 9.00 6.00 3.00
2.00 8.00 6.00 2.00
3.00 6.00 4.00 2.00
Number of cases read: 3 Number of cases listed: 3
Example #2: Two variables
In the example above, we reshaped
only one variable, age. In the example below, we reshape the variables
age
and wt. Note that for each variable to be reshaped, you need to add a
vector
and a compute command. You also need to get the max (or min) of
the variable in the aggregate command. Hence, you can reshape as many
variables as needed.
get file 'c:kidslw.sav'.
list famid birth age.
ID FAMID BIRTH AGE
1.00 1.00 1.00 9.00
2.00 1.00 2.00 6.00
3.00 1.00 3.00 3.00
4.00 2.00 1.00 8.00
5.00 2.00 2.00 6.00
6.00 2.00 3.00 2.00
7.00 3.00 1.00 6.00
8.00 3.00 2.00 4.00
9.00 3.00 3.00 2.00
Number of cases read: 9 Number of cases listed: 9
vector age(3).
vector wt(3).
compute age(birth) = age.
compute wt(birth) = wt.
list.
The variables are listed in the following order:
LINE 1: ID FAMID KIDNAME BIRTH AGE WT SEX AGE1
LINE 2: AGE2 AGE3 WT1 WT2 WT3
ID: 1.00 1.00 Beth 1.00 9.00 60.00 f 9.00
AGE2: . . 60.00 . .
ID: 2.00 1.00 Bob 2.00 6.00 40.00 m .
AGE2: 6.00 . . 40.00 .
ID: 3.00 1.00 Barb 3.00 3.00 20.00 f .
AGE2: . 3.00 . . 20.00
ID: 4.00 2.00 Andy 1.00 8.00 80.00 m 8.00
AGE2: . . 80.00 . .
ID: 5.00 2.00 Al 2.00 6.00 50.00 m .
AGE2: 6.00 . . 50.00 .
ID: 6.00 2.00 Ann 3.00 2.00 20.00 f .
AGE2: . 2.00 . . 20.00
ID: 7.00 3.00 Pete 1.00 6.00 60.00 m 6.00
AGE2: . . 60.00 . .
ID: 8.00 3.00 Pam 2.00 4.00 40.00 f .
AGE2: 4.00 . . 40.00 .
ID: 9.00 3.00 Phil 3.00 2.00 20.00 m .
AGE2: . 2.00 . . 20.00
Number of cases read: 9 Number of cases listed: 9
aggregate outfile 'c:kids2.sav'
/break famid
/age1 to age3 = max(age1 to age3)
/wt1 to wt3 = max(wt1 to wt3).
get file 'c:kids2.sav'.
list.
FAMID AGE1 AGE2 AGE3 WT1 WT2 WT3
1.00 9.00 6.00 3.00 60.00 40.00 20.00
2.00 8.00 6.00 2.00 80.00 50.00 20.00
3.00 6.00 4.00 2.00 60.00 40.00 20.00
Number of cases read: 3 Number of cases listed: 3
Example #3: Both numeric and character variables
The examples above showed how to reshape data using numeric
variables, but sometimes you will need to reshape string variables as well. In
this example, we reshape two numeric variables, age and wt, and
two string variables, sex and kidname. We need to create
the two string variables before creating the vectors, and we use the string
command to do this. Note that while you can use the compute command to
create a numeric variable, you can only use the compute command to
modify a string variable.
Consider the kidslw
data file shown below.
get file 'c:kidslw.sav'.
list.
ID FAMID KIDNAME BIRTH AGE WT SEX
1.00 1.00 Beth 1.00 9.00 60.00 f
2.00 1.00 Bob 2.00 6.00 40.00 m
3.00 1.00 Barb 3.00 3.00 20.00 f
4.00 2.00 Andy 1.00 8.00 80.00 m
5.00 2.00 Al 2.00 6.00 50.00 m
6.00 2.00 Ann 3.00 2.00 20.00 f
7.00 3.00 Pete 1.00 6.00 60.00 m
8.00 3.00 Pam 2.00 4.00 40.00 f
9.00 3.00 Phil 3.00 2.00 20.00 m
Number of cases read: 9 Number of cases listed: 9
vector age(3).
vector wt(3).
string sex1 to sex3 (A4).
vector sex= sex1 to sex3.
string kidname1 to kidname3 (A4).
vector kidname= kidname1 to kidname3.
compute kidname(birth) = kidname.
compute wt(birth) = wt.
compute sex(birth) = sex.
compute age(birth) = age.
list.
The variables are listed in the following order:
LINE 1: ID FAMID KIDNAME BIRTH AGE WT SEX AGE1
LINE 2: AGE2 AGE3 WT1 WT2 WT3 SEX1 SEX2 SEX3 KIDNAME1 KIDNAME2
LINE 3: KIDNAME3
ID: 1.00 1.00 Beth 1.00 9.00 60.00 f 9.00
AGE2: . . 60.00 . . f Beth
KIDNAME3:
ID: 2.00 1.00 Bob 2.00 6.00 40.00 m .
AGE2: 6.00 . . 40.00 . m Bob
KIDNAME3:
ID: 3.00 1.00 Barb 3.00 3.00 20.00 f .
AGE2: . 3.00 . . 20.00 f
KIDNAME3: Barb
ID: 4.00 2.00 Andy 1.00 8.00 80.00 m 8.00
AGE2: . . 80.00 . . m Andy
KIDNAME3:
ID: 5.00 2.00 Al 2.00 6.00 50.00 m .
AGE2: 6.00 . . 50.00 . m Al
KIDNAME3:
ID: 6.00 2.00 Ann 3.00 2.00 20.00 f .
AGE2: . 2.00 . . 20.00 f
KIDNAME3: Ann
ID: 7.00 3.00 Pete 1.00 6.00 60.00 m 6.00
AGE2: . . 60.00 . . m Pete
KIDNAME3:
ID: 8.00 3.00 Pam 2.00 4.00 40.00 f .
AGE2: 4.00 . . 40.00 . f Pam
KIDNAME3:
ID: 9.00 3.00 Phil 3.00 2.00 20.00 m .
AGE2: . 2.00 . . 20.00 m
KIDNAME3: Phil
Number of cases read: 9 Number of cases listed: 9
aggregate outfile 'c:kids3.sav'
/break famid
/age1 to age3 = max(age1 to age3)
/wt1 to wt3 = max(wt1 to wt3)
/sex1 to sex3 = max(sex1 to sex3)
/kidname1 to kidname3 = max(kidname1 to kidname3).
get file 'c:kids3.sav'.
list.
The variables are listed in the following order:
LINE 1: FAMID AGE1 AGE2 AGE3 WT1 WT2 WT3 SEX1
LINE 2: SEX2 SEX3 KIDNAME1 KIDNAME2 KIDNAME3
FAMID: 1.00 9.00 6.00 3.00 60.00 40.00 20.00 f
SEX2: m f Beth Bob Barb
FAMID: 2.00 8.00 6.00 2.00 80.00 50.00 20.00 m
SEX2: m f Andy Al Ann
FAMID: 3.00 6.00 4.00 2.00 60.00 40.00 20.00 m
SEX2: f m Pete Pam Phil
Number of cases read: 3 Number of cases listed: 3
Example #4: Character suffixes
You cannot use character suffixes
in SPSS when reshaping data because you cannot aggregate the data to form the wide data file. Hence, in
this example, we use a numeric suffix while reshaping and rename the variables with the character
suffix as the last step.
get file 'c:dadmoml.sav'. list.ID FAMID NAME INC DADMOM 1.00 2.00 Art 22000.00 dad 2.00 1.00 Bill 30000.00 dad 3.00 3.00 Paul 25000.00 dad 4.00 1.00 Bess 15000.00 mom 5.00 3.00 Pat 50000.00 mom 6.00 2.00 Amy 18000.00 mom Number of cases read: 6 Number of cases listed: 6if dadmom = "mom" dadmomN = 1. if dadmom = "dad" dadmomN = 2. list.ID FAMID NAME INC DADMOM DADMOMN 1.00 2.00 Art 22000.00 dad 2.00 2.00 1.00 Bill 30000.00 dad 2.00 3.00 3.00 Paul 25000.00 dad 2.00 4.00 1.00 Bess 15000.00 mom 1.00 5.00 3.00 Pat 50000.00 mom 1.00 6.00 2.00 Amy 18000.00 mom 1.00 Number of cases read: 6 Number of cases listed: 6string name1 to name2 (A4). vector name = name1 to name2. compute name(dadmomN) = name. vector inc(2). compute inc(dadmomN) = inc. list.ID FAMID NAME INC DADMOM DADMOMN NAME1 NAME2 INC1 INC2 1.00 2.00 Art 22000.00 dad 2.00 Art . 22000.00 2.00 1.00 Bill 30000.00 dad 2.00 Bill . 30000.00 3.00 3.00 Paul 25000.00 dad 2.00 Paul . 25000.00 4.00 1.00 Bess 15000.00 mom 1.00 Bess 15000.00 . 5.00 3.00 Pat 50000.00 mom 1.00 Pat 50000.00 . 6.00 2.00 Amy 18000.00 mom 1.00 Amy 18000.00 . Number of cases read: 6 Number of cases listed: 6aggregate outfile 'c:kids4.sav' /break famid /inc1 to inc2 = max(inc1 to inc2) /name1 to name2 = max(name1 to name2). get file 'c:kids4.sav'. list.FAMID INC1 INC2 NAME1 NAME2 1.00 15000.00 30000.00 Bess Bill 2.00 18000.00 22000.00 Amy Art 3.00 50000.00 25000.00 Pat Paulrename variables (inc1 to inc2 = incmom incdad). rename variables (name1 to name2 = namemom namedad). list. Number of cases read: 3 Number of cases listed: 3FAMID INCMOM INCDAD NAMEMOM NAMEDAD 1.00 15000.00 30000.00 Bess Bill 2.00 18000.00 22000.00 Amy Art 3.00 50000.00 25000.00 Pat Paul Number of cases read: 3 Number of cases listed: 3
Example #5: No index variable
There may be times when your data are ordered
but an index variable is not present. In this situation, you will need to create an index variable before you can
reshape the data. This example illustrates how to create an index variable and then use that index variable in the
reshaping. We will use the data from Example 1. In that example, birth is the index variable. Hence, we will
create an index variable which will have the same values as the variable birth. We will call our index
variable index.
get file 'c:kidslw.sav'. list famid birth age.FAMID BIRTH AGE 1.00 1.00 9.00 1.00 2.00 6.00 1.00 3.00 3.00 2.00 1.00 8.00 2.00 2.00 6.00 2.00 3.00 2.00 3.00 1.00 6.00 3.00 2.00 4.00 3.00 3.00 2.00 Number of cases read: 9 Number of cases listed: 9compute index = 1. if famid = lag(famid) index = lag(index) + 1. vector age(3). compute age(index) = age. list id famid age age1 age2 age3 birth index.ID FAMID AGE AGE1 AGE2 AGE3 BIRTH INDEX 1.00 1.00 9.00 9.00 . . 1.00 1.00 2.00 1.00 6.00 . 6.00 . 2.00 2.00 3.00 1.00 3.00 . . 3.00 3.00 3.00 4.00 2.00 8.00 8.00 . . 1.00 1.00 5.00 2.00 6.00 . 6.00 . 2.00 2.00 6.00 2.00 2.00 . . 2.00 3.00 3.00 7.00 3.00 6.00 6.00 . . 1.00 1.00 8.00 3.00 4.00 . 4.00 . 2.00 2.00 9.00 3.00 2.00 . . 2.00 3.00 3.00 Number of cases read: 9 Number of cases listed: 9aggregate outfile 'c:kids5.sav' /break famid /age1 to age3 = max(age1 to age3). get file 'c:kids5.sav'. list.FAMID AGE1 AGE2 AGE3 1.00 9.00 6.00 3.00 2.00 8.00 6.00 2.00 3.00 6.00 4.00 2.00 Number of cases read: 3 Number of cases listed: 3