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;
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!
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.
Ready to level-up your skills? Choose your own adventure.