topic Re: proc sort - SUM function on group by returns different results depending on sort order in SAS Procedures

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

quintonsmit_gmail_com — Wed, 10 Jun 2015 05:49:59 GMT

I am hoping somebody can explain why - and how to work around - the following.

This comes from sales data - essentially a few sales and then all of the items were returned for a refund - which is why I have positive and the same negative values.

The expected net result of all of the transactions is zero.

Run the code as is and then remove the comments on the SORT procedure and the proc sql group by will produce 2 different results

either:

num=.0000000000000568434188608080000

num=.0000000000000000000000000000000

if num >0 then a sale is counted if num=0 then no sale

So depending on the sort order of the input data set I can either have a sale counted or not - this is obviously inaccurate!!!

I know I can round and format to get a value closer to zero - that's not the question - at the office there are many examples of this type of coding and not enough time to go and recode all of the analytic code around the enterprise.

Preferably somebody has come across this and there is a SAS Option I can use or a hotfix that can be installed.

currently tested on:

aix 6.1 with SAS 9.2

and

aix 7.1 with SAS 9.4

data work.Input_Data;

category ='a';

length num 8.;

input num;

cards;

299

-299

-283.8

299

283.8

-299

283.8

-283.8

-218.31

218.31

-218.31

;

run;

* UNCOMMENT THIS SORT BELOW TO SEE CORRECT RESULTS IN WORK.OUTPUT1 *;

/*proc sort data=work.Input_Data; by num; quit;*/

proc sql;

* this (group by) sometimes produces incorrect results *;

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

group by category;

* this works *;

create table WORK.output2 as select sum(num) as num from work.Input_Data;

quit;

* this works *;

proc summary data=work.Input_Data nway n;

class category;

var num;

output out=WORK.output3 sum=;

quit;

data _null_; set WORK.output1;

* num should be equal to zero - strangely depends on the the order of rows in the work.Input_Data above *;

format num tst 32.31;

tst=round(num,0.001);

put num=;

if num >0 then put "Greater";

if num =0 then put "Equal";

if num <0 then put "Less";

put tst=;

if tst >0 then put "Greater";

if tst =0 then put "Equal";

if tst <0 then put "Less";

run;

data _null_; set WORK.output2;

* num equal to zero *;

format num 32.31;

put num=;

if num >0 then put "Greater";

if num =0 then put "Equal";

if num <0 then put "Less";

run;

data _null_; set WORK.output3;

* num equal to zero *;

format num 32.31;

put num=;

if num >0 then put "Greater";

if num =0 then put "Equal";

if num <0 then put "Less";

run;

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

jakarman — Wed, 10 Jun 2015 06:45:17 GMT

Did you read all those things about numeric orecision?

As soon as you are going into not whole numbers or having more than 12 digits you are in the area that 1 might not be equal to 1 due to that precision. Remember the slider.

It is not a SAS problem it is numeric binary global one.

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

quintonsmit_gmail_com — Wed, 10 Jun 2015 06:54:33 GMT

Thanks Jaap - I am aware of what you are discussing but that is not the question.

This is not about precision or scale.

My question:

Why does the sort order of the records in the input data set return different total values when summed in the proc sql - when using the SAME individual record & values?

Which indicates a SAS proc sql bug.

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

quintonsmit_gmail_com — Wed, 10 Jun 2015 06:57:08 GMT

Also - look at the example code.

The CORRECT result is returned with a PROC SUMMARY (implies the data gets sorted) where as the PROC SQL .. SUM ... GROUP BY returns a different result to the proc summary on the SAME records when not sorted - but the correct result when the input data set is sorted.

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

Kurt_Bremser — Wed, 10 Jun 2015 07:54:31 GMT

#327 on my constantly growing list of reasons why to avoid PROC SQL.

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

quintonsmit_gmail_com — Wed, 10 Jun 2015 08:18:16 GMT

Unfortunately - SAS DI studio produces a lot of proc sql code - so how reliable are the results?

must I go to 20 plus analysts and senior management to explain that SAS proc sql is not 100% accurate - that the scoring models used for profitability are most likely producing inaccurate results.

I.E:

Depending on the record SORT ORDER - a customer might be scored as profitable?

However the same customer with the same records (sales behavior) - but using a different SORT ORDER will NOT be scored profitable?

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

Kurt_Bremser — Wed, 10 Jun 2015 08:26:52 GMT

Well, the inaccuracies are very small.

Note that when working with data in "real" storage format, it is prudent to round before doing a = comparison, as you may always have artifacts from previous computations.

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

quintonsmit_gmail_com — Wed, 10 Jun 2015 08:41:38 GMT

well - for statistics - any inaccuracies is unacceptable

0 (zero) is either 0 (zero) or it is greater than or less than 0 (zero)

My challenge is that there are perhaps a hundred different SAS programs dealing with more than 5 SAS data sets each and each SAS data set has many hundreds of variables.

So going back to recode (round) each of those variables in all of that code is a huge undertaking - and impracticable right now.

I am in the process of migrating from SAS 9.2 to a new SAS 9.4 environment and have to compare the 2 environments - so this might need to be done in both environments to get equal results.

Which goes back to the original question(s)

- is there a SAS option that can be used to force a SAS data set to be sorted before a proc sql (since this seems to solve the issue but I don't know of such an option)

- is there a SAS option that globally will "round" numeric variables - i.e. evaluate using the first 3 decimals only?

- why does proc summary produce the expected "correct" result but the proc sql does not?

I am just trying to ensure that there is no quick fix solution before going back to the client and informing them that their current code is inaccurate.

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

SteveDenham — Wed, 10 Jun 2015 12:04:55 GMT

This goes back to 's statement about it being a numeric binary problem--sum is a sequential operation, and when you exceed the numerical precision, you start accumulating rounding "errors". It seems logical, at least to me, that the sort order is going to affect this dramatically. The difference comes into the sum at different points in the sequence, and propagates. i see this happen a fair amount in iterative statistical procedures (maximum likelihood estimation, especially), so when duplicating analyses on separate machines, it is critical to have the sort order prior to analysis identical on the two machines. At least that way, the propagated errors should be identical.

Can you shift everything to integer arithmetic, and not overflow? That seems to be the most straightforward way. The other is to compare using a fuzzy logic that accounts for accumulated error.

Steve Denham

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

data_null__ — Wed, 10 Jun 2015 12:32:31 GMT

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

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

quintonsmit_gmail_com — Wed, 10 Jun 2015 12:37:22 GMT

Thanks Steven -

if you look at the test data - a SAS numeric variable of length 8 should be able to store these test values - without accumulating rounding "errors"

299

-299

-283.8

299

283.8

-299

283.8

-283.8

-218.31

218.31

-218.31

It also doesn't seem to explain why in this test case the GROUP BY function - in this test case only 1 group - produces different totals with the same records.

I would expect the same "accumulating rounding errors" to occur since the single group total is the same as the 'grand' total.

With testing (changing the order of records) I can produce different 'errors' (value differences) - so I agree it has something to do with the numeric binary problem - I don't see why it should be occurring in this test case though.

SAS length 8 numeric variable should be able to store these values - without rounding concerns.

Many thanks for all the replies

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

quintonsmit_gmail_com — Wed, 10 Jun 2015 12:42:40 GMT

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

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

SteveDenham — Wed, 10 Jun 2015 12:44:32 GMT

None of those values will translate exactly in hexadecimal form. The 8 byte format has nothing to do with the physical length of the string--that only applies to character variables (as I found out the other day when I tried to format a numeric variable as 2.1). So order and representation of decimals as hexadecimals are having an effect. Data_null's solution seems the easiest to me.

Steve Denham

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

quintonsmit_gmail_com — Wed, 10 Jun 2015 12:54:17 GMT

Hi Steven

taken from - SAS(R) 9.2 Companion for UNIX Environments

====

Numeric Variable Length and Precision in UNIX Environments

The default length of numeric variables in SAS data sets is 8 bytes.

The issue of numeric precision affects the return values of almost all SAS math functions and many numeric values returned from SAS procedures. Numeric values in SAS for UNIX are represented as IEEE double-precision floating-point numbers. The decimal precision of a full 8-byte number is effectively 15 decimal digits.

=====

So - once again - All of those test numbers should easily fit into a length 8 numeric SAS variable that has precision to 15 decimal digits.

Just to clarify - I am using a length 8 numeric variable - the HIGHEST precision that SAS offers.

I am not formatting the numeric variable and getting 'error's or mismatches.

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

Kurt_Bremser — Wed, 10 Jun 2015 13:55:54 GMT

It is not the VARIABLES, it is the OPERATIONS that are most affected in this case.I guess you could even work with 256 bit variables, as soon as you have fractions that do not easily translate into binary, you will end up with non-zero digits at the end of the mantissa, which will cause problems after mathematical operations. Sometimes those cancel each other out, thanks to the sequence, sometimes not.

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

quintonsmit_gmail_com — Wed, 10 Jun 2015 14:48:59 GMT

Hi Kurt - thanks for the reply.

I am starting to understand this a bit more:

The IEEE 754 standard is a method of storing floating-point numbers in a compact way that is easy to manipulate. This standard is used by Intel coprocessors and most PC-based programs that implement floating-point math.

IEEE 754 specifies that numbers be stored in binary format to reduce storage requirements and allow the built-in binary arithmetic instructions that are available on all microprocessors to process the data in a relatively rapid fashion. However, some numbers that are simple, nonrepeating decimal numbers are converted into repeating binary numbers that cannot be stored with perfect accuracy.

For example, the number 1/10 can be represented in a decimal number system with a simple decimal:

However, the same number in binary format becomes the repeating binary decimal:

.0001100011000111000111 (and so on)

This number cannot be represented in a finite amount of space. Therefore, this number is rounded down by approximately -2.78E-17 when it is stored.

If several arithmetic operations are performed to obtain a given result, these rounding errors may be cumulative.

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

jakarman — Wed, 10 Jun 2015 16:05:33 GMT

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.

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

Kurt_Bremser — Thu, 11 Jun 2015 05:24:19 GMT

THIS is exactly what happens. The only way out of this is by adding additional numeric storage formats. Like packed decimal or integer, where the programmer has to provide logic for fractions.

SAS obviously does not want to do that, for reasons of keeping everything simple.

Everytime you have the need to make comparisons with exact discrete values, have to deal with fractions, and need to do computations first, it is very wise to use the round() function on the results of the computations.

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

jakarman — Thu, 11 Jun 2015 05:58:43 GMT

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.

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

quintonsmit_gmail_com — Thu, 11 Jun 2015 07:09:05 GMT

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

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