Date Format 31dec1899

[Niklas Adolph]

Hi,

I have a problem with the date format. As I imported all dates from excel (also formatted as date) all missing data which had a 0 assigned turn into 31dec1899.
I already found out that this is the equivalent to 0 in the date format from stata. (Sorry - stata beginner)
But I really need the 0 there as I want to count the numbers of dates which are not existent and compare them to all the others (if it is possible to replace them with a 1 that would be awesome).

I used this one for another variable but it doesnt work for dates - any ideas:
gen n_Restricted_Areas=.
replace n_Restricted_Areas=1 if Restricted_Areas~="0"
replace n_Restricted_Areas=0 if Restricted_Areas=="0"

Kind regards,
Niklas

[Clyde Schechter]

0 is not equivalent to 31dec1899 in Stata. 0 in Stata is equivalent to 1jan1960. However, 0 is equivalent to 31dec1899 as a date in Excel. When Stata imports an Excel file using the -import excel- command, Stata converts Excel's date coding into its own and applies an appropriate correction to the underlying numbers so that what shows as 15aug2018 in Excel comes out showing as 15aug2018 in your Stata data set. But the underlying numbers in Stata are different from those in Excel.

In Stata, it is rarely useful, and often quite treacherous, to have missing data coded as 0 or 1 or any other real number. Stata has both a system missing value (.) and 26 extended missing values (.a-.z) available for that purpose. So you can do something like this:

replace date = . if date == td(31dec1899)

If you want to distinguish this particular kind of missing date from other missing dates, use one of the extend values instead of system missing in the above code.

I used this one for another variable but it doesnt work for dates - any ideas:
gen n_Restricted_Areas=.
replace n_Restricted_Areas=1 if Restricted_Areas~="0"
replace n_Restricted_Areas=0 if Restricted_Areas=="0"

What exactly are you trying to do here? Never say something "doesn't work" without explaining exactly what did happen? Did you get unexpected values in the variable you tried to create? Did you get a syntax error? When asking for help with code that is not living up to your expectations, always show the exact results you got with it, by copying directly from the Results window or your log file, and pasting into the Forum editor between code delimiters. (If you are not familiar with code delimiters, read Forum FAQ #12 for help.) It isn't possible to troubleshoot a problem without being told what went wrong.

[Niklas Adolph]

Thanks a lot for the fast reply. I will try to improve my posts in the future - thanks for all the advices!