Hi @js5,
Excel uses fractions of days for the time component of a datetime value. In your example, 22:35 h is expressed as
(22*3600+35*60)/86400=0.9409722222222...
and together with the date component (11 Apr 2018) the number is 43201.9409722222..., in theory with an infinite sequence of 2s. However, in practice the computer stores this number in a binary format with, of course, only a finite number of bits (binary digits). Using the IEEE floating-point format which is also used by SAS (under Windows) that binary number amounts to
1010100011000001.1111000011100011100011100011100011100
with 53 digits in total, where subsequent digits (01...) have been rounded off. Hence, in the decimal system this number is already slightly smaller than it should be:
43201.9409722222189884...
I unzipped your example .xlsx file and opened the relevant sheet1.xml file in Notepad. Excerpt:
<v>43201.940972222219</v>
Obviously, this is a rounded decimal representation of the value above.
Presumably, SAS reads this value and, after another decimal-to-binary conversion, converts it into a SAS datetime value (i.e., subtracts an offset of 21916 days and multiplies the result by 86400, but all this is done in the binary system). In the decimal sytem the result should be something like 1839105299.99999972..., in the internal hexadecimal representation: 41DB67A144FFFFFF, where the last "F" is rounded up from "ED...". Except for the last rounding (up vs. down), this is what you found using the HEX16. format. In any case the value differs from 41DB67A145000000 (internal repr.), decimal: 1839105300, the SAS datetime value '11APR2018:22:35:00'dt.
So, to resolve the issue, you should apply the ROUND function once the values have been read (from Excel) into SAS. If the datetime values actually don't contain fractions of a second, round to integers, otherwise round to 0.001 (seconds) or whatever the appropriate rounding unit for your data is.
Using these clean values in an export from SAS to a database minimizes the risk of introducing rounding errors in the target environment.
Edit: Cleaning the values also helps to avoid unexpected wrong results within SAS. For example, the IF condition in
if a>='11APR2018:22:35:00'dt then ...
would not be satisfied for a numeric value a with an internal HEX16. representation of 41DB67A144FFFFFF (or ...FFFE).
... View more
