Weird effect in date transformation

[rollanders]

I have date and time for admission to hospital and date of discharge. Some dates are apparently registered with error on the year. I want to correct these. I create variables for the year, month and day, I correct the year variable and then transform back. Some of these tranfsforms does not work

Here is my data

lengtofstay is generated with gen lengthofstay=datumut-dofc(ankomstdatumtid)

ankomstdatumtid	datumutkoll	yearutkoll	månadutkoll	dagutkoll	lengthofstay
2010-12-14 12:12	14dec2011	2011	12	14	365
2010-01-11 08:35	13jan2011	2011	1	13	367
2011-06-13 08:15	13jun2013	2013	6	13	731
2011-02-19 12:51	21feb2013	2013	2	21	733

Year/mån/dag-utkoll was created by:

gen yearutkoll=year(datumutkoll)
gen månadutkoll=month(datumutkoll)
gen dagutkoll=day(datumutkoll)

The yearutkoll is wrong with 1 and 2 years, as supposed by the lengthofstay-variable.

So I correct the year-variable with

replace yearutkoll=yearutkoll-1 if lengthofstay>364&lengthofstay<730
replace yearutkoll=yearutkoll-2 if lengthofstay>729&lengthofstay<.

I get this

ankomstdatumtid	datumutkoll	yearutkoll	månadutkoll	dagutkoll	lengthofstay
2010-12-14 12:12	14dec2011	2010	12	14	365
2010-01-11 08:35	13jan2011	2010	1	13	367
2011-06-13 08:15	13jun2013	2011	6	13	731
2011-02-19 12:51	21feb2013	2011	2	21	733

I backtransform the date

. replace datumutkoll=date(string(yearutkoll)+string(månadut koll)+string(dagutkoll),"YMD") if lengthofstay>360&lengthofstay<.
(4 real changes made, 2 to missing)

ankomstdatumtid	datumutkoll	yearutkoll	månadutkoll	dagutkoll	lengthofstay
2010-12-14 12:12	14dec2010	2010	12	14	365
2010-01-11 08:35	13jan2010	2010	1	13	367
2011-06-13 08:15		2011	6	13	731
2011-02-19 12:51		2011	2	21	733

Why the two missing?

[rollanders]

Is this a bug?

Here are some data and the results of the date transformations.

Eg the recommended YMD gives incorrect or no result. YDM or DYM gives the correct values most of the time but not always.

Tips how I can avoid this.

year	month	day	dateYMD	dateYDM	dateDYM	dateDMY	dateMYD	dateMDY
2010	1	9
2010	1	10		2010-01-10	2010-01-10
2010	1	11	2010-11-01	2010-01-11	2010-01-11		01dec0101
2010	1	19	2010-11-09	2010-01-19	2010-01-19		09dec0101
2010	1	20		2010-01-20	2010-01-20
2010	2	9
2010	2	10		2010-02-10	2010-02-10
2010	2	11		2010-02-11	2010-02-11
2010	2	19		2010-02-19	2010-02-19
2010	2	20		2010-02-20	2010-02-20
2011	2	9
2011	1	9

gen dateYMD=date(string(year)+string(month)+string(day ) ,"YMD")
gen dateYDM=date(string(year)+string(day)+string(month ) ,"YDM")
gen dateDYM=date(string(day)+string(year)+string(month ),"DYM")
gen dateDMY=date(string(day)+string(month)+string(year ),"DMY")
gen dateMYD=date(string(month)+string(year)+string(day ),"MYD")
gen dateMDY=date(string(month)+string(day)+string(year ),"MDY")

[rollanders]

My solution:

tostring month, replace
replace month="0"+month if real(month)<10

Any better solution?

[William Lisowski]

There are certainly better solutions. Lacking easily usable sample data to demonstrate better solutions with, I'll limit this response to general advice that can lead you to better solutions.

Stata's "date and time" 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.

In the future, for better advice please be sure to use the dataex command to show your example data. If you are running version 15.1 or a fully updated version 14.2, it is already part of your official Stata installation. If not, run ssc install dataex to get it. Either way, run help dataex to read the simple instructions for using it. dataex will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.

When asking for help with code, always show example data. When showing example data, always use dataex.