Contributor
Posts: 26

# Handling decimals numbers in SUM function

Hello

have a set of numbers and sum in the end:

 0,0000000000 0,5000000000 4,3333333333 0,0000000000 0,0000000000 -0,5000000000 4,3333333333 0,0000000000 6,5000000000 0,0000000000 0,0000000000 0,0000000000 -4,5000000000 -18,0000000000 20,3333333333 0,0000000000 4,5000000000 19,0000000000 1,0000000000 37,4999999999

when I try to sum in sas, I receive result 37.5 instead of 37,4999999999

cause I need to round it.

so I should receive round(37,4999999999) = 37

but I received round(37.5) = 38

does anyone know how to handle decimal numbers in sum statement and also in proc report?

Regular Contributor
Posts: 233

## Handling decimals numbers in SUM function

data test;
input num;
datalines;
0.0000000000
0.5000000000
4.3333333333
0.0000000000
0.0000000000
-0.5000000000
4.3333333333
0.0000000000
6.5000000000
0.0000000000
0.0000000000
0.0000000000
-4.5000000000
-18.0000000000
20.3333333333
0.0000000000
4.5000000000
19.0000000000
1.0000000000
;
run;

proc print data = test;
run;

proc sql;
select round(sum(num)) as total from test;
quit;

Output:

total
ƒƒƒƒƒƒƒƒ
37

Contributor
Posts: 26

## Handling decimals numbers in SUM function

data one;

input d1 d2;

datalines;

1 2

13 3

-1 2

13 3

13 2

9 2

-18 1

61 3

-9 2

19 1

1 1

run;

proc sql;

create table two as

select d1/d2 as res, round(sum(calculated res)) as finrez

from one;

quit;

result equal 38, not 37.

I took same digits wthout zeros and define them through deviding another digits.

Regular Contributor
Posts: 233

## Handling decimals numbers in SUM function

data one;
input d1 d2;
datalines;
1 2
13 3
-1 2
13 3
13 2
9 2
-18 1
61 3
-9 2
19 1
1 1
;
run;

proc sql;
create table two as select d1/d2 as res from one;
quit;

proc sql;
select sum(round(res)) as total from two;
quit;

Contributor
Posts: 26

## Handling decimals numbers in SUM function

It gives 38, not 37.

Regular Contributor
Posts: 233

## Handling decimals numbers in SUM function

I am getting 37. Not sue how you are getting 38.

total

ƒƒƒƒƒƒƒƒ

37

Posts: 3,167

## Handling decimals numbers in SUM function

You are pushing to the limit of numeric precision that SAS can handle.

980  data _null_;

981  a1=37.4999999999;

982  a2=37.49999999999;

983  b1=round(a1);

984  b2=round(a2);

985  put b1 b2;

986  run;

37 38

This code was run on Winxp. Different system may have different precision.

Regards,

Haikuo

Super User
Posts: 10,784

## Handling decimals numbers in SUM function

37.5 is because SAS just show you , deep down inside it is actually 37.4999999 you need a format to display it.

Hima is right . I also got 37 when I use round(), or maybe you should try roundz() which will not fuzz.

```data test;
input num;
datalines;
0.0000000000
0.5000000000
4.3333333333
0.0000000000
0.0000000000
-0.5000000000
4.3333333333
0.0000000000
6.5000000000
0.0000000000
0.0000000000
0.0000000000
-4.5000000000
-18.0000000000
20.3333333333
0.0000000000
4.5000000000
19.0000000000
1.0000000000
;
run;

proc print data = test;
run;

proc sql;
select sum(num) as total format=best32.9 from test;
quit;

```

Ksharp

PROC Star
Posts: 8,164

## Handling decimals numbers in SUM function

Using the data the OP and routines the OP provided, neither the format, nor roundz will correct the problem.  It is simply a numeric precision issue that may not be avoidable.  Windows comes up with an answer of 38.

data one;

input d1 d2;

datalines;

1 2

13 3

-1 2

13 3

13 2

9 2

-18 1

61 3

-9 2

19 1

1 1

run;

proc sql;

create table two as

select d1/d2 as res, roundz(sum(calculated res))

format=best32.9 as finrez

from one;

quit;

Discussion stats
• 8 replies
• 2695 views
• 0 likes
• 5 in conversation