Identifying consecutive observations that meet a determined value

[Alan Kastner]

Hello Statalisters,

I am starting in the world of Stata and I would appreciate suggestions on how to approach the following. (Stata 14.2 IC for Mac)

I have a dataset with aprox. 60 numeric variables (pres_visit1 through pres_visit60) of pressure readings during consecutive visits to the doctor for about 500 patients. The dataset looks like this (resumed version):

	pres_visit1	pres_visit2	pres_visit4	pres_visit5	pres_visit6	pres_visit7
patient1	12	13	9	9	11	12
patient2	19	16	18	19	15	14
patient3	21	20	15	16	19	18
patient4	21	22	22	20	24	.
patient5	12	13	11	.	.	.
patient6	19	15	17	18	20	.

I need to identify, across variables "pres_visit1" to "pres_visit50" the first time a patient has 2 consecutive readings ≥17. The output could be generating a new variable "failure" ==1 when a patient experienced the event "2 consecutive readings above 17", and ideally, another variable displaying varname of the first of these 2 consecutive readings ≥17.

In other words, I need to evaluate for each patient, the first variable with a value of "≥17" and only if this is met, then determine if the immediate next variable is also "≥17" and repeat this until it finds the first pair of such qualifying values.

I greatly appreciate your time and knowledge,

Alan Kastner.

[Clyde Schechter]

The tableau you show is not your Stata data set, as it is not legal to have an unnamed variable. Also, you have no pres_visit3. If you really have gaps in the number sequence in your variables, I suggest you either renumber or fill them in with consecutively named variables having all missing values: your life will go considerably easier with consecutively numbered variables here.

I have modified your example data accordingly.

* Example generated by -dataex-. To install: ssc install dataex
clear
input str8 id byte(pres_visit1 pres_visit2 pres_visit3 pres_visit4 pres_visit5 pres_visit6)
"patient1" 12 13  9  9 11 12
"patient2" 19 16 18 19 15 14
"patient3" 21 20 15 16 19 18
"patient4" 21 22 22 20 24  .
"patient5" 12 13 11  .  .  .
"patient6" 19 15 17 18 20  .
end

forvalues i = 1/6 {
    gen pv_`i' = inrange(pres_visit`i', 17, .)
}
egen mashup = concat(pv_*), punct("")
gen first_failure = strpos(mashup, "11")

Evidently, replace the 6 in the -forvalues- command by the actual number of the last pres_visit variable.

In the future, when showing data examples, please use the -dataex- command to do so, as I have here. If you are running version 15.1 or a fully updated version 14.2, -dataex- is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.

When asking for help with code, always show example data. When showing example data, always use -dataex-.

[Clyde Schechter]

By the way, the above code produces a value of 0 for the first_failure variable if the patient never has two consecutive pressure measurements >= 17. You might prefer to have nothing recorded in the variable for that situation. If so, just add:

replace first_failure = . if first_failure == 0

[Alan Kastner]

Hello Clyde and thank you for your swift reply. I will certainly use the -dataex- command when presenting data in future posts. The relevance of this is further evidenced in the fact I "skipped" pres_visit3 in my example.

I will try the code you provided and keep you posted with the results.