identify firms in panel data

[Lynn Nguyen]

Dear Members,

I try to identify firms (id) in a panel data with 'status' == 1 for at least 3 years in a row and then code these years as 1, zero otherwise. Please help me with this.
My data set looks like below. Many thanks. Lynn.

clear
input float(id year status)
1 2003 1
1 2004 1
1 2005 0
1 2006 1
1 2007 1
1 2008 1
1 2009 0
1 2010 1
1 2011 1
1 2012 1
1 2013 1
1 2014 0
1 2015 0
1 2016 0
1 2017 0
1 2018 0
1 2019 0
1 2020 0
1 2021 0
2 2002 0
2 2003 0
2 2004 1
2 2005 0
2 2006 1
2 2007 0
2 2008 1
2 2009 1
2 2010 1
2 2011 0
2 2012 0
2 2013 0
2 2014 0
2 2015 0
3 2011 1
3 2012 1
4 2015 0
5 2017 1
5 2019 1
5 2020 0
5 2021 1 
end

[Navi Reddy]

Below is one approach:

sort id year
by id: gen flag = (status == 1) & (status[_n-1] == 1) & (status[_n-2] == 1)
by id: replace flag = 1 if flag[_n+1] == 0 & flag[_n+2] == 1
by id: replace flag = 1 if flag[_n-1] == 1 & flag[_n+1] == 1 & status != 0

There may be other better ways. I am still learning Stata.

[Andrew Musau]

If you have holes in the panel (missing observations), e.g., id=5 and year=2018, then you may not get the intended results. Below, I assume that if such holes exist, then status=0.

* Example generated by -dataex-. To install: ssc install dataex
clear
input float(id year status)
1 2003 1
1 2004 1
1 2005 0
1 2006 1
1 2007 1
1 2008 1
1 2009 0
1 2010 1
1 2011 1
1 2012 1
1 2013 1
1 2014 0
1 2015 0
1 2016 0
1 2017 0
1 2018 0
1 2019 0
1 2020 0
1 2021 0
2 2002 0
2 2003 0
2 2004 1
2 2005 0
2 2006 1
2 2007 0
2 2008 1
2 2009 1
2 2010 1
2 2011 0
2 2012 0
2 2013 0
2 2014 0
2 2015 0
3 2011 1
3 2012 1
4 2015 0
5 2017 1
5 2019 1
5 2020 0
5 2021 1
end

*FILL HOLES IN PANEL
xtset id year
tsfill
replace status=!status if missing(status)

*WANTED
bys id (year): gen counter= (_n==1& status) | (status & !status[_n-1])
by id: replace counter= sum(counter) if status
bys id counter: gen wanted= _N >=3 & counter

Res.:

. list, sepby(id)

     +---------------------------------------+
     | id   year   status   counter   wanted |
     |---------------------------------------|
  1. |  1   2003        1         1        0 |
  2. |  1   2004        1         1        0 |
  3. |  1   2005        0         0        0 |
  4. |  1   2006        1         2        1 |
  5. |  1   2007        1         2        1 |
  6. |  1   2008        1         2        1 |
  7. |  1   2009        0         0        0 |
  8. |  1   2010        1         3        1 |
  9. |  1   2011        1         3        1 |
 10. |  1   2012        1         3        1 |
 11. |  1   2013        1         3        1 |
 12. |  1   2014        0         0        0 |
 13. |  1   2015        0         0        0 |
 14. |  1   2016        0         0        0 |
 15. |  1   2017        0         0        0 |
 16. |  1   2018        0         0        0 |
 17. |  1   2019        0         0        0 |
 18. |  1   2020        0         0        0 |
 19. |  1   2021        0         0        0 |
     |---------------------------------------|
 20. |  2   2002        0         0        0 |
 21. |  2   2003        0         0        0 |
 22. |  2   2004        1         1        0 |
 23. |  2   2005        0         0        0 |
 24. |  2   2006        1         2        0 |
 25. |  2   2007        0         0        0 |
 26. |  2   2008        1         3        1 |
 27. |  2   2009        1         3        1 |
 28. |  2   2010        1         3        1 |
 29. |  2   2011        0         0        0 |
 30. |  2   2012        0         0        0 |
 31. |  2   2013        0         0        0 |
 32. |  2   2014        0         0        0 |
 33. |  2   2015        0         0        0 |
     |---------------------------------------|
 34. |  3   2011        1         1        0 |
 35. |  3   2012        1         1        0 |
     |---------------------------------------|
 36. |  4   2015        0         0        0 |
     |---------------------------------------|
 37. |  5   2017        1         1        0 |
 38. |  5   2018        0         0        0 |
 39. |  5   2019        1         2        0 |
 40. |  5   2020        0         0        0 |
 41. |  5   2021        1         3        0 |
     +---------------------------------------+

[Lynn Nguyen]

Thank you very much Navi and Andrew. The code works perfectly.