Dear Statalist,
I am having troubles creating a forvalues loop that works exactly as required. I have a dataset (over 60,000 observations) with viral load measurements (vl) measured up to 54times for some patients …vl54 and the corresponding date of measurement (dvl).
I want to create a loop that diagnoses “failure” and registers it as 1, (failure= two successive vl values >1000) and then generate “failure_date”=the date of the second of the 2 successively high vl.
NB. A patient might more than 1 failure which I hope to generate “failure1, failure_date1, failure2, failure_date2… etc.
There are a lot of missing values of vl and dvl at the end some patients’ vl(max)

What I have done

Code:
generate vlfails=0
forvalues a =1 / 53 {
local b=`a'+1 
replace vlfails=1 if vl`a'>1000 & vl`b'>1000 & vl`a'!=. & vl`b'!=.
}
 
generate vlfails_d=.
forvalues a =1 / 53 {
local b=`a'+1 
replace vlfails=1 if vl`a'>1000 & vl`b'>1000 & vl`a'!=. & vl`b'!=.
replace vlfails_d = dvl`b' if vlfails==1 & vlfails_d==.
    }
The problem…
1.The loops simply generate “failure_date” which is equal to the second ever dvl value for any patient who has ever had a failure and does not consider the actual date when the failure occurred.
2. Only one failure or failure_date is identifiable from the output.

Please, how can I correct the code.

PS. I am a beginner in Stata. Sample data below.

Thanks in advance for your assistance.

Code:
* Example generated by -dataex-. To install: ssc install dataex
clear
input double vl1 float dvl1 double vl2 float dvl2 double vl3 float dvl3 double vl4 float dvl4 double vl5 float dvl5 double vl6 float dvl6
    24 18753  194881 19030   27060 20104   54449 20319   32577 20433      19 20599
    51 19855      24 20228      24 20360      24 20425      24 20726      24 20865
 27480 20419   10384 20545   58619 20716       .     .       .     .       .     .
    24 18295      24 18325   32888 18661      59 18759       .     .       .     .
    47 19395      24 19802      24 20164      24 20635     894 21203   98902 21319
    24 18574   10764 20654      32 20765      24 21208       .     .       .     .
    24 20034      24 20580      99 21077      24 21411       .     .       .     .
    24 19527  258409 20384   83666 20521      99 20640    3049 20783      19 20852
    24 18206  290000 18378   83567 18862  132380 19047  148628 19054       .     .
    24 17266      24 17512   21000 18374   51000 18428     730 18490     178 18590
 30019 20832  210126 20930  323160 21078      24 21473       .     .       .     .
    24 17472   50000 18294  150000 18408  150000 18466  301374 18570 1678220 18736
    24 18431      24 18644      24 19004      24 19158      24 19802      24 20138
    24 17308      83 17476     330 17953      62 18023     300 18177     900 18282
   300 17988      24 18170      24 18359      24 18723      24 19087      24 19505
   236 18662  749320 18837     548 19015   28840 19150   42860 19276   52785 20046
    24 18924      24 19165      24 19515      24 19879      24 20269      24 20531
    24 18696      24 19081      51 19456     488 19687      63 19743     663 19932
  2348 18696       .     .       .     .       .     .       .     .       .     .
    24 19304      24 19340      24 19612    4418 20325    5304 20410      24 20557
    24 17350      24 17490      24 18085      24 18225      24 18393      24 18672
    24 18051      24 18221   23000 18392   14603 18533   63221 18715      24 18911
    94 17420      24 17980      24 18395      24 18737  125312 19099    6808 19375
    24 18682      24 19046   49070 20663     111 20747      24 21011      24 21210
    24 19633       .     .       .     .       .     .       .     .       .     .
 24 18129    5900 18451     148 18743  372913 19232  374777 19261  422582 19780
end
format %d dvl1
format %d dvl2
format %d dvl3
format %d dvl4
format %d dvl5
format %d dvl6