Hi all,
I have a dataset in the following format:
ID var1 var2 cat 1 1 1 A 2 0 1 B 1 0 0 A 2 1 1 B
I want the dataset in this format:
ID A_var1 A_var2 B_var1 B_var2 1 1 1 0 1 2 0 0 1 1
i.e I want to transpose data based on categorical variable.
I tried to do this using proc transpose but couldn't figure it out so I used the following code to get what I want:
data data_A;
set data;
where cat = "A";
rename var1 = A_var1 var2 = A_var2;
run;
data data_B;
set data;
where cat = "B";
rename var1 = B_var1 var2 = B_var2;
run;
data data_merged;
merge data_A data_B;
by id;
run;
Is there a way to do this using proc transpose or another more efficient method?
HI @RoddyJ Yes you can using Proc transpose but you need a double transpose like
data have;
input ID var1 var2 cat $;
cards;
1 1 1 A
2 0 1 B
3 0 0 A
4 1 1 B
;
proc transpose data=have out=temp;
by id cat;
var var1 var2;
run;
proc transpose data=temp out=want(drop=_name_) delimiter=_;
by id ;
var col1;
id cat _name_;
run;
HI @RoddyJ Yes you can using Proc transpose but you need a double transpose like
data have;
input ID var1 var2 cat $;
cards;
1 1 1 A
2 0 1 B
3 0 0 A
4 1 1 B
;
proc transpose data=have out=temp;
by id cat;
var var1 var2;
run;
proc transpose data=temp out=want(drop=_name_) delimiter=_;
by id ;
var col1;
id cat _name_;
run;
It can be done with a single datastep:
data want;
merge
have(where=(cat='A') rename=(var1=A_var1 var2=A_var2))
have(where=(cat='B') rename=(var1=B_var1 var2=B_var2))
;
by id;
run;
But I do not think you can get the exact variable names that you want with PROC TRANSPOSE directly.
@RoddyJ : I'm confused! Was your example wrong? You had four records with IDs 1, 2, 3 and 4, but only want to end up with IDs 1 and 2.
Art, CEO, AnalystFinder.com
@RoddyJ : You haven't answered my question, so I will respond regarding both scenarios (i.e., if your example was correct and if it wasn't).
Both scenarios can be solved by using the %transpose macro.
In the case where your example was incorrect, the following would provide the desired result:
data have;
input ID var1 var2 cat $;
cards;
1 1 1 A
1 0 1 B
2 0 0 A
2 1 1 B
;
run;
filename tr url 'https://raw.githubusercontent.com/art297/transpose/master/transpose.sas';
%include tr ;
%transpose(data=have, out=want, by=ID, id=cat,
var=var1 var2, delimiter=_, var_first=no)
Conversely, if your example was correct, then you could use the following data step, then application of the %transpose macro:
data have;
input ID var1 var2 cat $;
cards;
1 1 1 A
2 0 1 B
3 0 0 A
4 1 1 B
;
run;
data need;
set have;
id=round(id/2);
run;
filename tr url 'https://raw.githubusercontent.com/art297/transpose/master/transpose.sas';
%include tr ;
%transpose(data=need, out=want, by=ID, id=cat,
var=var1 var2, delimiter=_, var_first=no)
Hope that helps,
Art, CEO, AnalystFinder.com
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.