## transpose long to wide - two ID variables, multiple measures

Solved
Occasional Contributor
Posts: 6

# 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:

 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!

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

## 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;``````

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

## 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.

Posts: 1,399

## 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.