DATA Step, Macro, Functions and more

INTNX for identifying New and Existing Customers

Accepted Solution Solved
Reply
New Contributor JFM
New Contributor
Posts: 4
Accepted Solution

INTNX for identifying New and Existing Customers

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.


Accepted Solutions
Solution
‎07-14-2015 05:34 PM
Super User
Posts: 11,343

Re: INTNX for identifying New and Existing Customers

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

View solution in original post


All Replies
Super User
Posts: 11,343

Re: INTNX for identifying New and Existing Customers

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.

New Contributor JFM
New Contributor
Posts: 4

Re: INTNX for identifying New and Existing Customers

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

Super User
Posts: 19,772

Re: INTNX for identifying New and Existing Customers

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?

Solution
‎07-14-2015 05:34 PM
Super User
Posts: 11,343

Re: INTNX for identifying New and Existing Customers

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 255 views
  • 4 likes
  • 3 in conversation