topic Re: Time between two incidents in SAS Procedures

Time between two incidents

spg — Wed, 21 Apr 2010 16:03:03 GMT

I have a dataset with purchase history (dates of purchase) of a product for different consumers. The dates of purchase (in the DD/MM/YY format) are sorted by consumers. I want to calculate the average time between two buys for each consumer. Can anybody please help? The product category is not important here, just the time between two purchases.

The data looks like this:

Date Consumer Product
1/1/08 1 24
1/1/08 2 23
6/2/08 2 24
2/3/07 3 22
7/3/08 3 24
4/4/06 4 25
2/4/07 5 23
5/9/07 5 23

Thank you.

Re: Time between two incidents

RickM — Wed, 21 Apr 2010 16:44:37 GMT

You might want to look into sorting by date and consumer and then in a data step use first. and a retain statement to figure out if you are looking at the first instance of a consumer or not and take the difference between consecutive times for each consumer.

Re: Time between two incidents

barheat — Wed, 21 Apr 2010 18:48:35 GMT

A follow up to the previous suggestion. Sort the data by consumer and purchase date.

proc sort data=purchases;
by consumer purchase_date;
run;

Use the lag function to count the days between purchases

data purchases;
set purchases;
if consumer=lag1(consumer) then days=purchase_date-lag1(purchase_date);
else days=.;
run;

Now run a proc means/summary.

proc summary data=purchases nway;
var days;
class consumer;
output out=purchase_analysis(drop=_type_) mean=days;
run;

In the output table _freq_ will be the number of purchases - 1.

Re: Time between two incidents

Cynthia_sas — Wed, 21 Apr 2010 22:12:46 GMT

Hi:
Two solutions that do not use the LAG function are shown below. The first approach assumes that there are ONLY 2 purchases. The second solution uses an ARRAY (after the TRANSPOSE) to create a DIFFn variable for every DATEn variable (except the first one) -- this would work for more than 2 purchases.

cynthia
[pre]
** If only 2 purchases, then simple PROC TRANSPOSE and
DATA step with subtraction;
data purch;
infile datalines;
input date : mmddyy8. consumer product;
return;
datalines;
1/1/08 1 24
1/1/08 2 23
6/2/08 2 24
2/3/07 3 22
7/3/08 3 24
4/4/06 4 25
2/4/07 5 23
5/9/07 5 23
;
run;

proc transpose data=purch out=purchout(drop=_name_) prefix=date;
by consumer;
var date;
run;

data calcdiff;
set purchout;
daysbetween = date2 - date1;
run;

proc print data=calcdiff;
title 'Calculate Difference in Dates when ONLY 2 purchases';
run;

** Approach 2: May have more purchases and want to know duration;
** between date2 and date1, date3 and date2, date4 and date3, etc;
ods listing;
data purch_more;
infile datalines;
input date : mmddyy8. consumer product;
return;
datalines;
1/1/08 1 24
1/1/08 2 23
6/2/08 2 24
1/1/09 2 25
2/3/07 3 22
7/3/08 3 24
4/4/06 4 25
2/4/07 5 23
5/9/07 5 23
6/15/07 5 24
12/22/07 5 25
;
run;

proc transpose data=purch_more out=moreout(drop=_name_) prefix=date;
by consumer;
var date;
run;

data morediff;
set moreout;
** define array bigger than needed;
array dt date1-date10;
array df diff1-diff10;

** find out number of purchases;
numpurch = n(of date1-date10);

** set diff1 = 0 (because date1 is first purchase);
diff1 = 0;

** use numpurch in do loop to calculate diff2-diff10;
** but loop will only go to NUMPURCH value for each obs;
do i = 2 to numpurch;
df(i) = dt(i) - dt(i-1);
end;
run;

proc print data=morediff;
var consumer date1 diff1 date2 diff2 date3 diff3 date4 diff4;
format date1-date4 mmddyy8.;
run;

[/pre]

Re: Time between two incidents

spg — Thu, 22 Apr 2010 04:06:05 GMT

Thanks RickM!
Thanks barheat!
Thanks Cynthia!
I learnt three ways to approach the problem. Tried all of them...(but am yet to master the first. and retain method).

Re: Time between two incidents

RickM — Thu, 22 Apr 2010 14:16:43 GMT

using first. and retain would be similar to using lag1. Something like

proc sort data=purchases;
by consumer date;
run;

data purchases;
set purchases;
retain olddate;
if first.customer then days=.;
else days=date-olddate;
olddate=date;
run;

Re: Time between two incidents

Ksharp — Fri, 23 Apr 2010 00:51:10 GMT

You have not By variable.
So first.customer doesn't work.

Re: Time between two incidents

RickM — Fri, 23 Apr 2010 13:35:01 GMT

Right, I forgot about that part. Thats what happens when you dont test your code.

Re: Time between two incidents

none1 — Mon, 26 May 2014 11:14:24 GMT

Hello,

I have a date format which looks as follows:

04.10.2011 01:33:39

Now suppost I have two observations. one at the above mentioned data and time and the second after 30 seconds:

i.e.

04.10.2011 01:34:09

What formula should I use the calcuate the differnece between the two measurement dates ?

I also tried, data differnce, it doesn't seem to work for this date format ?

Kind Regards

Re: Time between two incidents

Ksharp — Mon, 26 May 2014 12:36:09 GMT

DIF( )