BookmarkSubscribeRSS Feed
Question
Fluorite | Level 6

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

7 REPLIES 7
ballardw
Super User

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
Fluorite | Level 6

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
Fluorite | Level 6

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

Question
Fluorite | Level 6

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

Ron_MacroMaven
Lapis Lazuli | Level 10

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
PROC Star

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
Calcite | Level 5

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 1091 views
  • 0 likes
  • 6 in conversation