How do I get the number of a month and year from a date var?

[Maarten Huurne]

Hello,

I have the following question.

I have a date string variable (DDDDMMYY) and I want to extract the number of the month in a year. So January would be a 1, February a 2, etc. In the next year the number of the months should keep on numbering (the data ranges from January 2012 till December 2016). So if January 2012 is month number 1, then January 2013 should be month number 13. I also want to do this for year. So 2012 should be year number 1, 2013 number 2, etc.

Below is an extract of the date variable.

[* Example generated by -dataex-. To install: ssc install dataex
clear
input str10 maaltijddatumafgehaald
"0018-06-12"
"0024-10-12"
"0009-11-12"
"0022-10-15"
"0020-09-13"
"0005-02-13"
"0003-09-12"
"0029-12-12"
"0016-10-13"
"0026-07-12"
"0027-01-16"
"0004-11-12"
"0029-08-12"
"0013-02-14"
"0011-09-12"
"0010-02-16"
"0030-09-12"
"0001-05-14"
"0003-10-12"
end

Your help would be much appreciated!

Best,
Maarten

[Clyde Schechter]

So, the first obstacle is that your string date variable is rather bizarre in that it uses 4 digits to present the day of the month, which, of course, can never exceed 31. And Stata's date conversion functions won't understand that. Then we go from a string date to a real Stata internal format numerical date variable. Then we extract the calendar month. Since you ask for a sequential integer variable starting at 1 in Jan 2012 I add that at the end, but in truth I cannot imagine what use that variable would be since the actual sif_month variable contains all of the same information and more.

If you are going to work with dates in Stata, you have to face the reality that you need to read the datetime variables section of the [D] volume of the PDF manuals that come with your Stata installation. (Select PDF Documentation from the Help Menu.) It's a long read, and it's complicated material. It's hard enough that even the most experienced users cannot remember it completely and have to refer back to the manual to remind themselves of details from time to time. But you will become familiar with how Stata respresents dates internally, how you can convert string variables that look like dates to Stata internal format dates, how to extract components from internal format dates, how to build dates up from components, and the various display format options available. It's an investment of time, but one that will repay itself handsomely.

[Nick Cox]

As I understand it the daily part is irrelevant. You want a monthly date such that 1 is January 2012. Can be done.

clear
input str10 maaltijddatumafgehaald
"0018-06-12"
"0024-10-12"
"0009-11-12"
"0022-10-15"
"0020-09-13"
"0005-02-13"
"0003-09-12"
"0029-12-12"
"0016-10-13"
"0026-07-12"
"0027-01-16"
"0004-11-12"
"0029-08-12"
"0013-02-14"
"0011-09-12"
"0010-02-16"
"0030-09-12"
"0001-05-14"
"0003-10-12"
end

gen mdate = monthly(substr(maal, -5, 5), "M20Y")
format mdate %tm
gen wanted = mdate - ym(2011, 12)

tabdisp mdate, c(wanted)

----------------------
    mdate |     wanted
----------+-----------
   2012m6 |          6
   2012m7 |          7
   2012m8 |          8
   2012m9 |          9
  2012m10 |         10
  2012m11 |         11
  2012m12 |         12
   2013m2 |         14
   2013m9 |         21
  2013m10 |         22
   2014m2 |         26
   2014m5 |         29
  2015m10 |         46
   2016m1 |         49
   2016m2 |         50
----------------------

The year part yields to extrdate (from numdate, SSC)

extrdate year ydate = mdate

followed by an elementary adjustment.

I have to say that explicit monthly and yearly dates sound a much better idea to me.

[Maarten Huurne]

Thank you very much for your answers. It works just fine. I've been struggling with it for quite a while.

[Maarten Huurne]

If I can add just another question.

I have another variable on which I want to perform the same transformation. Only the problem is that within this variable there are 2 different date notations, namely (1) DD/MM/YY HH:MM and (2) YYYY-DD-MM HH:MM:SS. Is there any way to solve this?

Best,
Maarten

[Nick Cox]

Data example please.

[Maarten Huurne]

Of course, sorry.

* Example generated by -dataex-. To install: ssc install dataex
clear
input str19 maaltijddatumaangemaakt
"2015-07-27 14:55:03"
"2015-08-03 14:17:14"
"2012-02-20 10:22:14"
"2012-03-02 16:58:09"
"20/02/12 12:39"     
"2012-02-20 11:53:43"
"2012-03-05 16:25:44"
"2012-03-05 15:18:07"
"2012-02-20 12:02:11"
"2014-09-06 17:48:37"
"2013-07-29 12:29:57"
"2013-04-03 13:24:12"
"2013-05-07 11:57:10"
"2014-02-06 09:56:35"
"2013-05-07 12:01:36"
"2012-03-12 14:07:32"
"2015-09-16 14:29:17"
"2014-05-12 16:00:15"
"2013-04-03 13:25:53"
"2014-03-27 16:18:48"
"2012-02-20 13:12:33"
"2014-03-05 12:39:28"
"12/04/13 19:11"     
"31/10/12 19:45"     
"14/06/12 13:22"     
"12/04/13 19:42"     
"30/11/13 15:32"     
"06/04/12 08:42"     
"03/04/13 13:30"     
"20/03/12 20:51"     
"18/07/13 15:39"     
"09/04/12 19:34"     
"17/06/13 15:17"     
"07/05/13 11:57"     
"06/11/12 11:48"     
"14/07/12 18:05"     
"14/12/12 19:47"     
"19/11/12 20:11"     
"19/04/13 11:59"     
"17/03/12 16:07"     
"12/06/12 20:35"     
"13/09/12 20:29"     
"25/07/13 16:45"     
"09/09/13 16:26"     
"22/05/13 11:28"     
end

[Nick Cox]

  
. gen mdate = monthly(substr(maal, 4, 5), "M20Y") if strpos(maal, "/") 
(21 missing values generated)

. replace mdate = monthly(substr(maal, 1, 7), "YM") if missing(mdate) 
(21 real changes made)

. format mdate %tm 

. 
. list 

     +-------------------------------+
     | maaltijddatumaang~t     mdate |
     |-------------------------------|
  1. | 2015-07-27 14:55:03    2015m7 |
  2. | 2015-08-03 14:17:14    2015m8 |
  3. | 2012-02-20 10:22:14    2012m2 |
  4. | 2012-03-02 16:58:09    2012m3 |
  5. |      20/02/12 12:39    2012m2 |
     |-------------------------------|
  6. | 2012-02-20 11:53:43    2012m2 |
  7. | 2012-03-05 16:25:44    2012m3 |
  8. | 2012-03-05 15:18:07    2012m3 |
  9. | 2012-02-20 12:02:11    2012m2 |
 10. | 2014-09-06 17:48:37    2014m9 |
     |-------------------------------|
 11. | 2013-07-29 12:29:57    2013m7 |
 12. | 2013-04-03 13:24:12    2013m4 |
 13. | 2013-05-07 11:57:10    2013m5 |
 14. | 2014-02-06 09:56:35    2014m2 |
 15. | 2013-05-07 12:01:36    2013m5 |
     |-------------------------------|
 16. | 2012-03-12 14:07:32    2012m3 |
 17. | 2015-09-16 14:29:17    2015m9 |
 18. | 2014-05-12 16:00:15    2014m5 |
 19. | 2013-04-03 13:25:53    2013m4 |
 20. | 2014-03-27 16:18:48    2014m3 |
     |-------------------------------|
 21. | 2012-02-20 13:12:33    2012m2 |
 22. | 2014-03-05 12:39:28    2014m3 |
 23. |      12/04/13 19:11    2013m4 |
 24. |      31/10/12 19:45   2012m10 |
 25. |      14/06/12 13:22    2012m6 |
     |-------------------------------|
 26. |      12/04/13 19:42    2013m4 |
 27. |      30/11/13 15:32   2013m11 |
 28. |      06/04/12 08:42    2012m4 |
 29. |      03/04/13 13:30    2013m4 |
 30. |      20/03/12 20:51    2012m3 |
     |-------------------------------|
 31. |      18/07/13 15:39    2013m7 |
 32. |      09/04/12 19:34    2012m4 |
 33. |      17/06/13 15:17    2013m6 |
 34. |      07/05/13 11:57    2013m5 |
 35. |      06/11/12 11:48   2012m11 |
     |-------------------------------|
 36. |      14/07/12 18:05    2012m7 |
 37. |      14/12/12 19:47   2012m12 |
 38. |      19/11/12 20:11   2012m11 |
 39. |      19/04/13 11:59    2013m4 |
 40. |      17/03/12 16:07    2012m3 |
     |-------------------------------|
 41. |      12/06/12 20:35    2012m6 |
 42. |      13/09/12 20:29    2012m9 |
 43. |      25/07/13 16:45    2013m7 |
 44. |      09/09/13 16:26    2013m9 |
 45. |      22/05/13 11:28    2013m5 |
     +-------------------------------+

[Maarten Huurne]

Thank you so much Nick!

[Maarten Huurne]

I have an additional question regarding the data used above.

I want to have the number of the month within each year. So January 2012 should be month #1, February 2012 month #2, and January 2013 should be month #1, and February 2013 should be month #2, etc. How would I go about it?

[Clyde Schechter]

gen desired = month(dofm(mdate))

Do not confuse the -month()-, -monthly()-, and -mofd()- functions. They do three different things.

[Nick Cox]

#4 already directed you to extrdate from numdate (SSC). Did you read its help?

clear
input str10 maaltijddatumafgehaald
"0018-06-12"
"0024-10-12"
"0009-11-12"
"0022-10-15"
"0020-09-13"
"0005-02-13"
"0003-09-12"
"0029-12-12"
"0016-10-13"
"0026-07-12"
"0027-01-16"
"0004-11-12"
"0029-08-12"
"0013-02-14"
"0011-09-12"
"0010-02-16"
"0030-09-12"
"0001-05-14"
"0003-10-12"
end

gen mdate = monthly(substr(maal, -5, 5), "M20Y")
format mdate %tm

extrdate month monthno = mdate

sort mdate
list mdate monthno  


    +-------------------+
     |   mdate   monthno |
     |-------------------|
  1. |  2012m6         6 |
  2. |  2012m7         7 |
  3. |  2012m8         8 |
  4. |  2012m9         9 |
  5. |  2012m9         9 |
     |-------------------|
  6. |  2012m9         9 |
  7. | 2012m10        10 |
  8. | 2012m10        10 |
  9. | 2012m11        11 |
 10. | 2012m11        11 |
     |-------------------|
 11. | 2012m12        12 |
 12. |  2013m2         2 |
 13. |  2013m9         9 |
 14. | 2013m10        10 |
 15. |  2014m2         2 |
     |-------------------|
 16. |  2014m5         5 |
 17. | 2015m10        10 |
 18. |  2016m1         1 |
 19. |  2016m2         2 |
     +-------------------+

Alternatively month(dofm()) extracts month of year from monthly dates:

. di month(dofm(ym(2017, 9)))
9

!!!

help datetime

!!!

has (almost) everything you need to know. Just one thing separates those who ask and those who answer these date questions: the latter have studied the help.

[Maarten Huurne]

OK. Thank you for your answers. I find the datetime a difficult function.

[Nick Cox]

Indeed. So does everyone I've ever heard discuss them. Dates are like graphs and tables. Almost everyone has just a few specific needs, but finding the detail you want among the many other details you don't care about right now can be a struggle.