How to add additional years to an individual/company after last observed year?

Question

I have an unbalanced panel data set in Stata. With observations from 1993 to 2013. I have a variable named bankrupcty year which indicates the year each company went bankrupt. The problem is that my Stata file is missing up to 2 years before each company went bankrupt. That is, a company went bankrupt in 2003, the last observation for the company is in 2002 or 2001. I do however want to create a dummy variable for when the company went bankrupt, but since I am missing that year I am unable to perform the command:

gen bankrupt=0
replace bankrupt=1 if year==bankruptcyyear

What I want to do is add years for each company so that I am able to add the dummy variable to the correct year of bankruptcy.

orgnumber year bankruptcyyear
810037342 2009    0
810037342 2010    0
810037342 2011    0
810037342 2012    0
810044292 1993 2006
810044292 1994 2006
810044292 1995 2006
810044292 1996 2006
810044292 1997 2006
810044292 1998 2006
810044292 1999 2006
810044292 2000 2006
810044292 2001 2006
810044292 2002 2006
810044292 2003 2006
810044292 2004 2006
810044292 2005 2006
810059672 1995    0
810059672 1996    0
810059672 2000    0

Nick Cox Nick Cox · Accepted Answer · 2016-11-15T10:54:02

This may help. Note that expand ignores all arguments < 2.

clear 
input long orgnumber year bankruptcyyear
810037342 2009    0
810037342 2010    0
810037342 2011    0
810037342 2012    0
810044292 1993 2006
810044292 1994 2006
810044292 1995 2006
810044292 1996 2006
810044292 1997 2006
810044292 1998 2006
810044292 1999 2006
810044292 2000 2006
810044292 2001 2006
810044292 2002 2006
810044292 2003 2006
810044292 2004 2006
810044292 2005 2006
810059672 1995    0
810059672 1996    0
810059672 2000    0
end 
bysort orgnumber (year) : gen toexpand = cond(_n == _N, 1 + bankruptcyyear - year[_N], 0) 
expand toexpand 
bysort orgnumber (year) : replace year = year[_n-1] + 1 if toexpand 
list, sepby(orgnumber) 

     +----------------------------------------+
     | orgnumber   year   bankru~r   toexpand |
     |----------------------------------------|
  1. | 810037342   2009          0          0 |
  2. | 810037342   2010          0          0 |
  3. | 810037342   2011          0          0 |
  4. | 810037342   2012          0      -2011 |
     |----------------------------------------|
  5. | 810044292   1993       2006          0 |
  6. | 810044292   1994       2006          0 |
  7. | 810044292   1995       2006          0 |
  8. | 810044292   1996       2006          0 |
  9. | 810044292   1997       2006          0 |
 10. | 810044292   1998       2006          0 |
 11. | 810044292   1999       2006          0 |
 12. | 810044292   2000       2006          0 |
 13. | 810044292   2001       2006          0 |
 14. | 810044292   2002       2006          0 |
 15. | 810044292   2003       2006          0 |
 16. | 810044292   2004       2006          0 |
 17. | 810044292   2005       2006          2 |
 18. | 810044292   2006       2006          2 |
     |----------------------------------------|
 19. | 810059672   1995          0          0 |
 20. | 810059672   1996          0          0 |
 21. | 810059672   1997          0      -1999 |
     +----------------------------------------+

How to add additional years to an individual/company after last observed year?

1 Answers