topic Re: Flag variable in SAS Programming

Flag variable

stat_sas — Thu, 27 Feb 2014 16:16:15 GMT

Hi Experts,

I am trying to create a flag variable based on transaction date. If customer has bought something within 6 months of the last transaction date then it will be flagged as existing customer otherwise acquired customer.

e.g.

For a particular customer last trans_date is 2012-01-29 then I need to see sale within last 6 months of the trans_date to identify this customer as acquired or existing.

Thanks in advance for your suggestions.

Regards,

Naeem

Re: Flag variable

jf — Thu, 27 Feb 2014 16:22:50 GMT

use proc sql to group all sales by customer id then find the most recent purchase.

Re: Flag variable

RW9 — Thu, 27 Feb 2014 16:23:40 GMT

Hi,

Several ways of doing it. First you would need to merge the last_trans_date onto your current data. Then you could go with INTNX - this function does intervals between time points. So if current trans date < = last trans date + 6 months then Y else N.

Can run up some code tomorrow if still unclear.

Rob

Re: Flag variable

stat_sas — Thu, 27 Feb 2014 16:35:45 GMT

Thanks jf and Rob.

Rob - If you could please provide some code whenever you get a chance that would be a great help.

Regards,

Naeem

Re: Flag variable

RW9 — Fri, 28 Feb 2014 09:20:32 GMT

Here is some code (sorry, spacing seems a bit weird on here). Have just done it quickly, so there is no checking done (i.e. if there are not previous transactions etc.) but it works. There are other methods, such as datdiff if you can specify a number of days rather than months.

---

/* Create some test data */
data previous_transactions;
attrib cust_id format=8.
tran_date format=date9.;
cust_id=1; tran_date='01mar13'd; output;
cust_id=1; tran_date='06jan13'd; output;
cust_id=1; tran_date='15may13'd; output;
cust_id=2; tran_date='01mar13'd; output;
cust_id=2; tran_date='03mar13'd; output;
run;
data new_transactions;
attrib cust_id format=8.
tran_date format=date9.;
cust_id=1; tran_date='30may13'd; output;
cust_id=2; tran_date='10oct13'd; output;
run;

/* New table with flag. */
proc sql;
create table new_transaction_with_flag as
select A.*,

/* If the maximum date from previous transactions + 6 months is after the date we are looking at then we know this is within 6 months */
          case when intnx('month',B.TRAN_DATE,6)>=A.TRAN_DATE then "Y"
                    else "N" end as FLAG
from    WORK.NEW_TRANSACTIONS A
/* Join on the maximum data from previous transactions */
left join (
              select distinct
                      CUST_ID,
                      MAX(TRAN_DATE) as TRAN_DATE
              from    WORK.PREVIOUS_TRANSACTIONS
              group by CUST_ID
            ) B
on      A.CUST_ID=B.CUST_ID;
quit;

Re: Flag variable

Astounding — Fri, 28 Feb 2014 14:17:52 GMT

A couple of basic assumptions let a DATA step accomplish this fairly easily.

First, your dates have to be actual SAS dates.

Second, you have to be able to sort your data.

So the full solution could be as easy as this:

proc sort data=have;

by cust_id tran_date;

run;

data want;

set have;

by cust_id;

previous_purchase = lag(tran_date);

if first.cust_id then new_customer_flag=1;

else if (tran_date - previous_purchase) < 183 then new_customer_flag=0;

else new_customer_flag=1;

run;

If you don't like the 183-day cutoff, you can always use some of the more complex methods (such as INTNX) to define the 6-month window.

Re: Flag variable

stat_sas — Fri, 28 Feb 2014 21:30:50 GMT

Thanks RW9 - Everything is perfect but I am looking for trans_date which is within past 6 months from the new transaction date. I am not taking into account future transactions for this analysis. How can I modify suggested code for this one please?

Regards,

Naeem

Re: Flag variable

stat_sas — Fri, 28 Feb 2014 23:52:30 GMT

Hi Astounding,

Thanks for providing solution. Actually I've two different datasets where one dataset contains campaign data which was executed within a specific time period. My problem is to take trans_date from campaign data and compare this with most previous date from the bigger dataset that contains all past information regarding customers . If difference between two dates is within six months window then I need to flag customer as retention and if the difference is zero or greater than six months customer would be acquisition.

Solution provided by RW9 is very close to what I was looking for but this is taking maximum date which is most recent transaction date for a particular customer. I know lag function is not going to work in sql but not sure how to tackle this problem with data step.

Any suggestion about this will be highly appreciated.

Regards,

Naeem

Re: Flag variable

RW9 — Mon, 03 Mar 2014 08:55:21 GMT

Well, this is one way, checking for the existence of a record where the old transaction date is between new transaction date - 6 months and new transaction date.

proc sql;
create table new_transaction_with_flag as
select A.*,

/* So if there is a record with a date between the new transaction - 6 months and new transaction
   then flag it */
          case when exists(
                              select distinct THIS.CUST_ID
                              from    WORK.PREVIOUS_TRANSACTIONS THIS
                              where   THIS.CUST_ID=A.CUST_ID
                                and   THIS.TRAN_DATE between intnx('month',A.TRAN_DATE,-6) and A.TRAN_DATE
                            ) then "Y"
                else "N" end as FLAG
from    WORK.NEW_TRANSACTIONS A;
quit;

Re: Flag variable

Astounding — Mon, 03 Mar 2014 13:57:55 GMT

OK, just to make sure I understand the problem correctly ... it sounds like you only need one result per CUST_ID with the FLAG. You don't actually need to flag each purchase, you only need to flag each CUST_ID. If that's right, here's one way (assuming both data sets are sorted by CUST_ID and date):

data want;

set purchases

transaction (in=in2 rename=(transaction_date = purchase_date));

by CUST_ID PURCHASE_DATE;

prior_date = lag(purchase_date);

if in2 then do;

if first.cust_id then new_customer_flag=1;

else if (purchase_date - prior_date) > 182 then new_customer_flag=1;

else new_customer_flag=0;

output;

end;

keep cust_id new_customer_flag;

run;

I hope we're both trying to solve the same problem.

Re: Flag variable

stat_sas — Mon, 03 Mar 2014 14:35:21 GMT

Thanks Guys - This is exactly that I was looking for. Really appreciate for suggesting solution.

Regards,

Naeem