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
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
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.
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.
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.
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
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.
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 .
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).
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: