07-25-2017 04:23 PM
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 -.
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.
07-25-2017 06:11 PM
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.