Calculating Days to Late Refill

[ssitharath0420]

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

[PaigeMiller]

---

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

[ssitharath0420]

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.

[PaigeMiller]

---

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

[Karlschaefer]

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;

[ssitharath0420]

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

[novinosrin]

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;