DATA Step, Macro, Functions and more

Finding New Customers on a monthly basis

Accepted Solution Solved
Reply
Contributor
Posts: 32
Accepted Solution

Finding New Customers on a monthly basis

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


Accepted Solutions
Solution
‎06-20-2016 11:10 AM
Super User
Super User
Posts: 7,988

Re: Finding New Customers on a monthly basis

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;

View solution in original post


All Replies
Solution
‎06-20-2016 11:10 AM
Super User
Super User
Posts: 7,988

Re: Finding New Customers on a monthly basis

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;
Contributor
Posts: 32

Re: Finding New Customers on a monthly basis

Thanks a lot.

Super User
Posts: 10,044

Re: Finding New Customers on a monthly basis

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;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 227 views
  • 0 likes
  • 3 in conversation