finding the last encounter date before a censoring date in longform data

[Ann Miller]

Hi--I have a study looking at healthcare encounters before and after an intervention. I'm trying to determine if (and how many) study participants were still being seen for patient encounters after the study formally closed data collection. I want to identify the last date a participant had an encounter ("HA_Contact_Date_HAcontactsthro"= encounter date) before the study close out date (March 15, 2024), and then if they had visits after the close out date. Data are in long form; I am using Stata 15.1 (but in process of upgrading to 18).

* Example generated by -dataex-. To install: ssc install dataex
clear
input str9 PatientID int HA_Contact_Date_HAcontactsthro float(close_date last_before_march last_before_march24)
"1" 23359 23450 0 23548
"1" 23377 23450 0 23548
"1" 23394 23450 0 23548
"1" 23422 23450 0 23548
"1" 23485 23450 .     .
"1" 23498 23450 .     .
"1" 23513 23450 .     .
"1" 23519 23450 .     .
"1" 23548 23450 .     .
"2" 23348 23450 0 23485
"2" 23355 23450 0 23485
"2" 23380 23450 0 23485
"2" 23411 23450 0 23485
"2" 23418 23450 0 23485
"2" 23428 23450 0 23485
"2" 23432 23450 0 23485
"2" 23439 23450 0 23485
"2" 23440 23450 0 23485
"2" 23446 23450 0 23485
"2" 23462 23450 .     .
"2" 23467 23450 .     .
"2" 23474 23450 .     .
"2" 23481 23450 .     .
"2" 23485 23450 .     .
end
format %tdnn/dd/CCYY HA_Contact_Date_HAcontactsthro
format %td close_date
format %td last_before_march24

I tried this:
bysort PatientID:generate last_before_march=(_n==_N) if HA_Contact_Date_HAcontactsthro<23450
this:
bysort PatientID (HA_Contact_Date_HAcontactsthro):generate last_before_march24=HA_Contact_Date_HAcontactsthro[_N] if HA_Contact_Date_HAcontactsthro<23450
and this:
generate close_date=23450
bysort PatientID:generate last_before_march24=HA_Contact_Date_HAcontactsthro[_N] if HA_Contact_Date_HAcontactsthro< close_date

but they all repeat a similar error; when the variable HA_Contact_Date_HAcontactsthro is <23450 it gives me the final contact date by participant (or a 0, if I'm comparing to the close_date), and when it is not, it gives me a blank. What I *think* I want is for the variable "last_before_march24" to list the date that, for each person, was the last HA_Contact_Date_HAcontactsthro before 23450. But would love advice if there's a simpler way too. What should I be doing differently to determine which record is the last before the cutoff date?

Many thanks!


Ann Miller

[Ann Miller]

UPDATE: I found a solution looking at the problem differently and finding something similar in another post:

sort PatientID ( HA_Contact_Date_HAcontactsthro )
bysort PatientID : generate gap = HA_Contact_Date_HAcontactsthro - close_date
bysort PatientID : generate absgap = abs(gap)
bysort PatientID: egen mindiff=min(abs(gap)) if gap<0
generate nearest_pre=1 if absgap==mindiff & absgap~=.

This gives me a "nearest_pre" variable that shows which of the many encounters was the closest before that date.

[Clyde Schechter]

local closeout_date = td(15mar2024)

by patient_id (contact_date), sort: egen last_pre_closeout_date = ///
    max(cond(contact_date >= `closeout_date', ., contact_date))
format last_pre_closeout_date %tdnn/dd/CCYY
by patient_id: egen had_post_closeout_contact = max(contact_date > `closeout_date')

Note: I have changed the names of your variables because I just found HA_Contact_Date_HAcontactsthro too cumbersome to type. You can either similarly rename your variables to match the code, or change the variable names in the code to match your data set.

Added: Crossed with #2.