SAS Programming

DATA Step, Macro, Functions and more
BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
JFM
Calcite | Level 5 JFM
Calcite | Level 5

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.

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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

4 REPLIES 4
ballardw
Super User

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

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
Super User

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
Super User

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

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

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