Reformat date variable to yyyy-mm

[Hanna Richardsson]

Hi dear Stata users!

I am trying to reformate my date variable from yyyy-mm-dd to yyyy-mm but nothing is working although it feels like it should be quite simple. What code can I use for this?

And if someone has a better idea for the analysis I am all ears. We have various different data sets that will be merged but their dates are not the same so we thought we should only write month and year but if something else could work better we would love to hear it. Another issue is that three data sets have around 10 observations per month while the fourth one only has 1 per month. See below for an excerpt of the data for one of the first three data sets.

Date /// Europertonne
"2012-11-22" 6.62
"2012-11-23" 6.62
"2012-11-27" 6.49
"2012-11-29" 6.48
"2012-11-30" 6.05
"2012-12-04" 5.78
"2012-12-06" 6.12
"2012-12-07" 6.22
"2012-12-10" 6.53
"2012-12-11" 6.86
"2012-12-13" 6.51
"2012-12-14" 6.5
"2012-12-17" 6.29
"2012-12-18" 6.32
"2013-01-07" 6.1
"2013-01-08" 6.26
"2013-01-10" 5.87
"2013-01-11" 5.69
"2013-01-14" 5.53
"2013-01-15" 5.71
"2013-01-17" 5.36
"2013-01-21" 4.57
"2013-01-22" 4.71
"2013-01-24" 4.44
"2013-01-25" 3.82
"2013-01-28" 3.83
"2013-01-29" 3.73
"2013-01-31" 3.42
"2013-02-01" 3.09
"2013-02-04" 3.91
"2013-02-05" 3.93
"2013-02-07" 3.99
"2013-02-08" 4.14
"2013-02-11" 4.07
"2013-02-12" 4.22
"2013-02-14" 4.75
"2013-02-15" 4.75
"2013-02-18" 4.85
"2013-02-19" 4.2
"2013-02-21" 4.5
"2013-02-25" 4.59
"2013-02-26" 4.17
"2013-02-26" 4.1
"2013-02-28" 4.35
"2013-03-01" 4.48

Best regards,
Hanna

[Carolina Hincapie]

Dear Hanna,

Is the variable Date in format: %td?
if it is the case use

 format Date %tm

Here some useful links:

ddatetimedisplayformats.pdf (stata.com)

ddatetime.pdf (stata.com)



Best,

[Nick Cox]

Thanks for the example in #1 which is close to what dataex could provide. Your date is a string daily date, so could be rendered as a numeric daily date. A monthly date would not be a "reformatting" of a daily date, as you lose information by discarding day of the month. There are at least two ways to do that, using the daily date or by using the relevant parts of the string variable.

Naturally we can't explain what was wrong with what you tried, as you don't show us any code.

The links in #2 are exactly on target but otherwise the advice in #2 is unfortunately wrong.

In the first place, the daily date in #1 is clearly a string variable and could not have a daily date display format. In the second place assigning a daily date a monthly date display format just produces nonsense. This is because a daily date is the number of days since 1 January 1960. If you tell Stata that the same numbers should be displayed as a monthly date you get a date far into the future.

Here is a quick demonstration:

. di daily("4 April 2024", "DMY")
23470

. di %td daily("4 April 2024", "DMY")
04apr2024

. di %tm daily("4 April 2024", "DMY")
3915m11

For more on the misconception that changing the display format changes the stored value, see https://www.stata-journal.com/articl...article=dm0067

Here is some code related to #1.

* Example generated by -dataex-. For more info, type help dataex
clear
input str10 Date
"2012-11-22"
"2012-11-23"
"2012-11-27"
"2012-11-29"
"2012-11-30"
"2012-12-04"
"2012-12-06"
"2012-12-07"
"2012-12-10"
"2012-12-11"
"2012-12-13"
"2012-12-14"
"2012-12-17"
"2012-12-18"
"2013-01-07"
"2013-01-08"
"2013-01-10"
"2013-01-11"
"2013-01-14"
"2013-01-15"
"2013-01-17"
"2013-01-21"
"2013-01-22"
"2013-01-24"
"2013-01-25"
"2013-01-28"
"2013-01-29"
"2013-01-31"
"2013-02-01"
"2013-02-04"
"2013-02-05"
"2013-02-07"
"2013-02-08"
"2013-02-11"
"2013-02-12"
"2013-02-14"
"2013-02-15"
"2013-02-18"
"2013-02-19"
"2013-02-21"
"2013-02-25"
"2013-02-26"
"2013-02-26"
"2013-02-28"
"2013-03-01"
end

gen ddate = daily(Date, "YMD")

gen mdate1 = mofd(ddate)

gen mdate2 = monthly(substr(Date, 1, 7), "YM")

format ddate %td 

format mdate* %tm 

list, sepby(mdate1)

     +--------------------------------------------+
     |       Date       ddate    mdate1    mdate2 |
     |--------------------------------------------|
  1. | 2012-11-22   22nov2012   2012m11   2012m11 |
  2. | 2012-11-23   23nov2012   2012m11   2012m11 |
  3. | 2012-11-27   27nov2012   2012m11   2012m11 |
  4. | 2012-11-29   29nov2012   2012m11   2012m11 |
  5. | 2012-11-30   30nov2012   2012m11   2012m11 |
     |--------------------------------------------|
  6. | 2012-12-04   04dec2012   2012m12   2012m12 |
  7. | 2012-12-06   06dec2012   2012m12   2012m12 |
  8. | 2012-12-07   07dec2012   2012m12   2012m12 |
  9. | 2012-12-10   10dec2012   2012m12   2012m12 |
 10. | 2012-12-11   11dec2012   2012m12   2012m12 |
 11. | 2012-12-13   13dec2012   2012m12   2012m12 |
 12. | 2012-12-14   14dec2012   2012m12   2012m12 |
 13. | 2012-12-17   17dec2012   2012m12   2012m12 |
 14. | 2012-12-18   18dec2012   2012m12   2012m12 |
     |--------------------------------------------|
 15. | 2013-01-07   07jan2013    2013m1    2013m1 |
 16. | 2013-01-08   08jan2013    2013m1    2013m1 |
 17. | 2013-01-10   10jan2013    2013m1    2013m1 |
 18. | 2013-01-11   11jan2013    2013m1    2013m1 |
 19. | 2013-01-14   14jan2013    2013m1    2013m1 |
 20. | 2013-01-15   15jan2013    2013m1    2013m1 |
 21. | 2013-01-17   17jan2013    2013m1    2013m1 |
 22. | 2013-01-21   21jan2013    2013m1    2013m1 |
 23. | 2013-01-22   22jan2013    2013m1    2013m1 |
 24. | 2013-01-24   24jan2013    2013m1    2013m1 |
 25. | 2013-01-25   25jan2013    2013m1    2013m1 |
 26. | 2013-01-28   28jan2013    2013m1    2013m1 |
 27. | 2013-01-29   29jan2013    2013m1    2013m1 |
 28. | 2013-01-31   31jan2013    2013m1    2013m1 |
     |--------------------------------------------|
 29. | 2013-02-01   01feb2013    2013m2    2013m2 |
 30. | 2013-02-04   04feb2013    2013m2    2013m2 |
 31. | 2013-02-05   05feb2013    2013m2    2013m2 |
 32. | 2013-02-07   07feb2013    2013m2    2013m2 |
 33. | 2013-02-08   08feb2013    2013m2    2013m2 |
 34. | 2013-02-11   11feb2013    2013m2    2013m2 |
 35. | 2013-02-12   12feb2013    2013m2    2013m2 |
 36. | 2013-02-14   14feb2013    2013m2    2013m2 |
 37. | 2013-02-15   15feb2013    2013m2    2013m2 |
 38. | 2013-02-18   18feb2013    2013m2    2013m2 |
 39. | 2013-02-19   19feb2013    2013m2    2013m2 |
 40. | 2013-02-21   21feb2013    2013m2    2013m2 |
 41. | 2013-02-25   25feb2013    2013m2    2013m2 |
 42. | 2013-02-26   26feb2013    2013m2    2013m2 |
 43. | 2013-02-26   26feb2013    2013m2    2013m2 |
 44. | 2013-02-28   28feb2013    2013m2    2013m2 |
     |--------------------------------------------|
 45. | 2013-03-01   01mar2013    2013m3    2013m3 |
     +--------------------------------------------+

.

[Maarten Buis]

Carolina is right in pointing to the help files, but changing the format to monthly does not turn a daily date into a monthly date.

. clear

. input str10 Date float Europertonne

           Date  Europer~e
  1. "2012-11-22" 6.62
  2. "2012-11-23" 6.62
  3. "2012-11-27" 6.49
  4. "2012-11-29" 6.48
  5. "2012-11-30" 6.05
  6. "2012-12-04" 5.78
  7. "2012-12-06" 6.12
  8. "2012-12-07" 6.22
  9. "2012-12-10" 6.53
 10. "2012-12-11" 6.86
 11. "2012-12-13" 6.51
 12. "2012-12-14" 6.5
 13. "2012-12-17" 6.29
 14. "2012-12-18" 6.32
 15. "2013-01-07" 6.1
 16. "2013-01-08" 6.26
 17. "2013-01-10" 5.87
 18. "2013-01-11" 5.69
 19. "2013-01-14" 5.53
 20. "2013-01-15" 5.71
 21. "2013-01-17" 5.36
 22. "2013-01-21" 4.57
 23. "2013-01-22" 4.71
 24. "2013-01-24" 4.44
 25. "2013-01-25" 3.82
 26. "2013-01-28" 3.83
 27. "2013-01-29" 3.73
 28. "2013-01-31" 3.42
 29. "2013-02-01" 3.09
 30. "2013-02-04" 3.91
 31. "2013-02-05" 3.93
 32. "2013-02-07" 3.99
 33. "2013-02-08" 4.14
 34. "2013-02-11" 4.07
 35. "2013-02-12" 4.22
 36. "2013-02-14" 4.75
 37. "2013-02-15" 4.75
 38. "2013-02-18" 4.85
 39. "2013-02-19" 4.2
 40. "2013-02-21" 4.5
 41. "2013-02-25" 4.59
 42. "2013-02-26" 4.17
 43. "2013-02-26" 4.1
 44. "2013-02-28" 4.35
 45. "2013-03-01" 4.48
 46. end

. gen stata_date = date(Date,"YMD")

. format stata_date %td

. gen mdate = mofd(stata_date)

. format mdate %tm

. list, sepby(mdate)

     +---------------------------------------------+
     |       Date   Europe~e   stata_d~e     mdate |
     |---------------------------------------------|
  1. | 2012-11-22       6.62   22nov2012   2012m11 |
  2. | 2012-11-23       6.62   23nov2012   2012m11 |
  3. | 2012-11-27       6.49   27nov2012   2012m11 |
  4. | 2012-11-29       6.48   29nov2012   2012m11 |
  5. | 2012-11-30       6.05   30nov2012   2012m11 |
     |---------------------------------------------|
  6. | 2012-12-04       5.78   04dec2012   2012m12 |
  7. | 2012-12-06       6.12   06dec2012   2012m12 |
  8. | 2012-12-07       6.22   07dec2012   2012m12 |
  9. | 2012-12-10       6.53   10dec2012   2012m12 |
 10. | 2012-12-11       6.86   11dec2012   2012m12 |
 11. | 2012-12-13       6.51   13dec2012   2012m12 |
 12. | 2012-12-14        6.5   14dec2012   2012m12 |
 13. | 2012-12-17       6.29   17dec2012   2012m12 |
 14. | 2012-12-18       6.32   18dec2012   2012m12 |
     |---------------------------------------------|
 15. | 2013-01-07        6.1   07jan2013    2013m1 |
 16. | 2013-01-08       6.26   08jan2013    2013m1 |
 17. | 2013-01-10       5.87   10jan2013    2013m1 |
 18. | 2013-01-11       5.69   11jan2013    2013m1 |
 19. | 2013-01-14       5.53   14jan2013    2013m1 |
 20. | 2013-01-15       5.71   15jan2013    2013m1 |
 21. | 2013-01-17       5.36   17jan2013    2013m1 |
 22. | 2013-01-21       4.57   21jan2013    2013m1 |
 23. | 2013-01-22       4.71   22jan2013    2013m1 |
 24. | 2013-01-24       4.44   24jan2013    2013m1 |
 25. | 2013-01-25       3.82   25jan2013    2013m1 |
 26. | 2013-01-28       3.83   28jan2013    2013m1 |
 27. | 2013-01-29       3.73   29jan2013    2013m1 |
 28. | 2013-01-31       3.42   31jan2013    2013m1 |
     |---------------------------------------------|
 29. | 2013-02-01       3.09   01feb2013    2013m2 |
 30. | 2013-02-04       3.91   04feb2013    2013m2 |
 31. | 2013-02-05       3.93   05feb2013    2013m2 |
 32. | 2013-02-07       3.99   07feb2013    2013m2 |
 33. | 2013-02-08       4.14   08feb2013    2013m2 |
 34. | 2013-02-11       4.07   11feb2013    2013m2 |
 35. | 2013-02-12       4.22   12feb2013    2013m2 |
 36. | 2013-02-14       4.75   14feb2013    2013m2 |
 37. | 2013-02-15       4.75   15feb2013    2013m2 |
 38. | 2013-02-18       4.85   18feb2013    2013m2 |
 39. | 2013-02-19        4.2   19feb2013    2013m2 |
 40. | 2013-02-21        4.5   21feb2013    2013m2 |
 41. | 2013-02-25       4.59   25feb2013    2013m2 |
 42. | 2013-02-26       4.17   26feb2013    2013m2 |
 43. | 2013-02-26        4.1   26feb2013    2013m2 |
 44. | 2013-02-28       4.35   28feb2013    2013m2 |
     |---------------------------------------------|
 45. | 2013-03-01       4.48   01mar2013    2013m3 |
     +---------------------------------------------+