## How to convert By group rows in to different columns?

Solved
Occasional Contributor
Posts: 7

# How to convert By group rows in to different columns?

Hi.

I want to convert BY group rows into several columns based on Month values and Booking Type.

Based on AID and SID, I want to create different columns containing order quantity representing Month and BK_Code such as

M1_D_Order for Month '1' and BK_Code 'D'

M2_D_Order for Month '2' and BK_Code 'D'

M3_D_Order for Month '3' and BK_Code 'D'

Similarly for other 3 BK_Code - 'W', 'B' and 'M'. Here I have data for 3 months. Hence there should be 3 x 4 = 12 columns representing the order quantity in respective columns for Month and BK_Code. Later, I have to expand for 12 months (12*4 = 48 columns).

Subsequently, each row will be a unique combination of AID and SID.

My data looks like this:

 AID SID Booking BK_Code Month Order Revenue 1 10 0 - 6 Nights D 1 3 149.97 1 10 15 - 29 Nights B 1 76 3105.49 1 10 15 - 29 Nights B 3 57 2627.22 1 10 30+ Nights M 1 0 0 1 10 7 - 14 Nights W 2 44 2227.32 1 13 0 - 6 Nights D 3 7 584.93 1 13 7 - 14 Nights W 3 42 2593.08 1 17 0 - 6 Nights D 2 2 194.73 1 17 7 - 14 Nights W 2 27 1932.03 1 18 7 - 14 Nights W 3 12 863.88 1 18 7 - 14 Nights W 1 4 287.96 2 20 30+ Nights M 2 17 1377 2 20 30+ Nights M 3 30 2088 2 25 15 - 29 Nights B 2 30 1367.7 2 25 15 - 29 Nights B 3 15 683.85 2 25 7 - 14 Nights W 2 9 410.31 2 25 7 - 14 Nights W 3 5 227.95

Expected Result:

 AID SID M1_D_Order M2_D_Order M3_D_Order M1_W_Order M2_W_Order M3_W_Order M1_B_Order M2_B_Order M3_B_Order M1_M_Order M2_M_Order M3_M_Order 1 10 3 44 76 57 0 1 13 2 7 42 1 17 2 27 1 18 4 12 2 20 17 30 2 25 9 5 30 15

Accepted Solutions
Solution
‎12-21-2017 09:52 AM
PROC Star
Posts: 1,218

## Re: How to convert By group rows in to different columns?

This gives you almost what you want.

Though you have to reorder the columns in the 'want' data set to your liking as there is no logical order to them as I see it?

``````data have;
input AID SID Booking:\$20. BK_Code\$ Month Order Revenue;
infile datalines dlm=',' dsd;
NewVar=cats("M",put(month,best8.), "_", bk_code, "_Order");
datalines;
1,10,0 - 6 Nights,D,1,3,149.97
1,10,15 - 29 Nights,B,1,76,3105.49
1,10,15 - 29 Nights,B,3,57,2627.22
1,10,30+ Nights,M,1,0,0
1,10,7 - 14 Nights,W,2,44,2227.32
1,13,0 - 6 Nights,D,3,7,584.93
1,13,7 - 14 Nights,W,3,42,2593.08
1,17,0 - 6 Nights,D,2,2,194.73
1,17,7 - 14 Nights,W,2,27,1932.03
1,18,7 - 14 Nights,W,3,12,863.88
1,18,7 - 14 Nights,W,1,4,287.96
2,20,30+ Nights,M,2,17,1377
2,20,30+ Nights,M,3,30,2088
2,25,15 - 29 Nights,B,2,30,1367.7
2,25,15 - 29 Nights,B,3,15,683.85
2,25,7 - 14 Nights,W,2,9,410.31
2,25,7 - 14 Nights,W,3,5,227.95
;

proc sort data=have;
by aid sid NewVar;
run;

proc transpose data=have out=want(drop=_NAME_);
by aid sid;
var order;
id NewVar;
idlabel NewVar;
run;``````

All Replies
Super User
Posts: 9,441

## Re: How to convert By group rows in to different columns?

Second, its really not the best method to work with transposed data, work with the good normalised structure you have now, and only transpose in the event of an output.

At a guess, create one variable with month and bk_code concatenated, then do a proc transpose, e.g.;

```data inter;
set have;
nm=cats("M",put(month,best.),"_",bk_code,"_Order");
run;

proc sort data=inter;
by aid sid nm;
run;

proc transpose data=inter out=want;
by aid sid;
var revenue;
id nm;
idlabel nm;
run;```
PROC Star
Posts: 1,218

## Re: How to convert By group rows in to different columns?

Also, I agree with @RW9, it is not a good idea to have time structures in columns.

See maxim 19 of @KurtBremsers great Maxims of Maximally Efficient SAS Programmers

Solution
‎12-21-2017 09:52 AM
PROC Star
Posts: 1,218

## Re: How to convert By group rows in to different columns?

This gives you almost what you want.

Though you have to reorder the columns in the 'want' data set to your liking as there is no logical order to them as I see it?

``````data have;
input AID SID Booking:\$20. BK_Code\$ Month Order Revenue;
infile datalines dlm=',' dsd;
NewVar=cats("M",put(month,best8.), "_", bk_code, "_Order");
datalines;
1,10,0 - 6 Nights,D,1,3,149.97
1,10,15 - 29 Nights,B,1,76,3105.49
1,10,15 - 29 Nights,B,3,57,2627.22
1,10,30+ Nights,M,1,0,0
1,10,7 - 14 Nights,W,2,44,2227.32
1,13,0 - 6 Nights,D,3,7,584.93
1,13,7 - 14 Nights,W,3,42,2593.08
1,17,0 - 6 Nights,D,2,2,194.73
1,17,7 - 14 Nights,W,2,27,1932.03
1,18,7 - 14 Nights,W,3,12,863.88
1,18,7 - 14 Nights,W,1,4,287.96
2,20,30+ Nights,M,2,17,1377
2,20,30+ Nights,M,3,30,2088
2,25,15 - 29 Nights,B,2,30,1367.7
2,25,15 - 29 Nights,B,3,15,683.85
2,25,7 - 14 Nights,W,2,9,410.31
2,25,7 - 14 Nights,W,3,5,227.95
;

proc sort data=have;
by aid sid NewVar;
run;

proc transpose data=have out=want(drop=_NAME_);
by aid sid;
var order;
id NewVar;
idlabel NewVar;
run;``````

Super User
Posts: 13,358

## Re: How to convert By group rows in to different columns?

If the purpose of creating the variables is to create a readable table for a report then consider using a report procedure.

```proc tabulate data=have;
class aid sid month bk_code;
var order;
table aid*sid,
bk_code*month*order*sum=' '
;
run;```

Assigning meaningful labels and such improves readability.

Occasional Contributor
Posts: 7

## Re: How to convert By group rows in to different columns?

Yes, I want to create this table for a report. This looks very clean and easier to understand.

Thank you so much.
☑ This topic is solved.