BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
dpurcell99
Fluorite | Level 6

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

3 REPLIES 3
Tom
Super User Tom
Super User

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
Fluorite | Level 6

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

mkeintz
PROC Star

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.

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 7952 views
  • 6 likes
  • 3 in conversation