Generate date variable

[Wenhan Yan]

I have following variable:

Date_of_Marriage
911990
10111990
7121990
1021990
1051990
10151990
10261990
10161990
1061990

and want to convert to date format:

Date_of_Marriage

9/11/990

10/11/1990

7/12/1990

10/2/1990

10/5/1990

10/15/1990

10/26/1990

10/16/1990

10/6/1990

Since this variable not only does not have consistent format, nor seperator for month, date and year, I don't think I can simply use following code to do that

gen Marriage = date(Date_of_Marriagae, "DMY")
formate birthday %d

[Ali Atia]

Assuming that the rest of the data follows the pattern in the example (i.e., no 0s preceding single-digit months or days):

gen year = substr(date,-4,4)
gen month = cond(substr(date,2,1)=="0",substr(date,1,2),substr(date,1,1))
gen day = cond(strlen(month)==1,substr(date,2,strlen(date)-(strlen(month)+strlen(year))),substr(date,3,strlen(date)-(strlen(month)+strlen(year))))
destring month day year,replace
gen newdate = mdy(month, day, year)
format newdate %tdn/d/cy

Or in one shot:

gen newdate = date(cond(substr(date,2,1)=="0",substr(date,1,2),substr(date,1,1)) + "/" + cond(strlen(cond(substr(date,2,1)=="0",substr(date,1,2),substr(date,1,1)))==1,substr(date,2,strlen(date)-(strlen(cond(substr(date,2,1)=="0",substr(date,1,2),substr(date,1,1)))+strlen(substr(date,-4,4)))),substr(date,3,strlen(date)-(strlen(cond(substr(date,2,1)=="0",substr(date,1,2),substr(date,1,1)))+strlen(substr(date,-4,4)))))+"/"+substr(date,-4,4),"MDY")
format newdate %tdn/d/cy

[Wenhan Yan]

Thank you so much for providing this wonderful way to conver the date, however, I forgot ti mention that if we have single-digit months and signle-digit days, they will add 0s:

6041990

7091990

And I also check for the special case, Nov 1st, Jan 11th, Oct 1st
For Nov 1st will present as 1101
For Jan 11th will present as 111
For Oct 1st will present as 101

And that's why I believe the code does not work with most November and December records

I am sorry I didn't mention this eariler .

[Ali Atia]

Your examples are inconsistent with the rule and with each other. You say that single-digit months and single-digit days have 0s at the beginning, but also that Jan-11th (single-digit month) will be displayed as 111 rather than 0111, and Oct-1st (single-digit day) will be displayed as 101 rather than 1001. The only example which adheres to the rule is Nov 1st (1101). Also, the examples of 6041990 and 7091990 do not add 0s to the beginning despite being single-digit months.

It's difficult to give an answer which will work with your real data without a real data example, which you can provide using the dataex command (as detailed in the FAQ section of this forum).

[Wenhan Yan]

I am if the date is a single-digit month and single-digit days, then there will be a 0 just for the day, like Jan 1st will be 101, and June 1st will be 601. And here is the data, thank you for pinting out the way to show the data.

* Example generated by -dataex-. For more info, type help dataex
clear
input long dateofmarriage
 3161990
 5291990
 7071990
 9011990
 9221990
12291990
12281990
 2241990
 8251990
 8181990
 8041990
 6301990
 7071990
11241990
 5311990
 8111990
 5131990
12211990
 8311990
12281990
 7141990
 7141990
11241990
 6091990
 9221990
 6301990
 3311990
 3031990
 8041990
 6151990
12211990
 7071990
 8141990
11241990
 3241990
 6011990
 5181990
 5191990
12021990
12091990
 1131990
 6091990
 9081990
 4131990
11171990
 9081990
 6291990
10211990
11241990
 5191990
end

[Ali Atia]

Thank you for the data example. Try:

gen date = date(cond(strlen(strofreal(dateofmarriage))==7,"0"+strofreal(dateofmarriage,"%10.0g"),strofreal(dateofmarriage,"%10.0g")),"MDY")
format date %tdn/d/cy

[Nick Cox]

Here's another way to do it:

gen wanted = daily(strofreal(dateofmarriage, "%08.0f"), "MDY")
format wanted %td

[Wenhan Yan]

Thank you guys, both of them are working.