Formatting date variable YYYY-MM

[Samuel Pfaff]

Hello everyone,

I apologize if this a rather easy problem, but I am new to Stata and have tried everything I could but nothing worked so far...

I have a date in the format of YYYY-MM (e.g. 2004-12) and I want to format it in a way that it is displayed as 2004m12

I already tried using .the date function and then formating the new variable using %tm, but this didn't work...

I really appreciate your help and time!

Thanks a lot in advance!

[Nick Cox]

date() is for producing daily dates and not applicable here.

This could mean that you have a string variable with values like "2004-12" or that you have a numeric date variable with display format such that you see 2004-12, e.g.

. di %tmCY!-N ym(2004, 12)
2004-12

Which is it? As emphasised at FAQ Advice #12 descriptions of dates are usually ambiguous without an explicit data example such as we request.

[Samuel Pfaff]

sorry for not being mindful of the FAQ advice. Indeed, I have a string variable (loaded into Stata from a csv file)

input str26(month_id SVI)
"2004-01" "22"
"2004-02" "11"
"2004-03" "19"
"2004-04" "13"
"2004-05" "31"
"2004-06" "23"
"2004-07" "6"
"2004-08" "5"
"2004-09" "23"
"2004-10" "23"
"2004-11" "18"
"2004-12" "14"

[Nick Cox]

That fixes it; thanks. Here are two ways to do it. numdate must be installed from SSC before it can be used but assigns a default display format for you.

clear
input str26(month_id SVI)
"2004-01" "22"
"2004-02" "11"
"2004-03" "19"
"2004-04" "13"
"2004-05" "31"
"2004-06" "23"
"2004-07" "6"
"2004-08" "5"
"2004-09" "23"
"2004-10" "23"
"2004-11" "18"
end 

gen mdate = monthly(month_id, "YM")
format mdate %tm 

ssc install numdate 
numdate tm mdate2 = month_id, pattern(YM)

list m*, sep(0)

     +------------------------------+
     | month_id     mdate    mdate2 |
     |------------------------------|
  1. |  2004-01    2004m1    2004m1 |
  2. |  2004-02    2004m2    2004m2 |
  3. |  2004-03    2004m3    2004m3 |
  4. |  2004-04    2004m4    2004m4 |
  5. |  2004-05    2004m5    2004m5 |
  6. |  2004-06    2004m6    2004m6 |
  7. |  2004-07    2004m7    2004m7 |
  8. |  2004-08    2004m8    2004m8 |
  9. |  2004-09    2004m9    2004m9 |
 10. |  2004-10   2004m10   2004m10 |
 11. |  2004-11   2004m11   2004m11 |
     +------------------------------+

[Samuel Pfaff]

Thank you so much, this solved the issue!

Please allow me one follow-up question:
I want to merge two data sets on the month_id as key variable.
Thanks to your help, I was able to transform the dates e.g. from "2004-01" to "2004m1") in the first data set.

Now, in my second data set, the month_id is also displayed as e.g. "2004m1", however, this time as string (and not as float as in the first data set).
Since I cannot merge two data sets, if the key variable doesn't have the same type, I tried to convert the string to a float using the "destring" command.
However, this doesn't work and Stata says "month_id: contains nonnumeric characters; no replace"

I would be really glad if someone could help me out with this.
Thanks already for your patience,

Samuel

[Nick Cox]

The answer is the same as #2. If a variable is string and contains dates like "2004m1" then this example shows that monthly() is the function of choice:

. di  monthly("2004m1", "YM")
528

. di %tm monthly("2004m1", "YM")
 2004m1

In your case you could apply generate to create a new variable -- or numdate.

As the original author of destring I can attest that it was never intended to work on dates -- unless years that happen to be string. In this case removing the m makes conversion to a numeric date more difficult, not easier. this. Unfortunately, at least one example in the manual entry implies otherwise and I have been lobbying StataCorp to change it.

https://www.stata-journal.com/articl...article=dm0098 is a round-up of technique that may be accessible but otherwise will emerge from behind a paywall around December 2021.

[Samuel Pfaff]

Again, thank you so much Nick, that solved my problem!