06-08-2016 04:14 PM - edited 08-03-2016 07:55 PM
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!
06-08-2016 04:24 PM
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.
06-08-2016 04:28 PM
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.
06-08-2016 04:33 PM
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.
06-08-2016 05:21 PM
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.