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!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.