How to create bins for date?

[Aninda_S]

 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.

[ballardw]

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.