Calculate how many days patients have had medication in year (excluding overlapping dates)

Hi,

I have tried to solve this problem by modifying similar codes I found from this forum but so far without success. I need to calculate the time each patient (A1=Patient id) have had medication in each year 2011-2017. Unfortunately the data is not perfect and there are overlapping dates (even though there should not be) and quite many cases are missing the end date.

It is not possible for me to know for sure if the missing end date is there on purpose (=the medication is on) or if they forgot to enter the end date to the medical records. For now, I will assume that the mediation is still on if there is no end date and new mediation phase has not been started for the patient. Additionally, the last medication phase should end at time the person dies (D1=Time of death).

I have managed to calculate the time in days for each year (2011_days_sum, 2012_sum, etc.), but the overlapping dates were causing issues. I have manually fixed this sample data to look somewhat how it should look, though there may be mistakes.


Best regards

Teppo

This looks very promising. I will test it with the real data to see how it works. Thank you very much so far!

I tested the code in my data and it works most of the time quite nicely. The sample data in my first post was a bit small and not very well chosen. It seems there are some strange results there with original data. For example, days for patient id 570 (now added to the sample data) did not calculate correctly. I tried to read the “monster code”, but so far, I have not figured out what is wrong. (Could be a user error as well?)

Another issue is the remaining overlapping dates.
In the original sample data I had also the sums of days per patient, (2011_days_sum etc.), because I need to know how many days a patient has had the medication in each year and I can’t just sum up the days as the dates are sometimes overlapping. Earlier I tried to calculate just the number of overlapping dates and subtract it from the aggregated sum, but failed with that.

Thank you so far for the help and very quick response. I really appreciate it.