New customer based on look back period

[pawandh]

 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

[art297]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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).