Can this be done using proc transpose?

[RoddyJ]

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? 

[novinosrin · Accepted solution]

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;

[RoddyJ]

Thank you!

[s_lassen]

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]

Thank you very much,
I made the response using proc transpose the solution because it answered my question, but I prefer this solution. Very concise and very understandable.

[art297]

@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

[art297]

@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

[RoddyJ]

Yes I made a mistake in my original post, now amended. Thank you for pointing that out and for your solution!