turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- Base SAS Programming
- /
- Round function NOT WORKING

Topic Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

2 weeks ago - last edited 2 weeks ago

Hi everyone,

I'm using SAS version 9.3.

I'd like to ask why the number 0.1254931228765450 imported from an excel file cannot be rounded off to 0.12549312287655, but another number 0.8216087968183150 can be rounded off to 0.82160879681832?

My code is **X=ROUND(Y, 0.00000000000001); format X BEST32.;**

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to JohnChen_TW

2 weeks ago

The reason is the numerical precision of SAS number's is limited to a maximum of 15 digits and you are trying to round off at the extremity of the range SAS can handle. So some numbers may work and others may not.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to JohnChen_TW

2 weeks ago

You are on the limits of Excel also. Add 10 to that number, and then subtract 10, and then look at the result.

---------------------------------------------------------------------------------------------

Maxims of Maximally Efficient SAS Programmers

How to convert datasets to data steps

How to post code

Maxims of Maximally Efficient SAS Programmers

How to convert datasets to data steps

How to post code

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to KurtBremser

2 weeks ago

Thanks @KurtBremser

I tried it in excel to add 10 to both numbers and got 10.1254895654575450 and 10.8216087968183150, and then subtracted 10 to get 0.1254895654575450 and 0.8216087968183160.

However, their 15th decimal place is still greater than or equal to 5.

I still have no idea why there is the difference between the rounded results of them.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to JohnChen_TW

2 weeks ago

Well, the operation in Excel 2013 with the first number got me these:

0,12548956545754500000 | 10,12548956545750000000 | 0,12548956545754600 |

And the difference between the first and the third turns out to be this:

-0,00000000000000063838 |

Bottom line: don't expect reliable results beyond 15 decimal digits in any kind of software that uses 8-byte real.

And be aware that the peculiarities of handling decimal fractions in binary can cause some quite interesting effects (Note that the +10 number ends at 54575, and the re-calculated number at the end gets two decimal digits after that "back") anyway.

---------------------------------------------------------------------------------------------

Maxims of Maximally Efficient SAS Programmers

How to convert datasets to data steps

How to post code

Maxims of Maximally Efficient SAS Programmers

How to convert datasets to data steps

How to post code

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to KurtBremser

2 weeks ago - last edited 2 weeks ago

Thank you @KurtBremservery much!

However, could this test method be applied to any decimal number?

I've tried another number 0.1254931228765450 which also cannot be rounded to 0.12549312287655, and I got the same number after +/- 10.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to JohnChen_TW

2 weeks ago

Some numbers will work better because they convert to a finite binary sequence that fits into the 8-byte space. Others will turn into a periodic binary number that can't be converted back reliably.

Those that work are those that either don't need more binary digits or where the (binary) rounding doesn't change the last decimal digit.

---------------------------------------------------------------------------------------------

Maxims of Maximally Efficient SAS Programmers

How to convert datasets to data steps

How to post code

Maxims of Maximally Efficient SAS Programmers

How to convert datasets to data steps

How to post code

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to KurtBremser

2 weeks ago - last edited 2 weeks ago

Thank you, @KurtBremser and @SASKiwi for all your help and reference!

Although I have no any basis of binary arithmetic or decimal arithmetic, I will try to understand it at first.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to JohnChen_TW

2 weeks ago

@JohnChen_TW wrote:

I'm using SAS version 9.3.

These numeric representation issues are platform dependent. What is your operating environment?

In my Windows operating environment ("X64_7PRO platform", using SAS 9.4) I cannot replicate your results. Both numbers (x1, x2) are rounded correctly: r1, r2 are identical to the numbers you wanted to obtain (w1, w2).

```
data _null_;
x1=0.1254895654575450;
w1=0.12548956545755;
x2=0.8216087968183150;
w2=0.82160879681832;
r1=round(x1, 0.00000000000001);
r2=round(x2, 0.00000000000001);
put (r:) (=best32. /) /;
put (r1 w1 r2 w2) (=hex16. /);
run;
```

Result:

r1=0.12548956545755 r2=0.82160879681832 r1=3FC0100AC5D0911D w1=3FC0100AC5D0911D r2=3FEA4A9E880E1957 w2=3FEA4A9E880E1957

Please note that format BEST32. does *not* display numbers with their ultimate (internal) precision. Only formats HEX16. and BINARY64. tell you the absolute truth about the contents of a numeric variable.

For x1 the least significant bit of the internal representation (Windows environment) represents 2**-55 (approx. 2.78E-17). For x2 it represents 2**-53 (approx. 1.11E-16). So, in both cases this is still well below your rounding unit (1E-14).

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to FreelanceReinhard

2 weeks ago

@FreelanceReinhardThanks for your reply.

My operating environment is X32_7PRO. I've updated the decimal number I posted previously to be 0.1254931228765450, and I've tried the format HEX16. to get the result as below.

```
data _null_;
x1=0.1254931228765450;
w1=0.12549312287654;
w2=0.12549312287655;
x3=0.8216087968183150;
w3=0.82160879681832;
r1=round(x1, 0.00000000000001);
r3=round(x3, 0.00000000000001);
put (r:) (=best32. /) /;
put (r1 w1 w2 r3 w3) (=hex16. /);
run;
```

Result:

```
r1=0.12549312287654
r3=0.82160879681832
r1=3FC010289D5056CB
w1=3FC010289D5056CB
w2=3FC010289D505834
r3=3FEA4A9E880E1957
w3=3FEA4A9E880E1957
```

Does the difference between r1 and w2 mean that the number 0.1254931228765450 cannot be rounded to 0.12549312287655 because of the limits of SAS system (maximum of 15 digits)?

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to JohnChen_TW

2 weeks ago

@JohnChen_TW: Thanks for providing this interesting example. I think, in this case the relevant "limits" are primarily those of the internal workings of the ROUND function.

To quote from the technical paper "Dealing with Numeric Representation Error in SAS® Applications", p. 11:

"If your applications need more control over rounding than is provided for in the ROUND function, you must define your own rounding routine to be used instead of the SAS ROUND function." (Details on how to do this are then presented.)

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to FreelanceReinhard

2 weeks ago

@JohnChen_TW: I've just noticed that you ran into an additional problem: Numeric literals can be written in different ways, e.g. by using scientific notation or not or by appending trailing zeros to the decimals. Although these different notations represent the same value from a mathematical perspective, it can very well happen that SAS interprets these literals differently. That is, the internal representation of the number (visible in HEX16. format) varies.

Example:

```
data _null_;
if 0.00001>1.0E-5 & 1.00E-5<1.000E-5 then put '?!';
run;
```

In my Windows operating environment both "nonsense" inequalities are evaluated as true. This is due to two different internal representations. (A couple of years ago I found that the sequence 1.0E-5, 1.00E-5, 1.000E-5, ... switches 96 [!] times between *four* different internal representations, rather erratically.)

In fact, your latest example, 0.125493122876545, *would have* been rounded to 0.12549312287655, as desired, *if* you had entered it as 1.25493122876545e-1. Unfortunately, rounding would have failed again, had you entered it as 1.25493122876545**0**e-1.

A few variations of your example:

```
data _null_;
array x x4_ x40-x56
(0.12549312287654
0.125493122876540
0.125493122876541
0.125493122876542
0.125493122876543
0.125493122876544
0.125493122876545
0.125493122876546
0.125493122876547
0.125493122876548
0.125493122876549
0.125493122876550
0.125493122876551
0.125493122876552
0.125493122876553
0.125493122876554
0.125493122876555
0.125493122876556);
file print;
put (x:) (=hex16. /);
run;
data _null_;
array x x4_ x40-x56
(1.2549312287654e-1
1.25493122876540e-1
1.25493122876541e-1
1.25493122876542e-1
1.25493122876543e-1
1.25493122876544e-1
1.25493122876545e-1
1.25493122876546e-1
1.25493122876547e-1
1.25493122876548e-1
1.25493122876549e-1
1.25493122876550e-1
1.25493122876551e-1
1.25493122876552e-1
1.25493122876553e-1
1.25493122876554e-1
1.25493122876555e-1
1.25493122876556e-1);
file print;
put (x:) (=hex16. /);
run;
```

A comparison of the results shows that the values of x40-x47 (but *not* the values of x4_ and x48-x56) increase by one unit in the least significant bit (2**-55) just by switching to scientific notation. (Your example value is x45.)

Not surprisingly, this minor increase can impact rounding in borderline cases, i.e., when the number to be rounded is halfway between the two nearest multiples of the rounding unit, as is the case in your example.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to FreelanceReinhard

Monday

@FreelanceReinhardMany thanks for your input and examples.

Although it may be read in different ways by SAS when using scientific notation or appending trailing zero as you said, the number 1.25493122876545e-1 is still rounded off to 0.12549312287654 instead of 0.12549312287655.

JC

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to JohnChen_TW

2 weeks ago

I am also curious to know why you have a need for so many significant digits. Is this required for a particular use case? My point being is if you don't need as many significant digits in your application then you can ROUND reliably with less.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to SASKiwi

Monday

@SASKiwi Thanks for your input.

Yes, that is a particular use case that our vendor requested.

The decimal places of the measurement results are always 15 or 16, so that is why I'd like to know how to import them in SAS completely and accurately.