Stockpiling Drugs

[tennis1]

Hi All,

I'm new to SAS (EG 7.1 64-bit) so I'm having trouble figuring this one out so any help would be appreciated. 

I am looking to determine if members are consistently filling their
prescriptions early and accumulating a large supply at home (stockpile of meds).
Currently, there is a 75/80% refill threshold (RTS = refill to soon)
meaning 75/80% of medications must be utilized before the member may refill at the medication.
If you have a 30 day supply (75% RTS is day day 23). This means the member may fill the
drug 7 days early. At the end of the year the member may have accumulated 84 days’ supply of medication.

I want to show the members that are consistently filling their medications early. 

ID	DRUG	START_DT	DAYS_SUPP	END_DT
1	A	3/14/19	30	4/13/19
1	B	5/6/09	30	6/5/09
1	C	7/9/19	60	9/7/19
1	E	3/1/19	90	5/30/19
1	B	5/25/19	30	6/24/19
1	D	2/1/19	30	3/3/19
1	C	6/6/19	60	8/4/19
2	B	4/1/19	30	5/1/19
2	A	7/1/19	30	7/31/19
2	C	7/3/19	60	9/1/19
2	B	4/15/19	30	5/14/19
2	E	6/5/19	90	9/5/19
2	A	5/1/19	90	8/2/19

[Reeza]

You have the drug cabinet problem where you basically need to find out the amount on each day in the 'cabinet' and then figure out if that amount is increasing over time. What's the window you're looking at?

An alternative option is to just pick an interval, add up the number dispensed over the days + number of days in that interval and you'll have the amount that's excess and filter off of that. 

Please show exactly what you expect as output from the data below. It should align with the data posted.

---

@tennis1 wrote:

Hi All,

I'm new to SAS (EG 7.1 64-bit) so I'm having trouble figuring this one out so any help would be appreciated. 

I am looking to determine if members are consistently filling their
prescriptions early and accumulating a large supply at home (stockpile of meds).
Currently, there is a 75/80% refill threshold (RTS = refill to soon)
meaning 75/80% of medications must be utilized before the member may refill at the medication.
If you have a 30 day supply (75% RTS is day day 23). This means the member may fill the
drug 7 days early. At the end of the year the member may have accumulated 84 days’ supply of medication.

I want to show the members that are consistently filling their medications early. 

ID	DRUG	START_DT	DAYS_SUPP	END_DT
1	A	3/14/19	30	4/13/19
1	B	5/6/09	30	6/5/09
1	C	7/9/19	60	9/7/19
1	E	3/1/19	90	5/30/19
1	B	5/25/19	30	6/24/19
1	D	2/1/19	30	3/3/19
1	C	6/6/19	60	8/4/19
2	B	4/1/19	30	5/1/19
2	A	7/1/19	30	7/31/19
2	C	7/3/19	60	9/1/19
2	B	4/15/19	30	5/14/19
2	E	6/5/19	90	9/5/19
2	A	5/1/19	90	8/2/19

---

[PGStats]

Consider this:

data have;
input ID DRUG $ START_DT :mmddyy. DAYS_SUPP;
format start_dt yymmdd10.;
datalines;
1   A   3/14/19 30  4/13/19
1   B   5/6/09  30  6/5/09
1   C   7/9/19  60  9/7/19
1   E   3/1/19  90  5/30/19
1   B   5/25/19 30  6/24/19
1   D   2/1/19  30  3/3/19
1   C   6/6/19  60  8/4/19
2   B   4/1/19  30  5/1/19
2   A   7/1/19  30  7/31/19
2   C   7/3/19  60  9/1/19
2   B   4/15/19 30  5/14/19
2   E   6/5/19  90  9/5/19
2   A   5/1/19  90  8/2/19
;

proc sort data=have; by id drug start_dt; run;
 
%let alarmingLeftover=20; /* Adjust */

data want;
do until(last.drug);
	set have; by id drug;
	if first.drug then leftover = 0;
	else do;
		leftover = max(0, supply - intck("day", lastSupplydate, start_dt));
		alarm_&alarmingLeftover._days = leftOver >= &alarmingLeftover;
		output;
		end;
	supply = leftover + days_supp;
	lastSupplyDate = start_dt;
	end;
drop supply lastSupplyDate;
run;

proc print data=want noobs; run;

ID 	DRUG 	START_DT 	DAYS_SUPP 	leftover 	alarm_20_days
1 	B 	2019-05-25 	30 	0 	0
1 	C 	2019-07-09 	60 	27 	1
2 	A 	2019-07-01 	30 	29 	1
2 	B 	2019-04-15 	30 	16 	0

[tennis1]

Thanks! This is super helpful. Is there a good way to make the leftover equal to a % based value? For example, I want to make the alarm be 1 when the leftover days >= 0.75*supply. 

[PGStats]

Sure, good idea. Simple to modify:

data have;
input ID DRUG $ START_DT :mmddyy. DAYS_SUPP;
format start_dt yymmdd10.;
datalines;
1   A   3/14/19 30  4/13/19
1   B   5/6/09  30  6/5/09
1   C   7/9/19  60  9/7/19
1   E   3/1/19  90  5/30/19
1   B   5/25/19 30  6/24/19
1   D   2/1/19  30  3/3/19
1   C   6/6/19  60  8/4/19
2   B   4/1/19  30  5/1/19
2   A   7/1/19  30  7/31/19
2   C   7/3/19  60  9/1/19
2   B   4/15/19 30  5/14/19
2   E   6/5/19  90  9/5/19
2   A   5/1/19  90  8/2/19
;

proc sort data=have; by id drug start_dt; run;
 
%let alarmingLeftoverPct=75; /* Adjust */

data want;
do until(last.drug);
	set have; by id drug;
	if first.drug then leftover = 0;
	else do;
		leftover = max(0, supply - intck("day", lastSupplydate, start_dt));
		alarm_&alarmingLeftoverPct._Pct = leftOver >= days_supp * &alarmingLeftoverPct / 100;
		output;
		end;
	supply = leftover + days_supp;
	lastSupplyDate = start_dt;
	end;
drop supply lastSupplyDate;
run;

proc print data=want noobs; run;

ID 	DRUG 	START_DT 	DAYS_SUPP 	leftover 	alarm_75_Pct
1 	B 	2019-05-25 	30 	0 	0
1 	C 	2019-07-09 	60 	27 	0
2 	A 	2019-07-01 	30 	29 	1
2 	B 	2019-04-15 	30 	16 	0

[tennis1]

Thanks so much! Also, is there a good way to identify the stockpiled drugs in a case like below where they are filled by the member on the same day like below?

data have;
input ID DRUG $ START_DT :mmddyy. DAYS_SUPP;
format start_dt yymmdd10.;
datalines;
1   A   3/14/19 30  4/13/19
1   A   3/14/19 30 4/13/19

ID 	DRUG 	START_DT     DAYS_SUPP leftover alarm_75_Pct
1 	A 	2019-03-14 	30 	0 	0
1 	A 	2019-03-14 	30 	30 	1

[PGStats]

I think you will get that if you move the output statement :

data want;
do until(last.drug);
	set have; by id drug;
	if first.drug then leftover = 0;
	else do;
		leftover = max(0, supply - intck("day", lastSupplydate, start_dt));
		alarm_&alarmingLeftoverPct._Pct = leftOver >= days_supp * &alarmingLeftoverPct / 100;
		end;
	output;
	supply = leftover + days_supp;
	lastSupplyDate = start_dt;
	end;
drop supply lastSupplyDate;
run;

[tennis1]

Unfortunately, that code is still not distinguishing between drugs being filled on the same date. Below is what I'm currently getting:

data have;
input ID DRUG $ START_DT :mmddyy. DAYS_SUPP;
format start_dt yymmdd10.;
datalines;1   A   3/14/19 30  4/13/19
1   A   3/14/19 30 4/13/19

ID 	DRUG 	START_DT     DAYS_SUPP leftover alarm_75_Pct
1 	A 	2019-03-14 	30 	0 	0
1 	A 	2019-03-14 	30 	0 	0

[PGStats · Accepted solution]

I  get:

data have;
input ID DRUG $ START_DT :mmddyy. DAYS_SUPP;
format start_dt yymmdd10.;
datalines;
1   A   3/14/19 30  4/13/19
1   A   3/14/19 30  4/13/19
1   B   5/6/09  30  6/5/09
1   C   7/9/19  60  9/7/19
1   E   3/1/19  90  5/30/19
1   B   5/25/19 30  6/24/19
1   D   2/1/19  30  3/3/19
1   C   6/6/19  60  8/4/19
2   B   4/1/19  30  5/1/19
2   A   7/1/19  30  7/31/19
2   C   7/3/19  60  9/1/19
2   B   4/15/19 30  5/14/19
2   E   6/5/19  90  9/5/19
2   A   5/1/19  90  8/2/19
;

proc sort data=have; by id drug start_dt; run;
 
%let alarmingLeftoverPct=75; /* Adjust */

data want;
do until(last.drug);
	set have; by id drug;
	if first.drug then leftover = 0;
	else do;
		leftover = max(0, supply - intck("day", lastSupplydate, start_dt));
		alarm_&alarmingLeftoverPct._Pct = leftOver >= days_supp * &alarmingLeftoverPct / 100;
		end;
	output;
	supply = leftover + days_supp;
	lastSupplyDate = start_dt;
	end;
drop supply lastSupplyDate;
run;

proc print data=want noobs; run;

ID 	DRUG 	START_DT 	DAYS_SUPP 	leftover 	alarm_75_Pct
1 	A 	2019-03-14 	30 	0 	.
1 	A 	2019-03-14 	30 	30 	1
1 	B 	2009-05-06 	30 	0 	.
1 	B 	2019-05-25 	30 	0 	0
1 	C 	2019-06-06 	60 	0 	.
1 	C 	2019-07-09 	60 	27 	0
1 	D 	2019-02-01 	30 	0 	.
1 	E 	2019-03-01 	90 	0 	.
2 	A 	2019-05-01 	90 	0 	.
2 	A 	2019-07-01 	30 	29 	1
2 	B 	2019-04-01 	30 	0 	.
2 	B 	2019-04-15 	30 	16 	0
2 	C 	2019-07-03 	60 	0 	.
2 	E 	2019-06-05 	90 	0 	.