BookmarkSubscribeRSS Feed
rakul
Fluorite | Level 6

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-2020Aug-2020Jul-2020Jun-2020May-2020Apr-2020Mar-2020Feb-2020Jan-2020Dec-2019Nov-2019Oct-2019            
rep_idstatezip_cdsale1sale2sale3sale4sale5sale6sale7sale8sale9sale10sale11sale12new_sales1new_sales2new_sales3new_sales4new_sales5new_sales6new_sales7new_sales8new_sales9new_sales10new_sales11new_sales12
1234576pa29878111111111111100010001001
1910876ll10974111141100030100010100010
1234098lp12345000000118191000000001001
1234768lo12345000300000060000100000010

 

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.

3 REPLIES 3
PaigeMiller
Diamond | Level 26

@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;

 

 

--
Paige Miller
Kurt_Bremser
Super User

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.

RichardDeVen
Barite | Level 11

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;

 

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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
  • 3 replies
  • 592 views
  • 1 like
  • 4 in conversation