How to convert to date when data is saved as long format (%tdD_m_Y ); for example-15 Oct 13

[Manirul Islam]

How to convert to date when data is saved as long format (%tdD_m_Y ); for example-15 Oct 13

[Andrew Musau]

* Example generated by -dataex-. To install: ssc install dataex
clear
input str9 strdate
"15 Oct 13"
end

gen date= date(strdate, "DM20Y")
format date %td

Res.:

. l

     +-----------------------+
     |   strdate        date |
     |-----------------------|
  1. | 15 Oct 13   15oct2013 |
     +-----------------------+

[Nick Cox]

date() will solve your problems, but watch the example below carefully if your dates include some from last century.

I use daily() -- which is the same function -- to be more precise to those reading the code about what is going on. date() goes back to the time when daily dates were first supported directly by Stata -- and before support was added for other kinds of dates such as monthly and quarterly. date() is not an all-purpose date function.

* Example generated by -dataex-. To install: ssc install dataex
clear
input str9 strdate
"15 Oct 13"
"25 Dec 99"
end

gen date1 = daily(strdate, "DM20Y")
gen date2 = daily(strdate, "DMY", 2025)

format date* %td

list

     +-----------------------------------+
     |   strdate       date1       date2 |
     |-----------------------------------|
  1. | 15 Oct 13   15oct2013   15oct2013 |
  2. | 25 Dec 99   25dec2099   25dec1999 |
     +-----------------------------------+

All explained at

help datetime

[Manirul Islam]

Dear Nick/Musau,

hanks a lot. I could not do it. Please see the attached file where I want to calculate age from dob,
The year is in 2013.

Thank you in anticipation.

[Andrew Musau]

Please use dataex to present data examples (see FAQ Advice #12 for details). The dataex output from your attachment in #4 is the following:

* Example generated by -dataex-. To install: ssc install dataex
clear
input double(perid Sex) long dob str6 dobstr long doc double Birth_age
4404012005802 2 -16879 "-16879" 19617 99.92060232717317
4405012030401 1 -16866 "-16866" 19518 99.61396303901438
4405017048002 2 -16832 "-16832" 19536 99.57015742642027
4405002027702 2 -16823 "-16823" 19546  99.5728952772074
4406024029601 1 -16821 "-16821" 19560 99.60574948665298
4405002025202 1 -16803 "-16803" 19542 99.50718685831622
4405017051302 2 -16802 "-16802" 19536  99.4880219028063
4405026000702 1 -16801 "-16801" 19524 99.45242984257358
4406015021302 2 -16800 "-16800" 19590 99.63039014373716
4405017034902 1 -16800 "-16800" 19529  99.4633812457221
end
format %tdD_m_Y dob
format %tdD_m_Y doc
label values Sex Sex
label def Sex 1 "Male", modify
label def Sex 2 "Female", modify

So, you have converted "dobstr" to "dob" which is a proper Stata date variable. If you want the age of individuals at "doc", then it appears that you have this from your variable "Birth_age". Note that the dates represented by the SIF values -16879 and 19617 are 15th Oct. 1913 and 16 Sep. 2013, respectively.

. di %td -16879
15oct1913

. di %td 19617
16sep2013

The difference between these two dates in years is

. di `=datediff_frac(td(16sep2013), td(15oct1913), "y")'
-99.920548

which is what you have. Therefore, you have to clarify what the issue is. #11 of the following link shows new date and time functions recently introduced in Stata 16, such as the one I use above.

https://www.statalist.org/forums/for...on-of-date-age

[Manirul Islam]

Thanks a lot. I used Stata-16, it gives 1913 although it should be 2013. How can I solve the problem in Stata-16?

[Andrew Musau]

From what I see in #5, you have a Stata date variable (SIF values) saved as a string variable. Otherwise, if your string variable was initially such as "15 Oct 13", the codes in #2 and #3 show that the date would have been converted correctly. So what you have to do is to provide a data example using dataex of your initial string variable. If it is what you have in #5, then I don't know what evidence you have that the year is 2013 and not 1913.

[Manirul Islam]

Thank you Musau. The original data was in SPSS and we know the year is 2013 but written in two digits that is 13. The conversion to string automatically gives %tdNN/DD/CCYY format and eventually become 1913 in date format.

[Andrew Musau]

It is better that you address this at the import stage following the advice in #2 and #3, but here is a way to get what you want.

* Example generated by -dataex-. To install: ssc install dataex
clear
input double(perid Sex) long dob str6 dobstr long doc double Birth_age
4404012005802 2 -16879 "-16879" 19617 99.92060232717317
4405012030401 1 -16866 "-16866" 19518 99.61396303901438
4405017048002 2 -16832 "-16832" 19536 99.57015742642027
4405002027702 2 -16823 "-16823" 19546  99.5728952772074
4406024029601 1 -16821 "-16821" 19560 99.60574948665298
4405002025202 1 -16803 "-16803" 19542 99.50718685831622
4405017051302 2 -16802 "-16802" 19536  99.4880219028063
4405026000702 1 -16801 "-16801" 19524 99.45242984257358
4406015021302 2 -16800 "-16800" 19590 99.63039014373716
4405017034902 1 -16800 "-16800" 19529  99.4633812457221
end
format %tdD_m_Y dob
format %tdD_m_Y doc
label values Sex Sex
label def Sex 1 "Male", modify
label def Sex 2 "Female", modify

gen new_dob=mdy(month(dob), day(dob), year(dob)+100)
format dob new_dob %td
l perid Sex dob new_dob, sep(0)

Res.:

. l perid Sex dob new_dob, sep(0)

     +--------------------------------------------+
     |     perid      Sex         dob     new_dob |
     |--------------------------------------------|
  1. | 4.404e+12   Female   15oct1913   15oct2013 |
  2. | 4.405e+12     Male   28oct1913   28oct2013 |
  3. | 4.405e+12   Female   01dec1913   01dec2013 |
  4. | 4.405e+12   Female   10dec1913   10dec2013 |
  5. | 4.406e+12     Male   12dec1913   12dec2013 |
  6. | 4.405e+12     Male   30dec1913   30dec2013 |
  7. | 4.405e+12   Female   31dec1913   31dec2013 |
  8. | 4.405e+12     Male   01jan1914   01jan2014 |
  9. | 4.406e+12   Female   02jan1914   02jan2014 |
 10. | 4.405e+12     Male   02jan1914   02jan2014 |
     +--------------------------------------------+

.

[Manirul Islam]

Dear Mr. Musau,
Thanks a lot. it works.
Best,