Date Translation from type: double, format: %tcnn/dd/ccYY to %tdDMy

[Canh Dang]

Hello, I want to calculate the age of firms at the time of survey whose registration date and the survey date are reported in different types and formats.

The registration dates are reported as double and %tcnn/dd/ccYY, while the survey date are as str23 and %23s

regdate = 6/12/1961 00:00:00
surveydate = 2008-12-31 00:00:00.00

Using

gen sur_date = date(surveydate , "YMDhms")
format %tdDMY sur_date

I managed to convert surveydate as of:

 sur_date 31December08

I can't do the same to the regdate variable. After using

gen birthday = regdate
format %tdDMY birthday

I get

birthday  = 4.56192e+10

I then calculate the firm age by

gen age = (surveydate- birthday)/365.25

that gives

age = -1.25e+08

which seems not right at all.

I am not sure if the way I did with my regdate variable is valid (as it is in double type). Any suggestion for a better way to calculate the age of the firm would be really appreciated.

Thank you very much in advance.

[Nick Cox]

It's not at all clear what you did exactly.

For example, your last example calculation uses surveydate but your definition of that is as a string variable, so that example would fail.

But if regdate is a date-time its units are milliseconds and subtracting a daily date variable whose units are days will indeed make no sense.

This works:

clear
set obs 1

gen surveydate = "2008-12-31 00:00:00.00"
gen sur_date = daily(surveydate , "YMDhms")


gen regdate = "6/12/1961 00:00:00"
gen birthday = daily(regdate, "DMYhms")

format %tdDMY sur_date birthday

gen age = (sur_date- birthday)/365.25

list

     +------------------------------------------------------------+
  1. |             surveydate |     sur_date |            regdate |
     | 2008-12-31 00:00:00.00 | 31December08 | 6/12/1961 00:00:00 |
     |------------------------------------------------------------|
     |             birthday          |               age          |
     |         06December61          |          47.06913          |
     +------------------------------------------------------------+

PS: It's the old Clyde-Nick unintentional double act again. What we tell you twice is true.

[Clyde Schechter]

A double that is formated %tc... and makes sense when you look at it is not a date variable, it's a date-time (clock) variable. They are on different scales. Clock variables are measured in milliseconds from midnight January 1, 19690, and date variables are measured in days from January 1, 1960. So what you want to do is:

gen birthday = dofc(regdate) // CONVERT CLOCK TO DATE
format birthday %tdDMY

gen sur_date =dofc(clock(surveydate, "YMDhms")
format sur_date %tdDMY

gen age = (sur_date - birthday)/365.25

Added later: crossed in cyberspace with Nick's post which gives essentially the same advice.

[Canh Dang]

Thank you very much for your double-act help, Nick and Clyde. Now I see where my issue was (I simply came across - dofc - and - daily - function. Thanks for teaching me a new thing.

[William Lisowski]

Allow me to add a suggestion. You write that you "simply came across" a pair of date functions, so I suspect you have not read the appropriate Stata documentation. If you have not already done so, it would benefit you to work your way through the guidance in help datetime, which is without a doubt the most visited documentation on my system, with the second-most-visited being Chapter 24 (Working with dates and times) of the Stata User's Guide PDF available from the PDF Documentation item on Stata's Help menu. Before working with dates and times, any Stata user should read the very detailed Chapter 24 thoroughly. After that, the help documentation will usually be enough to point the way. Some people may be able to remember everything without have to continually refer to the documentation, but I for one am not such a person.

[Canh Dang]

Dear William,

Thanks for your suggestion. I have not actually come across help datetime. The only Stata Help I tried were help datetime_translation and help datetime_display_formats and I saw the two functions daily dofc somewhere else on the Internet. I did not know how to apply them back then.

Thanks for letting me know.

Dear Clyde and Nick,

I just want to let you know that Clyde's code works fine but somehow Nick's code (using daily ) does not.
When using

gen birthday = daily(regdate, "DMYhms")

Stata returns the same problem I got before.

type mismatch

Is this because regdate type is double ?

[Nick Cox]

Note that Stata sometimes changes the names of help files slightly. Your problem may arise if you are using an older version of Stata other than 14.1. As the FAQ Advice says

11. What should I say about the version of Stata I use?

The current version of Stata is 14.1. Please specify if you are using an earlier version; otherwise, the answer to your question may refer to commands or features unavailable to you. Moreover, as bug fixes and new features are issued frequently by StataCorp, make sure that you update your Stata before posting a query, as your problem may already have been solved.

My example was self-contained and worked as given and depended on my definition of regdate as string. So, it certainly won't work otherwise.

[Clyde Schechter]

Is this because regdate type is double ?

Yes. The daily() function converts human-readable strings into Stata internal numeric dates. Your variable regdate already started out as a Stata internal clock (date_time) variable, so it is of the wrong type for this function. To convert an internal clock variable to an internal date variable, you must use dofc().