stacking data in SAS

Question

I am trying to rearrange my data but am having a difficult time. The data I have looks something like this:

date    a    b   c
====================
1996    5    7   8
1997    4    2   3    
1998    1    9   6

what I want is to rearrange the data (presumably using arrays) to get this:

date  val var
=============
1996  5   a
1997  4   a
1998  1   a
1996  7   b
1997  2   b
1998  9   b
1996  8   c
1997  3   c
1997  6   c

So that I've essentially stacked the variables (a,b,c) along with the corresponding date and name of the variable.

Thanks in advance!

score 6 · Answer 1 · answered Apr 21 '15 at 05:03

Use PROC TRANSPOSE to pivot the data.

First sort by DATE

proc sort data=have;
by date;
run;

Then use transpose

proc transpose data=have out=want(rename=(COL1=VAL _NAME_=VAR));
by date;
var a b c;
run;

Finally, it looks like you want this sorted by VAR, and then DATE

proc sort data=want;
by VAR date;
run;

score 2 · Answer 2 · answered Apr 21 '15 at 10:30

Since you mention arrays, here's how you would achieve the result using them.
I would, however, use the proc transpose method in the answer from @DomPazz as procedures are generally easier to read and understand by others who may need to look at the code

/* create initial dataset */
data have;
input date a b c;
datalines;
1996    5    7   8
1997    4    2   3
1998    1    9   6
;
run;

/* transpose data */
data want;
set have;
array vars{*} a b c; /* create array of required values */
length val 8 var $8; /* set lengths of new variables */
do i = 1 to dim(vars); /* loop through each element of the array */
    val = vars{i}; /* set val to be current array value */
    var = vname(vars{i}); /* set var to be name of current array variable name */
    drop a b c i; /* drop variables not required */
    output; /* output each value to a new row */
end;
run;

/* sort data in required order */
proc sort data=want;
by var date;
run;

stacking data in SAS

2 Answers2