topic Handling decimals numbers in SUM function in SAS Procedures

Handling decimals numbers in SUM function

Yuriy2301 — Wed, 22 Feb 2012 13:24:42 GMT

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?

Handling decimals numbers in SUM function

Hima — Wed, 22 Feb 2012 13:46:59 GMT

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

Handling decimals numbers in SUM function

Yuriy2301 — Wed, 22 Feb 2012 14:20:41 GMT

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.

Handling decimals numbers in SUM function

Hima — Wed, 22 Feb 2012 14:24:39 GMT

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;

Handling decimals numbers in SUM function

Yuriy2301 — Wed, 22 Feb 2012 15:10:09 GMT

It gives 38, not 37.

Handling decimals numbers in SUM function

Hima — Wed, 22 Feb 2012 15:59:40 GMT

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

total

ƒƒƒƒƒƒƒƒ

Handling decimals numbers in SUM function

Haikuo — Wed, 22 Feb 2012 16:01:53 GMT

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

Handling decimals numbers in SUM function

Ksharp — Thu, 23 Feb 2012 02:59:50 GMT

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

Handling decimals numbers in SUM function

art297 — Thu, 23 Feb 2012 03:45:56 GMT

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;