Discrepancies in final results caused by the way you make the calculations

[Zatere]

Hi there,

I would have a general question.

I have done some changes in the way a program generates results, such as averages, mean etc. For example, I have reduced the number of data steps by combining those many steps in one bigger query.

At the end of the process I export the data sets as excel files.

When I compare the results of the amended code with the original code in excel, I don’t get any discrepancies.

However, when I compare those in SAS using PROC COMPARE for some of the variables in those data sets I get really small discrepancies like 4.589E-13. The difference is very small, in this example it is only 0.0000000000004589.

Is there any explanation why that is happening? Is it the way that SAS make the calculations or the results are wrong?

Thanks!

[FreelanceReinh · Accepted solution]

Hi @Zatere,

These are typical rounding errors due to finite-precision arithmetic.

Simple example: Mathematically, 2/3 + 2/3 = 4/3. But if the operands are stored only with a finite number of decimals, the left-hand side is something like 0.6666...667 + 0.6666...667 = 1.3333...334, whereas the right-hand side is correctly rounded off to 1.3333...333, so the results will differ regardless if the finite precision is 12, 25 or even 1000 digits.

In the case of machine arithmetic we're talking about binary digits rather than decimal digits, which makes things even worse, as shown by the example below:

data _null_;
a=0.1+0.3+0.7;
b=0.1+0.7+0.3;
c=1.1;
if a=b then put 'equal';
else put 'unequal';
put (a b c) (/ =hex16.);
run;

With SAS 9.4 under Windows the result in the log is most likely:

unequal

a=3FF199999999999A
b=3FF1999999999999
c=3FF199999999999A

That is, just because of a different order of summands, the result in b is affected by rounding error and thus differs from a and c.

In PROC COMPARE you can use the CRITERION= and METHOD= options to simplify the result in the presence of such irrelevant small differences.

[Zatere]

Thanks a lot! It makes sense!