DATETIME16. incorrect years resulted using datepart and year functions

[Tom]

Looks like your data have values for January 1 , 3000. 

So a calculated year of 3000 is correct.

So it looks like your original program is working correctly.

SAS stores all numbers as floating point.  The DATE and DATETIME formats are just methods of display some numbers in ways that make them look like dates to humans.

You didn't provide the original raw value of the input DT variable, but if I use the DHMS() function to convert the value you derived from it by using the DATEPART() function I see that it was 32,819,299,200.

Raw Value  =   379,853
As DateTime=     05JAN1960:09:30:53
As Date    =01JAN3000

Raw Value  =      32,819,299,200
As DateTime=     01JAN3000:00:00:00
As Date    =*********

[ChrisNZ]

These dates are invalid.

Since you won't make it easy to be helped, at least understand the response given by @ballardw "Here is an example of some possibly odd datetime values read with anydtdtm format."

This is probably why your dates are wrong.

[Cruise]

Hi Chris, I'm convinced that bad date has something to do how data was read into SAS data in the first place. I have no access to original file. I'm tempted to delete these records where years are 3000.

[ballardw · Accepted solution]

Last time and why we keep asking about the file that was read.

Here is an example of some possibly odd datetime values read with anydtdtm format.

When you can explain how the result is derived for each one of these you might be ready to debug your original data that was read with that informat:

data junk;
  infile datalines truncover;
  input x anydtdtm40.;
  format x datetime20.;
datalines;
20.03.20.04.00.00
20.03.20.54.00.00
20.03.20.1054.00.00
20.03.20.154.00.00
20.03.2018.154.00.00
;
run;

And so everyone doesn't have to actually run the code:

                   x

  20MAR2020:04:00:00
  22MAR2020:06:00:00
  02MAY2020:22:00:00
  26MAR2020:10:00:00
  26MAR2018:10:00:00

Moral of the story: missing or extra characters in your source file can change the results in some pretty odd fashions.

So find the records in the raw data and show the text of the original values read with the informat and we can probably suggest either a fix or a change to the informat.

If the original file was using proc import and that assigned the in anydtdtm informat  and the data is supposed to have a consistent layout then this is an indication that SAS may have found something that did not match your expectations and grabbed that informat instead of a basic datetime informat because a number of values had odd characters or different lengths to be read.