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!
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;
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;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With