Calculating Intervals between Purchases

[dartlee]

Hi SAS community, i would like to create a column to calculate the interval in days between each Purchase_Flg = 1 for each USER_ID_Hash. I've racked my brains but it seems like i've arrived at a standstill. 

Methods i've tried would be to use it in a by statement with User_ID_Hash, I_Date, Purchase_Flg (of course after sorting them) to try to obtain first.XXX and last.XXX variables to see if i could define some where statements based on them but it does not seem to work. I'm not sure how to approach this in the right way and it'll be great if someone can somehow point me in the right direction. Appreciate the help.

[Reeza]

Please show what you've tried and what didn't work. 

data want;
set have;
by user_id;

retain last_purchase; 

*reset to missing at first ID;
if first.user_id then last_purchase = .;


if purchase_flag = 1 then do;
if not missing(last_purchase) then diff = i_date - last_purchase; 
last_purchase = i_date;
end;

run;

Also, don't post pictures, please post actual sample data. This is untested because I will not type out your data. 

If you have issues with the code, please post the full log. 

Here are instructions on how to provide sample data as a data step:
https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...

---

@dartlee wrote:

Hi SAS community, i would like to create a column to calculate the interval in days between each Purchase_Flg = 1 for each USER_ID_Hash. I've racked my brains but it seems like i've arrived at a standstill. 

Methods i've tried would be to use it in a by statement with User_ID_Hash, I_Date, Purchase_Flg (of course after sorting them) to try to obtain first.XXX and last.XXX variables to see if i could define some where statements based on them but it does not seem to work. I'm not sure how to approach this in the right way and it'll be great if someone can somehow point me in the right direction. Appreciate the help.

---

[ballardw]

Since your I_date variable appears to be datetime values you may need DATEPART with it to extract the dates. You don't specify what units you want your interval in.

The following would return days between.

proc sort data=have out=temp (where=(purchase_flg=1));
   by user_id_hash idate;
run;

data want;
   set temp;
   by user_id;
   last_date= datepart(lag(idate));
   if first.id then interval=.;
   else interval = datepart(idate) - lastdate;
   drop last_date; 
run;

Merge back onto your original data by  user_id idate.

[mkeintz]

@ballardw :

You don't need to extract date values from datetimes to generate number of days (i.e. number of midnights crossed) between two datetimes

data want;
   set temp;
   by user_id;
   interval=intck('hour24',lag(date),date);
   if first.id then interval=.;
run;

regards,

Mark

[ballardw]

---

@mkeintz wrote:

@ballardw :

You don't need to extract date values from datetimes to generate number of days (i.e. number of midnights crossed) between two datetimes

data want;
   set temp;
   by user_id;
   interval=intck('hour24',lag(date),date);
   if first.id then interval=.;
run;

regards,

Mark

---

Or use 'DTDAY' I believe.