Large panel model: counting time periods since variable ==1

[Rob Shaw]

Hi

I have a very large panel dataset (50 time periods, 30m panel IDs) and I have a variable (called event) that is a combination of 0 and 1. I need to create another variable, timesinceevent, that counts the number of time periods since that variable was last equal to 1. An example is given in the table below,

Please could someone suggest how I could calculate the variable timesinceevent please?

Thanks
Rob

panel_ID	timevar	event	timesinceevent
1	1	0	.
1	2	0	.
1	3	0	.
1	4	1	0
1	5	0	1
1	6	0	2
1	7	0	3
1	8	0	4
1	9	0	5
1	10	0	6
2	1	0	.
2	2	1	0
2	3	0	1
2	4	0	2
2	5	1	0
2	6	0	1
2	7	0	2
2	8	1	0
2	9	0	1

[Fei Wang]

* Example generated by -dataex-. For more info, type help dataex
clear
input byte(panel_id timevar event)
1  1 0
1  2 0
1  3 0
1  4 1
1  5 0
1  6 0
1  7 0
1  8 0
1  9 0
1 10 0
2  1 0
2  2 1
2  3 0
2  4 0
2  5 1
2  6 0
2  7 0
2  8 1
2  9 0
end

gen timesinceevent = 0 if event == 1
bys panel_id (timevar): replace timesinceevent = timesinceevent[_n-1] + timevar - timevar[_n-1] if event == 0

[Rob Shaw]

Fei, that work perfectly. Many many thanks. Rob

[Nick Cox]

This also works when time increases by 1 each step. .

* Example generated by -dataex-. For more info, type help dataex
clear
input byte(panel_id timevar event timesinceevent)
1  1 0 .
1  2 0 .
1  3 0 .
1  4 1 0
1  5 0 1
1  6 0 2
1  7 0 3
1  8 0 4
1  9 0 5
1 10 0 6
2  1 0 .
2  2 1 0
2  3 0 1
2  4 0 2
2  5 1 0
2  6 0 1
2  7 0 2
2  8 1 0
2  9 0 1
end

. gen wanted = 0 if event == 1
(15 missing values generated)

. bysort panel_id (timevar) : replace wanted = wanted[_n-1] + 1 if missing(wanted)
(11 real changes made)

. l , sepby(panel_id)

     +------------------------------------------------+
     | panel_id   timevar   event   timesi~t   wanted |
     |------------------------------------------------|
  1. |        1         1       0          .        . |
  2. |        1         2       0          .        . |
  3. |        1         3       0          .        . |
  4. |        1         4       1          0        0 |
  5. |        1         5       0          1        1 |
  6. |        1         6       0          2        2 |
  7. |        1         7       0          3        3 |
  8. |        1         8       0          4        4 |
  9. |        1         9       0          5        5 |
 10. |        1        10       0          6        6 |
     |------------------------------------------------|
 11. |        2         1       0          .        . |
 12. |        2         2       1          0        0 |
 13. |        2         3       0          1        1 |
 14. |        2         4       0          2        2 |
 15. |        2         5       1          0        0 |
 16. |        2         6       0          1        1 |
 17. |        2         7       0          2        2 |
 18. |        2         8       1          0        0 |
 19. |        2         9       0          1        1 |
     +------------------------------------------------+