BookmarkSubscribeRSS Feed
jfreeze03
Calcite | Level 5

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?

3 REPLIES 3
Kurt_Bremser
Super User

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.

 

 

LinusH
Tourmaline | Level 20

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?

 

Data never sleeps
ballardw
Super User

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.

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 4619 views
  • 1 like
  • 4 in conversation