Generate date variable from string monthly data

[Francisco Nobre]

Dear all,

I have my date variable in the following string format 201501, 201502, 201503, etc..
I would like to generate a date variable. I have been using the following command gen month=monthly(date,"YM"). However, I am just getting a sequence of missing values.
Do you know where the problem lies?
Many thanks in advance!

Francisco

[Nick Cox]

The limitation here is documented, e.g. within help datetime in Stata 17: my emphasis here:

However, the functions marked with an asterisk [NJC: including monthly()] require that the string date contain a space or punctuation between the year and the other component if the string consists only of numbers.

There are at least two ways forward. First, you need some surgery or engineering to separate year and month components. Second, numdate from SSC works harder on such dates than monthly().

. clear

. set obs 5
Number of observations (_N) was 0, now 5.

. gen problem = strofreal(200500 + _n)

. l

     +---------+
     | problem |
     |---------|
  1. |  200501 |
  2. |  200502 |
  3. |  200503 |
  4. |  200504 |
  5. |  200505 |
     +---------+

. gen wanted1 = ym(floor(real(problem)/100), mod(real(problem), 100))

. format wanted1 %tm

. numdate tm wanted2 = problem, pattern(YM)

. list

     +-----------------------------+
     | problem   wanted1   wanted2 |
     |-----------------------------|
  1. |  200501    2005m1    2005m1 |
  2. |  200502    2005m2    2005m2 |
  3. |  200503    2005m3    2005m3 |
  4. |  200504    2005m4    2005m4 |
  5. |  200505    2005m5    2005m5 |
     +-----------------------------+

[William Lisowski]

Yes, the monthly() function is inexplicably poor at parsing 6-digit year-and-month with no separator between the two. You have several options to avoid this.

1. Install the community contributed numdate command from SSC

ssc install numdate

And then use it to create the Stata Internal Format monthly date.

. numdate monthly month = date, pattern("YM")

. describe date month

              storage   display    value
variable name   type    format     label      variable label
------------------------------------------------------------------------------------------------
date            str6    %9s                  
month           float   %tm                   monthly date from date

. list, clean

         date    month  
  1.   201501   2015m1  
  2.   201502   2015m2  
  3.   201503   2015m3  

.

2. As an alternative, add a separator to the string and use monthly() on that.

. generate datedash = substr(date,1,4)+"-"+substr(date,5,2)

. generate month = monthly(datedash,"YM")

. format month %tm

. describe date datedash month

              storage   display    value
variable name   type    format     label      variable label
------------------------------------------------------------------------------------------------
date            str6    %9s                  
datedash        str7    %9s                  
month           float   %tm                  

. list, clean

         date   datedash    month  
  1.   201501    2015-01   2015m1  
  2.   201502    2015-02   2015m2  
  3.   201503    2015-03   2015m3  

.

Added in edit: This crossed with Nick's post #2 presenting a third approach, along with my favorite, his numdate command. I will add that the explanation he highlights is, I believe, new in Stata 17, and while appreciated, remains, to me, an inexplicable limitation.

[Francisco Nobre]

Than you both for your very useful comments!