Solved
Contributor
Posts: 25

# how do i identify if member had early re-fill

Hi,

I am looking to identify if a member had early refill and if so number days he filled early for the same combination of Drug and strength.

Mem_id DOS qty_disp days_supply Drug Strength
1001 01/18/2016 4 28 drug1 10 MCG/HR
1001 02/25/2016 4 14 drug1 5 MCG/HR
1001 03/24/2016 30 30 drug2 350 MG
1001 03/25/2016 4 28 drug1 10 MCG/HR
1001 04/05/2016 60 15 drug3 7.5-325MG
1001 05/12/2016 60 15 drug3 7.5-325MG
1001 01/18/2016 4 14 drug1 5 MCG/HR
1001 01/21/2016 120 30 drug4 10 MG
1002 01/21/2016 90 30 drug5 10 MG
1002 02/10/2016 90 30 drug5 10 MG
1002 02/18/2016 120 30 drug4 10 MG
1002 03/20/2016 120 30 drug4 10 MG
1002 03/20/2016 90 30 drug5 10 MG
1002 04/22/2016 90 30 drug5 10 MG
1002 04/22/2016 120 30 drug4 10 MG
1002 02/21/2016 40 5 drug3 7.5-325MG

output , i am looking for early refilled members with no. of days and drug combinations

mem_id drug stength eary_refill_days

1002 drug5 10MG 8

.

.

.

Accepted Solutions
Solution
‎05-23-2016 11:05 AM
PROC Star
Posts: 2,370

## Re: how do i identify if member had early re-fill

Indeed you are right. There was an error. This is better:

``````
data WANT;
set HAVE;
by MEM_ID DRUG STRENGTH DOS ;
if ^first.STRENGTH & DOS-lag(DOS) < lag(DAYS_SUPPLY) *0.75 then output;
run;``````

All Replies
Super User
Posts: 13,583

## Re: how do i identify if member had early re-fill

"Early" implies comparing two dates or times. Is DOS the date of the prescription or the date filled?

How would we know what is "early"?

Can you show from your example data which ones are "early"?

Contributor
Posts: 25

## Re: how do i identify if member had early re-fill

Early implies a same combination drug and strength that is again filled before the refill_Due date(fill_dt + days_supply).

In my example i am considering below line no.10 as early re-fill

The durg5 first filled on 1/21/2016 and it was supposed to re-fill on or later 2/20/2016 (refill_due date)

where as here it is re-filled on 2/10/2016 that is 10 days earlier.

 Sno Mem_id DOS (fill_dt) qty_disp days_supply Refill_Due(fill_dt + days_supply) Drug Strength 1 1001 01/18/2016 4 28 02/15/2016 drug1 10 MCG/HR 2 1001 02/25/2016 4 14 03/10/2016 drug1 5 MCG/HR 3 1001 03/24/2016 30 30 04/23/2016 drug2 350 MG 4 1001 03/25/2016 4 28 04/22/2016 drug1 10 MCG/HR 5 1001 04/05/2016 60 15 04/20/2016 drug3 7.5-325MG 6 1001 05/12/2016 60 15 05/27/2016 drug3 7.5-325MG 7 1001 01/18/2016 4 14 02/01/2016 drug1 5 MCG/HR 8 1001 01/21/2016 120 30 02/20/2016 drug4 10 MG 9 1002 01/21/2016 90 30 02/20/2016 drug5 10 MG 10 1002 02/10/2016 90 30 03/11/2016 drug5 10 MG 11 1002 02/18/2016 120 30 03/19/2016 drug4 10 MG 12 1002 03/20/2016 120 30 04/19/2016 drug4 10 MG 13 1002 03/20/2016 90 30 04/19/2016 drug5 10 MG 14 1002 04/22/2016 90 30 05/22/2016 drug5 10 MG 15 1002 04/22/2016 120 30 05/22/2016 drug4 10 MG 16 1002 02/21/2016 40 5 02/26/2016 drug3 7.5-325MG
Super User
Posts: 13,583

## Re: how do i identify if member had early re-fill

Do you have a rule for how many days early is to be reported as early?

Contributor
Posts: 25

## Re: how do i identify if member had early re-fill

Any re-fill before 75% of refill_due considered as early.

for a 30 days_supply drug with fill_dt 1/1/2016 and refill_due as 1/31/2016. Anything before 1/23/16 considered early refill.

PROC Star
Posts: 2,370

## Re: how do i identify if member had early re-fill

[ Edited ]

Like this?

``````data HAVE(index=(A=(MEM_ID DRUG STRENGTH DOS)));
input MEM_ID DOS mmddyy10. QTY_DISP DAYS_SUPPLY DRUG \$ STRENGTH & \$10.  ;
cards;
1001 01/18/2016 4 28 drug1 10 MCG/HR
1001 02/25/2016 4 14 drug1 5 MCG/HR
1001 03/24/2016 30 30 drug2 350 MG
1001 03/25/2016 4 28 drug1 10 MCG/HR
1001 04/05/2016 60 15 drug3 7.5-325MG
1001 05/12/2016 60 15 drug3 7.5-325MG
1001 01/18/2016 4 14 drug1 5 MCG/HR
1001 01/21/2016 120 30 drug4 10 MG
1002 01/21/2016 90 30 drug5 10 MG
1002 02/10/2016 90 30 drug5 10 MG
1002 02/18/2016 120 30 drug4 10 MG
1002 03/20/2016 120 30 drug4 10 MG
1002 03/20/2016 90 30 drug5 10 MG
1002 04/22/2016 90 30 drug5 10 MG
1002 04/22/2016 120 30 drug4 10 MG
1002 02/21/2016 40 5 drug3 7.5-325MG
run;

data WANT;
set HAVE;
by MEM_ID DRUG STRENGTH DOS ;
if ^first.STRENGTH & DOS-lag(DOS) < DAYS_SUPPLY *0.75 then output;
run;``````

Contributor
Posts: 25

## Re: how do i identify if member had early re-fill

Thanks chris, This is exactly what i was looking for.

however, the code is also displaying unwanted data for below example

have

 MEM_ID DOS QTY_DISP days_supply Drug Strength refill_due 1003 01/05/2016 45 15 Drug1 37.5-325MG 01/20/2016 1003 02/06/2016 45 15 Drug1 37.5-325MG 02/21/2016 1003 02/23/2016 180 90 Drug1 37.5-325MG 05/23/2016 1003 05/10/2016 270 90 Drug1 37.5-325MG 08/08/2016

want:

Below highlighted row shouldn't have been listed.

 MEM_ID DOS QTY_DISP days_supply Drug Strength refill_due days_early 1003 2/23/2016 180 90 Drug1 37.5-325MG 5/23/2016 73 1003 5/10/2016 270 90 Drug1 37.5-325MG 8/8/2016 13
Solution
‎05-23-2016 11:05 AM
PROC Star
Posts: 2,370

## Re: how do i identify if member had early re-fill

Indeed you are right. There was an error. This is better:

``````
data WANT;
set HAVE;
by MEM_ID DRUG STRENGTH DOS ;
if ^first.STRENGTH & DOS-lag(DOS) < lag(DAYS_SUPPLY) *0.75 then output;
run;``````
Contributor
Posts: 25

## Re: how do i identify if member had early re-fill

This is perfect. Thank you so much!

Super User
Posts: 10,784

## Re: how do i identify if member had early re-fill

[ Edited ]

Not sure whether the following code is what you are looking for .

``````data have;
input Mem_id DOS : mmddyy10. qty_disp days_supply Drug \$ Strength & \$20.;
Refill_Due=DOS+days_supply ;
format DOS Refill_Due mmddyy10.;
cards;
1001 01/18/2016 4 28 drug1 10 MCG/HR
1001 02/25/2016 4 14 drug1 5 MCG/HR
1001 03/24/2016 30 30 drug2 350 MG
1001 03/25/2016 4 28 drug1 10 MCG/HR
1001 04/05/2016 60 15 drug3 7.5-325MG
1001 05/12/2016 60 15 drug3 7.5-325MG
1001 01/18/2016 4 14 drug1 5 MCG/HR
1001 01/21/2016 120 30 drug4 10 MG
1002 01/21/2016 90 30 drug5 10 MG
1002 02/10/2016 90 30 drug5 10 MG
1002 02/18/2016 120 30 drug4 10 MG
1002 03/20/2016 120 30 drug4 10 MG
1002 03/20/2016 90 30 drug5 10 MG
1002 04/22/2016 90 30 drug5 10 MG
1002 04/22/2016 120 30 drug4 10 MG
1002 02/21/2016 40 5 drug3 7.5-325MG
;
run;
data want;
array _DOS{99999} _temporary_;
array _Refill_Due{99999} _temporary_;

n=0;
call missing(of _DOS{*} _Refill_Due{*});
do until(last.Strength);
set have;
by Mem_id Drug Strength notsorted;
n+1;
_DOS{n}=DOS;
_Refill_Due{n}=Refill_Due;
end;

do until(last.Strength);
set have;
by Mem_id Drug Strength notsorted;
flag=0;
do i=1 to n;
if DOS<_DOS{i}<Refill_Due and Refill_Due<_Refill_Due{i} then do;
flag=1;leave;
end;
end;
output;
end;
drop i n;
run;``````

🔒 This topic is solved and locked.