I have data that is set up like this:
Pers Year Month Variable Value
AAA 2001 01 Var1 100
AAA 2001 01 Var2 200
AAA 2001 06 Var1 110
AAA 2001 06 Var2 210
AAA 2002 01 Var1 120
AAA 2002 01 Var2 .
BBB 2001 01 Var1 100
BBB 2001 01 Var2 200
BBB 2001 06 Var1 110
BBB 2001 06 Var2 210
BBB 2002 01 Var2 220
I would like data that looks like this:
Pers Year Month Var1 Var2
AAA 2001 01 100 200
AAA 2001 06 110 210
AAA 2002 01 120 .
BBB 2001 01 100 200
BBB 2001 06 110 210BBB 2002 01 . 220
How can I do this in SAS?
Note that in the input data, above, Person BBB is missing an observation for 2002-01 Var1, but the output data has returned a missing value in the last line, i.e. ".".
Hi
You can use the TRANSPOSE procedure to do this. See example below:
Hi
You can use the TRANSPOSE procedure to do this. See example below:
Hi Andrew,
Alternatively the same output could be obtained by arrays.
data have;
input Pers$ Year Month Variable$ Value ;
ord=input(compress(variable,,'kd'),1.);
cards;
AAA 2001 01 Var1 100
AAA 2001 01 Var2 200
AAA 2001 06 Var1 110
AAA 2001 06 Var2 210
AAA 2002 01 Var1 120
AAA 2002 01 Var2 .
BBB 2001 01 Var1 100
BBB 2001 01 Var2 200
BBB 2001 06 Var1 110
BBB 2001 06 Var2 210
BBB 2002 01 Var2 220
;
proc sort data=have;
by pers year month variable;
run;
data want;
set have;
retain var1 var2;
by pers year month variable;
array vs(2) var1 var2;
if first.month then do;
do i = 1 to 2;
vs(i)=.;
end;
end;
vs(ord)=value;
if last.month;
run;
Hope it helps.
Thanks,
Jag
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.