topic Re: Calculating Days to Late Refill in SAS Programming

Calculating Days to Late Refill

ssitharath0420 — Thu, 13 Feb 2020 19:39:08 GMT

Hello,

I am working with pharmacy data. I am trying to calculate how many days a member is late to their next fill date. Can someone please help? Do I use the lag function or do a transpose of the data?

An example of the data is:

ID	Fill_dt	DS	End_Dt
78923	2/15/2019	30	3/17/2019
78923	3/15/2019	30	4/14/2019
78923	4/10/2019	30	5/10/2019
78923	5/8/2019	30	6/7/2019
78923	6/5/2019	30	7/5/2019
78923	7/3/2019	30	8/2/2019
78923	8/5/2019	30	9/4/2019
78923	9/1/2019	30	10/1/2019
78923	11/10/2019	30	12/10/2019
78923	12/6/2019	30	1/5/2020

Re: Calculating Days to Late Refill

PaigeMiller — Thu, 13 Feb 2020 19:46:13 GMT

@ssitharath0420 wrote:

Hello,

I am working with pharmacy data. I am trying to calculate how many days a member is late to their next fill date. Can someone please help? Do I use the lag function or do a transpose of the data?

Please explain the algorithm is more detail. What is the exact calculation?

Re: Calculating Days to Late Refill

ssitharath0420 — Thu, 13 Feb 2020 19:53:13 GMT

ID	Fill_dt	DS	End_Dt
78923	2/15/2019	30	3/17/2019
78923	3/15/2019	30	4/14/2019
78923	4/10/2019	30	5/10/2019
78923	5/8/2019	30	6/7/2019
78923	6/5/2019	30	7/5/2019
78923	7/3/2019	30	8/2/2019
78923	8/5/2019	30	9/4/2019
78923	9/1/2019	30	10/1/2019
78923	11/10/2019	30	12/10/2019
78923	12/6/2019	30	1/5/2020

So their first fill is 02/15/2019 and they have a 30 days supply. They can't get another fill until after 03/17/2019. Their second fill is 03/15/2019, therefore they are not late. Basically I want to take the End_Dt and subtract it from their next subsequent fill date and so forth.

Re: Calculating Days to Late Refill

Karlschaefer — Thu, 13 Feb 2020 19:56:02 GMT

Assuming you want to compare fill date to previous record end date. Use a retain variable in your data step.

data laterefills;

set exampledata;

by ID;

retain last_end_dt .;

if first.id then call missing(last_end_dt);

if fill_dt>last_end_dt and first.id=0 then output;

last_end_dt=end_dt;

run;

Re: Calculating Days to Late Refill

novinosrin — Thu, 13 Feb 2020 20:12:30 GMT


data have;
input ID	Fill_dt :mmddyy10.	DS	End_Dt :mmddyy10.;
format Fill_dt mmddyy10.	End_Dt mmddyy10.;
cards;
78923	2/15/2019	30	3/17/2019
78923	3/15/2019	30	4/14/2019
78923	4/10/2019	30	5/10/2019
78923	5/8/2019	30	6/7/2019
78923	6/5/2019	30	7/5/2019
78923	7/3/2019	30	8/2/2019
78923	8/5/2019	30	9/4/2019
78923	9/1/2019	30	10/1/2019
78923	11/10/2019	30	12/10/2019
78923	12/6/2019	30	1/5/2020
;

data want;
 set have;
 by id;
 if first.id then _iorc_=end_dt;
 else do;
  days=ifn(fill_dt-_iorc_<=0,.,fill_dt-_iorc_); /*invalid days set to missing*/
  _iorc_=end_dt;
 end;
run;
/*Or*/
data want;
 set have;
 by id;
 if first.id then _iorc_=end_dt;
 else do;
  days=fill_dt-_iorc_;
  _iorc_=end_dt;
 end;
run;

Re: Calculating Days to Late Refill

PaigeMiller — Thu, 13 Feb 2020 20:12:55 GMT

@ssitharath0420 wrote:
Basically I want to take the End_Dt and subtract it from their next subsequent fill date and so forth.

data want;
    set have;
    prev_end_dt=lag(end_dt);
    days_late = fill_dt - prev_end_dt;
    drop prev_end_dt;
run;

Re: Calculating Days to Late Refill

ssitharath0420 — Thu, 13 Feb 2020 20:14:04 GMT

Thank you! This is exactly what I am looking to do!