topic Re: Stockpiling Drugs in SAS Programming

Stockpiling Drugs

tennis1 — Tue, 15 Oct 2019 15:10:58 GMT

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

Re: Stockpiling Drugs

Reeza — Tue, 15 Oct 2019 15:40:27 GMT

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

Re: Stockpiling Drugs

PGStats — Tue, 15 Oct 2019 19:28:11 GMT

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

Re: Stockpiling Drugs

tennis1 — Thu, 17 Oct 2019 21:45:45 GMT

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.

Re: Stockpiling Drugs

PGStats — Sat, 19 Oct 2019 03:48:31 GMT

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

Re: Stockpiling Drugs

tennis1 — Mon, 21 Oct 2019 20:02:46 GMT

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

Re: Stockpiling Drugs

PGStats — Tue, 22 Oct 2019 04:25:36 GMT

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;

Re: Stockpiling Drugs

tennis1 — Tue, 22 Oct 2019 19:28:33 GMT

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

Re: Stockpiling Drugs

PGStats — Wed, 23 Oct 2019 05:18:48 GMT

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 	.