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?
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
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.
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;
It gives 38, not 37.
I am getting 37. Not sue how you are getting 38.
total
ƒƒƒƒƒƒƒƒ
37
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
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
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.