year and month format

[Ed Suh]

Hello,

I created the month variable into Stata format from 201509 to 2015m9 using the command:

gen month = ym(floor(month/100), mod(month,100))
format month %tm

Now, I would like to do the reverse from 2015m9 to 201509. (%tm to numeric)

What would be the command for this? I read the 'date and time' Stata documentation and couldn't find the opposite.

Thank you so much!

[Nick Cox]

It's not surprising if this is not documented; in Stata's terms this is a poor way to hold a numeric date, if only because of the gaps of 89 between each December and the following January.

But it can be done, naturally.

clear
set obs 14 
gen mdate = ym(2004, 12) + _n
replace mdate = ym(2020, 9) in L 
format mdate %tm

gen wanted = 100 * year(dofm(mdate)) + month(dofm(mdate))

gen wanted2 = 196000 + 100 * floor(mdate/ 12) + mod(mdate, 12) + 1

l, sep(6)

     +----------------------------+
     |   mdate   wanted   wanted2 |
     |----------------------------|
  1. |  2005m1   200501    200501 |
  2. |  2005m2   200502    200502 |
  3. |  2005m3   200503    200503 |
  4. |  2005m4   200504    200504 |
  5. |  2005m5   200505    200505 |
  6. |  2005m6   200506    200506 |
     |----------------------------|
  7. |  2005m7   200507    200507 |
  8. |  2005m8   200508    200508 |
  9. |  2005m9   200509    200509 |
 10. | 2005m10   200510    200510 |
 11. | 2005m11   200511    200511 |
 12. | 2005m12   200512    200512 |
     |----------------------------|
 13. |  2006m1   200601    200601 |
 14. |  2020m9   202009    202009 |
     +----------------------------+

Note some abuse of terminology in #1: a variable with display format %tm is every bit (so to speak) as numeric as what is asked for here.

[Ed Suh]

Dear Nick,

Thank you so much! It works perfectly. I really appreciate your help.

[William Lisowski]

Let me add that if the 6-digit numeric version of the monthly date was wanted only for display purposes, it could be accomplished with an appropriate %tm format applied to the existing SIF version of the monthly value.

. l, sep(6)

     +---------+
     |   mdate |
     |---------|
  1. |  2005m1 |
  2. |  2005m2 |
  3. |  2005m3 |
  4. |  2005m4 |
  5. |  2005m5 |
  6. |  2005m6 |
     |---------|
  7. |  2005m7 |
  8. |  2005m8 |
  9. |  2005m9 |
 10. | 2005m10 |
 11. | 2005m11 |
 12. | 2005m12 |
     |---------|
 13. |  2006m1 |
 14. |  2020m9 |
     +---------+

. format mdate %tmCCYYNN

. l, sep(6)

     +--------+
     |  mdate |
     |--------|
  1. | 200501 |
  2. | 200502 |
  3. | 200503 |
  4. | 200504 |
  5. | 200505 |
  6. | 200506 |
     |--------|
  7. | 200507 |
  8. | 200508 |
  9. | 200509 |
 10. | 200510 |
 11. | 200511 |
 12. | 200512 |
     |--------|
 13. | 200601 |
 14. | 202009 |
     +--------+

From the output of help datetime follow the link to "datetime display formats" for details on constructing Stata datetime display formats.

[Ed Suh]

Dear William,

thank you so much! I was confused converting %tm to numeric but now I get it!

[Nick Cox]

As in #2 what you want is not a conversion between %tm and numeric because that wording confuses a display format and a storage type, more broadly a group of storage types.

Once again: Stata supports monthly dates held in a numeric variable with the following rule: January 1960 is, or was, origin 0, and later or earlier months are positive or negative counts away from that origin. Such storage is convenient for Stata and makes most manipulations easier, which is convenient for you: so for example, differences between dates (which we often need) are trivial and plotting against date. which we often need too. is then easy.

A date display format such as %tm is Stata's way of apologising and making up for the arbitrariness of that origin. It means nothing much to me that a monthly date is held as 728 although if obliged I could do the mental arithmetic to work out what that means in calendar terms. Applying a date display format means that I see text that explains and get the best of both worlds. What lies behind that display format is numeric, always.

. di ym(2020, 9)
728

. di ym(1960, 1)
0

. di %tm ym(2020, 9)
 2020m9

. di %tm ym(1960, 1)
 1960m1

What you want is therefore a conversion between different numeric representations -- except that as William Lisowski explains an appropriate display format will let you see (e.g.) 202009 even though Stata is holding 728 in storage. (Strictly it is holding 1011011000 as a binary number, but there you go.)

More at https://www.stata-journal.com/articl...article=dm0067 (which links to a freely available pdf) .

[Jane Quan]

It's not surprising if this is not documented; in Stata's terms this is a poor way to hold a numeric date, if only because of the gaps of 89 between each December and the following January.

But it can be done, naturally.

clear
set obs 14
gen mdate = ym(2004, 12) + _n
replace mdate = ym(2020, 9) in L
format mdate %tm

gen wanted = 100 * year(dofm(mdate)) + month(dofm(mdate))

gen wanted2 = 196000 + 100 * floor(mdate/ 12) + mod(mdate, 12) + 1

l, sep(6)

+----------------------------+
| mdate wanted wanted2 |
|----------------------------|
1. | 2005m1 200501 200501 |
2. | 2005m2 200502 200502 |
3. | 2005m3 200503 200503 |
4. | 2005m4 200504 200504 |
5. | 2005m5 200505 200505 |
6. | 2005m6 200506 200506 |
|----------------------------|
7. | 2005m7 200507 200507 |
8. | 2005m8 200508 200508 |
9. | 2005m9 200509 200509 |
10. | 2005m10 200510 200510 |
11. | 2005m11 200511 200511 |
12. | 2005m12 200512 200512 |
|----------------------------|
13. | 2006m1 200601 200601 |
14. | 2020m9 202009 202009 |
+----------------------------+

Note some abuse of terminology in #1: a variable with display format %tm is every bit (so to speak) as numeric as what is asked for here.

Hi Nick,

This method perfectly converts dates from 2015m9 to 201509, as the poster claimed.
However, when I use the "xtset" command, it shows there are gaps, even though there are none.
Is there a way to use the year and month in numeric format without gaps? The command I use needs to recognize both the year and month as numeric.

Thanks.

[Nick Cox]

I was and am not recommending integers such as 201509 for analysis, merely showing how to get them if you need that for some bizarre purpose.

So, just don't convert like that. Use proper monthly dates.

[Clyde Schechter]

No new contribution by me here, but I just want to emphasize an aspect of this that has already been mentioned, but not highlighted:

Re #7: As Nick pointed out in #2, and you echoed in #7: "in Stata's terms this is a poor way to hold a numeric date, if only because of the gaps of 89 between each December and the following January." [emphasis added] These are exactly the gaps that -xtset- is pointing out. And, no, there is no way to avoid this if you insist on using a numeric variable where the number itself, in decimal, looks like a date. To get consecutive dates to have consecutive numeric representations requires using proper Stata monthly date variables.