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,876

## 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: 183

## Re: Transpose 4 columns into 2 rows

Hi Jawon

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
Discussion stats
• 4 replies
• 158 views
• 0 likes
• 4 in conversation