DATA Step, Macro, Functions and more

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

Reply
Frequent Contributor
Posts: 96

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

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

Super User
Posts: 10,538

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

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?

Frequent Contributor
Posts: 96

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

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

Regular Contributor
Posts: 195

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

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

Frequent Contributor
Posts: 96

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

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

Regular Contributor
Posts: 200

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

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

Super User
Posts: 5,093

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

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.

Occasional Contributor
Posts: 15

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

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

Ask a Question
Discussion stats
  • 7 replies
  • 306 views
  • 0 likes
  • 6 in conversation