Calculating difference between 2 dates with limits

[Matthew Milano]

I want to keep individuals who have 2 consecutive prescriptions of drug X within 90 days. Then use the 2nd date as a variable to use for my start of follow up for further analysis.

I have used the following command -by id (rx_date), sort: gen count=_n- to count the number of prescriptions per unique ID. You will see from the -dataex- example below that some id's have 3 prescriptions. As long as the first 2 dates are within 90 days of each other I can drop the 3rd prescription.

Thank you in advance for any help.

* Example generated by -dataex-. For more info, type help dataex
clear
input byte id int rx_date str1 drug byte count
 1 17303 "X" 1
 1 17322 "X" 2
 1 17336 "X" 3
 2 17367 "X" 1
 2 17478 "X" 2
 3 17541 "X" 1
 3 17576 "X" 2
 3 17633 "X" 3
 4 17695 "X" 1
 4 17814 "X" 2
 5 17422 "X" 1
 5 17842 "X" 2
 5 17905 "X" 3
 6 17960 "X" 1
 6 18025 "X" 2
 6 18057 "X" 3
 7 18113 "X" 1
 8 14655 "X" 1
 8 14704 "X" 2
 9 14732 "X" 1
 9 14857 "X" 2
10 17755 "X" 1
end
format %td rx_date

[Ken Chui]

You can use something like this to calculate the day elapsed between rows:

bysort id (rx_date): gen elapsed = rx_date - rx_date[_n - 1]

Then, keep the short Rx, and keep only the first one if there were multiples per ID:

* Keep if difference <= 90
keep if elapsed <= 90

* Keep the first instance if there were multiple per id
bysort id (rx_date): gen short = _n
keep if short == 1

[Matthew Milano]

Thank you Ken,

Both lines of code worked perfectly. The only additional thing I did was -drop if count>2 - after calculating the days elapsed between rows. So I could only work with the difference between the 1st and 2nd prescriptions.

Thanks again!