first occurrence of certain value

[Lucy Garcia]

Dear all,

I'm having a problem coding the first occurrence of certain value equal to 1. I have a panel data with 10 years of observation (at most 10 years, some firms have less than 10 years of observation), the relevant variables are firm, year and value. The value variable can take values 0, 1, or 2. I want to code the first year a firm with value 1 as 1, say the new variable is called "occur1". Here is an example of data

year    firm    value
2010    2092    2
2011    2092    2
2012    2092    2
2013    2092    1
2014    2092    1
2015    2092    1
2016    2092    1
2017    2092    2
2018    2092    2
2019    2092    2
2010    2103    0
2011    2103    1
2012    2103    1
2013    2103    1
2014    2103    1
2015    2103    1
2016    2103    1
2017    2103    1
2018    2103    0
2019    2103    0
2010    2106    2
2011    2106    2
2012    2106    2
2013    2106    2
2014    2106    2
2015    2106    1
2016    2106    0
2017    2106    0
2018    2106    0
2019    2106    0
2010    2456    0
2011    2456    0
2012    2456    0
2013    2456    2
2014    2456    0
2015    2456    2
2016    2456    2
2017    2456    2
2018    2456    2
2019    2456    2

I would like the bold ones to code 1 for occur1.

After reading https://www.stata.com/support/faqs/d...t-occurrences/ I tried

by firm (year), sort: gen byte occur1 = sum(value) == 1 & (sum(value[_n-1]) == 0 | sum(value[_n-1]) == 2)

however it only works when firms with value of 0 and 1 but not when there is value 2.

Any suggestions on how to correct this code? Thanks a lot!

Lucy

[River Huang]

Dear Lucy, Please try

bys firm (year): gen noccur = sum(value == 1)
by firm: gen byte first = noccur == 1  & noccur[_n - 1] != noccur

[Nick Cox]

Thanks for the data example. Here are two ways to do it. The first hinges on Section 9 in https://www.stata-journal.com/articl...article=dm0055 but is explained in the FAQ you cite.

clear
input year    firm    value
2010    2092    2
2011    2092    2
2012    2092    2
2013    2092    1
2014    2092    1
2015    2092    1
2016    2092    1
2017    2092    2
2018    2092    2
2019    2092    2
2010    2103    0
2011    2103    1
2012    2103    1
2013    2103    1
2014    2103    1
2015    2103    1
2016    2103    1
2017    2103    1
2018    2103    0
2019    2103    0
2010    2106    2
2011    2106    2
2012    2106    2
2013    2106    2
2014    2106    2
2015    2106    1
2016    2106    0
2017    2106    0
2018    2106    0
2019    2106    0
2010    2456    0
2011    2456    0
2012    2456    0
2013    2456    2
2014    2456    0
2015    2456    2
2016    2456    2
2017    2456    2
2018    2456    2
2019    2456    2
end

egen yearfirst = min(cond(value == 1, year, .)), by(firm)
gen isfirst = yearfirst == year

bysort firm (year) : gen ones = sum(value == 1)
by firm: gen ISFIRST = ones == 1 & inlist(ones[_n-1], 0, .)  

list, sepby(firm)


     +-----------------------------------------------------------+
     | year   firm   value   yearfi~t   isfirst   ones   ISFIRST |
     |-----------------------------------------------------------|
  1. | 2010   2092       2       2013         0      0         0 |
  2. | 2011   2092       2       2013         0      0         0 |
  3. | 2012   2092       2       2013         0      0         0 |
  4. | 2013   2092       1       2013         1      1         1 |
  5. | 2014   2092       1       2013         0      2         0 |
  6. | 2015   2092       1       2013         0      3         0 |
  7. | 2016   2092       1       2013         0      4         0 |
  8. | 2017   2092       2       2013         0      4         0 |
  9. | 2018   2092       2       2013         0      4         0 |
 10. | 2019   2092       2       2013         0      4         0 |
     |-----------------------------------------------------------|
 11. | 2010   2103       0       2011         0      0         0 |
 12. | 2011   2103       1       2011         1      1         1 |
 13. | 2012   2103       1       2011         0      2         0 |
 14. | 2013   2103       1       2011         0      3         0 |
 15. | 2014   2103       1       2011         0      4         0 |
 16. | 2015   2103       1       2011         0      5         0 |
 17. | 2016   2103       1       2011         0      6         0 |
 18. | 2017   2103       1       2011         0      7         0 |
 19. | 2018   2103       0       2011         0      7         0 |
 20. | 2019   2103       0       2011         0      7         0 |
     |-----------------------------------------------------------|
 21. | 2010   2106       2       2015         0      0         0 |
 22. | 2011   2106       2       2015         0      0         0 |
 23. | 2012   2106       2       2015         0      0         0 |
 24. | 2013   2106       2       2015         0      0         0 |
 25. | 2014   2106       2       2015         0      0         0 |
 26. | 2015   2106       1       2015         1      1         1 |
 27. | 2016   2106       0       2015         0      1         0 |
 28. | 2017   2106       0       2015         0      1         0 |
 29. | 2018   2106       0       2015         0      1         0 |
 30. | 2019   2106       0       2015         0      1         0 |
     |-----------------------------------------------------------|
 31. | 2010   2456       0          .         0      0         0 |
 32. | 2011   2456       0          .         0      0         0 |
 33. | 2012   2456       0          .         0      0         0 |
 34. | 2013   2456       2          .         0      0         0 |
 35. | 2014   2456       0          .         0      0         0 |
 36. | 2015   2456       2          .         0      0         0 |
 37. | 2016   2456       2          .         0      0         0 |
 38. | 2017   2456       2          .         0      0         0 |
 39. | 2018   2456       2          .         0      0         0 |
 40. | 2019   2456       2          .         0      0         0 |
     +-----------------------------------------------------------+

EDIT: If 1 is seen in the first observation of a panel, that needs a fix to previous code. @River Huang's code solved this too.

[Lucy Garcia]

Hi River and Nick,

Thanks a lot for your kind help!

Yes I checked both and found if 1 is the first observation of a firm, Nick's code would give 0 rather than 1.

[Lucy Garcia]

Oh I didn't see Nick's code is revised, sorry! This time the two codes give the same result.

Thanks again for helping me

[Nick Cox]

That's OK, but the first method I gave should work generally.

[Romalpa Akzo]

Just a minor contribution for the qualification to identify the First 1.

River's in #2

noccur[_n - 1] != noccur

and Nick's in #3

inlist(ones[_n-1], 0, .)

could be replaced by

value ==1

That appears to be a bit more direct in the calculation.

bys firm (year): gen FirstOne = sum(value==1) == 1 & value==1

[Nick Cox]

#7 Romalpa Akzo Yes, that's a very good detail.

[Lucy Garcia]

Thanks Romalpa for your suggestion!