04-24-2013 11:10 AM
Hi, We have contacted customers who stopped transacting in the last 3 months. For example this customer below stopped transaction on 09/03/2012 and we called him on 31 May 2012 and he reactivated on 25/06/2012. Basically I would like to identify all customers who have stopped transacting for 3 months and reactivated because of the call they have received. I would like to follow this logic : IF LAST_TRANS_DATE (in this case 09/03/2012) =90 days 000001 23/07/2012 25/06/2012 Activation Date
04-24-2013 11:42 AM
We need an example of a few records of what your data set looks like, dummy example data is fine. Also are the dates SAS date values or character?
04-24-2013 12:21 PM
Sorry I thought I have attached the table ...The dates are SAS dates..Please see below an example of data.Please let me know if you can see it. CUST_ACCOUNT_ID CALL_DATE DELIVERY_DATE 000001 26/01/2012 10/01/2012 000001 16/05/2012 03/02/2012 000001 31/05/2012 10/02/2012 000001 23/07/2012 17/02/2012 000001 23/07/2012 24/02/2012 000001 23/07/2012 09/03/2012 /*This is the last transaction*/ 000001 23/07/2012 25/06/2012 /*This is the activation date*/
04-25-2013 07:43 AM
Can you please help us by providing structurised data...so that one can help you based on your requirement...
04-25-2013 08:12 AM
Sorry, I have attached a structured file but you didn't get the message. How do I attach a file?
04-24-2013 11:43 AM
RTFM on the intnx function
%let today = %sysfunc(today());
%let interval = day;
%let D_Begin =%sysfunc(intnx(&Interval,&today,-91,begin));
%let D_End =%sysfunc(intnx(&Interval,&today,0,end ));
%let format = weekdate29.;
%put today: &today %sysfunc(putn(&today,worddate18.));
%put today: &today %sysfunc(putn(&today,&format));
%put D_Begin : &D_Begin %sysfunc(putn(&D_Begin ,&format));
%put D_End : &D_End %sysfunc(putn(&D_End ,&format));
IF LAST_TRANS_DATE gt &D_Begin then
note: Last_Trans_Date must be numeric and a date, not a datetime.
btw there are 91 or 92 days in a quarter
04-24-2013 12:59 PM
Well, there are definitely a few holes here. Should the call date come into play? If so, how? Are you guaranteed that the activation date is the final date for each customer?
So making a few assumptions about those topics, let's simplify the problem to this. Find any customers where their last DELIVERY_DATE is more than 90 days from their prior DELIVERY_DATE. That's relatively easy:
by cust_account_id delivery_date;
days_since = diff(delivery_date);
if last.cust_account_id=1 and first.cust_account_id=0 and days_since > 90;
If the problem should be more complex than that, you'll have to describe what needs to be added.
04-25-2013 09:46 AM
You could try something like this:
proc sort data=transfile;
by customer last_trans_date;
if last_trans_date gt hold_last+90 then