Prescription Data

[cf8585]

Hello,

I have a large dataset that has a table set up like below.  I am trying to determine the average # of days each NDC is filled late i.e., for memberid drug x should be refilled on 2/1/2020 however it was filled on 2/15/10 so it was 14 days late, etc.  I need the average late time per each NDC.  Can someone please help me out with coding this?

Thanks

MemberID	Date	NDC	QuantityDispensed	DaySupply
1	1/1/2020	X	30	30
1	1/10/2020	Y	15	15
1	2/15/2020	X	30	30
1	3/19/2020	X	30	30
1	4/10/2020	Y	15	15
2	1/15/2020	X	180	180
2	3/18/2020	Y	30	30
2	4/15/2020	X	30	30
2	6/18/2020	Y	180	180

[HB]

I am not a good programmer.  This is very inelegant and not the best way.

I will assume the expected refill date is issue date plus days of supply. 

Given:

data scriptinfo;
   input MemberID issuedate:mmddyy10. NDC:$1. QuantityDispensed DaySupply;
datalines;
1 1/1/2020 X 30 30
1 1/10/2020 Y 15 15
1 2/15/2020 X 30 30
1 3/19/2020 X 30 30
1 4/10/2020 Y 15 15
2 1/15/2020 X 180 180
2 3/18/2020 Y 30 30
2 4/15/2020 X 30 30
2 6/18/2020 Y 180 180
;
run;

We can sort the data by patient, drug, and date. Then calculate an expire date and a dayslate.

proc sort data =  scriptinfo;
	by memberid ndc issuedate;
run;


data manipulate;
	set scriptinfo;
	by memberid ndc issuedate;
	expiredate = issuedate + daysupply;
	counter = first.ndc; *visual marker;
	dayslate = issuedate -lag(expiredate);
	if counter = 1 then dayslate = .;
	format issuedate date9. expiredate date9.;
run;

this gives us:

MemberID	issuedate	NDC	QuantityDispensed	DaySupply	expiredate	counter	dayslate
1	1-Jan-20	X	30	30	31-Jan-20	1	.
1	15-Feb-20	X	30	30	16-Mar-20	0	15
1	19-Mar-20	X	30	30	18-Apr-20	0	3
1	10-Jan-20	Y	15	15	25-Jan-20	1	.
1	10-Apr-20	Y	15	15	25-Apr-20	0	76
2	15-Jan-20	X	180	180	13-Jul-20	1	.
2	15-Apr-20	X	30	30	15-May-20	0	-89
2	18-Mar-20	Y	30	30	17-Apr-20	1	.
2	18-Jun-20	Y	180	180	15-Dec-20	0	62

indicating patient 1 was 15 and then 3 days late when refilling his X while patient 2 was 89 days early refilling her 180 day supply of x. 

You could probably average dayslate column by patient or by drug.  

Maybe this will get you closer to where you need to be.