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 |
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.
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.
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
For SAS newbies, this video is a great way to get started. James Harroun walks through the process using SAS Studio for SAS OnDemand for Academics, but the same steps apply to any analytics project.
Find more tutorials on the SAS Users YouTube channel.