Help using Base SAS procedures

Transpose 4 columns into 2 rows

Reply
Contributor
Posts: 20

Transpose 4 columns into 2 rows

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!
Super User
Posts: 5,255

Re: Transpose 4 columns into 2 rows

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

/Linus
Data never sleeps
SAS Employee
Posts: 160

Re: Transpose 4 columns into 2 rows

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;
Contributor
Posts: 20

Re: Transpose 4 columns into 2 rows

Excellent. Thank you. I think I was overthinking this!
Regular Contributor
Posts: 229

Re: Transpose 4 columns into 2 rows

good
Ask a Question
Discussion stats
  • 4 replies
  • 106 views
  • 0 likes
  • 4 in conversation