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

[Question]

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

[ballardw]

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?

[Question]

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

[UrvishShah]

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

[Question]

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

[Ron_MacroMaven]

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

[Astounding]

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.

[ChrisSelley]

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