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-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
  • 4 replies
  • 853 views
  • 4 likes
  • 3 in conversation