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;
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.