calculating mean dose taken for the first 6 months of therapy

[Banke]

Hello everyone, I need your help please:

I want to calculate the average daily dose (ADD) for each patient (= quantity_supplied/days_supplied) for the first 6 months of prescription fills (fill_date), such that for each patient, I have a single value of ADD that corresponds to the average number of tablets they took per day from the first time they received the medication (index date) till up to 6 months.

Your help will be greatly appreciated. Thank you

patient_id	fill_date	index_date	quantity_supplied	days_supplied
1	01/11/2018	01/11/2018	140	30
1	01/12/2018	01/11/2018	140	30
1	01/22/2018	01/11/2018	160	30
1	02/16/2018	01/11/2018	90	90
1	03/19/2018	01/11/2018	90	90
1	06/18/2018	01/11/2018	90	90
2	02/12/2017	02/12/2017	120	30
2	03/23/2017	02/12/2017	120	30
2	06/14/2017	02/12/2017	90	90
2	07/30/2017	02/12/2017	90	90
2	08/31/2017	02/12/2017	60	60

[PaigeMiller · Accepted solution]

So just to make sure I understand completely, for patient 1 we would sum all of the quantity_supplied and then divide by the sum of days_supplied, because the last record 6/18/2018 is less than 6 months after the index date. For patient 2 we would do the same thing with all records except the record for 8/31/2017 because this is more than 6 months after the index date.

Is this correct?

If so, try this (UNTESTED CODE):

proc summary data=have(where=(fill_date<intnx('month',index_date,6,'s'))) nway;
    class patient_id;
    var quantity_supplied days_supplied;
    output out=sums sum=;
run;
data want; 
    set sums;
    add = quantity_supplied/days_supplied;
run;

This assumes that your date values are valid numeric SAS date values (representing the integer number of days since 01JAN1960). Otherwise, the code will not work.

[Banke]

Yes, you are correct. It worked perfectly. Thank you so much!