Hello,

I have a data-set comprising multiple tables with different types of information. One table contains data on each patient's movements between various hospital units. It is of the form (note data is made up, not real patient data):
patient_id admit_unit admit_date discharge_unit discharge_date
1 ICU 1/1/2020 WARD 1/2/2020
1 WARD 1/2/2020 ICU 1/4/2020
1 ICU 1/4/2020 MORGUE 1/6/2020
2 ICU 2/1/2020 WARD 2/2/2020
2 WARD 2/2/2020 HOME 2/3/2020
Each patient can have a variable number of rows (practically ranges from 1 to 40) depending on how many unit transitions they have

I have a second table that is my master data-set that contains one observation for each patient i.e. one patient ID per row.

I am trying to identify for each patient the date of their first transition from ICU to WARD, and eventually pull this back into my main data-set. Note that the data in this unit transitions table is sorted by patient ID and then chronologically by unit transitions.

I am conceptualizing trying to do something of the form:

Code:
bysort patientid: gen obs = _n /* to create sub-levels for each patient ID */
generate ward_transfer = . /*create new variable to flag ward transfers */

quietly levelsof registryid, local(levels) /*store the unique values of patient IDs in a local macro called 'levels' */

foreach i of local levels { /*loop through each value of patient ID */
    quietly levelsof obs if registryid == "`i'", local(sublevels) /*for the specific patient id, get the number of unit transitions */
        
    foreach j of local sublevels { /*Loop over each sub-level of the current registry id */
        /*Here I am not sure what to do. I want something that captures the first occurrence of an admit to the ward for a patient and flags that observation with the ward_transfer variable, and then breaks out of this nested loop so that I can cycle to the next registry ID */
    }
}
I have a feeling I am conceptualizing this incorrectly (more used to thinking in SAS programming in the past) but I basically want the command in the second loop to go through each of the observations for that patient ID, in order, stop at the first one that says WARD in the admit unit, change the flag variable on that observation and then move on to the next patient ID. I would then be able to delete the observations that are not flagged and merge it with my master data-set using the patient ID field.

Thanks in advance for any guidance you can provide.

-Josh