topic Re: caluculed in sas proc in SAS Programming

caluculed in sas proc

LineMoon — Wed, 13 Jan 2016 20:55:55 GMT

Hello,

Is it possible to get the differents results for same code and the same input by using

calculated in sas proc ?

(calculated v)*m as T

T can have a different results for the same code and in put ? but with a very small différence ?

Thank you

Re: caluculed in sas proc

FreelanceReinh — Wed, 13 Jan 2016 21:20:32 GMT

Yes, this is possible, in particular if the code was executed on different platforms. Perhaps you can expand a little bit on how you obtained the two different results.

Re: caluculed in sas proc

LineMoon — Wed, 13 Jan 2016 21:38:46 GMT

Thank for your message.

Please, why this problem with calculted in sas proc ?

Can you explain more ? is it possible to know the machine and to identify the main raison?

Re: caluculed in sas proc

PGStats — Wed, 13 Jan 2016 22:08:11 GMT

The main reason is the representation of floating point numbers in computers. It is only a (very good) approximation. Sometimes, the order of evaluation is enough to make a difference, particularly in calculations involving the sum of numbers with very different scale. Consider for example:

data test;
a = 1; b = 1e-14;
run;

proc sql;
select
    a + 2*b as c format=e17.,
    calculated c - a - b as d format=e12.,
    calculated c - b - a as e format=e12.
from test;
quit;

                                   c             d             e
                   ----------------------------------------------------------------
                    1.0000000000E+00   9.98401E-15   9.98398E-15

Re: caluculed in sas proc

FreelanceReinh — Wed, 13 Jan 2016 22:12:57 GMT

PG was faster and gave a nice example.

Yes, numeric representation issues can be one reason for slightly different results of calculations which should actually give identical results from a mathematical point of view. PROC SQL is particularly prone to such issues, because (as you probably know) it sometimes orders the rows of a table in an unpredictable (and platform-dependent) way. Now, if a calculation, let's say a sum, is done like a+b+c+d+e+... or d+a+e+c+b+..., the results obtained by the computer can differ, because rounding errors would accumulate differently depending on the order of the summands, see PG's example.

Re: caluculed in sas proc

LineMoon — Wed, 13 Jan 2016 22:44:36 GMT

Thank you.

It is very interessting.

Please, do you have a good doc for "PROC SQL is particularly prone to such issues, because (as you probably know) it sometimes orders the rows of a table in an unpredictable (and platform-dependent) " ?

I suppose, it depends the platform, so how can I the machine in sas log ? an other means which give the best result ?

To me, if I have (calculated x)*g as f is a different of (calculated x)*g as f , it will be the platform ? how can i identify the problem in sas log ?

Re: caluculed in sas proc

LineMoon — Wed, 13 Jan 2016 22:45:56 GMT

Than you

That's very intersting

To me, if I have (calculated x)*g as f is a different of (calculated x)*g as f , it will be the platform ? how can i identify the problem in sas log ?

Re: caluculed in sas proc

Reeza — Wed, 13 Jan 2016 23:08:05 GMT

What are you considering large differences? The difference here could be considered the difference between rounding your numbers and calculating or calculating with all the raw data. I wouldn't expect significant differences and I don't think you'd see any information in the log regarding such an issue.

This isn't a SAS SQL issue, its a computer numerical representation issue, Excel will have the same issue, SAS, Python, R ... they all have the same limitations.

Here's the documentation representation of the issue:

http://support.sas.com/documentation/cdl/en/lrcon/62955/HTML/default/viewer.htm#a000695157.htm

Re: caluculed in sas proc

FreelanceReinh — Wed, 13 Jan 2016 23:11:25 GMT

The documentation of the ORDER BY clause, for example, says: "Without an ORDER BY clause, the order of the output rows is determined by the internal processing of PROC SQL, the default collating sequence of SAS, and your operating environment." That order does matter for certain calculations, has been demonstrated by PG's example.

I don't know whether it is the platform ("operating environment") in your specific case. You haven't told us so far, how you obtained your results and whether different platforms were involved at all.

You will hardly be able to identify the problem in the SAS log, because from SAS's perspective everything was done correctly and as usual. It won't reveal any details about the "internal processing of PROC SQL" mentioned above.

When you're asking about the "best result", are you really concerned about those tiny differences like the 3E-20 in PG's example?

Re: caluculed in sas proc

FreelanceReinh — Wed, 13 Jan 2016 23:16:59 GMT

The SAS 9.4 version of the documentation linked by @Reeza has been improved considerably compared to the earlier SAS versions:

http://support.sas.com/documentation/cdl/en/lrcon/68089/HTML/default/viewer.htm#p0ji1unv6thm0dn1gp4t01a1u0g6.htm

Re: caluculed in sas proc

PGStats — Wed, 13 Jan 2016 23:28:50 GMT

The precision of numerical methods is a huge topic in itself that is covered in any introductory numerical analysis course.

Try Googling rounding error numerical analysis.

Re: caluculed in sas proc

ballardw — Wed, 13 Jan 2016 23:54:42 GMT

@PGStats wrote:

The precision of numerical methods is a huge topic in itself that is covered in any introductory numerical analysis course.

Try Googling rounding error numerical analysis.

And should also be covered in any serious programming course.

In a significant number of general programming languages you specifically the precision of storage when declaring variables and incautious assignments can lead to, let us say, interesting results.

If I remember correctly IBM370 assembler language had something like 16 ways to add numbers. Depending on the specific add and the types of variables you could very easily have a+b not equal to b+a .

Re: caluculed in sas proc

LineMoon — Thu, 14 Jan 2016 21:52:30 GMT

I use sas 9.2 under unix : AIX 6.1

Re: caluculed in sas proc

LineMoon — Thu, 14 Jan 2016 21:54:26 GMT

Thank you

Please, can I have an exemple for a+b is not equal to b+a ?

Re: caluculed in sas proc

LineMoon — Fri, 15 Jan 2016 23:26:35 GMT

Thank you Reeza for your message

As you say

<<it can be a computer numerical representation issue>>

How can I identify that ?

Re: caluculed in sas proc

LineMoon — Sat, 16 Jan 2016 00:45:54 GMT

My OS is unix , AIX 6.1

Re: caluculed in sas proc

Reeza — Sat, 16 Jan 2016 03:26:46 GMT

@LineMoon wrote:

How can I identify that ?

You just have.

What are you looking for, the source of the difference is likely numerical precision error. Due you have reasons to believe it might be otherwise? If so, please post your code and some sample data.

If you need examples of numerical precision the documentation has examples in addition to @PGStats example

Re: caluculed in sas proc

LineMoon — Sat, 16 Jan 2016 09:47:26 GMT

Thank you Reeza.

My codes is ==> (calculted x)*y as v

==> sum(v) as col

To reply to your question, which identification I am looking for ?

If i suppose the first rusult (r1) was done by machine A

and the second rusult (r2) was done by machine B

As r1 is different from r2. I want to identify the machine to see if the different caused by the machine system ?

Thank you

Re: caluculed in sas proc

PGStats — Sat, 16 Jan 2016 19:58:24 GMT

When two floating point calculation results are supposed to be the same but differ by a small amount it is usually assumed that the difference is due to rounding error. With IEEE 8 bytes floating point numbers, that small amount rule often translates into abs((r1-r2)/(r1+r2)) < 1e-12.

Re: caluculed in sas proc

LineMoon — Sun, 17 Jan 2016 01:11:27 GMT

That's great PGStats.

Thank for your exemple.

is it possible to have an exemple sum_v1 is different from sum_v2

like this :

proc sql;

sum(v) as sum_v1

from toto

group by y1,y2,y3;

quit;

proc sql;

sum(v) as sum_v2

from toto

group by y1,y2,y3;

quit;