topic Re: Do Loop query in SAS Programming

Do Loop query

rakul — Sat, 05 Sep 2020 10:54:46 GMT

I have a dataset as below.

data geo;
input 
rep_id$ state$ zip_cd$ sale1 sale2 sale3 sale4 sale5 sale6 sale7 sale8 sale9 sale10 sale11 sale12;
cards;
1234576 pa 29878 1 1 1 1 1 1 1 1 1 1 1 1
1910876 ll 10974 1 1 1 1 4 1 1 0 0 0 3 0
1234098 lp 12345 0 0 0 0 0 0 1 1 8 1 9 1
1234768 lo 12345 0 0 0 3 0 0 0 0 0 0 6 0
;
run;

Sale1 corresponds to Sep-2020 and sale2 is Aug-202 and so on.

Now these 12 months are split into 3 trimesters (4 months each) Jan-Apr, May-Aug, Sep-Dec

I want to create 12 new columns with new sales in each month (this should be trimester based).

Output should be like below. Please do the needful.

			Sep-2020	Aug-2020	Jul-2020	Jun-2020	May-2020	Apr-2020	Mar-2020	Feb-2020	Jan-2020	Dec-2019	Nov-2019	Oct-2019
rep_id	state	zip_cd	sale1	sale2	sale3	sale4	sale5	sale6	sale7	sale8	sale9	sale10	sale11	sale12	new_sales1	new_sales2	new_sales3	new_sales4	new_sales5	new_sales6	new_sales7	new_sales8	new_sales9	new_sales10	new_sales11	new_sales12
1234576	pa	29878	1	1	1	1	1	1	1	1	1	1	1	1	1	0	0	0	1	0	0	0	1	0	0	1
1910876	ll	10974	1	1	1	1	4	1	1	0	0	0	3	0	1	0	0	0	1	0	1	0	0	0	1	0
1234098	lp	12345	0	0	0	0	0	0	1	1	8	1	9	1	0	0	0	0	0	0	0	0	1	0	0	1
1234768	lo	12345	0	0	0	3	0	0	0	0	0	0	6	0	0	0	0	1	0	0	0	0	0	0	1	0

In may we can see sales from 2 reps so new sales in may is 2 but in June there is another sale from new rep so for that it'll b 1 and rest all should be 0 and in july and august there are no sales from new reps so it is all 0.

Re: Do Loop query

PaigeMiller — Sat, 05 Sep 2020 11:52:44 GMT

@rakul wrote:

I have a dataset as below.
data geo;
input 
rep_id$ state$ zip_cd$ sale1 sale2 sale3 sale4 sale5 sale6 sale7 sale8 sale9 sale10 sale11 sale12;
cards;
1234576 pa 29878 1 1 1 1 1 1 1 1 1 1 1 1
1910876 ll 10974 1 1 1 1 4 1 1 0 0 0 3 0
1234098 lp 12345 0 0 0 0 0 0 1 1 8 1 9 1
1234768 lo 12345 0 0 0 3 0 0 0 0 0 0 6 0
;
run;
Sale1 corresponds to Sep-2020 and sale2 is Aug-202 and so on.

Now these 12 months are split into 3 trimesters (4 months each) Jan-Apr, May-Aug, Sep-Dec

I want to create 12 new columns with new sales in each month (this should be trimester based).

Output should be like below. Please do the needful.

Sep-2020 Aug-2020 Jul-2020 Jun-2020 May-2020 Apr-2020 Mar-2020 Feb-2020 Jan-2020 Dec-2019 Nov-2019 Oct-2019

rep_id state zip_cd sale1 sale2 sale3 sale4 sale5 sale6 sale7 sale8 sale9 sale10 sale11 sale12 new_sales1 new_sales2 new_sales3 new_sales4 new_sales5 new_sales6 new_sales7 new_sales8 new_sales9 new_sales10 new_sales11 new_sales12

1234576 pa 29878 1 1 1 1 1 1 1 1 1 1 1 1 1 0 0 0 1 0 0 0 1 0 0 1

1910876 ll 10974 1 1 1 1 4 1 1 0 0 0 3 0 1 0 0 0 1 0 1 0 0 0 1 0

1234098 lp 12345 0 0 0 0 0 0 1 1 8 1 9 1 0 0 0 0 0 0 0 0 1 0 0 1

1234768 lo 12345 0 0 0 3 0 0 0 0 0 0 6 0 0 0 0 1 0 0 0 0 0 0 1 0

In may we can see sales from 2 reps so new sales in may is 2 but in June there is another sale from new rep so for that it'll b 1 and rest all should be 0 and in july and august there are no sales from new reps so it is all 0.

I really don't understand this about new sales. Please explain further.

Also, if you want output by trimesters, show us the desired output.

I will say that you would be much better off if you worked with a long data set rather than a wide data set. If you are creating this data set, it should be long, not wide, then no DO loops are needed. If you received it this way, then converting it to long with PROC TRANSPOSE will make everything a lot easier. I would not even bother to try to solve this problem with a wide data set.

Here is code that turns this into a long data set. Then figuring out tri-mesters is pretty simple, and probably the figuring out of new sales is much easier too, if only I understood what you mean.

proc transpose data=geo out=geo_t;
    by notsorted rep_id state zip_cd;
    var sale1-sale12;
run;
data geo_t1;
   set geo_t;
   nummonth=input(substr(_name_,5),2.);
   month=intnx('month','01SEP2020'd,-nummonth+1,'b');
   format month monyy.;
   drop nummonth _name_;
run;

Re: Do Loop query

Kurt_Bremser — Sat, 05 Sep 2020 12:17:18 GMT

The first thing you do is to transpose the wide dataset to a long one, keeping the rep_id and sales. In a second step, use the _NAME_ variable to create the dates.

From your original dataset, split off rep_id, state and zip_cd. You now have a dimension table and a fact table. Once this is done, all calculations become a piece of cake.

Re: Do Loop query

RichardDeVen — Sat, 05 Sep 2020 13:49:37 GMT

Should I guess you are not a parent ?

The term for a four month period is quadrimester. As for trimester the dictionary says "a period of three months, especially as a division of the duration of pregnancy."

You will need an array, an indexed loop and a position in quadrimester calculation based on possibly shifting month meanings of 1 through 12. Because the array index 1 to 12 associate to descending months the computation will involve ` - index`

Presuming the business logic apparently deals with 'new sales' concept as follows:

sales in current month are higher than sales in prior month, or
some sales occur at the start of the quadrimester
some sales occur at the earliest month for which data is available

Sample code:

data geo;
input 
rep_id$ state$ zip_cd$ sale1 sale2 sale3 sale4 sale5 sale6 sale7 sale8 sale9 sale10 sale11 sale12;
cards;
1234576 pa 29878 1 1 1 1 1 1 1 1 1 1 1 1
1910876 ll 10974 1 1 1 1 4 1 1 0 0 0 3 0
1234098 lp 12345 0 0 0 0 0 0 1 1 8 1 9 1
1234768 lo 12345 0 0 0 3 0 0 0 0 0 0 6 0
;
run;

data want;
  set geo;
  array sales sale1-sale12;
  array flags new_sales1-new_sales12;
  
  retain month_of_index1 9; * month 9 is September, sale1 contains September data;

  do index = 1 to 12;

    month_sequence_in_quadrimester = mod(12 - index + month_of_index1, 4);

    flags(index) = 0;

    if month_sequence_in_quadrimester = 0 or index = hbound(sales) then do;
      if sales(index) > 0 then 
        flags(index) = 1;
    end;
    else
    if month_sequence_in_quadrimester > 0 and sales(index) > sales(index+1) then 
      flags(index) = 1;

  end;

  format sale: new: 4.;
run;