topic Re: Please Help : How to retrieve the transaction date before the last transaction date? Thanks in SAS Programming

Please Help : How to retrieve the transaction date before the last transaction date? Thanks

Question — Wed, 24 Apr 2013 15:10:54 GMT

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

Re: Please Help : How to retrieve the transaction date before the last transaction date? Thanks

ballardw — Wed, 24 Apr 2013 15:42:36 GMT

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?

Re: Please Help : How to retrieve the transaction date before the last transaction date? Thanks

Ron_MacroMaven — Wed, 24 Apr 2013 15:43:17 GMT

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.

Re: Please Help : How to retrieve the transaction date before the last transaction date? Thanks

Question — Wed, 24 Apr 2013 16:21:58 GMT

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*/

Re: Please Help : How to retrieve the transaction date before the last transaction date? Thanks

Astounding — Wed, 24 Apr 2013 16:59:16 GMT

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.

Re: Please Help : How to retrieve the transaction date before the last transaction date? Thanks

UrvishShah — Thu, 25 Apr 2013 11:43:24 GMT

Can you please help us by providing structurised data...so that one can help you based on your requirement...

Re: Please Help : How to retrieve the transaction date before the last transaction date? Thanks

Question — Thu, 25 Apr 2013 12:12:45 GMT

Sorry, I have attached a structured file but you didn't get the message. How do I attach a file?

Re: Please Help : How to retrieve the transaction date before the last transaction date? Thanks

ChrisSelley — Thu, 25 Apr 2013 13:46:22 GMT

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............................................