DATA Step, Macro, Functions and more

transpose long to wide - two ID variables, multiple measures

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 6
Accepted Solution

transpose long to wide - two ID variables, multiple measures

[ Edited ]

Hi all,

 

I've searched through the forums and found many long-to-wide posts, but I can't seem to find a solution for this. I'm up against a time crunch, so I'll appreciate any help you can provide.

 

I have 40 groups that are each doing 5 interventions. Each group/intervention combination has four measures. So the data looks like this:

groupinterventionm1m2m3m4
AAAint199989796
AAAint289888786
AAAint399999999
BBBint199999999
BBBint299999999
BBBint399999999

 


What I want is to have each group with their intervention/measure combos in one row, like this: 

groupint1_m1int1_m2int1_m3int1_m4int2_m1in2_m2 
AAA999897968988and so on…
BBB       

 

Any help?

 

Thanks!


Accepted Solutions
Solution
‎11-04-2016 02:41 PM
Super User
Super User
Posts: 6,502

Re: transpose long to wide - two ID variables, multiple measures

You could just use PROC TRANSPOSE twice.

 

data have ;
  input group $ intervention $ m1 m2 m3 m4 ;
cards;
AAA int1 99 98 97 96
AAA int2 89 88 87 86
AAA int3 99 99 99 99
BBB int1 99 99 99 99
BBB int2 99 99 99 99
BBB int3 99 99 99 99
;

proc transpose data=have out=middle;
  by group intervention ;
  var m1-m4 ;
run;

proc transpose data=middle out=want delim=_;
  by group ;
  id intervention _name_ ;
  var col1;
run;

View solution in original post


All Replies
Solution
‎11-04-2016 02:41 PM
Super User
Super User
Posts: 6,502

Re: transpose long to wide - two ID variables, multiple measures

You could just use PROC TRANSPOSE twice.

 

data have ;
  input group $ intervention $ m1 m2 m3 m4 ;
cards;
AAA int1 99 98 97 96
AAA int2 89 88 87 86
AAA int3 99 99 99 99
BBB int1 99 99 99 99
BBB int2 99 99 99 99
BBB int3 99 99 99 99
;

proc transpose data=have out=middle;
  by group intervention ;
  var m1-m4 ;
run;

proc transpose data=middle out=want delim=_;
  by group ;
  id intervention _name_ ;
  var col1;
run;
Occasional Contributor
Posts: 6

Re: transpose long to wide - two ID variables, multiple measures

Tom and Mkeintz -- thanks much for your quick replies. Tom, your suggestion worked perfectly -- thanks! Mk, I'll try yours laster as well. 

Valued Guide
Posts: 797

Re: transpose long to wide - two ID variables, multiple measures

[ Edited ]

Here is a single step solution:

 

data want;
   merge have (where=(intervention="int1") rename=(m1=int1_m1 m2=int1_m2 m3=int1_m3 m4=int1_m4))
         have (where=(intervention="int2") rename=(m1=int2_m1 m2=int2_m2 m3=int2_m3 m4=int2_m4))
         have (where=(intervention="int3") rename=(m1=int3_m1 m2=int3_m2 m3=int3_m3 m4=int3_m4))
         have (where=(intervention="int4") rename=(m1=int4_m1 m2=int4_m2 m3=int4_m3 m4=int4_m4));
   by group;
   drop intervention;
run;

 

 

Corrected typo and missing parens.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 201 views
  • 2 likes
  • 3 in conversation