How do I calculate specific time lapses?

[Nabil Abrar]

Hello, Statalisters.
I am working with a prescription database, in which a patient may have one or more recorded prescriptions. A simple subset of the data is shown below. One entry is one prescription for one drug. I will be only including variables pertinent to this question.

* Example generated by -dataex-. To install: ssc install dataex
clear
input str12 mrn int(order_date end_date) byte(drug1 drug2 drug3 drug4 drug5)
"738491"       19071 19221 0 0 0 1 0
"738491"       19324 19414 0 0 0 1 0
"738491"       19522 19642 0 0 0 1 0
"738491"       19641 19761 0 0 0 1 0
"738491"       19792 19882 0 0 0 1 0
"738491"       19842 19962 0 0 0 1 0
"738491"       19942 20122 0 0 0 1 0
"738491"       19942 20122 0 0 1 0 0
"738491"       20011 20018 0 1 0 0 0
"738491"       20131 20311 0 0 0 1 0
"738491"       20131 20311 0 0 1 0 0
"738491"       20311 20491 0 0 1 0 0
"738491"       20311 20401 0 0 0 1 0
"738491"       20340 20347 0 1 0 0 0
"738491"       20340 20345 1 0 0 0 0
"738491"       20417 20424 0 1 0 0 0
"738491"       20417 20422 1 0 0 0 0
"738491"       20486 20666 0 0 1 0 0
"738491"       20486 20666 0 0 0 1 0
"738491"       20604 20609 1 0 0 0 0
"738491"       20604 20611 0 1 0 0 0
"738491"       20668 20848 0 0 1 0 0
"738491"       20824 21004 0 0 1 0 0
"461020"       19460 19463 1 0 0 0 0
"1457123"      21741 21748 1 0 0 0 0
"1387160"      21469 21476 1 0 0 0 0
"1465491"      21712 21726 1 0 0 0 0
"893079"       20506 20520 1 0 0 0 0
"1396608"      21378 21392 1 0 0 0 0
"1396608"      21385 21392 1 0 0 0 0
"1271983"      20817 20822 1 0 0 0 0
"HSNZ00381865" 20772 20832 1 0 0 0 0
"900277"       19304 19311 0 1 0 0 0
"HSNZ00078984" 18741 18761 0 0 0 1 0
"HSNZ00078984" 19263 19323 0 0 0 0 1
"HSNZ00078984" 19324 19354 0 0 0 0 1
end
format %td order_date
format %td end_date

(Apologies for the string format of the RN variable, I'm working with 2 different hospitals with different RN formats, and I'm afraid some of the RN entries will overlap if I remove the non-numeric characters).

I want to create a new variable for time lapses from the previous prescription to the current prescription. The currently working code that I have done, and the results, are something like this

* Example generated by -dataex-. To install: ssc install dataex
clear
by mrn (order_date): gen time_elapsed = order_date - end_date[_n-1]

input str12 mrn int(order_date end_date) byte(drug1 drug2 drug3 drug4 drug5) float time_elapsed
"738491"       19071 19221 0 0 0 1 0    .
"738491"       19324 19414 0 0 0 1 0  103
"738491"       19522 19642 0 0 0 1 0  108
"738491"       19641 19761 0 0 0 1 0   -1
"738491"       19792 19882 0 0 0 1 0   31
"738491"       19842 19962 0 0 0 1 0  -40
"738491"       19942 20122 0 0 0 1 0 -180
"738491"       19942 20122 0 0 1 0 0  -20
"738491"       20011 20018 0 1 0 0 0 -111
"738491"       20131 20311 0 0 0 1 0 -180
"738491"       20131 20311 0 0 1 0 0  113
"738491"       20311 20491 0 0 1 0 0  -90
"738491"       20311 20401 0 0 0 1 0    0
"738491"       20340 20347 0 1 0 0 0   -5
"738491"       20340 20345 1 0 0 0 0 -151
"738491"       20417 20424 0 1 0 0 0   -5
"738491"       20417 20422 1 0 0 0 0   70
"738491"       20486 20666 0 0 1 0 0   62
"738491"       20486 20666 0 0 0 1 0 -180
"738491"       20604 20609 1 0 0 0 0  -62
"738491"       20604 20611 0 1 0 0 0   -5
"738491"       20668 20848 0 0 1 0 0   57
"738491"       20824 21004 0 0 1 0 0  -24
"461020"       19460 19463 1 0 0 0 0    .
"1457123"      21741 21748 1 0 0 0 0    .
"1387160"      21469 21476 1 0 0 0 0    .
"1465491"      21712 21726 1 0 0 0 0    .
"893079"       20506 20520 1 0 0 0 0    .
"1396608"      21378 21392 1 0 0 0 0    .
"1396608"      21385 21392 1 0 0 0 0   -7
"1271983"      20817 20822 1 0 0 0 0    .
"HSNZ00381865" 20772 20832 1 0 0 0 0    .
"900277"       19304 19311 0 1 0 0 0    .
"HSNZ00078984" 18741 18761 0 0 0 1 0    .
"HSNZ00078984" 19263 19323 0 0 0 0 1  502
"HSNZ00078984" 19324 19354 0 0 0 0 1    1
end
format %td order_date
format %td end_date

(I don't mind negative results for the time_elapsed variable, as I will only be including the first entry of each patient (time_elapsed == .) and entries with time_elapsed > 365 in the subsequent analysis.)

After revising the methodology of my analysis, I decided that I need to have more specific time lapses. Specifically, for each entry, I need to have five new time lapse variables (let's call them time_lapse1 until time_lapse5. time_lapse1 will be the the time elapsed, in days, from the last prescription with drug 1 until the current prescription for each patient, time_lapse2 the time lapse from the last prescription with drug 2, time_lapse3 the time lapse from the last prescription with drug3, and so on. I am working with the 15.1 version of Stata, so I don't have access to the newest date-time functions in the 17 version. Can this be done? Thank you for your time.

[Clyde Schechter]

forvalues i = 1/5 {
    by mrn (order_date), sort: egen last_rx_date`i' = min(cond(drug`i', order_date, .))
    by mrn (order_date): replace last_rx_date`i' = ///
        cond(drug`i', order_date, last_rx_date`i'[_n-1])
    gen time_lapse`i' = order_date - last_rx_date`i'
}
format last_rx_date* %td

Thank you for using -dataex- and reporting your non-current version of Stata on your very first post!!