Handling decimals numbers in SUM function

[Yuriy2301]

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?

[Hima]

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

[Yuriy2301]

what about this?

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.

[Hima]

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;

[Yuriy2301]

It gives 38, not 37.

[Hima]

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

total

ƒƒƒƒƒƒƒƒ

37

[Haikuo]

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.

for details, please refer: http://support.sas.com/techsup/technote/ts654.pdf

Regards,

Haikuo

[Ksharp]

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

[art297]

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;