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