Keeping only first and last obs of the day

[Stephen Ch]

Hi folks, consider the following data for which it logs blood pressure measures.

For each patientid, I want to keep only the earliest and latest BP readings of EACH DAY and disregards anything in between INTRADAY. So, for each day, I want to have only two observations of BP readings per patient. What if I want to keep three records (first, middle, last)?

For each patientid, how can I find how many logs each patient has recorded per day to see the summary statistics?

How would I go about doing this?

Many thanks!

 * Example generated by -dataex-. For more info, type help dataex clear input byte patientid str14 time int sys byte dia
1 "2023-11-17 23 " 123 77
1 "2023-11-18 08 " 135 80
1 "2023-11-18 15" 128 70
2 "2023-11-11 23 " 117 68
2 "2023-11-18 08 " 160 100
3 "2023-11-05 23 " 155 90
4 "2023-11-21 23 " 140 88
4 "2023-11-22 07 " 121 72
4 "2023-11-23 15" 120 70
end

[Mike Lacy]

* Example generated by -dataex-. To install: ssc install dataex
clear
input byte patientid str14 time int sys byte dia
1 "2023-11-17 23" 123  77
1 "2023-11-18 08" 135  80
1 "2023-11-18 15" 128  70
2 "2023-11-11 23" 117  68
2 "2023-11-18 08" 160 100
3 "2023-11-05 23" 155  90
4 "2023-11-21 23" 140  88
4 "2023-11-22 07" 121  72
4 "2023-11-23 15" 120  70
end

To do what you want, you first need to translate your time string into a numerical variable. Nothing (?) useful can be done in Stata with times and dates represented as strings. You can then work with that numeric time variable to mark the first and last observations within each patientid. (I'm assuming that the last digit group in your time string is the hour of the day.) You can then use that -touse- variable to select the observations of interest for dropping or inclusion in statistical commands.

gen double ntime = clock(time, "YMDh")  // numeric time variable
format ntime %tc 
bysort patientid (ntime): gen byte touse  = (_n ==1) | (_n == _N) 
list
summarize sys if touse // command applied to just the observations of interest
// drop if touse == 0 // you can drop the observations you don't want

To understand what's going on here, look at -help date-. If you're going to work with data involving dates and times, you'll find a lot of important information there. You'll also need to learn about the -by- command and the _n system variable. See -help by- and -help _n.

[Stephen Ch]

Hi Mike,

This was quite helpful. Thank you. Clyde actually made a similar comment, and I think it is a good practice to conver strings to date variables.

if I want to keep three observations (e.g., assuming the log is at least 3 records, morning, lunch, dinner): would this work:

bysort patientid (ntime): gen byte touse = (_n ==1) | (_n==median(_N)) | (_n == _N)

* Example generated by -dataex-. To install: ssc install dataex
clear
input byte patientid str14 time int sys byte dia
1 "2023-11-17 23" 123 77
1 "2023-11-18 08" 135 80
1 "2023-11-18 15" 128 70
2 "2023-11-11 23" 117 68
2 "2023-11-18 08" 160 100
3 "2023-11-05 23" 155 90
4 "2023-11-21 23" 140 88
4 "2023-11-22 07" 121 72
4 "2023-11-23 15" 120 70
end

To do what you want, you first need to translate your time string into a numerical variable. Nothing (?) useful can be done in Stata with times and dates represented as strings. You can then work with that numeric time variable to mark the first and last observations within each patientid. (I'm assuming that the last digit group in your time string is the hour of the day.) You can then use that -touse- variable to select the observations of interest for dropping or inclusion in statistical commands.

gen double ntime = clock(time, "YMDh") // numeric time variable
format ntime %tc
bysort patientid (ntime): gen byte touse = (_n ==1) | (_n == _N)
list
summarize sys if touse // command applied to just the observations of interest
// drop if touse == 0 // you can drop the observations you don't want

To understand what's going on here, look at -help date-. If you're going to work with data involving dates and times, you'll find a lot of important information there. You'll also need to learn about the -by- command and the _n system variable. See -help by- and -help _n.

[Stephen Ch]

Hi Mike,

Your suggestion was quite helpful, but it doesn't get the first and last of each day, instead it averages out for the entire time period.

How would I compute the first and last BP reading of EACH day by patientid?

Thanks in advance.

* Example generated by -dataex-. To install: ssc install dataex
clear
input byte patientid str14 time int sys byte dia
1 "2023-11-17 23" 123 77
1 "2023-11-18 08" 135 80
1 "2023-11-18 15" 128 70
2 "2023-11-11 23" 117 68
2 "2023-11-18 08" 160 100
3 "2023-11-05 23" 155 90
4 "2023-11-21 23" 140 88
4 "2023-11-22 07" 121 72
4 "2023-11-23 15" 120 70
end

To do what you want, you first need to translate your time string into a numerical variable. Nothing (?) useful can be done in Stata with times and dates represented as strings. You can then work with that numeric time variable to mark the first and last observations within each patientid. (I'm assuming that the last digit group in your time string is the hour of the day.) You can then use that -touse- variable to select the observations of interest for dropping or inclusion in statistical commands.

gen double ntime = clock(time, "YMDh") // numeric time variable
format ntime %tc
bysort patientid (ntime): gen byte touse = (_n ==1) | (_n == _N)
list
summarize sys if touse // command applied to just the observations of interest
// drop if touse == 0 // you can drop the observations you don't want

To understand what's going on here, look at -help date-. If you're going to work with data involving dates and times, you'll find a lot of important information there. You'll also need to learn about the -by- command and the _n system variable. See -help by- and -help _n.

[Mike Lacy]

Sorry, I erred in overlooking your request about "each day." Also, to have some data in which "middle" is meaningful (about which see below), I created a new data item.

clear
input byte patientid str14 time int sys byte dia
1 "2023-11-17 23" 123 77
1 "2023-11-18 08" 135 80
1 "2023-11-18 15" 128 70
1 "2023-11-18 10" 119 85  // new item
2 "2023-11-11 23" 117 68
2 "2023-11-18 08" 160 100
3 "2023-11-05 23" 155 90
4 "2023-11-21 23" 140 88
4 "2023-11-22 07" 121 72
4 "2023-11-23 15" 120 70
end

Anyway, for looking at each day, it's convenient to have separate variables for the daily date and the hour of the day. Try this:

// Pick date and hour out of the time string and translate each to numeric.
gen double daydate = date((word(time),1), "YMD")
format daydate %td
gen byte hour = real(word(time),2)
bysort patientid daydate (hour): gen byte first = _n ==1
bysort patientid daydate (hour): gen byte last = _n == _N
bysort patientid daydate: gen nmeasure = _N // Number of measurements on each patient day.
list   // inspect to see if this is what you want.
//
// Descriptive stats for mean systolic BP for first and last measurement of each patient-day,
// which *might* be something you want. Note that first may also be last.
summ sys if first
summ sys if last

Your request about "middle" presents several issues: 1) To have a "middle" measure, I'd think you'd need to have at least three measurements in a day, which your example does not show, so I created one such patient-day. 2) What would you want for "middle" if there are an even number of measurements in a day? 3) What do you want for middle if there is only 1 or 2 measurements in a day? You'll have to decide what to do about 1, 2, 3, but here's an illustration of technique:

// Define middle as: 1) missing if nmeasurme <= 2; 2) observation at the median time position of if nmeasure is odd;
// 3) observation at the nmeasure/2 time position if nmeasure is even
by patientid daydate (hour): ///
   gen byte middle = (_n == ceil(nmeasure/2)) if (nmeasure > 2)

"in between INTRADAY"
I don't know what this means so I ignored it.

[Stephen Ch]

Hi Mike, this was helpful. Thanks. I basically created another date similar to what you did (MM/DD/YY) and did bysort patientid daydate. Thanks for your suggestion on getting "the middle".