SPSS Frequently Asked Questions Reshaping a File from Long to Wide

This page is adapted from the SPSS AnswerNet.  We are grateful to SPSS for permission to use provide their information to adapt and distribute this page from our web site.

Q.
I currently have many records for each unique id in my data set. I would like to have a single case per id without losing information. That is, I would like to restructure my data such that a variable that currently has multiple values across records but within cases, is restructured into many variables with multiple values within a single case and on a single record.

A.
The following is a worked example restructuring one variable. Included in this syntax is output from LIST commands. This output shows you how the sample file is being restructured at each step. If you run the sample syntax, don’t include the output portions that are included here.

* To make one from many *.
DATA LIST FREE / ID Y.
BEGIN DATA
1 3
1 4
1 5
1 6
1 3
2 5
2 5
2 7
2 1
END DATA .
COMPUTE REC=1.
IF ID EQ LAG(ID) REC=LAG(REC)+1 .
LIST.

ID Y REC

1.00 3.00 1.00
1.00 4.00 2.00
1.00 5.00 3.00
1.00 6.00 4.00
1.00 3.00 5.00
2.00 5.00 1.00
2.00 5.00 2.00
2.00 7.00 3.00
2.00 1.00 4.00

VECTOR Y_(5).
COMPUTE Y_(REC)=Y.
LIST.

ID Y REC Y_1 Y_2 Y_3 Y_4 Y_5

1.00 3.00 1.00 3.00 . . . .
1.00 4.00 2.00 . 4.00 . . .
1.00 5.00 3.00 . . 5.00 . .
1.00 6.00 4.00 . . . 6.00 .
1.00 3.00 5.00 . . . . 3.00
2.00 5.00 1.00 5.00 . . . .
2.00 5.00 2.00 . 5.00 . . .
2.00 7.00 3.00 . . 7.00 . .
2.00 1.00 4.00 . . . 1.00 .

AGGREGATE OUTFILE *
/ BREAK ID
/ Y_1 TO Y_5 = MAX( Y_1 TO Y_5).
LIST.

ID Y_1 Y_2 Y_3 Y_4 Y_5

1.00 3.00 4.00 5.00 6.00 3.00
2.00 5.00 5.00 7.00 1.00 .