09-23-2016 07:55 AM
I have a timestamp value in teradata that is '9999-12-31 23:59:59.999999' that we use as a high date. When i see this value in SAS, it comes in as '01JAN****:00:00:00.000000'. This leads me to believe that there is some max date that SAS is limited to. Any suggestions as to how to make SAS read it as '31DEC9999:23:59:59.999999' or is this even possible?
09-23-2016 08:32 AM
The problem here is not so much the date range, but the fact that you run into the typical numeric precision problem of the real number representation that SAS uses to store numbers.
Since a datetime value is the count of seconds from 01jan1960:00:00:00, you get sufficiently large numbers that force some (binary) rounding.
Therefore, the datetime literal '31dec9999:23:59:59.900000'dt; will actually display as 31DEC9999:23:59:59.899994.
Adding more '9' digits forces more imprecision, and at one point SAS rounds up to the next second before displaying, and that is 00:00:00 on January 1st, 10000, which is outside the displayable date range.
I suggest reading such timestamps as dates and times separately, and setting the year to 2999 if it is 9999.
09-23-2016 09:37 AM
Sounds like you had an over zealous dba/solution designer...
Could someone even imagine a real world date that would be between 01jan9999:23:59:59 and 01jan9999:23:59:59.9999999?
As a comparison, SAS uses 01Jan5999:00:00 as a high dat by default.
Is SAS responsible for updating this data?
What happens if you try to filter out high dates using a constant with the dt literal?
09-23-2016 10:55 AM
Pretty sure it's a precision of storage issue.
31DEC9999:23:59:59 doesn't have an issue because it is integer. As soon as you add the decimals though it starts causing problems.
The **** appear because the storage ends up rounding up to when displayed and SAS hasn't yet provided formats to display year 10,000.