BookmarkSubscribeRSS Feed
pawandh
Fluorite | Level 6

 I want to create a table where I will have new customer.Suppose a customer purchased something in Feb'17 and prior to 12 months i.e from jan'16-feb'15 customer didn't purchase anythning, then it's a new customer for Feb'17. The look back period is for 12 months.

 

cust_id date sales;

001 23feb2017 100

001 22jan2016 200

001 23mar2017 300

002 01june2015 200

002 03aug2016 300

002 05april2016 400

 

output should be

new_cust date sales;

001 23feb2017 100

002 05april2016 400

 

How can I do using SAS or SQL

10 REPLIES 10
art297
Opal | Level 21

I'm a bit confused regarding your example. If you put your dates in descending order (i.e.,

 

cust_id date sales;

001 23mar2017 300

001 23feb2017 100

001 22jan2016 200

 

002 03aug2016 300

002 05april2016 400

002 01june2015 200

---------

001 23feb2017 100 would be the only date that meets your conditions, not

001 23feb2017 100 and 002 05april2016 400

 

Is that what you want or did I miss something?

 

If it's what you want, assuming you also would want brand new customers that don't have any gap, I'd guess that you want something like:

 

data have;
  input cust_id date date9. sales;
  format date date9.;
  cards;
001 23feb2017 100
001 22jan2016 200
001 23mar2017 300
002 01jun2015 200
002 03aug2016 300
002 05apr2016 400
003 06apr2017 200
;

proc sort data=have;
  by cust_id date;
run;
 
data want (drop=gap);
  set have;
  by cust_id;
  gap = dif(date);
  if (first.cust_id and last.cust_id) or
   (first.cust_id=0 and gap > 365);
run;

 

Art, CEO, AnalystFinder.com

 

Astounding
PROC Star

Why should customer 002 appear as a new customer?  I don't see a 12-month gap here.

 

Assuming that's a mistake, here's one way.  (It uses 365 days as the time period, but that can be made more accurate if you need to account for leap years.)

 

data have;

informat date date9.;

input cust_id $ date sales;

format date date9.;

datalines;

001 23feb2017 100

001 22jan2016 200

001 23mar2017 300

002 01jun2015 200

002 03aug2016 300

002 05apr2016 400

;

 

proc sort data=have;

by cust_id date;

run;

 

data want;

set have;

by cust_id;

gap = dif(date);

if (first.cust_id) or (first.cust_id=0 and gap > 365);

run;

 

Note that the data should contain just the first three letters of the month.

 

OK, modified the program to match what it seems you are looking for.

pawandh
Fluorite | Level 6

Here for customer 002, new writer flag would be in JUN'15 as this is the most recent month where customer purchased anything and prior to that  12 month customer didn't buy anything.

 

Let's say you purchased something  on Feb'17 and Mar'17,now you are a new customer in Feb'17 because from the past 12 months you didn't purchase anything.

Astounding
PROC Star

OK, which of these would be a better solution?

 

The earliest record for each customer, plus all later records that have at least a 365-day gap.

 

vs.

 

For each customer, all records that have a 365-day gap.  If there are none, then use the earliest record instead.

art297
Opal | Level 21

Now I'm more confused. According to your most recent post, all customers are being considered as new customers. They are considered new as of the most recent date of:

1. the first date they did business with you

or

2. the most recent date that came after a year period where they didn't do any business with you

 

Is that what you are trying to get?

 

Art, CEO, AnalystFinder.com

pawandh
Fluorite | Level 6

Suppose you are a customer, and you purchased in Feb'17,Mar'17 and so on. You start purchasing in Feb'17 so you are starting from that month, new customer for that month. And will check prior to that 12 months do you have any record or not. So if you purchased in Aug'16 as well then for Aug'16 you will be a new customer not for Feb'17.

 

 

 

Yavuz
Quartz | Level 8
For customer 2, can you explain how criteria work please ?

002 01june2015 200...gap=start point
002 05april2016 400...gap<365
002 03aug2016 300...gap<365
pawandh
Fluorite | Level 6

Customer 002 has purchased on aug2016, now check any purchase prior to 12 months. So the purchase is on april2016,now look again  back for 12 months, purchase is on june2015. And prior to jun2015 there is no purchase so jun'15 is the month where 002 started purchaisng ,now for this month customer is new .

Yavuz
Quartz | Level 8
But between jun15 and april 16 there is not 12 month period. Can you tell me what is the difference of jun15 and april16.
pawandh
Fluorite | Level 6

We have data like this

feb'17 jan'17 dec'16 nov'16 oct'16 sep'16 ...

so first month is feb'17, 2month is jan'17 and so on so look back for Feb'17 will be Jan'17 to Feb'16(12 months look back).

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
  • 10 replies
  • 1556 views
  • 1 like
  • 4 in conversation