interpolate between two year dates using expand

[tom berk]

Hi all,

For my panel data, I want to linearly interpolate between two years. I have data for the periods 1980-1989 and of 2000-2010 for every 'fipsmerg'. I want to linearly generate new observations that show interpolated values of the period between 1989 and 2000.

* Example generated by -dataex-. For more info, type help dataex
clear
input long fipsmerg int year double Male
1001 1980  96.54099560761347
1001 1981  96.25107375138053
1001 1982  96.02300538423887
1001 1983  95.93545626795428
1001 1984   95.8071789871412
1001 1985  95.58466763706939
1001 1986  95.40810264940002
1001 1987  95.29176940237173
1001 1988  95.18946207857019
1001 1989  95.03183982559807
1001 2000  94.45999908447266
1001 2001  94.52999877929688
1001 2002  94.97000122070313
1001 2003  94.68000030517578
1001 2004   94.5999984741211
1001 2005  94.98999786376953
1001 2006  94.87000274658203
1001 2007  94.55000305175781
1001 2008  94.41000366210938
1001 2009  94.93000030517578
1001 2010  94.87999725341797
1003 1980  95.39413438930825
1003 1981  95.21042522671465
1003 1982  94.97466016198551
1003 1983  94.96680131866091
1003 1984  94.86736000359397
1003 1985  94.68446496232743
1003 1986  94.62432057977193
1003 1987  94.60937826214038
1003 1988  94.55704008221994
1003 1989  94.47128504720128
1003 2000  96.19999694824219
1003 2001  95.87000274658203
1003 2002  95.81999969482422
1003 2003   95.9800033569336
1003 2004   96.0199966430664
1003 2005  95.94999694824219
1003 2006   95.6500015258789
1003 2007   95.4000015258789
1003 2008  95.30000305175781
1003 2009  95.62999725341797
1003 2010  95.83999633789063
end

I have tried some variations on a previous provided solution from Nick Cox but I am stilll to create what I want. That code is:

bysort fipsmerg year : replace year = year + _n - 1  
gen logMale = log(Male) if mod(year, 10) == 0  
ipolate logMale year, by(fipsmerg) gen(ipolated)
replace ipolated = exp(ipolated)

Any suggestions?

Best,

Tom

[Nick Cox]

As you say, your solution requires expand but I can't see that in your code. I set on one side whether you need to interpolate logarithms and then exponentiate.

expand 11 if year == 1989, gen(extra)

bysort fipsmerg (year extra) : replace year = year[_n-1] + 1 if extra

replace Male = . if extra

ipolate Male year, gen(Male2) by(fipsmerg)

list if inlist(1, extra, extra[_n-1], extra[_n+1]), sepby(fipsmerg)

    +----------------------------------------------------+
     | fipsmerg   year        Male      extra       Male2 |
     |----------------------------------------------------|
 10. |     1001   1989    95.03184   Original    95.03184 |
 11. |     1001   1990           .   Duplicat   94.979854 |
 12. |     1001   1991           .   Duplicat   94.927869 |
 13. |     1001   1992           .   Duplicat   94.875883 |
 14. |     1001   1993           .   Duplicat   94.823898 |
 15. |     1001   1994           .   Duplicat   94.771912 |
 16. |     1001   1995           .   Duplicat   94.719927 |
 17. |     1001   1996           .   Duplicat   94.667941 |
 18. |     1001   1997           .   Duplicat   94.615956 |
 19. |     1001   1998           .   Duplicat    94.56397 |
 20. |     1001   1999           .   Duplicat   94.511985 |
 21. |     1001   2000   94.459999   Original   94.459999 |
     |----------------------------------------------------|
 41. |     1003   1989   94.471285   Original   94.471285 |
 42. |     1003   1990           .   Duplicat   94.628441 |
 43. |     1003   1991           .   Duplicat   94.785596 |
 44. |     1003   1992           .   Duplicat   94.942752 |
 45. |     1003   1993           .   Duplicat   95.099908 |
 46. |     1003   1994           .   Duplicat   95.257063 |
 47. |     1003   1995           .   Duplicat   95.414219 |
 48. |     1003   1996           .   Duplicat   95.571374 |
 49. |     1003   1997           .   Duplicat    95.72853 |
 50. |     1003   1998           .   Duplicat   95.885686 |
 51. |     1003   1999           .   Duplicat   96.042841 |
 52. |     1003   2000   96.199997   Original   96.199997 |
     +----------------------------------------------------+

[tom berk]

Hi Nick Cox,
Worked perfectly fine again. Thank you.