identifying two consecutive values within a variables

[Umama Afr]

Dear Stata team,
I am trying to compare the time to culture conversion (TCC) of a bacterial detection test where the variable "result" is as the following

result=1 if positive and 0 if negative

. The TCC is defined as time needed to observe 2 (or more) consecutive negative results, so that if I manage to detect the 2 consecutive negative results, then I will consider this time onward to be converted to negative.
the data set looks like the following. I am struggling to find a way to apply this definition of TCC in codes , and would highly appreciate your help.

 
id
date of collecting sample
date of result
result
visit week

1
20oct2016
29oct2016
1
2

1
04nov2016
11Nov2016
0
4

1
17nov2016
25Nov2016
1
6

1
30nov2016
7Dec2016
0
8

1
17dec2016
23Dec2016
0
10

1
31 Jan2017
05Feb2017
1
12

2





2





2

thank you very much for your time
Umama

[Nick Cox]

This may help:

bysort id (dateofresult) : gen first_negative = result == 0 & result[_n-1] == 0 
clonevar wanted = first_negative 
by id: replace wanted = 1 if wanted[_n-1] == 1

[Umama Afr]

thank you Nick that was helpful but there is one issue that resulted: so because the negative is defined as observing two consecutive negative, we consider the first value of the two consecutive negative to be negative whereas the codes above will look at this value as positive. so in the table below I am comparing the variable wanted (from the above codes) and the variable "should"
I am not sure if adding the command to the above commands is the right thing to do

 
 by id: replace wanted =1 if result==0

 
id
date of collecting sample
date of result
result
first_negative
wanted
should

1


1
0
0
0

1


0
0
0
0

1


1
0
0
0

1


0
0
0
1

1


0
1
1
1

1


1
1
1
1

I highly appreciate your help
thank you very much
Umama

[Umama Afr]

actually, I have tried the following and it seems to fix it by adding it to the commands above

by id: replace wanted=0 if wanted[_n]==1 & wanted[_n+1]==0

thank you again
Umama