topic Calculating Intervals between Purchases in SAS Data Management

Calculating Intervals between Purchases

dartlee — Mon, 16 Sep 2019 15:49:51 GMT

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.

Re: Calculating Intervals between Purchases

Reeza — Mon, 16 Sep 2019 16:11:17 GMT

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-data-AKA-generate/ta-p/258712

@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.

Re: Calculating Intervals between Purchases

ballardw — Mon, 16 Sep 2019 17:59:37 GMT

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.

Re: Calculating Intervals between Purchases

mkeintz — Wed, 18 Sep 2019 17:44:08 GMT

@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

Re: Calculating Intervals between Purchases

ballardw — Wed, 18 Sep 2019 21:43:18 GMT

@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.