topic Re: Number conversion issue between SAS and Oracle in SAS Programming

Number conversion issue between SAS and Oracle

Michael_Weiss — Wed, 28 Oct 2020 15:22:08 GMT

I have encountered an issue when accessing numeric data stored in an Oracle Table in a column of type NUMBER.

For a lot of numbers the retrieved value slightly differs from the one entered in Oracle. This seems to be a conversion issue between Oracle and SAS.

The following code should illustrate the issue:

In Oracle DB (I use 11.2) run the following statements:

CREATE TABLE num_tmp1 (myNum NUMBER); insert into num_tmp1 (myNum) VALUES (1.2); insert into num_tmp1 (myNum) VALUES (6.6); insert into num_tmp1 (myNum) VALUES (4.1234); insert into num_tmp1 (myNum) VALUES (4.2025);

Afterwards run the following code in SAS (I use 9.4 TS1M5):

LIBNAME O_SP2 ORACLE user="&user." password="&password." path="&sid."; DATA _NULL_; SET O_SP2.num_tmp1; IF myNUM EQ round(myNUM, 0.0000001) THEN PUT "Works for " myNUM=; ELSE PUT "Works NOT for " myNUM=; RUN;

This results in the following log file:

1 DATA _NULL_; 2 SET O_SP2.num_tmp1; 3 IF myNUM EQ round(myNUM, 0.0000001) 4 THEN PUT "Works for " myNUM=; 5 ELSE PUT "Works NOT for " myNUM=; 6 RUN; Works NOT for MYNUM=4.2025 Works NOT for MYNUM=6.6 Works for MYNUM=1.2 Works for MYNUM=4.1234 NOTE: There were 4 observations read from the data set O_SP2.NUM_TMP1. NOTE: DATA statement used (Total process time): real time 0.05 seconds cpu time 0.00 seconds

I understand the issue is related to numeric conversion between Oracle and SAS. I also understand that I could change the Oracle Column Type to BINARY_DOUBLE but I really wonder if there is an SAS option that can be used (e.g. to the libname statement) to correctly convert the value between both systems.

Re: Number conversion issue between SAS and Oracle

ChrisNZ — Wed, 28 Oct 2020 20:49:46 GMT

The NUMBER type is an odd data type.

1. What's the precision and scale defined for that NUMBER field?

2. What are the values returned?

Re: Number conversion issue between SAS and Oracle

unison — Thu, 29 Oct 2020 05:27:14 GMT

As @ChrisNZ mentioned, you first will need to determine the precision and scale i.e. p,s for number(p,s)

Easiest way (without leaving SAS) is to use an explicit pass-through to access ALL_TAB_COLS in Oracle:

proc sql;
connect using O_SP2 as orcl;
select * from 
connection to orcl (
	select * 
	from 
		all_tab_cols 
	where 
		owner='SCHEMA_NAME' /*<-- Need Schema Name*/
		and table_name='NUM_TMP1' 
		and column_name='MYNUM'
);
disconnect from orcl;
quit;

Re: Number conversion issue between SAS and Oracle

Michael_Weiss — Thu, 29 Oct 2020 06:58:38 GMT

Sorry for not mentioning this in my first post. NUMBER is used to store numeric values "as is" with a precision of (up to) 38 digits (See Orace Docs). I did not provided a scale to not allow rounding by mistake in this example.

@unison the result of your mentioned query is NULL for pesicion and scale and 22 for length (executed directly in Oracle DB).

The actual issue happens for all types of NUMBER(p,s) e.g. NUMBER(10.4) or NUMBER(9.3) and for a lot of different numbers.

NUMBER is not storing values in binary (float/double) format but in "decimal" format (two digits per byte). Therefore as long as the number looks correctly in Oracle it is stored correctly in Oracle (as I understand it). And maybe to add this in addition. The values look good in Oracle and can be retrieved correctly in Java using following code:

Class.forName("oracle.jdbc.driver.OracleDriver"); Connection con = DriverManager.getConnection("jdbc:oracle:thin:" + user + "/" + passwd + "@//" + host + ":" + port + "/" + name); DecimalFormat NUM_FORMAT = new DecimalFormat( "0.##################", new DecimalFormatSymbols(Locale.ENGLISH)); ResultSet rs = con.createStatement() .executeQuery("SELECT * FROM num_tmp1"); while (rs.next()) { double val = rs.getDouble(1); System.out.println(NUM_FORMAT.format(val)); double val2 = (Math.round(val * 100000)) / 100000.0d; if (val == val2) { System.out.println("YES: " + val + "-" + val2); } else { System.out.println("NO: " + val + "-" + val2); } }

The result of the code is as follows:

4.2025 YES: 4.2025-4.2025 6.6 YES: 6.6-6.6 1.2 YES: 1.2-1.2 4.1234 YES: 4.1234-4.1234

Re: Number conversion issue between SAS and Oracle

ChrisNZ — Thu, 29 Oct 2020 07:36:24 GMT

And what values do you get in SAS?

From the second reply, where the values are correct when queried from Oracle, it seems that SAS Tech Support may need to get involved.

Re: Number conversion issue between SAS and Oracle

Michael_Weiss — Fri, 30 Oct 2020 12:14:33 GMT

@ChrisNZ , you are right. I have raised a ticket with SAS support and will wait for there answer.

To show the difference in values I have used the following SAS program:

LIBNAME O_SP2 ORACLE user="&user." password="&password." path="&sid.";
DATA _NULL_;
    SET O_SP2.num_tmp1;
    rounded=round(myNUM, 0.000001);
    PUT myNUM= 22.20 rounded= 22.20 myNUM= hex16. rounded= hex16.;
RUN;

The result looks as follows:

2          DATA _NULL_;
3              SET O_SP2.num_tmp1;
4              rounded=round(myNUM, 0.000001);
5              PUT myNUM= 22.20 rounded= 22.20 myNUM= hex16. rounded= hex16.;
6          RUN;

MYNUM=1.20000000000000000000 rounded=1.20000000000000000000 MYNUM=3FF3333333333333 rounded=3FF3333333333333
MYNUM=6.60000000000000000000 rounded=6.60000000000000000000 MYNUM=401A666666666667 rounded=401A666666666666
MYNUM=4.12340000000000000000 rounded=4.12340000000000000000 MYNUM=40107E5C91D14E3C rounded=40107E5C91D14E3C
MYNUM=4.20250000000000000000 rounded=4.20250000000000000000 MYNUM=4010CF5C28F5C290 rounded=4010CF5C28F5C28F
NOTE: There were 4 observations read from the data set O_SP2.NUM_TMP1.
NOTE: DATA statement used (Total process time):
      real time           0.05 seconds
      cpu time            0.00 seconds

It shows that the last digit of the number displayed in HEX format differs by 1 401A666666666667 --> 401A666666666666

This is a common issue when comparing calculated values, but in my case I don't do calculations.

Re: Number conversion issue between SAS and Oracle

ChrisNZ — Mon, 02 Nov 2020 00:10:09 GMT

So the SAS value is 6.6, represented as 401A666666666666.

And after you send this to Oracle, what you get back from Oracle is 6.6, represented as 401A666666666667 ?

Re: Number conversion issue between SAS and Oracle

Michael_Weiss — Mon, 02 Nov 2020 08:08:07 GMT

I always get the "wrong" representation after reading from Oracle, independent of the version I send. I used the following code for testing:

DATA num_tmp2;
    SET O_SP2.num_tmp1;
    OUTPUT;
    mynum = round(mynum, 0.000001);
    OUTPUT;
RUN;

PROC SQL NOPRINT;
    DELETE FROM O_SP2.num_tmp1;
    INSERT INTO O_SP2.num_tmp1 (mynum) SELECT mynum from num_tmp2;
QUIT;

This writes all values in original (wrong) and rounded (correct) SAS representation to Oracle. Afterwards I read the values with the code from above and get the same result for both representations. In Oracle both representations are mapped correctly.

I really wonder if this case is so uncommon that nobody noticed this up to now? I did some additional checks and noticed that from the numbers of the following code almost 30% get converted wrong:

DATA loop(KEEP=mynum);
    DO i = 0 TO 10 BY 0.001;
        mynum = round(i, 0.000001);
        OUTPUT;
    END;
RUN;

Re: Number conversion issue between SAS and Oracle

ChrisNZ — Mon, 02 Nov 2020 08:42:56 GMT

I wonder where the precision gets lost (gets even more lost I mean).

According to this page

https://babbage.cs.qc.cuny.edu/IEEE-754.old/64bit.html

401A666666666666 is about 6.6000000000000000

while

401A666666666667 is about 6.6000000000000005

Can you see the hex value in Oracle?

Do you access Oracle via ODBC?

Re: Number conversion issue between SAS and Oracle

Michael_Weiss — Mon, 02 Nov 2020 09:02:04 GMT

Both values are represented in the same way in Oracle. Oracle NUMBER data type stores numbers differently, as far as I understand they store single digits (two per byte) instead of binary floating point number. I'm accessing Oracle using Java ODBC (Oracle SQL Developer).

Additionally Oracle applies rounding for data types like NUMBER(10,4), so in Oracle 6.6000000000000005 would be rounded to 6.6 anyway if column is of type NUMBER(10,4). And the issue applies to all NUMBER columns (tested with 9,3 and 10,4).

Additionally this only applies to columns of data type NUMBER. Columns of data type BINARY_DOUBLE are not affected.

Re: Number conversion issue between SAS and Oracle

FreelanceReinh — Mon, 02 Nov 2020 13:28:23 GMT

Hello @Michael_Weiss,

I'm not sure, but maybe this is related to the known issue of SAS (under Windows on x64 processors) with numeric literals exceeding the precision of the internal binary floating-point representation. There was a long discussion about this (led by @ChrisNZ and involving responsible SAS personnel) in 2016, after which the issue was mentioned in the documentation.

I was reminded of that issue because you wrote that Oracle DB columns of type NUMBER "store single digits (two per byte)" and you mentioned "22 for length" and "(up to) 38 digits." Indeed, if I append a sufficient number of insignificant zeros to your example values 6.6 and 4.2025, I obtain the same slightly incorrect binary representations on my Windows workstation (see below). A similar effect also occurs with 1.2 and 4.1234, though.

data _null_;
input x;
r=round(x,1e-8);
put (x r)(=hex16./)/;
cards;
1.20000000000000000000000
6.6000000000000000000000000000
4.20250000000000000000000
4.1234000000000000000000000
;

Result:

x=3FF3333333333334
r=3FF3333333333333

x=401A666666666667
r=401A666666666666

x=4010CF5C28F5C290
r=4010CF5C28F5C28F

x=40107E5C91D14E3B
r=40107E5C91D14E3C

In each case I used the minimum number of trailing zeros needed to cause the error (22, 27, 19 and 21, respectively). As a "rule," the internal representation fluctuates erratically between two or more slightly different values when more and more trailing zeros are appended.

Please let us know when you get an interesting answer from Tech Support.

Re: Number conversion issue between SAS and Oracle

Michael_Weiss — Wed, 06 Jan 2021 09:16:02 GMT

Happy New Year to Everyone!

I just received the answer from SAS Support that the current behavior is correct and will not be changed. We should use the round function in this case.

@FreelanceReinh

sorry for the late reply and thanks for pointing this out, from my point of view the issue is simply because of different conversion methods between ORACLE NUMBER <> SAS DOUBLE <> Text, so likely related to the same / a similar code.

Re: Number conversion issue between SAS and Oracle

ChrisNZ — Wed, 06 Jan 2021 11:11:47 GMT

"The behaviour is correct" seems an awfully terse reply. How can it be correct when the numbers differ? What makes them say the behaviour is correct? You need a better explanation than that.

Re: Number conversion issue between SAS and Oracle

FreelanceReinh — Wed, 06 Jan 2021 12:54:12 GMT

Hi @ChrisNZ,

First of all, congrats on your (second) appointment as Super User of the Communities!

@ChrisNZ wrote:
(...) How can it be correct when the numbers differ? What makes them say the behaviour is correct?

My interpretation of the term "correct" in this particular context is:

The internal code producing the results does what the developers intended and it has no known bugs.
The results may seem slightly surprising at times, but they are consistent with the algorithm used and there are (good?) reasons for using this algorithm.
The developers don't want to change the algorithm (maybe to avoid unwanted side effects) and would not regard any change as a "correction."

@Michael_Weiss: Many thanks for the update.

Re: Number conversion issue between SAS and Oracle

Tom — Wed, 06 Jan 2021 15:40:58 GMT

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.

Re: Number conversion issue between SAS and Oracle

ChrisNZ — Thu, 07 Jan 2021 08:04:02 GMT

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.

Re: Number conversion issue between SAS and Oracle

ChrisNZ — Thu, 07 Jan 2021 08:09:01 GMT

> The results may seem slightly surprising at times, but they are consistent with the algorithm used and there are (good?) reasons for using this algorithm

Only the results count. That the algorithm is executed as expected is poor consolation if the algorithm is lacking.

Also, no one likes surprises. Just do your job, Oracle engine!

Re: Number conversion issue between SAS and Oracle

Tom — Thu, 07 Jan 2021 14:33:49 GMT

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

Re: Number conversion issue between SAS and Oracle

wmil — Thu, 19 Mar 2026 11:35:49 GMT

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.