Oracle vs. SAS numerics - any ideas?

From: A. Barnet <abarnet_at_yahoo.com>
Date: 17 Oct 2003 10:09:31 -0700
Message-ID: <4c4a7d1e.0310170909.4121d3bc_at_posting.google.com>

Hi, all,

I posted a similar message at comp.soft-sys.sas.

I used code like the following via SAS PROC SQL pass-thru to create a table named user.tmp in Oracle:

drop table user.tmp;
create table user.tmp ( cnum varchar(5), xnum number );

insert into user.tmp values ( '0.001', null );
insert into user.tmp values ( '0.056', null );
insert into user.tmp values ( '0.432', null );

update user.tmp set xnum = to_number( cnum );

and then in SAS I fetched the data with code like:

proc sql;
  connect to oracle ( ... );
  create table work.tmp as
    select *
    from connection to oracle

            ( select cnum,
                     xnum,
                     dump(xnum) dnum,
                     rawtohex(xnum) hnum
              from   user.tmp );

  disconnect from oracle;
quit;

Then, I manually calculated (%chartohex) the binary equivalent of cnum beyond the IEEE representation used on Alpha OpenVMS SAS (double precision), and compared that extended result with the internal representation of xnum after it had been copied to SAS:

data work.tmp;
  set work.tmp;

  orahex = put( xnum, hex16. );
  sasnum = input( cnum, ??best32. );
  sashex = put( sasnum, hex16. );

  %chartohex( cnum, bint, bfrac, bin, hex, max = 53 );   nbit = substr( bin, 53, 1 );
run;
proc print; run;

and got this:

Obs CNUM  DNUM                   HNUM    
1   0.001 Typ=2 Len=2: 191,11    BF0B    

2 0.056 Typ=2 Len=3: 192,6,61 C0063D
3 0.432 Typ=2 Len=3: 192,44,21 C02C15

Obs ORAHEX SASHEX HEX NBIT

1 3F50624DD2F1A9FC 3F50624DD2F1A9FC 3F50624DD2F1A9FBE 1 2 3FACAC083126E978 3FACAC083126E979 3FACAC083126E978D 1 3 3FDBA5E353F7CEDA 3FDBA5E353F7CED9 3FDBA5E353F7CED91 0 I am reasonably sure that the %CHARTOHEX macro correctly calculated the extra HEX nibble. It confirms that the values of SASHEX are rounded.

But the values of ORAHEX do not appear to be consistently rounded.

Obs=1 appears to have rounded HEX=...A9FBE to ORAHEX=...A9FC, as expected since NBIT=1. In this case, I got the same result as in SASHEX. But Obs=2 appears to have truncated rather than rounded up, from HEX=...E978D to just ORAHEX=...E978, even though NBIT=1 (next bit).

Worse yet, for Obs=3 rounded up from HEX=...CED91 to ORAHEX=...CEDA, even though NBIT=0.

I cannot tell if this occurred in "connection to oracle" on the SAS side somewhere, or if the conversion to IEEE was done on the Oracle side. So, I don't know exactly where these roundings and truncations are occurring.

Note that the values of SASHEX round up when NBIT=1, and round down when NBIT=0, as expected.

Does anyone know why there is such inconsistency in the ORAHEX results, or what rule makes them consistent?

Thanks!
Andy Received on Fri Oct 17 2003 - 19:09:31 CEST