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 |
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 .
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
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
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.
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
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
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;
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
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 .
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: