Finding the second occurence in panel data with conditions

[Jesper Eriksson]

Hi,

I have a panel data set. I want to identify the second occurence of "1" in VariableA under the following the condition that there can only be missing between the first value of "1" and the next value of "1" (the value I want to identify). If there is a value of "0" after the first "1", we need to start over.

I have marked the three "1"s that I want to identify in bold in my dataex example below.

Thank you in advance för your help!

Best, Jesper.

* Example generated by -dataex-. For more info, type help dataex
clear
input str12 ID float VariableA
"Name" .
"Name" .
"Name" .
"Name" .
"Name" 0
"Name" .
"Name" .
"Name" 1
"Name" .
"Name" 0
"Name" 0
"Name" 0
"Name" 0
"Name" 0
"Name" 0
"Name" 0
"Name" 0
"Name" 0
"Name" 0
"Name" 1
"Name" 0
"Name" 0
"Name" 0
"Name" 0
"Name" 0
"Name" 0
"Name" 0
"Name" 0
"Name" 0
"Name" 0
"Name" 0
"Name" 0
"Name" 1
"Name" 0
"Name" 1
"Name" 0
"Name" 0
"Name" 1
"Name" .
"Name" .
"Name" 1
"Name" 0
"Name" 0
"Name" 0
"Name" 0
"Name" 1
"Name" .
"Name" .
"Name" 1
"Name" .
"Name" 0
"Name" .
"Name" 1
"Name" .
"Name" .
"Name" 0
"Name" .
"Name" 1
"Name" 1
"Name" .
"Name" .
"Name" 0
end

[Nick Cox]

I don't think I understand this, but the code here may be a start.

I understand your rules this way:

1. Work separately by identifiers. There should be a time order or other sequence, despite your not showing a time variable.

2. If there is a zero, we start again. Call these phases.

3. You want the second 1 in each phase, except that it must follow a run of missings.

This code catches 2 out of 3 incidences you identify. It doesn't agree on the last case you flag.

     +------------------------------------------------+
     |   ID   Variab~A   time   zeros   ones   wanted |
     |------------------------------------------------|
  1. | Name          .      1       0      0        0 |
  2. | Name          .      2       0      0        0 |
  3. | Name          .      3       0      0        0 |
  4. | Name          .      4       0      0        0 |
     |------------------------------------------------|
  5. | Name          0      5       1      0        0 |
  6. | Name          .      6       1      0        0 |
  7. | Name          .      7       1      0        0 |
  8. | Name          1      8       1      1        0 |
  9. | Name          .      9       1      1        0 |
     |------------------------------------------------|
 10. | Name          0     10       2      0        0 |
 11. | Name          0     11       2      0        0 |
 12. | Name          0     12       2      0        0 |
 13. | Name          0     13       2      0        0 |
 14. | Name          0     14       2      0        0 |
 15. | Name          0     15       2      0        0 |
 16. | Name          0     16       2      0        0 |
 17. | Name          0     17       2      0        0 |
 18. | Name          0     18       2      0        0 |
 19. | Name          0     19       2      0        0 |
 20. | Name          1     20       2      1        0 |
     |------------------------------------------------|
 21. | Name          0     21       3      0        0 |
 22. | Name          0     22       3      0        0 |
 23. | Name          0     23       3      0        0 |
 24. | Name          0     24       3      0        0 |
 25. | Name          0     25       3      0        0 |
 26. | Name          0     26       3      0        0 |
 27. | Name          0     27       3      0        0 |
 28. | Name          0     28       3      0        0 |
 29. | Name          0     29       3      0        0 |
 30. | Name          0     30       3      0        0 |
 31. | Name          0     31       3      0        0 |
 32. | Name          0     32       3      0        0 |
 33. | Name          1     33       3      1        0 |
     |------------------------------------------------|
 34. | Name          0     34       4      0        0 |
 35. | Name          1     35       4      1        0 |
     |------------------------------------------------|
 36. | Name          0     36       5      0        0 |
 37. | Name          0     37       5      0        0 |
 38. | Name          1     38       5      1        0 |
 39. | Name          .     39       5      1        0 |
 40. | Name          .     40       5      1        0 |
 41. | Name          1     41       5      2        1 |
     |------------------------------------------------|
 42. | Name          0     42       6      0        0 |
 43. | Name          0     43       6      0        0 |
 44. | Name          0     44       6      0        0 |
 45. | Name          0     45       6      0        0 |
 46. | Name          1     46       6      1        0 |
 47. | Name          .     47       6      1        0 |
 48. | Name          .     48       6      1        0 |
 49. | Name          1     49       6      2        1 |
 50. | Name          .     50       6      2        0 |
     |------------------------------------------------|
 51. | Name          0     51       7      0        0 |
 52. | Name          .     52       7      0        0 |
 53. | Name          1     53       7      1        0 |
 54. | Name          .     54       7      1        0 |
 55. | Name          .     55       7      1        0 |
     |------------------------------------------------|
 56. | Name          0     56       8      0        0 |
 57. | Name          .     57       8      0        0 |
 58. | Name          1     58       8      1        0 |
 59. | Name          1     59       8      2        0 |
 60. | Name          .     60       8      2        0 |
 61. | Name          .     61       8      2        0 |
     |------------------------------------------------|
 62. | Name          0     62       9      0        0 |
     +------------------------------------------------+

.

[Jesper Eriksson]

You are absolutely right, I forgot to include the time variable. Sorry, new dataex below. Regarding your comments;
1. Yes, work separetely by identifiers ( I only included one ID in my dataex example, this is not the part I need help with. I understand your confusion. My fault.)
2. Yes, if there is a zero we start again. Phases is an appropiate decription.
3. Yes I only want the second 1 in each phase. However, it does not have to follow a run of missings. I want the second 1 for a phase, even if there is two 1s after each other. As long there is no zero in between.
The last bolded 1 is an example I would like to catch where there is two 1s after each other.


Can you post the code you used as well?

New dataex:

* Example generated by -dataex-. For more info, type help dataex
clear
input str12 ID int date float VariableA
"Name" 22627 .
"Name" 22628 .
"Name" 22629 .
"Name" 22630 .
"Name" 22631 0
"Name" 22634 .
"Name" 22635 .
"Name" 22636 1
"Name" 22637 .
"Name" 22641 0
"Name" 22642 0
"Name" 22643 0
"Name" 22644 0
"Name" 22648 0
"Name" 22649 0
"Name" 22650 0
"Name" 22652 0
"Name" 22655 0
"Name" 22656 0
"Name" 22657 1
"Name" 22658 0
"Name" 22659 0
"Name" 22662 0
"Name" 22663 0
"Name" 22664 0
"Name" 22665 0
"Name" 22666 0
"Name" 22669 0
"Name" 22670 0
"Name" 22671 0
"Name" 22672 0
"Name" 22673 0
"Name" 22676 1
"Name" 22677 0
"Name" 22678 1
"Name" 22679 0
"Name" 22680 0
"Name" 22683 1
"Name" 22684 .
"Name" 22685 .
"Name" 22686 1
"Name" 22687 0
"Name" 22690 0
"Name" 22691 0
"Name" 22692 0
"Name" 22693 1
"Name" 22694 .
"Name" 22697 .
"Name" 22698 1
"Name" 22699 .
"Name" 22700 0
"Name" 22701 .
"Name" 22704 1
"Name" 22705 .
"Name" 22706 .
"Name" 22707 0
"Name" 22708 .
"Name" 22711 1
"Name" 22712 1
"Name" 22713 .
"Name" 22714 .
"Name" 22715 0
end
format %td datum

[Nick Cox]

#1 said

there can only be missing between the first value of "1" and the next value of "1"

!!!

OK. I didn't give my code in #2 but here I've revised that code to get closer to what you want.

* Example generated by -dataex-. For more info, type help dataex
clear
input str12 ID float VariableA
"Name" .
"Name" .
"Name" .
"Name" .
"Name" 0
"Name" .
"Name" .
"Name" 1
"Name" .
"Name" 0
"Name" 0
"Name" 0
"Name" 0
"Name" 0
"Name" 0
"Name" 0
"Name" 0
"Name" 0
"Name" 0
"Name" 1
"Name" 0
"Name" 0
"Name" 0
"Name" 0
"Name" 0
"Name" 0
"Name" 0
"Name" 0
"Name" 0
"Name" 0
"Name" 0
"Name" 0
"Name" 1
"Name" 0
"Name" 1
"Name" 0
"Name" 0
"Name" 1
"Name" .
"Name" .
"Name" 1
"Name" 0
"Name" 0
"Name" 0
"Name" 0
"Name" 1
"Name" .
"Name" .
"Name" 1
"Name" .
"Name" 0
"Name" .
"Name" 1
"Name" .
"Name" .
"Name" 0
"Name" .
"Name" 1
"Name" 1
"Name" .
"Name" .
"Name" 0
end

gen time = 1 + sum(ID == ID[_n-1])

bysort ID (time) : gen zeros = sum(VariableA == 0 & VariableA[_n-1] != 0) 

bysort ID zeros (time) : gen ones = sum(VariableA == 1)

by ID zeros : gen wanted = (VariableA == 1) & (ones == 2)  

list, sepby(ID zeros)

    +------------------------------------------------+
     |   ID   Variab~A   time   zeros   ones   wanted |
     |------------------------------------------------|
  1. | Name          .      1       0      0        0 |
  2. | Name          .      2       0      0        0 |
  3. | Name          .      3       0      0        0 |
  4. | Name          .      4       0      0        0 |
     |------------------------------------------------|
  5. | Name          0      5       1      0        0 |
  6. | Name          .      6       1      0        0 |
  7. | Name          .      7       1      0        0 |
  8. | Name          1      8       1      1        0 |
  9. | Name          .      9       1      1        0 |
     |------------------------------------------------|
 10. | Name          0     10       2      0        0 |
 11. | Name          0     11       2      0        0 |
 12. | Name          0     12       2      0        0 |
 13. | Name          0     13       2      0        0 |
 14. | Name          0     14       2      0        0 |
 15. | Name          0     15       2      0        0 |
 16. | Name          0     16       2      0        0 |
 17. | Name          0     17       2      0        0 |
 18. | Name          0     18       2      0        0 |
 19. | Name          0     19       2      0        0 |
 20. | Name          1     20       2      1        0 |
     |------------------------------------------------|
 21. | Name          0     21       3      0        0 |
 22. | Name          0     22       3      0        0 |
 23. | Name          0     23       3      0        0 |
 24. | Name          0     24       3      0        0 |
 25. | Name          0     25       3      0        0 |
 26. | Name          0     26       3      0        0 |
 27. | Name          0     27       3      0        0 |
 28. | Name          0     28       3      0        0 |
 29. | Name          0     29       3      0        0 |
 30. | Name          0     30       3      0        0 |
 31. | Name          0     31       3      0        0 |
 32. | Name          0     32       3      0        0 |
 33. | Name          1     33       3      1        0 |
     |------------------------------------------------|
 34. | Name          0     34       4      0        0 |
 35. | Name          1     35       4      1        0 |
     |------------------------------------------------|
 36. | Name          0     36       5      0        0 |
 37. | Name          0     37       5      0        0 |
 38. | Name          1     38       5      1        0 |
 39. | Name          .     39       5      1        0 |
 40. | Name          .     40       5      1        0 |
 41. | Name          1     41       5      2        1 |
     |------------------------------------------------|
 42. | Name          0     42       6      0        0 |
 43. | Name          0     43       6      0        0 |
 44. | Name          0     44       6      0        0 |
 45. | Name          0     45       6      0        0 |
 46. | Name          1     46       6      1        0 |
 47. | Name          .     47       6      1        0 |
 48. | Name          .     48       6      1        0 |
 49. | Name          1     49       6      2        1 |
 50. | Name          .     50       6      2        0 |
     |------------------------------------------------|
 51. | Name          0     51       7      0        0 |
 52. | Name          .     52       7      0        0 |
 53. | Name          1     53       7      1        0 |
 54. | Name          .     54       7      1        0 |
 55. | Name          .     55       7      1        0 |
     |------------------------------------------------|
 56. | Name          0     56       8      0        0 |
 57. | Name          .     57       8      0        0 |
 58. | Name          1     58       8      1        0 |
 59. | Name          1     59       8      2        1 |
 60. | Name          .     60       8      2        0 |
 61. | Name          .     61       8      2        0 |
     |------------------------------------------------|
 62. | Name          0     62       9      0        0 |
     +------------------------------------------------+

.

[Jesper Eriksson]

That's great! Exactly what I needed. Many thanks!

[Romalpa Akzo]

Another way to go.

gen w = VariableA==1
bys ID (date): replace w = w[_n-1] + w if VariableA!=0 & _n>1
replace w = (w==2) & (VariableA==1)