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 |
Thanks in advance.
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;
Not downloading Excel files - post test data in the form of a datastep.
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;
Also, I agree with @RW9, it is not a good idea to have time structures in columns.
See maxim 19 of @Kurt_Bremsers great Maxims of Maximally Efficient SAS Programmers 🙂
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;
If the purpose of creating the variables is to create a readable table for a report then consider using a report procedure.
Please see this example:
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.
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.