INTNX for identifying New and Existing Customers

[JFM]

Hi,

I'm need to working with SAS date functions any help is appreciated.

I'm trying to set two flags in a data set, one for New Customers and one for Existing.

New Customers = have an original_purchase_date in the previous month

Existing Customers = have an original_purchase_date before previous month

And I want to pull in all activity for the last 18 months as of the Last day in the previous month where the Last day in the previous month is

%let end='30Jun15'd;run;

This is what I have so far and I know this code is incorrect.

proc sql;

create table Cust as select *, case when original_purchase_date between intnx('MONTH', &end, -1) and intnx('MONTH', &end) then 1 else 0 end as new,

case when original_purchase_date lt intnx('MONTH', &end, -1) then 1 else 0 end as Existing from orders where original_purchase_date between intnx('MONTH', &end, -1) and intnx('MONTH', &end, -1) -18;

quit;

Again, any help is appreciated.

[ballardw]

I think you should provide some example data and the desired result.

I am concerned that, if I understand you correctly, if I run this data approach at a latter date a client that was previously Existing becomes New because their initial purchase was near the beginning of the 18 month frame and don't show up in a later frame if there are long intervals between purchases.

[JFM]

Hi,

Forget about the 18 months. I'm just looking to identify

New Customers = have an original_purchase_date in the previous month

Existing Customers = have an original_purchase_date before previous month

Based on a month end date of

%let end='30Jun15'd

[Reeza]

Your terminology is confusing. Previous month is actually the last month in your data set, is that correct? So if some one's first purchase is in the last month they're new and if later then existing?

Also, start with the basics, are you sure you're pulling 18 months of data correctly?

[ballardw · Accepted solution]

If the question is the month boundaries

to select:

where intnx('month',original_purchase_date, &end)  =1    /* if only in the previous month*/

where intnx('month',original_purchase_date, &end)  le 1    /* if in the previous or current month*/

flag:

(intnx('month',original_purchase_date, &end)  =1) as new

or

( intnx('month',original_purchase_date, &end)  le 1) as new