Counting number of times the customer has been contacted: Count restart with 1 after each order

Solved
Occasional Contributor
Posts: 12

Counting number of times the customer has been contacted: Count restart with 1 after each order

Hi,I would appreciate your help. Here's what I am trying to do. I have a dataset that tells me customerID, contact date, order date. I want to find out how many times on averagecustomers  have been contacted before they place order.

Right now, I have my code as

data  contact1; set contact;

by accountid contactdate orderdate;

if first.accountid then do;

temp=0;

end;

temp+1;

run:

This code create a column call temp that keep counting 1,2,3,4... for each accountid. However, I have this count to reset once contactdate>orderdate. How do I do that?

Any help would be very much appreciated.

Thank you

Accepted Solutions
Solution
‎10-08-2014 01:41 PM
Posts: 1,270

Re: Counting number of times the customer has been contacted: Count restart with 1 after each order

data want(drop=order);

set have;

by ID Contactdate Orderdate;

order=lag(orderdate);

format order mmddyy10.;

if first.id or Contactdate=Order+1 then Contact_no=0;

Contact_no+1;

run;

All Replies
Posts: 1,270

Re: Counting number of times the customer has been contacted: Count restart with 1 after each order

Better to have a sample data. Try this

data  contact1; set contact;

by accountid contactdate orderdate;

if first.accountid or contactdate>orderdate then do;

temp=0;

end;

temp+1;

run:

Occasional Contributor
Posts: 12

Re: Counting number of times the customer has been contacted: Count restart with 1 after each order

Hi,

Thank you so much for your reply. Here's the sample data.  When I did what you suggested, anything after row 6 below has Contact#=1.  I want it to recount again 1,2,3,4...  Is that possible?

 ID Contact date Orderdate Contact # 11111 1/2/2014 1/6/2014 1 11111 1/3/2014 1/6/2014 2 11111 1/4/2014 1/6/2014 3 11111 1/5/2014 1/6/2014 4 11111 1/6/2014 1/6/2014 5 11111 1/7/2014 1/6/2014 1 11111 1/8/2014 1/6/2014 2 11111 1/9/2014 1/6/2014 3 11111 1/10/2014 1/6/2014 4 11111 1/11/2014 1/6/2014 5 11111 1/15/2014 1/6/2014 6 11111 1/16/2014 1/6/2014 7 11111 1/17/2014 1/6/2014 8 11111 1/18/2014 1/6/2014 9 11111 1/19/2014 1/6/2014 10 11111 1/20/2014 1/6/2014 11 11111 1/21/2014 1/6/2014 12 11111 1/22/2014 1/6/2014 13 11111 1/23/2014 1/6/2014 14
Solution
‎10-08-2014 01:41 PM
Posts: 1,270

Re: Counting number of times the customer has been contacted: Count restart with 1 after each order

data want(drop=order);

set have;

by ID Contactdate Orderdate;

order=lag(orderdate);

format order mmddyy10.;

if first.id or Contactdate=Order+1 then Contact_no=0;

Contact_no+1;

run;

Occasional Contributor
Posts: 12

Re: Counting number of times the customer has been contacted: Count restart with 1 after each order

Thank you so much!! I really appreciate it.

🔒 This topic is solved and locked.