topic Re: Why the sum value is not zero when it really is? in SAS Programming

Why the sum value is not zero when it really is?

huhuhu — Mon, 14 Mar 2022 21:18:41 GMT

Hi All,

I'm using PROC SQL to create the sum values for numeric variables, which contain both positive and negative values.

My raw table looks like below:

Group Date Var

A 2/2/2022 -1

A 2/2/2022 -3

A 2/2/2022 4

A 2/6/2022 5

A 2/6/2022 -3

A 2/6/2022 -2

I'm calculating the sum by group and date for var:

PROC SQL

SELECT DISTINCT GROUP,

DATE,

SUM(VAR) AS SUM

FROM RAW

GROUP BY GROUP, DATE;

QUIT;

So I know the sum is zero for each group on each date. However, my result shows a scientific notation like 8.881784E-16 for each sum, which is very close to zero but does not equal to zero. I tried to change to format of my numeric variable but it didn't work. Any suggestions?

Thank you so much!!

Re: Why the sum value is not zero when it really is?

PaigeMiller — Mon, 14 Mar 2022 21:29:05 GMT

Your numbers are not exactly integers. THere is some machine precision (also called machine epsilon) going on here that can't be avoided on binary computers. This is nothing to worry about, it is perfectly normal.

Re: Why the sum value is not zero when it really is?

Patrick — Mon, 14 Mar 2022 21:36:05 GMT

Most likely your data source comes from another platform like a data base.

Because of differences in how floating point data gets stored there can be some very small as soon as you're dealing with decimals. Here a start if you want to get into this topic.

The way to get around this is to round the values to non-significant digits. Below an example how you you could go about this.

data raw;
  input Group $ Date:ddmmyy10. Var;
  format date date9.;
  datalines;
A 2/2/2022 -1.00000000000001
A 2/2/2022 -3
A 2/2/2022 4
A 2/6/2022 5
A 2/6/2022 -3
A 2/6/2022 -2
;

PROC SQL;
  SELECT DISTINCT 
    GROUP,
    DATE,
    sum(var) as SUM,
    round(SUM(VAR),.000000000001) AS SUM2,
    SUM(round(VAR,.000000000001)) AS SUM3
  FROM RAW
  GROUP BY 
    GROUP, 
    DATE
    ;
QUIT;

Re: Why the sum value is not zero when it really is?

SASKiwi — Mon, 14 Mar 2022 21:41:49 GMT

SAS stores numbers in 8 bytes of storage, so it can hold up 15 digits accurately. So if your number is either longer than 15 digits, either as an integer or a decimal, it will not report accurately hence the tiny differences you are seeing beyond 15 digits. Using the ROUND function should remove tiny differences:

PROC SQL
SELECT DISTINCT GROUP,
              DATE,
              round(SUM(VAR), 1) AS SUM
FROM RAW
GROUP BY GROUP, DATE;
QUIT;

Re: Why the sum value is not zero when it really is?

ballardw — Mon, 14 Mar 2022 22:21:34 GMT

Numeric precision.

Well known issue though more predominate with decimal values. Some values just don't do will with the conversion to binary and so there is a loss of precision when the XX number of bytes used for numeric storage get full and truncate lower order digits.

You do not mention what format you attempted to apply but that would be one way such as F8. would round every result to no decimals at all.

Or use the ROUND function on the sum. Hard to say what the solution might be as we don't know what your actual need is.

Re: Why the sum value is not zero when it really is?

WarrenKuhfeld — Mon, 14 Mar 2022 23:57:42 GMT

https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/lrcon/p0ji1unv6thm0dn1gp4t01a1u0g6.htm

Re: Why the sum value is not zero when it really is?

PGStats — Tue, 15 Mar 2022 02:57:39 GMT

SAS has the FUZZ function for dealing with values that are close to integers (see the doc)

PROC SQL;
SELECT DISTINCT GROUP,
              DATE,
              SUM(FUZZ(VAR)) AS SUM
FROM RAW
GROUP BY GROUP, DATE;
QUIT;

Re: Why the sum value is not zero when it really is?

huhuhu — Tue, 15 Mar 2022 13:40:47 GMT

Thank you all for the recommendation! The round method works well. My numeric variables are with up to 6 decimals, which cause the problem.