BookmarkSubscribeRSS Feed
ernie86
Calcite | Level 5

I have problem transposing to long to wide

 

Here is what i have:

COL1 Plan target_code Name Question_ID Parent Question Child Question
Chicago Plan A 1234 p1 Q001 address city
IL Plan A 1234 p1 Q002 address state
Red Plan A 1234 p1 Q003 Program Color
2009 Plan A 1234 p1 Q004 Program Start Year
New York Plan A 1221 p2 Q001 address city
NY Plan A 1221 p2 Q002 address state
Blue Plan A 1221 p2 Q003 Program Color
2009 Plan A 1221 p2 Q004 Program Start Year

 

Here is what i want:

      address address Program Program
      city state Color Start Year
Plan target_code Name Q001 Q002 Q003 Q004
Plan A 1234 p1 Chicago IL Red 2009
Plan A 1221 p2 New York NY Blue 2009

 

 

I have this code here, but the output is not what i want. It is combining Question_ID, 'Parent Question'n, and 'Child Question'n all into one column. 

 

proc transpose data=prg_final2 out= final_report (drop=_NAME_);
by Plan target_code Name;
id Question_ID 'Parent Question'n 'Child Question'n;
var COL1;
run;

 

 

1 REPLY 1
pau13rown
Lapis Lazuli | Level 10

just edit id statement:

 

proc transpose data=prg_final2 out= final_report (drop=_NAME_) ;
by Plan target_code Name;
id Question_ID;
var COL1;
run;

 

and then:

 

data final_report2;

set final_report;

label Q001 ='address city' Q002='address state' .... etc;

run;

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 1 reply
  • 1404 views
  • 0 likes
  • 2 in conversation