Estimate monthly data with annual time series data

[Silvia Kim]

I would like to calculate an estimate of the monthly population with population data sorted by region, gender, age group, and year.
I calculated the estimate by subtracting the previous year's data from the following year's data and adding the monthly value divided by 12.
For example, since the population as of January 1998 was 23424, and 1999 was 25832 (25832-23424)/12 was added to 23424 and estimated the value for February 1998.
What do I write to create time (year and month) variables and calculate the estimate, as shown in the figure from the left to the right in the attached figure?




Thank you in advance for your help.

[Andrew Musau]

Please use dataex when presenting a data example in the future.

* Example generated by -dataex-. To install: ssc install dataex
clear
input float(resid sex ageg year population)
1 2 1 1998 23424
1 2 1 1999 25832
1 2 1 2000 27828
1 2 1 2001 29323
end

expand 12, g(new)
replace pop=. if new
bys resid sex ageg (year new): gen month=_n
bys resid sex ageg: ipolate pop month, gen(wanted) epolate
bys resid sex ageg year (month): replace month=_n

Res.:

. l, sepby(resid sex ageg year)

     +----------------------------------------------------------------+
     | resid   sex   ageg   year   popula~n   new   month      wanted |
     |----------------------------------------------------------------|
  1. |     1     2      1   1998      23424     0       1       23424 |
  2. |     1     2      1   1998          .     1       2   23624.667 |
  3. |     1     2      1   1998          .     1       3   23825.333 |
  4. |     1     2      1   1998          .     1       4       24026 |
  5. |     1     2      1   1998          .     1       5   24226.667 |
  6. |     1     2      1   1998          .     1       6   24427.333 |
  7. |     1     2      1   1998          .     1       7       24628 |
  8. |     1     2      1   1998          .     1       8   24828.667 |
  9. |     1     2      1   1998          .     1       9   25029.333 |
 10. |     1     2      1   1998          .     1      10       25230 |
 11. |     1     2      1   1998          .     1      11   25430.667 |
 12. |     1     2      1   1998          .     1      12   25631.333 |
     |----------------------------------------------------------------|
 13. |     1     2      1   1999      25832     0       1       25832 |
 14. |     1     2      1   1999          .     1       2   25998.333 |
 15. |     1     2      1   1999          .     1       3   26164.667 |
 16. |     1     2      1   1999          .     1       4       26331 |
 17. |     1     2      1   1999          .     1       5   26497.333 |
 18. |     1     2      1   1999          .     1       6   26663.667 |
 19. |     1     2      1   1999          .     1       7       26830 |
 20. |     1     2      1   1999          .     1       8   26996.333 |
 21. |     1     2      1   1999          .     1       9   27162.667 |
 22. |     1     2      1   1999          .     1      10       27329 |
 23. |     1     2      1   1999          .     1      11   27495.333 |
 24. |     1     2      1   1999          .     1      12   27661.667 |
     |----------------------------------------------------------------|
 25. |     1     2      1   2000      27828     0       1       27828 |
 26. |     1     2      1   2000          .     1       2   27952.583 |
 27. |     1     2      1   2000          .     1       3   28077.167 |
 28. |     1     2      1   2000          .     1       4    28201.75 |
 29. |     1     2      1   2000          .     1       5   28326.333 |
 30. |     1     2      1   2000          .     1       6   28450.917 |
 31. |     1     2      1   2000          .     1       7     28575.5 |
 32. |     1     2      1   2000          .     1       8   28700.083 |
 33. |     1     2      1   2000          .     1       9   28824.667 |
 34. |     1     2      1   2000          .     1      10    28949.25 |
 35. |     1     2      1   2000          .     1      11   29073.833 |
 36. |     1     2      1   2000          .     1      12   29198.417 |
     |----------------------------------------------------------------|
 37. |     1     2      1   2001      29323     0       1       29323 |
 38. |     1     2      1   2001          .     1       2   29447.583 |
 39. |     1     2      1   2001          .     1       3   29572.167 |
 40. |     1     2      1   2001          .     1       4    29696.75 |
 41. |     1     2      1   2001          .     1       5   29821.333 |
 42. |     1     2      1   2001          .     1       6   29945.917 |
 43. |     1     2      1   2001          .     1       7     30070.5 |
 44. |     1     2      1   2001          .     1       8   30195.083 |
 45. |     1     2      1   2001          .     1       9   30319.667 |
 46. |     1     2      1   2001          .     1      10    30444.25 |
 47. |     1     2      1   2001          .     1      11   30568.833 |
 48. |     1     2      1   2001          .     1      12   30693.417 |
     +----------------------------------------------------------------+

[Nick Cox]

Although Andrew Musau gave an excellent answer as you asked, a slightly better method in the absence of other information is geometric interpolation, i.e. linear interpolation of the logarithms, exponentiated.

Also, sooner or later you will need a monthly date variable such as

gen mdate = ym(year, month) 
format mdate %tm 

[Silvia Kim]

Thank you so much. It was really helpful.