BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
amanegm
Fluorite | Level 6

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.

1 ACCEPTED SOLUTION

Accepted Solutions
PeterClemmensen
Tourmaline | Level 20

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

5 REPLIES 5
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;
PeterClemmensen
Tourmaline | Level 20

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 🙂

PeterClemmensen
Tourmaline | Level 20

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;

 

ballardw
Super User

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.

 

amanegm
Fluorite | Level 6
Yes, I want to create this table for a report. This looks very clean and easier to understand.

Thank you so much.

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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