Oracle vs. SAS numerics - any ideas?

[A. Barnet]

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 3F50624DD2F1A9F C 3F50624DD2F1A9F C 3F50624DD2F1A9F BE 1
2 3FACAC083126E97 8 3FACAC083126E97 9 3FACAC083126E97 8D 1
3 3FDBA5E353F7CED A 3FDBA5E353F7CED 9 3FDBA5E353F7CED 91 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