Number conversion issue between SAS and Oracle

[Tom]

You are comparing apples to oranges.

The Oracle system is storing the values using decimal numbers (base 10). SAS stores all numbers as floating point numbers (base 2).

There are many numbers that have exact representations in base 10 that do not have exact representations in base 2.  And vice versa. So there is no real way to handle this.  

In addition even for pure integers Oracle can support 22 or more decimal digits of precision and the 8 byte floating point representation SAS only allows SAS to represent integers exactly (with no gaps) to about 15 decimal digits.

[ChrisNZ]

SAS knows the Oracle data type, so SAS could easily read the number as expected. A variable typed NUMBER(10,4) could easily be rounded to 4 decimals by SAS. Should be rounded to 4 decimals, imho. That's the sole purpose of an access engine: to take care of such details.

That the SAS engine doesn't do that is a defect in my world. If these details are not taken care of, and users have to fine-tune the data passed to SAS, the value of the engine decreases significantly. Also, if some values are distorted, and questions such as seen in this post arise, the trust placed in the engine is compromised.

[Tom]

You will have to talk to SAS about how their engine works. Perhaps they are letting Oracle do the transformation from DECIMAL numbers into FLOATING point and that is why you are seeing slight differences in how non-exact binary values are represented.

If you want simulate work with decimal numbers in SAS then store them as integers and just remember where the decimal point belongs.  You can even make your own picture format to display the integers with the implied decimal point when printing.

177   proc format ;
178     picture mydollar low-high = '009.99' (multiplier=1) ;
NOTE: Format MYDOLLAR is already on the library WORK.FORMATS.
NOTE: Format MYDOLLAR has been output.
179   run;

NOTE: PROCEDURE FORMAT used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds


180
181   data test;
182     input x @@;
183     put x=  x= mydollar. ;
184   cards;

x=100 x=1.00
x=123 x=1.23
x=4567 x=45.67

[wmil]

I am aware that this discussion has been dormant for years, but I wanted to share my insights anyway.

This is neither an issue with Oracle nor an issue with SAS.

The root cause of the discrepancy is the format in which numbers are stored in an Oracle variable with NUMBER datatype versus a SAS variable with length 8.

Oracle NUMBER datatype is a variable-length datatype. It can use up to 21 (!) bytes to store a number, but it will only use the number of bytes needed to store the number. It will store it in a decimal format though. The 21 bytes potential allows you to store a number with a very high precision (=number of significant decimal digits (not decimal places!)) in an Oracle NUMBER variable. Numbers can be stored with a precision of up to 38 (!) significant decimal digits.

SAS numerical variables on the other hand, are fixed-length variables (default of 8 bytes). It is evident that a variable with a limit of 8 bytes cannot store the same kind of precision as with 21 bytes. So, when Oracle NUMBER is "wav", SAS is "mp3": SAS will require less storage, and will be much faster to open and fast-forward, but not the HiFi sound that Oracle will give.

SAS is built to do science, it is efficient in performing complex calculations.

Oracle is built to store data. It is efficient in storing high precision data.

However, the most important difference with Oracle is that the SAS variable stores the number in the IEEE 754 double-precision (=8 byte=64 bits) format, which is a binary representation of the number. As said above, it will not be able to store (all) numbers with exactly the same precision (8 byte vs 21 byte).

If it needs to store a number which it cannot represent exactly in those 8 bytes, it will store it as the number closest to it, which it can still represent in those 8 bytes.

The number "0.1" is a perfect example of this "floating point issue":

Oracle NUMBER will be able to perfectly store this value, using 2 bytes only. It is possible to do so, because it is storing the number in a decimal format.

SAS on the other will (always) need 8 bytes, and cannot store it exactly, because SAS wants to store it in the binary IEEE 754 double-precision format.

"0.1" in this format would in binary be:

"0011111110111001100110011001100110011001100110011001100110011001..." (with an infinite repeat of the sequence "1001")

SAS only has 8 byte (64bits) to its disposal, so the number cannot be represented exactly. Therefore it will store an approximated value, being the one closes to "0.1" which can still fit in 64 bits.

And no, this is not just truncating the above binary code at 64 bits. The closest 64 bit value is actually

"0011111110111001100110011001100110011001100110011001100110011010" (ending with "10" i.o. "01").

In decimal notation, this is no longer exact "0.1" but it is in fact "0.1000000000000000055511151231257827021181583404541015625..."

And this is the root cause: the Oracle NUMBER variables and SAS numerical variables are not entirely compatible because they are based on another numeral system (decimal vs binary = base 10 vs base 2).

You cannot write "0.1" in the form of a power of 2 (or the sum of powers of 2).

So if you import an Oracle NUMBER variable in SAS, at some point SAS will need to convert the 21 bytes in decimal notation to 8 bytes in binary notation, and it will loose precision. And there is absolutely nothing you can do about that. That's just life, you'll have to live with it.

There is one guarantee though with the IEEE 754 double-precision format (which SAS uses): within a certain (extremely wide) range of numbers, it is mathematically guaranteed that the first 15 significant digits that you put in, will still be unchanged if you pull them out again.

E.g. if you insert the value "0.0012345678901234567890" (i.e. 20 significant digits) into a SAS numerical variable, if you pull it out again (e.g. for a calculation), the number that you pull out will always (guarantee) be "0.00123456789012345###"  with the part "###" being entirely unreliable (could be empty, could be 67890, could also be 518461651861654, you simply can no longer rely on that part).

That is the reason why

0.3-0.2=0.1 in SAS is not TRUE.

0.3 will be stored as an approximated value.

0.2 will be stored as an approximated value.

0.3-0.2 will be calculated based on those approximated values, and the result will in its turn also be stored as an approximated value.

And 0.1 will also be stored as an approximated valued, but it will be slightly different as the approximated value above.

And that is why

0.3-0.2=0.1 in SAS is not TRUE

but

ROUND(0.3-0.2,1E-10)=0.1 in SAS is TRUE.

I hope this explains a thing or two.