Q:

SAS: reshape data (stacking)

How do I reshape this data in SAS?
id  q1a q2a q1b q2b q1c q2c
1   3   0   1   1   1   9
2   4   9   1   2   2   0
3   5   9   1   2   4   0

into this:
id  q1  q2 type
1   3   0   a
1   1   1   b
1   1   9   c
.............

A:

The simplest way is to transpose the data, split out the last letter from the q variables, then re-transpose.
data have;
input id  q1a q2a q1b q2b q1c q2c;
datalines;
1   3   0   1   1   1   9
2   4   9   1   2   2   0
3   5   9   1   2   4   0
;
run;

proc transpose data=have out=temp1;
by id;
run;

data temp2;
set temp1;
length type $1;
type=substr(_NAME_,3);
_NAME_=substr(_NAME_,1,2);
run;

proc transpose data=temp2 out=want (drop=_:) ;
by id type;
id _NAME_;
var COL1;
run;

