BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
tennis1
Calcite | Level 5

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. 

 

 

IDDRUGSTART_DTDAYS_SUPPEND_DT
1A3/14/19304/13/19
1B5/6/09306/5/09
1C7/9/19609/7/19
1E3/1/19905/30/19
1B5/25/19306/24/19
1D2/1/19303/3/19
1C6/6/19608/4/19
2B4/1/19305/1/19
2A7/1/19307/31/19
2C7/3/19609/1/19
2B4/15/19305/14/19
2E6/5/19909/5/19
2A5/1/19908/2/19
1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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

View solution in original post

8 REPLIES 8
Reeza
Super User

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
Opal | Level 21

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
PG
tennis1
Calcite | Level 5

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
Opal | Level 21

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

 

PG
tennis1
Calcite | Level 5

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
Opal | Level 21

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;
PG
tennis1
Calcite | Level 5

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
Opal | Level 21

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

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 962 views
  • 1 like
  • 3 in conversation