ID  Time  Condition Seq Want_temp Want_final_v1 

A   1     yes       0   0         0             
A   2     yes       0   .         1             
A   3     no        1   .         .             
A   4     yes       0   .         2             
A   5     no        1   .         .             
A   6     no        2   .         .             
B   1     yes       0   .         4             
B   2     no        1   .         .             
B   3     no        2   .         .             
B   4     no        3   .         .             
B   5     no        4   .         .             
B   6     yes       0   0         0             
B   7     yes       0   0         0             
...    

This is a simplified data set I have and the variable that I am willing to generate is Want_final_v1 which I’ve been struggling to generate for the whole day..

To be brief, the Seq variable is the numerical sequence starting from 0 when the value of the Condition variable is equal to ‘yes’. The value of the Seq increases by 1 when the next value of the Condition is ‘no’.

What I want is Want_final_v1 which contains 0 for Condition==’yes’ but contains the last or the max value of the Seq for Condition==’yes’ only when following values for Condition==’no’.

I am still learning Stata and my initial plan is to derive Want_final_v1 after generating Want_temp (which I’ve done).

Please help me out.