topic Re: Finding New Customers on a monthly basis in SAS Programming

Finding New Customers on a monthly basis

skallamp — Mon, 20 Jun 2016 14:19:34 GMT

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

Re: Finding New Customers on a monthly basis

RW9 — Mon, 20 Jun 2016 14:23:04 GMT

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;

Re: Finding New Customers on a monthly basis

skallamp — Mon, 20 Jun 2016 15:10:45 GMT

Thanks a lot.

Re: Finding New Customers on a monthly basis

Ksharp — Tue, 21 Jun 2016 02:38:43 GMT

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;