BookmarkSubscribeRSS Feed
Aninda_S
Calcite | Level 5

 I have a date value for each customer id.I want to create a bin for each date on 30 days rolling basis till 36 months.Example data in the output ,I have provided till 4 months

 

 For customer id 001 purchase date is 12AUG2014.I need to calculate a 30 days period from 12AUG2014 .That would be 11SEP2014 .So M1_Start would be 12AUG2014  and end would be 11SEP2014 .Now M2_start would be 1 day after M1_end which is 12SEP2014.Again M2_END would be 30 days after 12SEP2014 which is 12OCT2014.So on .I have to calculate till M36_start and M36_end.

 

Data I have  -.

 

cust_id           purchase_dt   

001                   12-Aug-14

002                   21-Aug-14

003                    2-Sep-14

004                    4-Aug-14 

005                    9-Aug-14

 

Data I want - 

 

cust_id   purchase_dt   M1_start      M1_end      M2_start     M2_end      M3_start      M3_end        M4_start      M4_end

001           12-Aug-14     12-Aug-14    11-Sep-14     12-Sep-14   12-Oct-14   13-Oct-14      12-Nov-14      13-Nov-14   13-Dec-14    
002          21-Aug-14     21-Aug-14    20-Sep-14    21-Sep-14   21-Oct-14    22-Oct-14    21-Nov-14      22-Nov-14   22-Dec-14 
003            2-Sep-14       2-Sep-14      2-Oct-14        3-Oct-14    2-Nov-14     3-Nov-14     3-Dec-14         4-Dec-14     3-Jan-15 
004            4-Aug-14      4-Aug-14       3-Sep-14      4-Sep-14    4-Oct-14      5-Oct-14     4-Nov-14           5-Nov-14     5-Dec-14 
005             9-Aug-14     9-Aug-14        8-Sep-14    9-Sep-14     9-Oct-14    10-Oct-14      9-Nov-14         10-Nov-14   10-Dec-14 

 

Thanks.Any leads will be very helpful.

1 REPLY 1
ballardw
Super User

First thing is to verify that you date variables actually contain SAS date values. Then adding 30 days is pretty trivial.

Here's one way to create date groups as indicated. Because I am way too lazy to write long lists of variables that are hard to use

I renamed them start_M1 to start_M36 to simplify the creation and format assignment statements. The use arrays to create the boundary values. The pairs are not adjacent in the data because of the creation. If you really want them paired then you'll have to write something with all 72 variables in the order you want them.

data want;
   informat cust_id $3. purchase_dt anydtdte.;
   input cust_id purchase_dt;
   format purchase_dt ;
   array start start_M1-start_M36 ;
   array end   end_M1 - end_M36 ;
   do i = 1 to 36;
      if i=1 then do;
         start[i]=purchase_dt;
      end;
      else do;
         start[i]=end[i-1]+1;
      end;
      end[i]  = start[i]+30;
   end;
   format start_M: end_M: date11.;
datalines;
001                   12-Aug-14
002                   21-Aug-14
003                    2-Sep-14
004                    4-Aug-14 
005                    9-Aug-14
;
run;

With that behind. I might suggest that you might do better with:

 

data want;
   informat cust_id $3. purchase_dt anydtdte.;
   input cust_id purchase_dt;
   format purchase_dt date11.;
   do Month = 1 to 36;
      if Month=1 then do;
         start=purchase_dt;
      end;
      else do;
         start=end+1;
      end;
      end  = start+30;
      output;
   end;
   format start end date11.;
datalines;
001                   12-Aug-14
002                   21-Aug-14
003                    2-Sep-14
004                    4-Aug-14 
005                    9-Aug-14
;
run;

For many purposes.

 

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
  • 1 reply
  • 1286 views
  • 0 likes
  • 2 in conversation