Extracting month and year from date with time

[Anne Todd]

Hello,

I hope to extract and create a new variable for both month and year, which I have seen some guidance on how to do already. But my issue is that the variable I'm working with is not in the proper format and the variable also includes the precise time of day, as you can see:

* Example generated by -dataex-. To install: ssc install dataex
clear
input str14 incident
"5/4/08 0:00"   
"12/1/07 18:30"
"12/21/07 23:26"
"9/31/14 22:50"
"3/10/14 23:42"
"1/13/13 22:55"
"1/13/13 23:42"
"12/31/08 23:53"
"12/31/07 23:20"
"6/1/08 0:10"   
"5/1/08 0:01"   
"5/1/07 22:15"
"12/1/17 23:53"
"3/4/16 0:55"   
"10/5/10 0:50"   
"10/1/09 1:00"   
"10/1/09 0:55"   
"10/1/08 0:30"   
"12/31/07 19:00"
"11/1/08 1:13"   
"1/1/08 1:15"   
"12/31/13 20:00"
"12/31/13 16:00"
"1/1/12 18:00"
"12/1/07 22:52"
"12/31/07 23:42"
"1/8/08 1:30"   
end

I don't need the precise time at all (if that helps to just erase it), and my main end goal is to just create a new variable for month and new variable for year. Thank you in advance for any insight!

[Nick Cox]

Thanks for the data example.

gen ddate = daily(word(incident, 1), "MD20Y")
format ddate %td

gen year = year(ddate)

gen mdate = mofd(ddate)
format mdate %tm

gen month = month(ddate)
sort ddate
list, sepby(year month)

     +-----------------------------------------------------+
     |       incident       ddate   year     mdate   month |
     |-----------------------------------------------------|
  1. |   5/1/07 22:15   01may2007   2007    2007m5       5 |
  2. |  12/1/07 18:30   01dec2007   2007   2007m12      12 |
  3. |  12/1/07 22:52   01dec2007   2007   2007m12      12 |
  4. | 12/21/07 23:26   21dec2007   2007   2007m12      12 |
  5. | 12/31/07 23:42   31dec2007   2007   2007m12      12 |
  6. | 12/31/07 23:20   31dec2007   2007   2007m12      12 |
  7. | 12/31/07 19:00   31dec2007   2007   2007m12      12 |
     |-----------------------------------------------------|
  8. |    1/1/08 1:15   01jan2008   2008    2008m1       1 |
  9. |    1/8/08 1:30   08jan2008   2008    2008m1       1 |
 10. |    5/1/08 0:01   01may2008   2008    2008m5       5 |
 11. |    5/4/08 0:00   04may2008   2008    2008m5       5 |
 12. |    6/1/08 0:10   01jun2008   2008    2008m6       6 |
 13. |   10/1/08 0:30   01oct2008   2008   2008m10      10 |
 14. |   11/1/08 1:13   01nov2008   2008   2008m11      11 |
 15. | 12/31/08 23:53   31dec2008   2008   2008m12      12 |
     |-----------------------------------------------------|
 16. |   10/1/09 1:00   01oct2009   2009   2009m10      10 |
 17. |   10/1/09 0:55   01oct2009   2009   2009m10      10 |
     |-----------------------------------------------------|
 18. |   10/5/10 0:50   05oct2010   2010   2010m10      10 |
     |-----------------------------------------------------|
 19. |   1/1/12 18:00   01jan2012   2012    2012m1       1 |
     |-----------------------------------------------------|
 20. |  1/13/13 22:55   13jan2013   2013    2013m1       1 |
 21. |  1/13/13 23:42   13jan2013   2013    2013m1       1 |
 22. | 12/31/13 16:00   31dec2013   2013   2013m12      12 |
 23. | 12/31/13 20:00   31dec2013   2013   2013m12      12 |
     |-----------------------------------------------------|
 24. |  3/10/14 23:42   10mar2014   2014    2014m3       3 |
     |-----------------------------------------------------|
 25. |    3/4/16 0:55   04mar2016   2016    2016m3       3 |
     |-----------------------------------------------------|
 26. |  12/1/17 23:53   01dec2017   2017   2017m12      12 |
     |-----------------------------------------------------|
 27. |  9/31/14 22:50           .      .         .       . |
     +-----------------------------------------------------+

Bogus dates like 31 September 2014 may in some cases be salvageable like this:

gen work = word(incident, 1) if missing(ddate)
split work, parse(/) destring
replace year = 2000 + work3 if missing(ddate)
replace month = work1 if missing(ddate)
replace mdate = ym(year, month) if missing(ddate)
drop work*
l if missing(ddate)

[Anne Todd]

Thank you very much Nick Cox .

If I may ask, what is the purpose of the gen mdate and format mdate lines? I was messing around with your code (which worked great as is!) to try to better understand it and it appears as if they don't impact the final creation of the month and year vars.

[Nick Cox]

No; they don't. But you asked for year and month, which often implies a need for monthly dates. You may not feel it now, but you will. For example, to plot data as a time series you would find month and year usually inadequate, for all that they contain the desired information.

Also, most of the point of a forum is that answers could benefit others, so that many other people reading this might want to know how to get a monthly date in similar circumstances.

All of this stuff is documented at

help datetime

[Anne Todd]

Very helpful to learn that. Thank you again! I appreciate it.