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
- /
- BI
- /
- Enterprise Guide
- /
- Problem with the round function! Please help

Topic Options

- Subscribe to 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
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

12-08-2008 10:32 AM

I tried the following code:

data testo; orig=0.54999999999999; output; Run;

Data testo; Set testo; rnd= round(orig,0.1); Run;

Could you please explain me what happened?? It should be rounded to 0.5 and not 0.6!!

Thanks for your help,

Regards,

data testo; orig=0.54999999999999; output; Run;

Data testo; Set testo; rnd= round(orig,0.1); Run;

Could you please explain me what happened?? It should be rounded to 0.5 and not 0.6!!

Thanks for your help,

Regards,

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

12-08-2008 11:54 AM

Hi:

You might look at the documentation on ROUND, the ROUNDE and the ROUNDZ functions...they operate a bit differently when rounding. According to the doc they operate this way:

"Computing Results When the Value Is Halfway between Multiples of the Rounding Unit

When the value to be rounded is approximately halfway between two multiples of the rounding unit, the ROUND function rounds up the absolute value and restores the original sign.

<<skip long program example>>

The ROUND function is the same as the ROUNDE function except that when the first argument is halfway between the two nearest multiples of the second argument, ROUNDE returns an even multiple. ROUND returns the multiple with the larger absolute value.

The ROUNDZ function returns a multiple of the rounding unit without trying to make the result match the result that is computed with decimal arithmetic."

You might experiment and see whether ROUNDZ gives you the kind of rounding your want.

cynthia

You might look at the documentation on ROUND, the ROUNDE and the ROUNDZ functions...they operate a bit differently when rounding. According to the doc they operate this way:

"Computing Results When the Value Is Halfway between Multiples of the Rounding Unit

When the value to be rounded is approximately halfway between two multiples of the rounding unit, the ROUND function rounds up the absolute value and restores the original sign.

<<skip long program example>>

The ROUND function is the same as the ROUNDE function except that when the first argument is halfway between the two nearest multiples of the second argument, ROUNDE returns an even multiple. ROUND returns the multiple with the larger absolute value.

The ROUNDZ function returns a multiple of the rounding unit without trying to make the result match the result that is computed with decimal arithmetic."

You might experiment and see whether ROUNDZ gives you the kind of rounding your want.

cynthia

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

12-09-2008 09:25 AM

Thank you very much for your great help!

Best regards,

Best regards,

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

06-13-2012 10:28 AM

Hi Cynthia,

I tried ROUND, ROUNDE, and ROUNDZ functions and my results still do not turn out correctly. I welcome any suggestion(s) you may have for me.

Thanks,

Hui

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

06-13-2012 10:58 AM

Hui,

When I did roundz, I got the answer Cynthia described. (SAS 9.3 on Windows). Maybe you need to show your log.

Doc Muhlbaier

Duke

data testo; orig=0.54999999999999; output; Run;

Data testo; Set testo;

rnd= round(orig,0.1);

rnde= rounde(orig,0.1);

rndz= roundz(orig,0.1);

FILE LOG; PUT orig rnd rnde rndz;

Run;

---------

16 Data testo; Set testo;

17 rnd= round(orig,0.1);

18 rnde= rounde(orig,0.1);

19 rndz= roundz(orig,0.1);

20 FILE LOG; PUT orig rnd rnde rndz;

21 Run;

0.55 0.6 0.6 0.5

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

06-13-2012 12:32 PM

Thanks Dr. Muhlbaier. Those functions work on single numbers but not when multiplication/division is involved. Do you happen to know the equivalent SAS syntax/functon for ROUND(+B50/J50,0)) in MS Excel? I assume that the plus(+) sign indicate a positive number and not a ceiling function but I could be wrong. Below is part of my syntax:

result(i)= round(numerator(i)/denominator(i));

or should I use result(i)= round((ceiling(numerator(i)))/(denominator(i))) instead?

I wonder if there is anything missing in my code . . .

Thanks again,

Hui

PS: I have to convert various Excel pivot tables into SAS datasets and my results differ.

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

06-13-2012 12:45 PM

Unfortunately, I don't know enough about Excel to help.

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

06-13-2012 04:43 PM

AFAIK and from a very quick test in Excel the + doesn't do anything.

I've seen people use it as a shortcut instead of typing equals because then excel treats it as a number/formula, and then the round was possibly introduced later on and then doesn't make sense.

Or apparently and old habit from a Lotus user http://www.excelbanter.com/showthread.php?t=178349

From a quick look at how the function works my guess would be you're looking for

round(numerator(i)/denominator(i), 1);

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

06-18-2012 04:12 PM

Thanks alot Reeza. Regardless of how I write the ROUND function, I find that rounding seems to 'operate' differently in SAS and Excel. For instance 46.54421 is rounded to 47 in Excel and 46 in SAS and 46.4645 is rounded to 46 in Excel and 47 in SAS. I will check the link out.

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

06-18-2012 04:20 PM

No it doesn't...46.544 rounds to 47 and 46.4645 rounds to 46 as expected in both SAS 9.2 and 9.3

I couldn't find a consistent way to get your results either, so you may want to post the code and the numbers you're trying to replicate.

2119 data _null_;

2120 x= round(46.54421, 1);

2121 y= round(46.4645, 1);

2122

2123 put x y;

2124 run;

47 46

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

06-18-2012 04:28 PM

Not when I do divisions. 46.54421 and 46.4645 are elements of result(i).

result(i)= round(numerator(i)/denominator(i))

Thanks, Reeza.

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

06-18-2012 04:50 PM

What are numerator(i) and denominator(i)? And what format is applied to the numbers

I still can't get those numbers you're getting, so there's something missing somewhere:

data test;

x= 46.54;

y= 46.46;

do i =1 to 5;

numerator1=4654*i;

numerator2=4646*i;

denominator=i*100;

unround1=numerator1/denominator;

unround2=numerator2/denominator;

round1=round(numerator1/denominator);

round2=round(numerator2/denominator);

output;

end;

run;

proc print data=test;

run;

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

06-19-2012 01:45 PM

demoninator = 49.2333333333333333

numerator = 1.057775751

Thanks again, Reeza

1.057775751 |

1.057775751 |

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

06-19-2012 02:33 PM

It works fine for me still, rounds to 47 as expected, there's something else in code that's causing this issue, not the round function, provide the full code for the data step.

183 data _null_;

184 x= round(49.23333333/1.0577775751, 1);

185 x2=49.233333/1.0577775751;

186 put x x2;

187 run;

47 46.54412625

NOTE: DATA statement used (Total process time):

real time 0.00 seconds

cpu time