topic Re: Rounding error in Proc Tabulate in SAS Programming

Rounding error in Proc Tabulate

Holger — Wed, 08 Feb 2023 11:16:51 GMT

Dear all,

I am facing a problem with Proc Tabulate.

I use 9.4 (TS1M7) on the X64_10PRO platform.

In the SAS datatable that goes into Proc Tabulate there is a value of 2.735. With a format being 8.2 the result in the table generated by the procedure is 2.73 instead of 2.74 which would be expected due to correct rounding.

The procedure looks like this:

proc tabulate data=Mean_and_SD_all;
class CAT transy methody Dose;
var value;
table CAT=''*(transy=''*Dose=''),methody=''*value=''*f=&decimal_val;
format transy tr. methody mt.;
run;

&decimal_val defines a value of 8.2

The rounding error occurs for "value" where format ist defined as 8.2.

I already tried to add "options cmplib=work.functions;" without success.

Does anybody have an idea how to solve the problem of false rounding?

Best regards

Holger

Re: Rounding error in Proc Tabulate

Holger — Wed, 08 Feb 2023 15:03:23 GMT

Just to add some information regarding my problem:: the wrong rounding does not appear for all numbers but only for single data points - and independ of time the program run and the computer I use for calculation regularly for the same data point while for other data points rounding works perfectly.

Best regards

Holger

Re: Rounding error in Proc Tabulate

PaigeMiller — Wed, 08 Feb 2023 15:13:40 GMT

@Holger wrote:

Dear all,

I am facing a problem with Proc Tabulate.

I use 9.4 (TS1M7) on the X64_10PRO platform.

In the SAS datatable that goes into Proc Tabulate there is a value of 2.735. With a format being 8.2 the result in the table generated by the procedure is 2.73 instead of 2.74 which would be expected due to correct rounding.

The procedure looks like this:

proc tabulate data=Mean_and_SD_all;
class CAT transy methody Dose;
var value;
table CAT=''*(transy=''*Dose=''),methody=''*value=''*f=&decimal_val;
format transy tr. methody mt.;
run;

&decimal_val defines a value of 8.2

The rounding error occurs for "value" where format ist defined as 8.2.

I already tried to add "options cmplib=work.functions;" without success.

Does anybody have an idea how to solve the problem of false rounding?

Best regards

Holger

It may not be false rounding. It may be that the underlying value is 2.7349, so if you display it with three decimal places, you get 2.735 while if you display it with two decimal places, it is 2.73.

So first step would be to determine the underlying value of this variable. You can do this by assigning the format best32. to this variable and seeing what the underlying value is. Then, assuming the value is really 2.7349, you have to decide what you really really really want this to appear as. Once you let us know what you want, we can provide further advice and provide code.

There is no global solution to eliminating round-off discrepancies, but there may be a solution if only we knew what the real number was and what you want to do with it.

Re: Rounding error in Proc Tabulate

ballardw — Wed, 08 Feb 2023 16:03:10 GMT

When discussing the displayed value of any a variable you should always mention the format you are looking at.

I suspect that your 2.735 value is displayed with either a BEST5. or maybe something like 8.3. So has already been rounded to that third decimal place.

I agree with @PaigeMiller that your underlying value is not actually 2.735 but something that rounds to that when forced to display 3 decimals.

Re: Rounding error in Proc Tabulate

FreelanceReinh — Wed, 08 Feb 2023 19:03:23 GMT

Hello @Holger,

The name "Mean_and_SD_all" of your analysis dataset suggests that it contains descriptive statistics computed in an earlier step. In this case, even values in the dataset which would exactly equal 2.735 in a hand calculation can differ from this value by a tiny rounding error incurred during the machine calculation in the binary system. This would be a special case of what PaigeMiller and ballardw have described.

Example (created with SAS 9.4M5 under Windows):

data test;
input dose value;
cards;
1  2.73
1  2.74
2 -3.40
2  8.87
3  0.70
3  1.40
3  0.30
3  8.54
4  0.70
4  0.30
4  1.40
4  8.54
;

proc summary data=test;
by dose;
var value;
output out=stats mean=;
run;

data _null_;
set stats;
put value= value 8.2 value best32. +2 value hex16.;
run;

Result in the log:

value=2.735     2.74                           2.735  4005E147AE147AE2
value=2.735     2.73                2.73499999999999  4005E147AE147AE0
value=2.735     2.73                2.73499999999999  4005E147AE147AE0
value=2.735     2.74                           2.735  4005E147AE147AE1

The mathematically exact mean value of variable VALUE in each of the four "dose groups" is 2.735 and this is what the default (BEST12.) format used in the first column ("value=2.735") seems to confirm. However, the 8.2 format in the second column shows that something is "wrong" with dose groups 2 and 3, which is clarified by the BEST32. format in the third column. Yet, it's only the HEX16. format in the rightmost column which reveals that also dose group 1 has a problem: The last hexadecimal digit 2 resulted from incorrectly rounding up 147AE147AE147AE... So, the equation (2.73+2.74)/2 = 2.735 is false (!) in SAS (9.4M5 under Windows).

The fact that dose groups 3 and 4 contain exactly the same values -- just in a slightly different order -- demonstrates how easily this type of rounding error can occur.

As a remedy you could try and "clean" the values using the ROUND function with a suitable rounding unit:

data want;
set stats;
value=round(value,1e-10);
run;

Re: Rounding error in Proc Tabulate

Holger — Fri, 10 Feb 2023 08:37:32 GMT

Thank you very much for your helpful information.

I tried to run your code and received the exactly same results. Thus, I know my installation is OK.

I trusted the automatic rounding of proc tabulate by defining the output format. Most of the times it works well and delivers the expected results. Now, I know that I should better use the round function before.

Best regards

Holger