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

How to convert By group rows in to different columns?

amanegm — Thu, 21 Dec 2017 13:48:09 GMT

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.

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

RW9 — Thu, 21 Dec 2017 14:15:23 GMT

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;

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

PeterClemmensen — Thu, 21 Dec 2017 14:29:08 GMT

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;

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

PeterClemmensen — Thu, 21 Dec 2017 14:32:02 GMT

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 🙂

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

ballardw — Thu, 21 Dec 2017 15:29:29 GMT

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.

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

amanegm — Fri, 22 Dec 2017 15:06:26 GMT

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

Thank you so much.