Creating new years

[Norbert Langerak]

Dear all,

I have the following issue with my dataset. Every observation applies to multiple years (beginning year until ending year) but only a single observation is created. Because of this I want to create new observations for the missing ones. For example, the first observation applies to 2002 and 2003 but only 2002 is present in my dataset so I want to add 2003. I was able to create a variable years_to_fill that states how many observations I want to create after the current observation. So for the first observation, I want to create 1 new observation and label that with year+1. I tried using tsset with tsfill but this doesn't work for all my observations. For example, for id 4 I want to create three new observations, 1991 1992 and 1993. If I do tsfill id year it however doesn't create any new observations. Can somebody please tell me how I am supposed to do this?

Thanks in advance
Norbert Langerak

id	year	beg_year	end_year	years_to_fill
1	2002	2002	2003	1
1	2004	2004	2005	1
1	2006	2006	2007	1
2	2004	2004	2005	1
2	2006	2006	2007	1
3	1998	1998	2000	1
3	2000	2000	2002	1
3	2002	2002	2003	1
3	2004	2004	2005	1
4	1990	1990	1993	3
5	1998	1998	2000	1
5	2000	2000	2002	1
5	2002	2002	2003	1
6	1990	1990	1993	2
6	1993	1993	1995	1
6	1995	1995	1998	3
7	1993	1993	1995	1
7	1995	1995	1998	2
7	1998	1998	2000	1
7	2000	2000	2002	1
7	2002	2002	2003	1
7	2004	2004	2005	1
7	2006	2006	2007	1
8	2006	2006	2007	1
9	2004	2004	2005	1
9	2006	2006	2007	1
10	1990	1990	1993	2
10	1993	1993	1995	1
10	1995	1995	1998	3
10	2002	2002	2003	1
10	2004	2004	2005	1
10	2006	2006	2007	1
11	2006	2006	2007	1
12	1990	1990	1993	3

[Rich Goldstein]

you need to use -expand- first; see

help expand

[Andrew Musau]

I would be interested in seeing a panel-specific application of expand since the command is not byable. Here is another approach.

* Example generated by -dataex-. To install: ssc install dataex
clear
input byte var1 int(year beg_year end_year) byte years_to_fill
 1 2002 2002 2003 1
 1 2004 2004 2005 1
 1 2006 2006 2007 1
 2 2004 2004 2005 1
 2 2006 2006 2007 1
 3 1998 1998 2000 1
 3 2000 2000 2002 1
 3 2002 2002 2003 1
 3 2004 2004 2005 1
 4 1990 1990 1993 3
 5 1998 1998 2000 1
 5 2000 2000 2002 1
 5 2002 2002 2003 1
 6 1990 1990 1993 2
 6 1993 1993 1995 1
 6 1995 1995 1998 3
 7 1993 1993 1995 1
 7 1995 1995 1998 2
 7 1998 1998 2000 1
 7 2000 2000 2002 1
 7 2002 2002 2003 1
 7 2004 2004 2005 1
 7 2006 2006 2007 1
 8 2006 2006 2007 1
 9 2004 2004 2005 1
 9 2006 2006 2007 1
10 1990 1990 1993 2
10 1993 1993 1995 1
10 1995 1995 1998 3
10 2002 2002 2003 1
10 2004 2004 2005 1
10 2006 2006 2007 1
11 2006 2006 2007 1
12 1990 1990 1993 3
end

bys var1: egen start= min(beg_year)
bys var1: egen end= max(end_year)
gen duration= (end-start)+1
keep var1 start end duration
bys var1: keep if _n==1
qui sum duration
forval i=1/`r(max)'{
gen year`i'= cond(duration>=`i',start-1+`i',.)
}
reshape long year, i(var1)
drop if missing(year)

Result:

. l, sepby(var1)

     +--------------------------------------------+
     | var1   _j   start    end   duration   year |
     |--------------------------------------------|
  1. |    1    1    2002   2007          6   2002 |
  2. |    1    2    2002   2007          6   2003 |
  3. |    1    3    2002   2007          6   2004 |
  4. |    1    4    2002   2007          6   2005 |
  5. |    1    5    2002   2007          6   2006 |
  6. |    1    6    2002   2007          6   2007 |
     |--------------------------------------------|
  7. |    2    1    2004   2007          4   2004 |
  8. |    2    2    2004   2007          4   2005 |
  9. |    2    3    2004   2007          4   2006 |
 10. |    2    4    2004   2007          4   2007 |
     |--------------------------------------------|
 11. |    3    1    1998   2005          8   1998 |
 12. |    3    2    1998   2005          8   1999 |
 13. |    3    3    1998   2005          8   2000 |
 14. |    3    4    1998   2005          8   2001 |
 15. |    3    5    1998   2005          8   2002 |
 16. |    3    6    1998   2005          8   2003 |
 17. |    3    7    1998   2005          8   2004 |
 18. |    3    8    1998   2005          8   2005 |
     |--------------------------------------------|
 19. |    4    1    1990   1993          4   1990 |
 20. |    4    2    1990   1993          4   1991 |
 21. |    4    3    1990   1993          4   1992 |
 22. |    4    4    1990   1993          4   1993 |
     |--------------------------------------------|
 23. |    5    1    1998   2003          6   1998 |
 24. |    5    2    1998   2003          6   1999 |
 25. |    5    3    1998   2003          6   2000 |
 26. |    5    4    1998   2003          6   2001 |
 27. |    5    5    1998   2003          6   2002 |
 28. |    5    6    1998   2003          6   2003 |
     |--------------------------------------------|
 29. |    6    1    1990   1998          9   1990 |
 30. |    6    2    1990   1998          9   1991 |
 31. |    6    3    1990   1998          9   1992 |
 32. |    6    4    1990   1998          9   1993 |
 33. |    6    5    1990   1998          9   1994 |
 34. |    6    6    1990   1998          9   1995 |
 35. |    6    7    1990   1998          9   1996 |
 36. |    6    8    1990   1998          9   1997 |
 37. |    6    9    1990   1998          9   1998 |
     |--------------------------------------------|
 38. |    7    1    1993   2007         15   1993 |
 39. |    7    2    1993   2007         15   1994 |
 40. |    7    3    1993   2007         15   1995 |
 41. |    7    4    1993   2007         15   1996 |
 42. |    7    5    1993   2007         15   1997 |
 43. |    7    6    1993   2007         15   1998 |
 44. |    7    7    1993   2007         15   1999 |
 45. |    7    8    1993   2007         15   2000 |
 46. |    7    9    1993   2007         15   2001 |
 47. |    7   10    1993   2007         15   2002 |
 48. |    7   11    1993   2007         15   2003 |
 49. |    7   12    1993   2007         15   2004 |
 50. |    7   13    1993   2007         15   2005 |
 51. |    7   14    1993   2007         15   2006 |
 52. |    7   15    1993   2007         15   2007 |
     |--------------------------------------------|
 53. |    8    1    2006   2007          2   2006 |
 54. |    8    2    2006   2007          2   2007 |
     |--------------------------------------------|
 55. |    9    1    2004   2007          4   2004 |
 56. |    9    2    2004   2007          4   2005 |
 57. |    9    3    2004   2007          4   2006 |
 58. |    9    4    2004   2007          4   2007 |
     |--------------------------------------------|
 59. |   10    1    1990   2007         18   1990 |
 60. |   10    2    1990   2007         18   1991 |
 61. |   10    3    1990   2007         18   1992 |
 62. |   10    4    1990   2007         18   1993 |
 63. |   10    5    1990   2007         18   1994 |
 64. |   10    6    1990   2007         18   1995 |
 65. |   10    7    1990   2007         18   1996 |
 66. |   10    8    1990   2007         18   1997 |
 67. |   10    9    1990   2007         18   1998 |
 68. |   10   10    1990   2007         18   1999 |
 69. |   10   11    1990   2007         18   2000 |
 70. |   10   12    1990   2007         18   2001 |
 71. |   10   13    1990   2007         18   2002 |
 72. |   10   14    1990   2007         18   2003 |
 73. |   10   15    1990   2007         18   2004 |
 74. |   10   16    1990   2007         18   2005 |
 75. |   10   17    1990   2007         18   2006 |
 76. |   10   18    1990   2007         18   2007 |
     |--------------------------------------------|
 77. |   11    1    2006   2007          2   2006 |
 78. |   11    2    2006   2007          2   2007 |
     |--------------------------------------------|
 79. |   12    1    1990   1993          4   1990 |
 80. |   12    2    1990   1993          4   1991 |
 81. |   12    3    1990   1993          4   1992 |
 82. |   12    4    1990   1993          4   1993 |
     +--------------------------------------------+

[Norbert Langerak]

Dear Rich Goldstein,

This does create the new rows I want, however it keeps the old year. I want to create new observations with the new year, for example 2002 and 2003 for the first observation but now I have two times 2002. Do you know how I could fix this?

[Norbert Langerak]

Andrew Masau your code works almost perfect for my example. The only issue I see however is that for id10 it creates too many observations. One of the enddates is 1998 and the first startdate after that is 2002. Therefore 1999, 2000 and 2001 should not appear but with your formula they do.

[Andrew Musau]

I didn't pay attention to the non-consecutive spells within panels. You can do the expansion at any level.

gen obs=_n
bys obs: egen start= min(beg_year)
bys obs: egen end= max(end_year)
gen duration= (end-start)+1
keep obs var1 start end duration
qui sum duration
forval i=1/`r(max)'{
 gen year`i'= cond(duration>=`i',start-1+`i',.)
}
reshape long year, i(obs)
drop if missing(year)
duplicates drop var1 year, force
sort var1 year
l, sepby(var1)

[William Lisowski]

In reply to post #4, here's how to use expand for your problem.

expand years_to_fill+1, generate(new)
sort id year new
replace year = year[_n-1]+1 if new
list if id<=3, sepby(id) noobs

. list if id<=3, sepby(id) noobs

  +--------------------------------------------------+
  | id   year   beg_year   end_year   years_~l   new |
  |--------------------------------------------------|
  |  1   2002       2002       2003          1     0 |
  |  1   2003       2002       2003          1     1 |
  |  1   2004       2004       2005          1     0 |
  |  1   2005       2004       2005          1     1 |
  |  1   2006       2006       2007          1     0 |
  |  1   2007       2006       2007          1     1 |
  |--------------------------------------------------|
  |  2   2004       2004       2005          1     0 |
  |  2   2005       2004       2005          1     1 |
  |  2   2006       2006       2007          1     0 |
  |  2   2007       2006       2007          1     1 |
  |--------------------------------------------------|
  |  3   1998       1998       2000          1     0 |
  |  3   1999       1998       2000          1     1 |
  |  3   2000       2000       2002          1     0 |
  |  3   2001       2000       2002          1     1 |
  |  3   2002       2002       2003          1     0 |
  |  3   2003       2002       2003          1     1 |
  |  3   2004       2004       2005          1     0 |
  |  3   2005       2004       2005          1     1 |
  +--------------------------------------------------+