how do i identify if member had early re-fill

[sasuser77]

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

.

.

.

Thanks in advance for your your help

[ballardw]

"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"?

[sasuser77]

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

[ballardw]

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

[sasuser77]

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.

[ChrisNZ]

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;

[sasuser77]

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

[ChrisNZ · Accepted solution]

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;

[sasuser77]

This is perfect. Thank you so much!

[Ksharp]

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;