How to only keep firms with at least three consecutive annual observations?

[kristian kristiansen]

Hi!
I have a panel data with information about different companies and observation over time.

CompanyID Year
1 2000
1 2001
1 2002
1 2003
2 2005
2 2006
2 2008
2 2009
2 2010
2 2011

I want to only keep the companies that has at least three consecutive annual observations. So the result should be like this:

CompanyID Year
1 2000
1 2001
1 2002
1 2003

2 2008
2 2009
2 2010
2 2011


Can someone please help me to write the Do-file codes?

Kind Regards,
Kristian

[Nick Cox]

This is an FAQ: http://www.stata.com/support/faqs/da...-observations/

Do you want only runs of consecutive observations for each panel, or just any panel with at least one run of three?

[kristian kristiansen]

I have tried the link you sendt, but it doesn't solve my problem.

I only have one panel data. It does include about 100 firms, with about ten years of data for each firm.
So I want to drop firms observation that doesn't include in a at least three consecutive annual observations.

Do you understand my problem?

[Nick Cox]

The FAQ cited discussed in detail how to define runs of consecutive observations and directed you to tsspell (SSC), whose help contains numerous pertinent examples.

Spelling it out, it seems that you want something like this:

. clear 

. input CompanyID Year

     CompanyID       Year
  1. 1 2000
  2. 1 2001
  3. 1 2002
  4. 1 2003
  5. 2 2005
  6. 2 2006
  7. 2 2008
  8. 2 2009
  9. 2 2010
 10. 2 2011
 11. end 

. tsset CompanyID Year 
       panel variable:  CompanyID (unbalanced)
        time variable:  Year, 2000 to 2011, but with a gap
                delta:  1 unit

. tsspell, f(L.Year == .)  
warning: data contain gaps; see help on tsspell

. egen length = max(_seq), by(CompanyID _spell) 

. list, sepby(CompanyID) 

     +-------------------------------------------------+
     | Compan~D   Year   _spell   _seq   _end   length |
     |-------------------------------------------------|
  1. |        1   2000        1      1      0        4 |
  2. |        1   2001        1      2      0        4 |
  3. |        1   2002        1      3      0        4 |
  4. |        1   2003        1      4      1        4 |
     |-------------------------------------------------|
  5. |        2   2005        1      1      0        2 |
  6. |        2   2006        1      2      1        2 |
  7. |        2   2008        2      1      0        4 |
  8. |        2   2009        2      2      0        4 |
  9. |        2   2010        2      3      0        4 |
 10. |        2   2011        2      4      1        4 |
     +-------------------------------------------------+

. keep if length >= 3 
(2 observations deleted)

. list, sepby(CompanyID) 

     +-------------------------------------------------+
     | Compan~D   Year   _spell   _seq   _end   length |
     |-------------------------------------------------|
  1. |        1   2000        1      1      0        4 |
  2. |        1   2001        1      2      0        4 |
  3. |        1   2002        1      3      0        4 |
  4. |        1   2003        1      4      1        4 |
     |-------------------------------------------------|
  5. |        2   2008        2      1      0        4 |
  6. |        2   2009        2      2      0        4 |
  7. |        2   2010        2      3      0        4 |
  8. |        2   2011        2      4      1        4 |
     +-------------------------------------------------+