Gen dummy if other variable takes the value 1 for this row and subsequent rows

[Ludvig Olsson]

I want to generate a new variable "risk" that takes the value 1 if nr_of_phys equals 1 on the same row and the subsequent rows until phys_quit==1 for each subject (ID). I have illustrated in the table with three different examples how it should look like. Both nr_of_phys and phys_quit can only take the values 0 or 1.

I'm not able to provide you with my dataset for confidentiality reasons hence the table instead.

ID	Year	nr_of_phys	phys_quit	risk
1	1	0	0	0
1	2	1	0	1
1	3	1	0	0
1	4	1	1	0
2	1	1	0	0
2	2	1	0	0
2	3	0	0	0
2	4	1	0	1
2	5	1	1	0
3	1	1	0	0
3	2	1	0	0
3	3	1	0	0
3	4	0	0	0
3	5	0	0	0

[Clyde Schechter]

by id (year), sort: gen run = sum(nr_of_phys == !nr_of_phys[_n-1])
by id (year): gen wanted = run == run[_N] & nr_of_phys[_N] == 1 & run[_n-1] < run[_N]

In your example data, each id starts out with nr_of_phys == 0 and then, perhaps, at some point this changes to 1 and stays there. You do not say if other patterns are possible, such as oscillations back and forth over the sequence of years. The code above will handle this type of general pattern, in all cases marking the first observation of a run of 1's that extends all the way to the end of the id's data, no matter how much complication may have preceded that.

[Nick Cox]

Thanks for the data example, here translated to dataex output.

See https://journals.sagepub.com/doi/pdf...867X0700700209 for some discussion of what are there called spells. This problem is a little different but seems to yield similarly to some fooling around with by:, sort order and subscripts. A solution starts with how a spell begins and how it ends and what must be true otherwise.

clear
input byte(id year nr_of_phys phys_quit risk)
1 1 0 0 0
1 2 1 0 1
1 3 1 0 0
1 4 1 1 0
2 1 1 0 0
2 2 1 0 0
2 3 0 0 0
2 4 1 0 1
2 5 1 1 0
3 1 1 0 0
3 2 1 0 0
3 3 1 0 0
3 4 0 0 0
3 5 0 0 0
end 

bysort id (year) : gen spell = year if nr_of_phys == 1 & nr_of_phys[_n-1] != 1  
by id : replace spell = spell[_n-1] if spell == . & nr_of_phys == 1 & phys_quit[_n-1] != 1 

bysort id spell (year) : gen wanted = nr_of_phys[1] == 1 & phys_quit[_N] == 1 & _n == 1 

assert risk == wanted 

sort id year 

list, sepby(id spell)

    +---------------------------------------------------------+
     | id   year   nr_of_~s   phys_q~t   risk   spell   wanted |
     |---------------------------------------------------------|
  1. |  1      1          0          0      0       .        0 |
     |---------------------------------------------------------|
  2. |  1      2          1          0      1       2        1 |
  3. |  1      3          1          0      0       2        0 |
  4. |  1      4          1          1      0       2        0 |
     |---------------------------------------------------------|
  5. |  2      1          1          0      0       1        0 |
  6. |  2      2          1          0      0       1        0 |
     |---------------------------------------------------------|
  7. |  2      3          0          0      0       .        0 |
     |---------------------------------------------------------|
  8. |  2      4          1          0      1       4        1 |
  9. |  2      5          1          1      0       4        0 |
     |---------------------------------------------------------|
 10. |  3      1          1          0      0       1        0 |
 11. |  3      2          1          0      0       1        0 |
 12. |  3      3          1          0      0       1        0 |
     |---------------------------------------------------------|
 13. |  3      4          0          0      0       .        0 |
 14. |  3      5          0          0      0       .        0 |
     +---------------------------------------------------------+

[Ludvig Olsson]

Thanks, this solved my issue!