Difference in the precision of TIMESTAMPS between SAP HANA and SAS

[klroesner]

We use the data type TIMESTAMP in our SAP HANA Database:

In Dbeaver it looks correct:

In the SAS EG it looks as if we have a problem with the precision of the Timestamp (Datetime26.7) :

Is the problem known and is there a solution?

[Tom · Accepted solution]

You appear to have hit the limit of precision of the 64bit floating point numbers that SAS uses.

It does not matter if the number is number of seconds or some other floating point number.

645  data xx;
646    dt='11MAY2011:12:59:08.1231111'dt;
647    put dt datetime28.7;
648    put dt best32.;
649  run;

  11MAY2011:12:59:08.1231110
                1620737948.12311
NOTE: The data set WORK.XX has 1 observations and 1 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.03 seconds


650  data xx;
651   *dt='11MAY2011:12:59:08.1231111'dt;
652    dt=1620737948.123111;
653    put dt datetime28.7;
654    put dt best32.;
655  run;

  11MAY2011:12:59:08.1231110
                1620737948.12311
NOTE: The data set WORK.XX has 1 observations and 1 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds

[FreelanceReinh]

Hello @klroesner,

I am not sure (rather skeptical) if the FedSQL data type TIMESTAMP(7) might be useful in your case (or if you are using PROC FedSQL at all, cf. Benefits of FedSQL).

Otherwise, as Tom has pointed out already, the precision of a (64-bit floating-point) numeric variable in SAS is insufficient for your timestamps. The place value of the least significant bit of a number's binary representation in a numeric variable depends on the number's order of magnitude. As SAS datetime values are measured in seconds since January 1st, 1960, that place value doubles whenever this number of seconds doubles: For datetime values later than 04 Jan 1977 the last bit represents already 1.192E-7 or more seconds, so that the seventh decimal cannot be exact in all cases. Another doubling occurred on 09 Jan 1994 and the next one will occur on 19 Jan 2028. So, as of 2024, the granularity of current datetime values (or from 2011 and 2023 like yours) is 2.384E-7 seconds.

One way to overcome this limitation is to store the date part and the time part of the timestamp (or alternatively: the integer part and the fractional seconds) in separate numeric variables. The separation could be done on the SAP side where sufficient precision is available or on the SAS side (as shown in the example log below) after importing the timestamp as a character string.

Example:

384   data _null_;
385   timestamp='2011-05-11 12:59:08.1231111';
386   date=input(scan(timestamp,1,' '),yymmdd10.);
387   time=input(scan(timestamp,2,' '),time20.);
388   format date yymmdd10. time time16.7;
389   put date= time=;
390   run;

date=2011-05-11 time=12:59:08.1231111

[klroesner]

Hello FreelanceReinh,

thanks for the detailed answer... What I do not understand is the default format for timestamps in SAP HANA is Datetime26.7. (https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/acreldb/n1jmk22yyo33xsn13b90bu4agnjh.htm). But SAS cannot map this in this way? Does that make sense? What do u think? 

[FreelanceReinh]

---

@klroesner wrote:

(...) What I do not understand is the default format for timestamps in SAP HANA is Datetime26.7. (https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/acreldb/n1jmk22yyo33xsn13b90bu4agnjh.htm). But SAS cannot map this in this way? Does that make sense? What do u think? 

---

Think of DATETIME26.7 as a best approximation. On average, the DATETIME26.7-formatted value is closer to the exact value from SAP HANA than the rounded DATETIME26.6-formatted value. And the situation is even better if the seventh decimal place (or the entire fractional seconds part) is constantly zero.

But they really should have mentioned the limited precision of those values in numeric SAS variables.

[Kurt_Bremser]

SAP has a dedicated TIMESTAMP type, while SAS uses the default 8-byte numeric type to store the value in seconds, with 1960-01-01:00:00:00 as the zero second.

I guess that internally, SAP uses a separate date and time value, allowing for the greater precision of the time part.

You will also find other limitations. SAP (like other databases) allows dates starting at 0001-01-01, while SAS does not accept dates before 1582 (the introduction of the Gregorian calendar).

The precision limit for timestamps in most systems was the driver to switch keys from timestamps to UUIDs.