loop for finding lowest value of one

[Greer Waldrop]

I am trying to find the value of "meas_value" (time point for meas value "RECORDED_TIME") at the closest time point to each time the patient has hypoxia (hypoxia_55_date#) I am able to do it with the code below, but I need to do this for up to 50 episodes per person with hypoxia and therefore I would like to create a loop instead of repeating this over and over.

gen diff6 = hypoxia_55_date6 - RECORDED_TIME

******6th hypoxia************
bysort person_id: generate absgap6 = abs(diff_6) if abs(diff_6)<=1
bysort person_id (absgap6 diff_6): gen MAP_HH6_count = _n
bysort person_id (absgap6 diff_6): gen nearestMAP_HH6 = meas_value if MAP_HH6_count==1
sort person_id nearestMAP_HH6
by person_id: fillmissing nearestMAP_HH6, with(first)
bysort person_id (absgap6 diff_6): gen disMAP_HH6 = absgap6 if MAP_HH6_count==1
sort person_id disMAP_HH6
by person_id: fillmissing disMAP_HH6, with(first)
order person_id hypoxia_55_date6 absgap6 nearestMAP_HH6 disMAP_HH6 meas_value diff_6

gen HH6_60 = 1 if nearestMAP_HH6 <60 & hypoxia_55_date6 !=.
sort person_id HH6_60
by person_id: fillmissing HH6_60, with(first)
replace HH6_60 = 0 if HH6_60 ==. & hypoxia_55_date6 !=.
tab HH6_60 if MAP_amount ==1
count if hypoxia_55_date6 !=. & MAP_amount ==1

******7th hypoxia************
bysort person_id: generate absgap7 = abs(diff_7) if abs(dif_7)<=1
bysort person_id (absgap7 dif_7): gen MAP_HH7_count = _n
bysort person_id (absgap7 dif_7): gen nearestMAP_HH7 = meas_value if MAP_HH7_count==1
sort person_id nearestMAP_HH7
by person_id: fillmissing nearestMAP_HH7, with(first)
bysort person_id (absgap7 dif_7): gen disMAP_HH7 = absgap7 if MAP_HH7_count==1
sort person_id disMAP_HH7
by person_id: fillmissing disMAP_HH7, with(first)
order person_id hypoxia_55_date7 absgap7 nearestMAP_HH7 disMAP_HH7 meas_value dif_7

gen HH7_60 = 1 if nearestMAP_HH7 <60 & hypoxia_55_date7 !=.
sort person_id HH7_60
by person_id: fillmissing HH7_60, with(first)
replace HH7_60 = 0 if HH7_60 ==. & hypoxia_55_date7 !=.
tab HH7_60 if MAP_amount ==1
count if hypoxia_55_date7 !=. & MAP_amount ==1

[Nick Cox]

Without a data example, it's likely to be hard work for anyone to be able and willing to read through this and get a really precise image of what is going on. I note also use of fillmissing which is a community-contributed command from somewhere. See FAQ Advice #12 on your being asked to explain that. See also https://www.stata.com/support/faqs/d...issing-values/ for how most problems of this kind don't need a separate command to be installed at all.

We need please a data example and for once it probably should not be real data (confidentiality issues even aside) but just simplified data with similar structure.

My guess is that your data may have this form

* Example generated by -dataex-. To install: ssc install dataex
clear
input float(id date meas which)
1 20000  . 1
1 20020 42 2
1 20030  . 1
1 20040 66 2
1 20070 78 2
1 20100  . 1
2 20000  . 1
2 20020  . 1
2 20030 12 2
2 20040  . 1
2 20070 13 2
2 20100  . 1
end
label values which which
label def which 1 "event", modify
label def which 2 "measurement", modify

in which patients have hypoxia events on some dates and measurements of something on some other dates.

That's certainly the form I would want to see the data. If so, then you just need to interpolate using the nearest available value, which is a known problem with a one-line solution. See https://www.statalist.org/forums/for...-interpolation

No loops are needed so far as I can see.

If my guesses are way off, sorry, but that just shows how we, or at least I, need a data example to be clear on the problem flavour.

[Greer Waldrop]

Thank you so much for your willingness to help and I apologize for my unclear/inelegant coding..

The data is set up like this
Id Meas Meas_time($tc) Hypoxia_date_1 (%tc) Hypoxia_date_2 (%tc) Diff_1 Diff_2
1 55 sept 10 2020 08:12:45 sept 10 2020 18:04:45 sept 12 2020 08:04:45
1 82 sept 10 2020 12:12:05 sept 10 2020 18:04:45 sept 12 2020 08:04:45
1 12 sept 10 2020 20:04:45 sept 10 2020 18:04:45 sept 12 2020 08:04:45
1 45 sept 11 2020 08:12:45 sept 10 2020 18:04:45 sept 12 2020 08:04:45
1 90 sept 11 2020 11:12:56 sept 10 2020 18:04:45 sept 12 2020 08:04:45
2 45 sept 19 2020 08:12:45 sept 19 2020 18:04:45 sept 20 2020 08:04:45
2 52 sept 19 2020 12:12:05 sept 19 2020 18:04:45 sept 20 2020 08:04:45
2 102 sept 19 2020 20:04:45 sept 19 2020 18:04:45 sept 20 2020 08:04:45
2 45 sept 20 2020 08:12:45 sept 19 2020 18:04:45 sept 20 2020 08:04:45
2 80 sept 20 2020 11:12:56 sept 19 2020 18:04:45 sept 20 2020 08:04:45


Where Diff_1 = meas time - hypoxia_date_1/86400000
Diff_2 meas_time - hypoxia_date_2/86400000

So the first step is identifying which meas value recorded is closest to each hypoxia date by time:
*This should make all tie diff values positive
bysort person_id: generate absgap6 = abs(diff_6) if abs(diff_6)<=1
*then i sort by absgap6 which will put the lowest (closest) value at the top and label the values in that order
bysort person_id (absgap6 diff_6): gen MAP_HH6_count = _n
*then create a new variable that give the meas value at the closest time to hypoxia_date
bysort person_id (absgap6 diff_6): gen nearestMAP_HH6 = meas_value if MAP_HH6_count==1
*then fill it in for all rows per person
sort person_id nearestMAP_HH6
by person_id: fillmissing nearestMAP_HH6, with(first)
*then i do the same thing but for the amount of time between hypoxia and that closest meas_value
bysort person_id (absgap6 diff_6): gen disMAP_HH6 = absgap6 if MAP_HH6_count==1
sort person_id disMAP_HH6
by person_id: fillmissing disMAP_HH6, with(first)

**Next i want to dicotomize the actual mea_value that is closet to the hypoxia date
so i dicotomize it by meas_value <60 and fill that in for all rows of the person
gen HH6_60 = 1 if nearestMAP_HH6 <60 & hypoxia_55_date6 !=.
sort person_id HH6_60
by person_id: fillmissing HH6_60, with(first)
replace HH6_60 = 0 if HH6_60 ==. & hypoxia_55_date6 !=.
tab HH6_60 if MAP_amount ==1
count if hypoxia_55_date6 !=. & MAP_amount ==1



The thing is I have hypoxia_dates that can be over 50 per person so wondering if there is a way to standardize this code, upon my review of the interpolation... not sure how to make it fit my data-- but will look closer over the coming days.
thank you again for being willing to help!

[Nick Cox]

Thanks for the details, but please use dataex and CODE delimiters in presenting example data and code. https://www.statalist.org/forums/help#stata explains.