Hi Matt,
As promised, please find below more details on your numeric representation issue:
Let's compare four numbers with decimal places in the binary system and their 64-bit binary floating-point representations, as which SAS stores them internally on Windows and Unix systems, and check what these numbers look like when converted back to the decimal system.
First, a quick look at the internal representations (again, revealed by the HEX16. format):
data _null_;
X1 = 0.73322; /* the fractional part of your datetime value */
X2 = 56.73322; /* the seconds of your datetime value */
X3 = 35936.73322; /* the time part of your datetime value */
X4 = 1744797536.73322; /* your datetime value */
put (X:) (= hex16. /);
run;
/* Result:
X1=3FE77689CA18BD66
X2=404C5DDA272862F6
X3=40E18C177689CA19
X4=41D9FFDFD82EED14
*/
(Edit: colored the above results.)
Now, the detailed comparison:
decimal: 0.73322
binary: 0.10111011101101000100111001010000110001011110101100110001001... (infinite sequence of 0s and 1s)
internal: exponent (hex): 3FE
mantissa (bin): 0111011101101000100111001010000110001011110101100110, last bit (place value 2^-53) rounded down
mantissa (hex): 77689CA18BD66
converted back to decimal: 0.73321999999999998287... (numeric representation error approx. -1.7E-17)
decimal: 56.73322
binary: 111000.10111011101101000100111001010000110001011110101100110001001...
internal: exponent (hex): 404
mantissa (bin): 1100010111011101101000100111001010000110001011110110, last bit (place value 2^-47) rounded up
mantissa (hex): C5DDA272862F6
converted back to decimal: 56.73322000000000286945... (numeric representation error approx. 2.9E-15)
decimal: 35936.73322
binary: 1000110001100000.10111011101101000100111001010000110001011110101100110001001...
internal: exponent (hex): 40E
mantissa (bin): 0001100011000001011101110110100010011100101000011001, last bit (place value 2^-37) rounded up
mantissa (hex): 18C177689CA19
converted back to decimal: 35936.73322000000189291313... (numeric representation error approx. 1.9E-12)
decimal: 1744797536.73322
binary: 1100111111111110111111101100000.10111011101101000100111001010000110001011110101100110001001...
internal: exponent (hex): 41D
mantissa (bin): 1001111111111101111111011000001011101110110100010100, last bit (place value 2^-22) rounded up
mantissa (hex): 9FFDFD82EED14
converted back to decimal: 1744797536.73322010040283203125 (numeric representation error approx. 1.0E-7)
The fractional part of the four numbers is always the same, but the number of bits required for the integer part increases as we move from X1 via X2 and X3 to X4. For a numeric variable of (default) length 8 bytes there are always 8*8=64 bits of storage space available. These 64 bits must accommodate both the integer part and the fractional part (as far as possible).
Looking at the four binary mantissas we see: The larger the integer part gets, the more bits from the fractional part we lose (red).
In particular, by adding the date part 86400*20194=1744761600 to the time value X3 (in order to obtain the datetime value X4) we rounded a sequence of 37 binary digits to only 22, which means a substantial loss of information.
The most interesting part is to see what happens if we subtract the largest multiple of 60 less than X4, i.e. 1744797480, from X4, in order to obtain the seconds from the datetime value. Since 1744797480 is almost as big as 1744797536, the integer parts cancel out to a large extent, both in the decimal and in the binary system. In the latter, the length of the integer part decreases from 31 to only 6 digits (see binary representations of X4 and X2 above).
Similarly, the internal binary floating-point representation of the result would actually require 31-6=25 bits less than what is needed for X4. However, the storage location is always 64 bits long. The 25 bits saved cannot be left blank. Ideally, these should be filled with the corresponding binary digits from X2. But this information is not available in the current calculation: even X3 contained only 15 of those 25 bits, and these 15 bits were lost in the transition from X3 to X4 (as described above). As the final resort, SAS fills all 25 "empty" bits with zeros, as shown below.
decimal: 1744797536.73322
-1744797480
= 56.73322
binary: 1100111111111110111111101100000.10111011101101000100111001010000110001011110101100110001001...
-1100111111111110111111100101000
= 111000.10111011101101000100111001010000110001011110101100110001001...
internal result:
exponent (hex): 404
mantissa (bin): 1100010111011101101000101000000000000000000000000000, last 25 bits padded with zeros
mantissa (hex): C5DDA28000000
converted back to decimal: 56.73322010040283203125 (This combines the absolute num. repr. error of X4 with
the much smaller number X2, thus pushing the relative error from order of magnitude 10^-17 to 10^-9.)
Even with "long" formats such as 23.20 SAS does not show all 20 decimals of the latter number, but only 13 (and pads the rest with zeros), although the information is available. This rounding is wise, because the place value of the least significant bit in the internal representation is 2^-47=7.105...E-15. This means that hardly one more digit could be justified by the precision of the internal binary representation.
So, finally we see that the "phantom decimals" you observed are not unpredictable random digits, but the result of reproducible calculations with binary floating-point numbers.
Here are two references on the internal representation of numbers in SAS and on strategies how to deal with numeric representation issues:
Numerical Accuracy in SAS Software
Dealing with Numeric Representation Error in SAS® Applications
... View more
