proc sort - SUM function on group by returns different results depending on sort order

[data_null__]

You know the precision of dollars is 2 decimal place so why not just round.

create table WORK.output1 as select category, round(sum(num),.01) as num from work.Input_Data

[quintonsmit_gmail_com]

thanks for the reply - I know about the syntax to round and it is the response that I will suggest to the team.

Unfortunately there are 1000's of lines of production code by other developers/analytical teams that will have to be manually checked and corrected.

I was looking for an global option that would alleviate the need to manually check those 1000's of lines of production code over hundreds of numeric variables since any change to the production code will have to go through a review process to confirm results and it could be weeks before the manually corrected code is again implemented into production.

regards

Quinton

[jakarman]

Thanks Steve, the numbers are getting into a binary way not decimal digits. You are getting the same isuues as the accuracy of 1/3 and than adding it.

The real maths will get some fractions to miss the real value as a result 0《》0 . There are many notes on that fuzz factor and more. The first calculators where als showing that. As people got confused they made an automatic rounding to the result.

Now people get confused on binary arithmetic. As the rounding got standardized that rounding could become introduced in SAS. I have seen some notes about that.

[jakarman]

The floating numbers are also named real numbers in mathematics because that is what is in the real world with measurements is happening. As the goal is statistics and modeling (predictive and uncertaintity) that makes perfectly sense to use. Super computers are many times evaluated by Tflops not mips. That automatic rounding as standard will help to hide some things ieee 754 Wikipedia is a good start.

Only do in additions and subtraction you could use paced zoned with the ds2 language. Only with external dbms systems that will also stored that as it is not continued in Sas datasets. That is a difference of the goal of world's analytics real type numbers. Not limited by whole numbers or decimal representation.

[quintonsmit_gmail_com]

Thanks to everybody that contributed - I learned something new which I will not forget soon.

/*

USING FLOATING-POINT (I.E., REAL BINARY) REPRESENTATION

THERE HAPPENS TO BE NO EXACT BINARY REPRESENTATION FOR THE DECIMAL VALUES OF 0.1 AND 0.3

3 EXAMPLES BELOW

references:

http://analytics.ncsu.edu/sesug/2008/PO-082.pdf

SAS(R) 9.4 Language Reference: Concepts, Fourth Edition

31560 - Choosing your degree of numeric precision

*/

==================================================================

** EXAMPLE USING 0.1 - THIS IS NOT ACCURATE **;

data _null_;

  x=10.1;

  put "ORIGINAL VALUE:";

  put x= 32.30;

  put x= hex16.;

  y=10;

  newx=(x+y)-y;

  put / "ORIGINAL VALUE AFTER ADDING AND SUBTRACTING 10:";

  put newx= 32.30;

  put newx= hex16.;

  put / "DIFFERENCE AFTER ADDING AND SUBTRACTING 10:";

  diff=x-newx;

  put diff= 32.30;

  put diff= hex16.;

run;

LOG OUTPUT:

ORIGINAL VALUE:

x=10.10000000000000000000000000000

x=4024333333333333

ORIGINAL VALUE AFTER ADDING AND SUBTRACTING 10:

newx=10.10000000000000000000000000000

newx=4024333333333334

DIFFERENCE AFTER ADDING AND SUBTRACTING 10:

diff=-.000000000000001776356839400250

diff=BCE0000000000000

==================================================================

** EXAMPLE USING 0.3 - THIS IS NOT ACCURATE **;

data _null_;

format ExactVar BinaryVar hex16.;

* LOGICALLY THESE ARE THE SAME VALUE *;

ExactVar=0.3;

BinaryVar=3*0.1;

Diff=ExactVar-BinaryVar;

* BUT THEY ARE STORED DIFFERENTLY IN HEX/BINARY *;

put ExactVar=  BinaryVar= Diff= 32.30;

run;

LOG OUTPUT:

ExactVar=3FD3333333333333 BinaryVar=3FD3333333333334 Diff=-.000000000000000055511151231258

==================================================================

** EXAMPLE USING 0.2 - THIS IS ACCURATE **;

data _null_;

format ExactVar BinaryVar hex16.;

* LOGICALLY THESE ARE THE SAME VALUE *;

ExactVar=0.2;

BinaryVar=2*0.1;

Diff=ExactVar-BinaryVar;

* BUT THEY ARE STORED DIFFERENTLY IN HEX/BINARY *;

put ExactVar=  BinaryVar= Diff= 32.30;

run;

LOG OUTPUT:

ExactVar=3FC999999999999A BinaryVar=3FC999999999999A Diff=0.000000000000000000000000000000

==================================================================