Transpose Data in SAS

Things under legendu.net/outdated are outdated technologies that the author does not plan to update any more. Please look for better alternatives.

You can use the transpose procedure to tranpose a fat/thin data to a thin/fat data in SAS. For example, suppose we have a data set s1 in SAS.

data s1; 
    input famid faminc96 faminc97 faminc98 spend96 spend97 spend98 ; 
    datalines; 
1 40000 40500 41000 38000 39000 40000 
2 45000 45400 45800 42000 43000 44000 
3 75000 76000 77000 70000 71000 72000 
; 
run;

You can transpose the fat data set to a thin one using the transpose procedure.

proc transpose data=s1 out=s2 (rename=(_name_=year)) prefix=x;
    by famid;
    var faminc96-faminc98;
run;

This gives you the following data set.

famid	year	x1
1	faminc96	40000
1	faminc97	40500
1	faminc98	41000
2	faminc96	45000
2	faminc97	45400
2	faminc98	45800
3	faminc96	75000
3	faminc97	76000
3	faminc98	77000

You can transpose this thin data back to a fat one.

proc transpose data=s2 out=s3 (drop=_name_);
    by famid ;
	id year;
run;

The following are a few tips.

The id statement specifies one or more variables in the input data set whose formatted values name the transposed variables in the output data set.
The var statement specified variables in the input data set to be transposed.
The by statement defines groups and the transpose will be done in each by group.