Hi!
I need some help with identifying if a patient has a prescription for a drug 6 months before the first fill date for the same drug. What is challenging here is that each patient will have a different first fill date and hence, the 6 month look back period will be different for each patient.
For example, for the year 2015 - one patient 1 has their first fill on January 5th while patient 2 has their first fill on March 30. For patient1 I will have to look back to July 5th while for patient 2 I will have to look back upto September 30.
Currently my data is in this format -
Patient ID Medication Fill date
1 Med1 01/23
1 Med2 5/22
1 Med3 ..
1 Med4 ..
2 Med1 ..
2 Med2 ..
3 Med1 ..
4 Med1 ..
4 Med2 ..
I can transpose the data and make it wide, but what I am struggling most with is identifying the different 6 month look back period for each patient from the fill date
Any help will be much appreciated!
Thank you!
This is probably a job for INTNX to find the start date of fill-6months. If you post representative data and desired output you will get better help.
I already have the start date (Fill date) - I am not sure how to code so that the 6 month look back period is different for each patient.
Look at the documentation for INTNX you will find how to find a date 6 months in the past.
If you post some representative data we can show example.
You don't need to change your data structure.
You use what's called a self join. Join table to itself with the join condition being that the dates are within 6 months given ID and drug match. As mentioned INTNX will calculate the interval, but note that 6 months is not the same duration so you may want to define a fixed number of days rather than 6 months, ie 180 day?
If you post sample input/output data for a code sample make sure to include the case where you have multiple dates for the same med, within the specified duration , ie patient 1 has med1 6 times in 6 months - monthly prescription.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.