BookmarkSubscribeRSS Feed
Yuriy2301
Calcite | Level 5

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?


8 REPLIES 8
Hima
Obsidian | Level 7

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
Calcite | Level 5

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
Obsidian | Level 7

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
Calcite | Level 5

It gives 38, not 37.

Hima
Obsidian | Level 7

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

total

ƒƒƒƒƒƒƒƒ

37

Haikuo
Onyx | Level 15

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
Super User

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
Opal | Level 21

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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