Calculating differences in dates of consecutive patient visits

[Laurel Redding]

Hello!

I have a dataset of medical records where every row of data is a pharmacy dispensation for a certain patient-visit. I would like to create a variable that tells me how many days have occurred between consecutive visits of a patient and a second variable that tells me how many days have elapsed between the patient's first and last visit.

My data is set up as follows: every patient has an ID#, and multiple visit numbers with a specific discharge date.
input str6 animal_number byte visit_num2 str21 dischargedate
"767237" 52 " 3/24/2013"
"767237" 52 " 3/24/2013"
"767237" 52 " 3/24/2013"
"767237" 52 " 3/24/2013"
"767237" 52 " 3/24/2013"
"767237" 54 " 3/31/2013"
"767237" 54 " 3/31/2013"
"767237" 54 " 3/31/2013"
"767237" 54 " 3/31/2013"
"767237" 54 " 3/31/2013"
"767237" 54 " 3/31/2013"
"767237" 54 " 3/31/2013"
"767237" 54 " 3/31/2013"
"767237" 54 " 3/31/2013"
"767237" 54 " 3/31/2013"
"767237" 54 " 3/31/2013"
"767237" 54 " 3/31/2013"
"767237" 54 " 3/31/2013"
"767237" 54 " 3/31/2013"
"841194" 20 " 2/3/2017"
"841194" 20 " 2/3/2017"
"841194" 20 " 2/3/2017"
"841194" 20 " 2/3/2017"
"841194" 22 "2/13/2017"
"841194" 28 " 5/9/2017"
"841194" 28 " 5/9/2017"


I've been able to generate the difference between the visit number of the last and first visits by doing this:
bys animal_number: egen last_visit=max(visit_num2)
bys animal_number: egen initial_visit=min(visit_num2)
bys animal_number: gen difference_in_visits=last-initial

but now I would like to be able to do this using the specific dates and also be able to do this with consecutive visits, not just the first and last.

Many thanks for any suggestions!

LR

[Nick Cox]

This may help:

clear 
input str6 animal_number byte visit_num2 str21 dischargedate
"767237" 52 " 3/24/2013"
"767237" 52 " 3/24/2013"
"767237" 52 " 3/24/2013"
"767237" 52 " 3/24/2013"
"767237" 52 " 3/24/2013"
"767237" 54 " 3/31/2013"
"767237" 54 " 3/31/2013"
"767237" 54 " 3/31/2013"
"767237" 54 " 3/31/2013"
"767237" 54 " 3/31/2013"
"767237" 54 " 3/31/2013"
"767237" 54 " 3/31/2013"
"767237" 54 " 3/31/2013"
"767237" 54 " 3/31/2013"
"767237" 54 " 3/31/2013"
"767237" 54 " 3/31/2013"
"767237" 54 " 3/31/2013"
"767237" 54 " 3/31/2013"
"767237" 54 " 3/31/2013"
"841194" 20 " 2/3/2017"
"841194" 20 " 2/3/2017"
"841194" 20 " 2/3/2017"
"841194" 20 " 2/3/2017"
"841194" 22 "2/13/2017"
"841194" 28 " 5/9/2017"
"841194" 28 " 5/9/2017"
end 

gen betterdate = daily(dischargedate, "MDY") 

bysort animal_number visit_num2 (betterdate) : assert betterdate[1] == betterdate[_N]

by animal_number : gen difference = betterdate - better[_n-1] if betterdate > better[_n-1] 

list if difference < ., sepby(animal_number)

     +--------------------------------------------------------+
     | animal~r   visit_~2   discharg~e   better~e   differ~e |
     |--------------------------------------------------------|
  6. |   767237         54    3/31/2013      19448          7 |
     |--------------------------------------------------------|
 24. |   841194         22    2/13/2017      20863         10 |
 25. |   841194         28     5/9/2017      20948         85 |
     +--------------------------------------------------------+

[Laurel Redding]

Thank you for your reply!
When I run this code, I get the following error message:

bysort animal_number visit_num2 (betterdate) : assert betterdate[1] == betterdate[_N]
1 contradiction in 135 by-groups
assertion is false

Any suggestions on what this means or how I fix this?

[Nick Cox]

It means that you have inconsistent dates for that group, so have a look.

bysort animal_number visit_num2 (betterdate) : gen looksbad = betterdate[1] != betterdate[_N]

edit if looksbad