How to convert By group rows in to different columns?

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 7
Accepted Solution

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:

 

AIDSIDBookingBK_CodeMonthOrderRevenue
1100 - 6 NightsD13149.97
11015 - 29 NightsB1763105.49
11015 - 29 NightsB3572627.22
11030+ NightsM100
1107 - 14 NightsW2442227.32
1130 - 6 NightsD37584.93
1137 - 14 NightsW3422593.08
1170 - 6 NightsD22194.73
1177 - 14 NightsW2271932.03
1187 - 14 NightsW312863.88
1187 - 14 NightsW14287.96
22030+ NightsM2171377
22030+ NightsM3302088
22515 - 29 NightsB2301367.7
22515 - 29 NightsB315683.85
2257 - 14 NightsW29410.31
2257 - 14 NightsW35227.95

 

Expected Result:

 

AIDSIDM1_D_OrderM2_D_OrderM3_D_OrderM1_W_OrderM2_W_OrderM3_W_OrderM1_B_OrderM2_B_OrderM3_B_OrderM1_M_OrderM2_M_OrderM3_M_Order
1103   44 76 570  
113 27  42      
117 2  27       
118   4 12      
220          1730
225    95 3015   

 

Thanks in advance.


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;

 

View solution in original post


All Replies
Super User
Super User
Posts: 9,441

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

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;
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 Smiley Happy

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.

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.

 

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 198 views
  • 0 likes
  • 4 in conversation