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

[devon59]

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

[stat_sas]

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:

[devon59]

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

[stat_sas · Accepted solution]

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;

[devon59]

Thank you so much!! I really appreciate it.