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
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?
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*/
Can you please help us by providing structurised data...so that one can help you based on your requirement...
Sorry, I have attached a structured file but you didn't get the message. How do I attach a file?
RTFM on the intnx function
%let today = %sysfunc(today());
%let interval = day;
* calculations;
%let D_Begin =%sysfunc(intnx(&Interval,&today,-91,begin));
%let D_End =%sysfunc(intnx(&Interval,&today,0,end ));
%let format = weekdate29.;
* display;
%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.
see also:
http://www.sascommunity.org/wiki/Macro_Loops_with_Dates
btw there are 91 or 92 days in a quarter
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:
data want;
set have;
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;
run;
If the problem should be more complex than that, you'll have to describe what needs to be added.
Good luck.
You could try something like this:
proc sort data=transfile;
by customer last_trans_date;
data xxx;
set transfile;
retain hold_last;
hold_last=lag1(last_trans_date);
if last_trans_date gt hold_last+90 then
do;
etc............................................
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.