Number conversion issue between SAS and Oracle

[Michael_Weiss]

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.

[ChrisNZ]

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? 

[unison]

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;

[Michael_Weiss]

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

[ChrisNZ]

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.

[Michael_Weiss]

@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.

[ChrisNZ]

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 ?

[Michael_Weiss]

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;

[ChrisNZ]

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?

[Michael_Weiss]

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.

[FreelanceReinh]

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.

[Michael_Weiss]

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.

[ChrisNZ]

"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.

[FreelanceReinh]

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.

[ChrisNZ]

> 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!