Differencing First and Last Entry in Panel

[Stephen Ch]

Consider the following blood pressure log panel data:

* Example generated by -dataex-. For more info, type help dataex
clear
input double logtime_01 float(patientid sys dia)
2.0213388e+12 100 140  82
2.0213928e+12 100 127  70
 2.020662e+12 101 120  68
  2.02077e+12 101 150  91
2.0232576e+12 102 155  93
2.0214108e+12 103 160 100
2.0214108e+12 103 120  69
2.0213028e+12 104 117  68
end
format %tc logtime_01

The sample period is Januar/1/2024 to March/30/2024.

I would like to do the following:

(1) For each patient, I want to the starting and ending date of each log. For example, patient 101 might have logged for the entire sample twice a day its BP reading. Patient 104 might have logged twice a day starting mid-January and had a surgery and got disrcharged and the log stops mid February.

(2) For each patient, I want to compute the difference in its systolic and diastolic measures of the first entry and last entry (so two separate variables) in percents.

The purpose of this exercise is similar to what you would see in survival analysis. If a patient's log stops within the sample period, it means either the patient is deceased or was discharged from the hospital (after stabilization), and I want to know track the hospital entry-BP readings and the end-BP readings to follow through their progress.

Help is much appreciated!

[Benno Schoenberger]

Hi Stephen,
if I got you right you only want to compare a patients frst and last BP reading and compute differences for these two, right?

Then try the following:

bysort patientid (logtime_01): keep if _n==1 | _n==_N        // keep first and last observation per patient
bysort patientid (logtime_01): gen nevent =_n                // gen observation numbers for reshaping
reshape wide logtime sys dia, i(patientid) j(nevent)

rename (*1 *2 ) (*_start *_end)            // rename variables to _start and _end

foreach bd in sys dia {
    gen `bd'_pct = round((`bd'_end-`bd'_start)/`bd'_start,.01) if !missing(`bd'_end) // calculate percentage change
}

Regards
Benno

[Stephen Ch]

Hi Benno,

This is quite helpful, but does the code restrict the analysis to only those who dropped out "before" the sample end date?

In other words, I want to perform this analysis on patients who either were deceased so no longer in the sample or were discharged so no longer in the sample.

Just wanted to check, but your code doesn't seem to have this condition.

Hi Stephen,
if I got you right you only want to compare a patients frst and last BP reading and compute differences for these two, right?

Then try the following:

bysort patientid (logtime_01): keep if _n==1 | _n==_N // keep first and last observation per patient
bysort patientid (logtime_01): gen nevent =_n // gen observation numbers for reshaping
reshape wide logtime sys dia, i(patientid) j(nevent)



rename (*1 *2 ) (*_start *_end) // rename variables to _start and _end

foreach bd in sys dia {
gen `bd'_pct = round((`bd'_end-`bd'_start)/`bd'_start,.01) if !missing(`bd'_end) // calculate percentage change
}

Regards
Benno

[Benno Schoenberger]

Your example data does not contain any information about whether a person died or was discharged, so I assumed that one of the two events is the cause of the people not having further blood pressure measurements. So if there are blood pressure measurements from people who have already died or been discharged - as strange as this would seem to me - then you need a variable to clearly identify these events.
If you want to build your analysis sample based on other conditions or covariates, then you must also provide other example data.

[Stephen Ch]

There seems to be a misunderstanding.

If my sample period is 1/1/2024 to 3/30/2024 and a patient appeared several times but dropped before 3/30/2024, I presuppose that they are either deceased or discharged from the hospital.

Within these patients, I want to compute the starting and final BP readings. Apologies if this was unclear.

Your example data does not contain any information about whether a person died or was discharged, so I assumed that one of the two events is the cause of the people not having further blood pressure measurements. So if there are blood pressure measurements from people who have already died or been discharged - as strange as this would seem to me - then you need a variable to clearly identify these events.
If you want to build your analysis sample based on other conditions or covariates, then you must also provide other example data.

[Benno Schoenberger]

It's difficult for me to fully understand your entire project based on the 8 observations of your sample data.
If "sample period" means the period in which your data was created, then my syntax does exactly what you described. If your data also includes measurements from outside your sample period and you only want to look at measurements within your sample period, then you would just have to drop all cases whose date is outside the time window you are interested in.
But as I said, based on your very sparse example data, I probably just can't imagine it correctly.