Transpose 4 columns into 2 rows

[jawon]

Of course this will be easier to explain with an example. This is what I'm starting with. The last 4 variables are numeric, representing quantity and revenue, each separated into 2 different groups...

Region | State | County | City | Qty_CB | Qty_VB | Revenue_CB | Revenue_VB
West | CA | LA | LA | 1 | 0 | $111 | $0
Midwest | IL | Cook | Chicago | 3 | 4 | $777 | $888

I want to transpose so that I end up with a new "Category" field that captures the 2 groups, and then have just one Qty field and one Revenue field...

Region | State | County | City | Category | Qty | Revenue
West | CA | LA | LA | CB | 1 | $111
West | CA | LA | LA | VB | 0 | $0
Midwest | IL | Cook | Chicago | CB | 3 | $777
Midwest | IL | Cook | Chicago | CB | 4 | $888

I think the prob I'm encountering is trying to transpose both Qty and Revenue fields, as opposed to just one. Any suggestions? Thanks much!

[LinusH]

By using a data step you will have full control of your output. No problem there I believe.

/Linus

[GertNissen]

Hi Jawon

I don't have access to SAS right now, so please test the follwing 🙂

data tworows(keep=Region State County City Category Qty Revenue);
set onerows(keep=Region State County City Qty_CB Qty_VB Revenue_CB Revenue_VB);
category = 'CB';
Qty = Qty_CB ;
Revenue = Revenue_CB ;
output;
category = 'VB';
Qty = Qty_VB ;
Revenue = Revenue_VB ;
output;
run;

[jawon]

Excellent. Thank you. I think I was overthinking this!

[R_Win]

good