Exclusions in a loop

[Maximilian Mayrhofer]

Hi everyone, just upfront: I am pretty new to Stata so please excuse certain rookie mistakes that might occur.

I have a long format dataset showing patients with their date of surgery and postoperative pain scores taken at postoperative visits (in no strict intervals).
The variable for the pain scores is labeled score_pain_* (* being the number of the visit up to 50), I already created a variable showing the time between surgery and the visit in days time_surgerytopainscore* (* again being the number of the visit up to 50).

Now I want to find the patients who reported a pain score of 3 or lower consecutively over any interval equal to or bigger than 3 months (91.3125 days).

So I put together the following code with i being the first visit where a score =< is reported and j any score <= after 3 months or more. However I want to exclude the patients who reported a score higher than 3 in between those visits i and j.
The option with k I tried includes patients who report a score <=3 at any visit in between i and k but that does not solve my problem of excluding those patients who report anything higher (there might be multiple visits in between i and j) and it excludes the patients who did not have any visits in between i and j.


forvalues i=1/50 {
forvalues j=1/50 {
forvalues k=1/50 {
tab record_number if score_pain_`i' <= 3 & score_pain_`j' <= 3 & score_pain_`i+1' <= 3 & ((time_surgerytopainscore`j' - time_surgerytopainscore`i') >= 91.3125) & ((time_surgerytopainscore`j' - time_surgerytopainscore`i') > (time_surgerytopainscore`j' - time_surgerytopainscore`k'))
}
}
}

I would be very thankful for any hints how to get the exclusion of patients reporting scores > 3 in between the visits i and j.
Thank you!

[Clyde Schechter]

You say you have data in long layout, but what you describe, with each visit's pain_score having a separate variable is wide layout. And if it is wide, that is a huge obstacle to solving this problem. It would have been much more helpful to actually show example data, so I could test the code, but here's more or less how I would do this:

//  FIRST GO TO LONG LAYOUT
reshape wide score_pain_ time_surgerytopainscore, i(record_number) j(_j)
drop if missing(score_pain_, time_surgerytopainscore)

//  IDENTIFY RUNS OF SCORE_PAIN RELATIVE TO 3
gen byte low_pain_score = score_pain_ <= 3
by record_number (time_surgerytopainscore), sort: ///
    gen int run_num = sum(low_pain_score != low_pain_score[_n-1])
    
//  NOW IDENTIFY RECORD NUMBERS WHERE THERE IS AT LEAST ONE RUN
//  3 MONTHS OR LONGER WITH PAIN SCORE <= 3
by record_number run_num (time_surgerytopainscore), sort: ///
    gen byte long_run = time_surgerytopainscore[_N] - time_surgerytopainscore[1] >= 91.3125
by record_number: egen wanted = max(long_run & score_pain_ <= 3)
tab record_number if wanted

Note: Because you did not show example data, this code is untested. It may contain typos or other errors. I have made some guesses about the nature of your data from the combination of your description and the code you tried. But those may be wrong guesses, and in that case the code may give incorrect results, or not run at all. In the future (including any subsequent post in this thread), please show example data, using the -dataex- command, when asking for help with code so that neither you, nor I, nor anybody else, will waste their time due to wrong guesses. If you are running version 17, 16 or a fully updated version 15.1 or 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-.

[Maximilian Mayrhofer]

Hi Clyde, thank you for your reply!
You are absolutely right, I am using wide layout so far.
Please find an example dataset below:

* Example generated by -dataex-. For more info, type help dataex
clear
input int record_number byte(score_pain_1 score_pain_2 score_pain_3 score_pain_15) float(time_surgerytopainscore1 time_surgerytopainscore2 time_surgerytopainscore3 time_surgerytopainscore15)
  1  2  1  0  .   1    2   22    .
  4  2  4  .  .   1   84    .    .
  6  4  2  0  .   1  333 1313    .
  7  3  7  5  .   1    2    3    .
  9  4  3  5  3   1    2    3   15
 10  0  .  .  .   1    .    .    .
 11 10  3  .  .   1  902    .    .
 15  7  7  8  .   1    2   15    .
 16  8 10  9 10   1    2    3 1255
 17  4  4  .  .   1   70    .    .
 18  5  .  .  .   1    .    .    .
 19  9  6  7  .   1    2    3    .
 32  8  8  8  .   1    2    3    .
 36  3  .  .  .   1    .    .    .
 39  0  .  .  .   1    .    .    .
 41  1  1  1  2   1    2    3   15
 44  1  0  .  .   1  843    .    .
 48  0  0  5  .   1    2    3    .
 50 10  8 10  .   1    2    3    .
 55  5  6  5  .   1    2    3    .
 58  8  7  6  .   1    2    3    .
 62  9  .  .  .   1    .    .    .
 65  5  2  7  .   1    4  220    .
 66  4  6  6  .   1    2    3    .
 70  9  4  2  0   1    2    3  745
 71  8  6  1  .   1   13  108    .
 74  5  3  .  .  76  117    .    .
 76  3  7  4  .   1   19   40    .
 77  5  4  5  0   1    2    3   15
 80  0  6  5  . 366   90  150    .
 82  7  5  8 10   1    2    3   15
 83  8  7  8  .   1    2   12    .
 84  8  6  3  .   1    2   13    .
 85  8  7  6  .  12   27  101    .
 86  7  7  1  .   1    2   12    .
 88  5  6  2  .   1    6   62    .
 89  2  8  2  4   1    2    3   15
 90  8 10 10  .   1    2    3    .
 91  5  3  4  5   1    2    3   15
 92  5  5  2  5   1    2    3   15
 95  6  1  7  9   1    2    3   15
 97  5  5  .  .   1    2    .    .
 98  4  6  7  8   1    2    3   15
100  9  7  6  .   1    2  188    .
101  7  4  5  7   1    2    3  178
102  2  0  0  1   1   77  146  391
105  9  7  7  5   1    2    3   15
106  2  2  0  .   1    2  333    .
110  5  0  2  1   1    2    3   15
112  4  2  2  .   1    2    3    .
113  3  2  2  .   1    2    3    .
114  4  0  0  .   1   47   90    .
117  7  4  7  .   1    2    3    .
120 10  7  3  .   1    2    3    .
123  7  7  8  .   1  367    3    .
124  7  4  .  .   6  123    .    .
125  6  1  3  .   1   20   68    .
128  5  5  .  .   1    6    .    .
140  7  8  6  9   1    2    3   15
141  7  5  4  5   1    2    3   19
142  3  3  5  5   1    2    3   62
143  0 10  0  0   1    2    3   15
146  3  2  1  .   1    2    3    .
147  6 10  4  .   1    2   35    .
154  0  0  4  3   1    2    3   15
156  4  2  2  .   2    5    8    .
162  8  7  4  .  11   37   93    .
164 10  4  5  .   1    2    3    .
167  1  5  .  .   1  206    .    .
168  8  3  .  .   1  146    .    .
171  8  3  6  6   1    2    3   15
176  7  .  .  .   1    .    .    .
177  8  5  6  .   1    2    3    .
181  2  .  .  .   1    .    .    .
184  7  .  .  .   1    .    .    .
191  4  3  6  .   1    2    3    .
192  8  5  8  5   0    1    2   14
195  9  6  6  5   1    2    3   15
199  5  8  8  0   1    2    3   15
202  7  .  .  .   1    .    .    .
206  7  3  4  7   1    2    3   15
208  8  6  5  .   1    2    3    .
210  6  8  7  5   1    2    3   15
211  9  7  7  .   1    2    3    .
215  6  4  .  .   1  546    .    .
217  6  5  1  .   8   27   71    .
218  5  4  5  .   1    2    3    .
220  5  5  0  0   1    2    3   15
221  7  8  3  .   1    6   34    .
222  6  3  4  .   1    2    3    .
224  8  8  5  .   1    2    3    .
228  8  8  9  8   1    2    3   15
230  4  9  2  .   1    2  104    .
242  7  6  7  .   1 1262 1335    .
253  5  3  9  .   1    2    3    .
259  0  0  0  0   1    2    3   15
261  5  3  6  2   1    2    3   15
276  6  9  .  .   1    6    .    .
278  7  3  7  .   1    2    3    .
279  8  9  9  9   1    2    3   15
end

[Clyde Schechter]

OK. I did make an error. Ironically, after I ranted at you about needing a long data set, I wrote the -reshape- command to take you to wide instead of the other way around! Sorry about that. Apart from that, however, the code was OK and produces correct results in your example data. Here is the corrected code.

//  FIRST GO TO LONG LAYOUT
reshape long score_pain_ time_surgerytopainscore, i(record_number) j(_j)
drop if missing(score_pain_, time_surgerytopainscore)

//  IDENTIFY RUNS OF SCORE_PAIN RELATIVE TO 3
gen byte low_pain_score = score_pain_ <= 3
by record_number (time_surgerytopainscore), sort: ///
    gen int run_num = sum(low_pain_score != low_pain_score[_n-1])
    
//  NOW IDENTIFY RECORD NUMBERS WHERE THERE IS AT LEAST ONE RUN
//  3 MONTHS OR LONGER WITH PAIN SCORE <= 3
by record_number run_num (time_surgerytopainscore), sort: ///
    gen byte long_run = time_surgerytopainscore[_N] - time_surgerytopainscore[1] >= 91.3125
by record_number: egen wanted = max(long_run & score_pain_ <= 3)
tab record_number if wanted

[Maximilian Mayrhofer]

Yes that works perfectly now, thank you so much!