transpose long to wide - two ID variables, multiple measures

[dpurcell99]

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:

group	intervention	m1	m2	m3	m4
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

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

group	int1_m1	int1_m2	int1_m3	int1_m4	int2_m1	in2_m2
AAA	99	98	97	96	89	88	and so on…
BBB

Any help?

Thanks!

[Tom · Accepted solution]

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;

[dpurcell99]

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

[mkeintz]

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.