Is there a technique that will help me to identify new customers on a monthly basis?
Here is the example.
For Jan-2016 there are 5 new customers (1001, 1002, 1003, 1004 & 1005)
For Feb-2016 there are 8 customers, 3 are from Jan and 5 new customers (2001 to 2005)
For Mar-2016 there are 10 customers, however, 5 of them are already giving business from Jan/Feb. There are 5 new customers.
Is there a method that I can use to flag new suctomers for each month?
Customer_ID | Month | Used Service |
1001 | Jan-16 | Y |
1002 | Jan-16 | Y |
1003 | Jan-16 | Y |
1004 | Jan-16 | Y |
1005 | Jan-16 | Y |
1001 | Feb-16 | Y |
1002 | Feb-16 | Y |
1003 | Feb-16 | Y |
2001 | Feb-16 | Y |
2002 | Feb-16 | Y |
2003 | Feb-16 | Y |
2004 | Feb-16 | Y |
2005 | Feb-16 | Y |
1001 | Mar-16 | Y |
1002 | Mar-16 | Y |
1003 | Mar-16 | Y |
2001 | Mar-16 | Y |
2002 | Mar-16 | Y |
3001 | Mar-16 | Y |
3002 | Mar-16 | Y |
3003 | Mar-16 | Y |
3004 | Mar-16 | Y |
3005 | Mar-16 | Y |
Thanks,
+Sathyan
Its useful to post your test data in the form of a datastep, so we can run it and see how the data is stored. I am going to assume that month is a SAS date format. Something like this, I haven't tested
proc sql; create table WANT as select * from HAVE group by CUSTOMER_ID having MONTH=min(MONTH); quit;
Its useful to post your test data in the form of a datastep, so we can run it and see how the data is stored. I am going to assume that month is a SAS date format. Something like this, I haven't tested
proc sql; create table WANT as select * from HAVE group by CUSTOMER_ID having MONTH=min(MONTH); quit;
Thanks a lot.
data have;
infile cards expandtabs;
input Customer_ID Month $;
cards;
1001 Jan-16
1002 Jan-16
1003 Jan-16
1004 Jan-16
1005 Jan-16
1001 Feb-16
1002 Feb-16
1003 Feb-16
2001 Feb-16
2002 Feb-16
2003 Feb-16
2004 Feb-16
2005 Feb-16
1001 Mar-16
1002 Mar-16
1003 Mar-16
2001 Mar-16
2002 Mar-16
3001 Mar-16
3002 Mar-16
3003 Mar-16
3004 Mar-16
3005 Mar-16
;
run;
data want;
if _n_=1 then do;
if 0 then set have;
declare hash h();
h.definekey('Customer_ID');
h.definedone();
end;
set have;
if h.check()=0 then New_Customer='N';
else do;New_Customer='Y';h.add();end;
run;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.