Extracting/filling year and monthly variables in Stata

Question

[]

Above is my data in Stata. The data are xtset using incidentCode and monthlyDate. I need to either interpolate or fill or extract missing values for the variables called, year and month. The variable called monthlyDate is a numeric datetime variable, not a string! It is formatted using %tm.

Here is what I have already tried:

1. When I convert monthlyDate using tostring, I get gibberish.

2.When I use ipolate to fill the missing values in year, I get decimal places that can't be rounded properly because my data are xtset using months.

3.When I try carryforward using the monthlyDate variable, I also get gibberish.

4.Finally, when I try to extract the year from the datetime variable using the year argument/command, the values are meaningless.

Answer 1

A monthly date is an integer with ..., -1 at December 1959, 0 at January 1960, 1 at February 1960, and so on. It is not a yearly or daily date, but one simple way to extract year or month components is often to convert it first to a daily date.

* Example generated by -dataex-. For more info, type help dataex
clear
input float mdate
518
554
594
670
end

format mdate %tm
gen year = year(dofm(mdate))
gen month = month(dofm(mdate))

list

     +------------------------+
     |   mdate   year   month |
     |------------------------|
  1. |  2003m3   2003       3 |
  2. |  2006m3   2006       3 |
  3. |  2009m7   2009       7 |
  4. | 2015m11   2015      11 |
     +------------------------+

Of your solutions,

1 is at best a step in the wrong direction, as the conversion problem is numeric to numeric.

2 can't help here if only because neither year nor month is linear in monthly date, so ipolate can't apply.

3 presumably fails because your dates are not regular sequences.

4 fails because year() feeds on daily dates, not monthly dates.

Direct solutions include

gen month = 1 + mod(mdate, 12)
gen year = 1960 + floor(mdate/12)