Unbalanced Panel data to balanced panel data for 5 Years

[Qazi Qureshi]

Dear All

I have an unbalanced panel data from year 2006 to year 2018.
There are firms with data as little as 1 year and also firms with all 13 years data.

I want a balanced data, and keep about 5 years data for each firm.
Means dropping firms which have less than 5 years data and also keeping the data for only 5 years for the firms having more than 5 years of data.

If I apply the following code, I only get to make subsample of firms with year 5... I want to create a subsample for regressions of firms with 5 years data. Its best if I can have data from last 5 years. For Example from 2013-2017(5 years) or 2013-2018(6 years) or something like this.

 
 tempvar q bysort Firms: gen `q' = _n gen byte balanced = 0 replace balanced = 1 if `q'==5

Can you please help me modify this code or any other code for my request.
Thank you so much!

Qazi

* Example generated by -dataex-. To install: ssc install dataex
clear
input str90 Name int Data_Year
"3Com Corp"                     2006
"3Com Corp"                     2007
"3Com Corp"                     2008
"3Com Corp"                     2009
"3D Systems Corporation"        2006
"3D Systems Corporation"        2007
"3D Systems Corporation"        2008
"3D Systems Corporation"        2009
"3D Systems Corporation"        2010
"3D Systems Corporation"        2011
"3D Systems Corporation"        2012
"3D Systems Corporation"        2013
"3D Systems Corporation"        2014
"3D Systems Corporation"        2015
"3D Systems Corporation"        2016
"3D Systems Corporation"        2017
"3D Systems Corporation"        2018
"3M Company"                    2006
"3M Company"                    2007
"3M Company"                    2008
"3M Company"                    2009
"3M Company"                    2010
"3M Company"                    2011
"3M Company"                    2012
"3M Company"                    2013
"3M Company"                    2014
"3M Company"                    2015
"3M Company"                    2016
"3M Company"                    2017
"3M Company"                    2018
"3Par Inc."                     2008
"3Par Inc."                     2009
"4Kids Entertainment Inc."      2006
"51job Inc Sponsored ADR"       2006
"51job Inc Sponsored ADR"       2007
"51job Inc Sponsored ADR"       2008
"51job Inc Sponsored ADR"       2010
"51job Inc Sponsored ADR"       2011
"51job Inc Sponsored ADR"       2013
"51job Inc Sponsored ADR"       2014
"51job Inc Sponsored ADR"       2016
"51job Inc Sponsored ADR"       2017
"51job Inc Sponsored ADR"       2018
"8x8, Inc."                     2010
"8x8, Inc."                     2011
"8x8, Inc."                     2012
"8x8, Inc."                     2013
"8x8, Inc."                     2014
"8x8, Inc."                     2015
"8x8, Inc."                     2016
"8x8, Inc."                     2017
"8x8, Inc."                     2018
"99 Cents Only Stores LLC"      2006
"99 Cents Only Stores LLC"      2007
"99 Cents Only Stores LLC"      2008
"99 Cents Only Stores LLC"      2009
"99 Cents Only Stores LLC"      2010
"99 Cents Only Stores LLC"      2011
"A. M. Castle & Co."            2007
"A. M. Castle & Co."            2008
"A. M. Castle & Co."            2009
"A. M. Castle & Co."            2010
"A. M. Castle & Co."            2011
"A. M. Castle & Co."            2012
"A. M. Castle & Co."            2013
"A. M. Castle & Co."            2014
"A. O. Smith Corporation"       2006
"A. O. Smith Corporation"       2007
"A. O. Smith Corporation"       2008
"A. O. Smith Corporation"       2009
"A. O. Smith Corporation"       2010
"A. O. Smith Corporation"       2011
"A. O. Smith Corporation"       2012
"A. O. Smith Corporation"       2013
"A. O. Smith Corporation"       2014
"A. O. Smith Corporation"       2015
"A. O. Smith Corporation"       2016
"A. O. Smith Corporation"       2017
"A. O. Smith Corporation"       2018
"A.C. Moore Arts & Crafts Inc." 2006
"A.C. Moore Arts & Crafts Inc." 2007
"A.C. Moore Arts & Crafts Inc." 2008
"A.C. Moore Arts & Crafts Inc." 2009
"A.C. Moore Arts & Crafts Inc." 2010
"AAON, Inc."                    2015
"AAON, Inc."                    2016
"AAON, Inc."                    2017
"AAON, Inc."                    2018
"AAR CORP."                     2006
"AAR CORP."                     2007
"AAR CORP."                     2008
"AAR CORP."                     2009
"AAR CORP."                     2010
"AAR CORP."                     2011
"AAR CORP."                     2012
"AAR CORP."                     2013
"AAR CORP."                     2014
"AAR CORP."                     2016
"AAR CORP."                     2017
"AAR CORP."                     2018
end

[Carlo Lazzaro]

Qazi:
you may want to try:

bysort Name ( Data_Year) : drop if _N<5

That said, this way you will end up with a sample that is only a pale picture of your original dataset.
Methodologically speaking, instead of making-up your dataset as a first action, you should better investigasting whether the missingness mechanism is igniorable or not.

[Qazi Qureshi]

Dear Carlo,

Thank you so much, yes it does exactly what I wanted. But at the same time, you are right, it gives me very few observations, which will hardly reflect my original dataset means I can't ignore this fact.

While, I read if my Panel data is unbalanced, I should use Pooled OLS rather than Fixed Effect.

Having said that I applied Fixed effect on my unbalanced panel data, the results are same (with different coefficient values), but I wanted to test if I can transform my unbalanced data to balanced and apply Fixed effect to check if the results support the claim.

With balanced panel data after bringing 13 years data to 5 years, I am only left with 3% of the original data...

Which regression should I keep Pooled OLS according to Wooldridge (2010) or Fixed Effect xtregression on unbalanced data?
I also am interested in applying GMM, will it work on unbalanced panel data?

I am sorry, I am asking too many questions in one post.
Thanks

[Carlo Lazzaro]

Qazi:
the main issue is not that the trick gives you back too few observations, but that you forced your data to be something they were not when they were collected.
Hence, first I would not convert my unbalanced panel in a balanced counterpart.
Stata can handle both balanced and unbalanced panels, so I would go -xtreg- (with both -fe- and -re- specifications) to check whether the evidence of a panel-wise effect comes alive.
I am not an expert with GMM so I canniot advise on that.

[Carlo Lazzaro]

Qazi:
elaborating on my previous reply #2, the following code will kick out all the firms with less than 5 waves of data and set an upper limit at 5 waves of data for the remaining ones:

. bysort Name (Data_Year): drop if _N<5

. bysort Name (Data_Year): gen counter=1

. bysort Name (Data_Year): gen excess=sum(counter)

. drop if excess>5

. list

     +----------------------------------------------------------+
     |                          Name   Data_Y~r   counter   sum |
     |----------------------------------------------------------|
  1. |        3D Systems Corporation       2006         1     1 |
  2. |        3D Systems Corporation       2007         1     2 |
  3. |        3D Systems Corporation       2008         1     3 |
  4. |        3D Systems Corporation       2009         1     4 |
  5. |        3D Systems Corporation       2010         1     5 |
     |----------------------------------------------------------|
  6. |                    3M Company       2006         1     1 |
  7. |                    3M Company       2007         1     2 |
  8. |                    3M Company       2008         1     3 |
  9. |                    3M Company       2009         1     4 |
 10. |                    3M Company       2010         1     5 |
     |----------------------------------------------------------|
 11. |       51job Inc Sponsored ADR       2006         1     1 |
 12. |       51job Inc Sponsored ADR       2007         1     2 |
 13. |       51job Inc Sponsored ADR       2008         1     3 |
 14. |       51job Inc Sponsored ADR       2010         1     4 |
 15. |       51job Inc Sponsored ADR       2011         1     5 |
     |----------------------------------------------------------|
 16. |                     8x8, Inc.       2010         1     1 |
 17. |                     8x8, Inc.       2011         1     2 |
 18. |                     8x8, Inc.       2012         1     3 |
 19. |                     8x8, Inc.       2013         1     4 |
 20. |                     8x8, Inc.       2014         1     5 |
     |----------------------------------------------------------|
 21. |      99 Cents Only Stores LLC       2006         1     1 |
 22. |      99 Cents Only Stores LLC       2007         1     2 |
 23. |      99 Cents Only Stores LLC       2008         1     3 |
 24. |      99 Cents Only Stores LLC       2009         1     4 |
 25. |      99 Cents Only Stores LLC       2010         1     5 |
     |----------------------------------------------------------|
 26. |            A. M. Castle & Co.       2007         1     1 |
 27. |            A. M. Castle & Co.       2008         1     2 |
 28. |            A. M. Castle & Co.       2009         1     3 |
 29. |            A. M. Castle & Co.       2010         1     4 |
 30. |            A. M. Castle & Co.       2011         1     5 |
     |----------------------------------------------------------|
 31. |       A. O. Smith Corporation       2006         1     1 |
 32. |       A. O. Smith Corporation       2007         1     2 |
 33. |       A. O. Smith Corporation       2008         1     3 |
 34. |       A. O. Smith Corporation       2009         1     4 |
 35. |       A. O. Smith Corporation       2010         1     5 |
     |----------------------------------------------------------|
 36. | A.C. Moore Arts & Crafts Inc.       2006         1     1 |
 37. | A.C. Moore Arts & Crafts Inc.       2007         1     2 |
 38. | A.C. Moore Arts & Crafts Inc.       2008         1     3 |
 39. | A.C. Moore Arts & Crafts Inc.       2009         1     4 |
 40. | A.C. Moore Arts & Crafts Inc.       2010         1     5 |
     |----------------------------------------------------------|
 41. |                     AAR CORP.       2006         1     1 |
 42. |                     AAR CORP.       2007         1     2 |
 43. |                     AAR CORP.       2008         1     3 |
 44. |                     AAR CORP.       2009         1     4 |
 45. |                     AAR CORP.       2010         1     5 |
     +----------------------------------------------------------+

.

That said, I would not sponsor this approach.

[Qazi Qureshi]

Dear Carlo

Thank you so much for your detailed answer.
I understand what you mean by converting my initial data into what it is now i.e. unbalanced. Actually I am trimming my data based on minimum analysts following which makes it unbalanced.

While I already applied the -xtreg- (with both -fe- and -re- specifications) with my unbalanand panel data and Hausman Test supports my -fe- model. I am just concerned which regression results should I explain in my research, because there are two arguments:
1. For Panel Data, -xtreg- (with both -fe- and -re- specifications) is preferable and suggested.
2. Panel Data (unbalanced) Pooled OLS is suggested and -xtreg- (with both -fe- and -re- specifications) may not give good results.

I am torn in between. I know I have read Stata Posts, and as you also suggested to run -xtreg- (with both -fe- and -re- specifications) and Stata handles unbalanced panel data, but I am unsure whether to keep one as the primary results and other as alternate.

By the way, both approaches give me similar results (different values in coefficients) and support my hypothesis.

Let me reiterate I am grateful for your help. And further guiding me through this will be greatly appreciated.

Thank you so much.

[Carlo Lazzaro]

Qazi:
if there's evidence of a panel-wise effect, go -xtreg-.
If you use default standard errors, -hausman- will point you toward the specification that fits your data better.