topic Re: Reading Numeric Values from Oracle into SAS in ODS and Base Reporting

Reading Numeric Values from Oracle into SAS

deleted_user — Tue, 24 Oct 2006 13:20:21 GMT

HI,

As I try to reconcile the data in Oracle (through SAS/Oracle) and SAS, I found there is slightly a very very small difference in the sum of numeric variables in both side.

Sample Codes:
PROC SUMMARY DATA = ORALib.inData NWAY MISSING;
VAR amount ;
OUTPUT OUT = inData_ORA (DROP =_TYPE_)
SUM=;
RUN;

PROC SUMMARY DATA = SASLib.inData NWAY MISSING;
VAR amount ;
OUTPUT OUT = inData_SAS (DROP =_TYPE_)
SUM=;
RUN;

Variable Oracle Amt Sum SAS Amt Sum Difference
AMOUNT 68726887.00 68726887.00 -.000000030

I suspect that SAS truncate some of the values of the numeric variables when I PROC SUMMARY straight into the Oracle Library. I understand this is very small difference but is there any way I avoid this different to happen? I tried LENGTH amount 8 but it does not really seem to solve the problem. What do you think if using INFORMAT rb8. in for PROC SUMMARY statement?

Thanks.
Jason@Aus

Re: Reading Numeric Values from Oracle into SAS

David_SAS — Fri, 27 Oct 2006 19:39:15 GMT

Our PROC SUMMARY expert in R&D says:

[pre]
Unfortunately, there's no simple answer as to why they're getting slightly different sums when reading directly from Oracle vs reading a copy of the data.
[/pre]

I'm sorry that we couldn't be of more help.

-- David Kelley, SAS

Re: Reading Numeric Values from Oracle into SAS

deleted_user — Mon, 30 Oct 2006 09:06:27 GMT

> HI,
>
> As I try to reconcile
> SAS/Oracle) and SAS, I found very
> very small difference in the sum of numeric
.....

> Variable Oracle Amt Sum SAS Amt Sum Difference
> AMOUNT 68726887.00 68726887.00
> -.000000030
>
> I suspect that SAS truncate
> numeric variables when I PROC SUMMARY
> this is very small
> is there any way I avoid this
.....
> Thanks.
> Jason@Aus

When is it important to eliminate differences in values that occur beyond 15 significant digits ?

SAS Real Numbers cannot support greater precision.

Be glad you are in SAS. Put a 16 digit credit card number into excel, and watch the 16th become zero !

Peter