Hi,
I want to to transpose the data with grouping in categories and new names like this,
From...
ID A1 A2 B1 B2 C1 C2
1 10 20 25 10 8 30
2 20 30 35 8 10 25
To be ....
ID Categories Var1 Var2
1 A 10 20
1 B 25 10
1 C 8 30
2 A 20 30
2 B 35 8
2 C 10 25
Thanks in advance!
sort of a brute force method:
data have;
input ID A1 A2 B1 B2 C1 C2;
cards;
1 10 20 25 10 8 30
2 20 30 35 8 10 25
run;
data want (keep=id category var1 var2);
set have;
category='A';
var1=a1;
var2=a2;
output;
category='B';
var1=b1;
var2=b2;
output;
category='C';
var1=c1;
var2=c2;
output;
run;
sort of a brute force method:
data have;
input ID A1 A2 B1 B2 C1 C2;
cards;
1 10 20 25 10 8 30
2 20 30 35 8 10 25
run;
data want (keep=id category var1 var2);
set have;
category='A';
var1=a1;
var2=a2;
output;
category='B';
var1=b1;
var2=b2;
output;
category='C';
var1=c1;
var2=c2;
output;
run;
Use proc transpose and in the results your variable named id in your dataset will be _NAME_ . Then substring the first character so you're left with A B C and sort as you please.
proc transpose
by id;
var A1 A2 B1 B2 C1 C2;
: I think the following will provide what you want:
data have;
input ID A1 A2 B1 B2 C1 C2;
cards;
1 10 20 25 10 8 30
2 20 30 35 8 10 25
;
proc transpose data=have out=tall;
by id;
run;
data tall;
set tall;
category=compress(_name_,,'kf');
_name_=compress(_name_,,'kd');
run;
proc transpose data=tall out=want (drop=_:) prefix=var;
by id category;
id _name_ ;
run;
Thank you so much guys!
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 the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.