Extracting date from a string variable

[Vince Williams]

I have a simple table. Column 1 is a string variable.

Column1

DEC16 Estimates reflect adoption

Mar17 Estimates reflect adoption

JUN15 Estimates reflect adoption

SEP17 Estimates reflect adoption

I would like a want table where column 2 is a date in a DATE9. format. We assume that it is the end of the month. I have some sample code but do not know what to do after that.

Could you please help.

Column1	Column 2
DEC16 Estimates reflect adoption	12/31/2016
Mar17 Estimates reflect adoption	3/31/2017
JUN15 Estimates reflect adoption	6/30/2015
SEP17 Estimates reflect adoption	9/30/2017

gen str  Column2= substr(Column1,1,5)
generate str2 dxyr1= substr(Column2,4,5)
generate str3 dxmo1 = substr(Column2,1,3)

[Vince Williams]

Sorry I meant %tdD_m_Y Stata format.

[William Lisowski]

Here is some code that creates the Stata Internal Format daily date of the last day of the month. To figure out the last day of the month, it uses the day before the first day of the next month.

* Example generated by -dataex-. To install: ssc install dataex
clear
input str32 text
"DEC16 Estimates reflect adoption"
"Mar17 Estimates reflect adoption"
"JUN15 Estimates reflect adoption"
"SEP17 Estimates reflect adoption"
end
generate word1 = word(text,1)
generate ym = monthly(word1,"M20Y")
format ym %tm
generate mdy = dofm(ym+1)-1
format mdy %tdnn/dd/CCYY
list, clean noobs

. list, clean noobs

                                text   word1        ym          mdy  
    DEC16 Estimates reflect adoption   DEC16   2016m12   12/31/2016  
    Mar17 Estimates reflect adoption   Mar17    2017m3    3/31/2017  
    JUN15 Estimates reflect adoption   JUN15    2015m6    6/30/2015  
    SEP17 Estimates reflect adoption   SEP17    2017m9    9/30/2017

And now for the advice I always give after answering a question like this.

Stata's "datetime" variables are complicated and there is a lot to learn. If you have not already read the very detailed Chapter 24 (Working with dates and times) of the Stata User's Guide PDF, do so now. If you have, it's time for a refresher. After that, the help datetime documentation will usually be enough to point the way. You can't remember everything; even the most experienced users end up referring to the help datetime documentation or back to the manual for details. But at least you will get a good understanding of the basics and the underlying principles. An investment of time that will be amply repaid.

All Stata manuals are included as PDFs in the Stata installation (since version 11) and are accessible from within Stata - for example, through the PDF Documentation section of Stata's Help menu.