topic Re: format w.d and rounding? in SAS Procedures

format w.d and rounding?

Drez — Fri, 03 Oct 2014 14:27:07 GMT

Not sure why I get a different answer for these two statements. Nothing is greater than 14 characters wide. Thanks

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

proc sql;

create table x as

select sum(allow) as totallow, count(*) as rec_cnt

from MyData;

quit;

run;

totallow=1077566016.60

rec_cnt=14403667

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

proc sql;

create table x2 as

select sum(allow) as totallow FORMAT=14.2, count(*) as rec_cnt

from MyData;

quit;

run;

totallow=1077566016.59

rec_cnt=14403667

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

MyData has 14403667 Rows and 36 Columns

Re: format w.d and rounding?

Ksharp — Fri, 03 Oct 2014 14:55:38 GMT

difference between BEST32. and 14.2

Re: format w.d and rounding?

jakarman — Fri, 03 Oct 2014 15:58:11 GMT

The format difference is one explanation, easy to check with your resulting datasets.
Another possible rootcause could be the order at which the additions are made As you have a lot of observations this could be a summing of the precision error of each addition. Summing amounts that are almost equal will cause less loss of precision as summing ammounts that have a lot of difference.

eg adding 0.123456789012 to 12345678901,12 will lose a lot of the precision of you first amount. (remember 15 digits is the absolute maximum)

As the order of summing with SQL (multi-threaded) is undetermined you can get small differences on each run. This effect requires knowing your data.

Re: format w.d and rounding?

Drez — Fri, 03 Oct 2014 19:00:37 GMT

I looked at the distinct values. They are dollar values.. nothing has more than 2 decimal places

interestingly enough when I then run this:

Proc print data=x;

Format totallow 14.2;

Run;

what prints out is

totallow=1077566016.59

so SAS is keeping it correctly but displaying it wrong. ???

Re: format w.d and rounding?

data_null__ — Fri, 03 Oct 2014 19:41:38 GMT

The w.d formats do not always round as we might expect as shown in the example below. It is usually best to round to the desired precision and not leave it to w.d;

46   data _null_;
47       do x = 0-1e-16, round(x,.01);
48           put;
49           put x=;
50           put x=6.2; 
51           put x=bestx6.2; 
52           end;
53       run;


x=-1E-16
x=-0.00
x=-0.00

x=0
x=0.00
x=0.00

Re: format w.d and rounding?

jakarman — Sat, 04 Oct 2014 06:46:52 GMT

Having changed the format has proved the difference in rounding is in the formats.

As a pitty it is not well documented how rounding is done behind scenes.

data_null_ has given another example how rounding effects can give odd results. The - sign is a nice example.

To be added:

We are into the digital ages no not digital numbers but binary numbers. https://support.sas.com/techsup/technote/ts654.pdf

Your decimal fractions are not possible to be represented exactly in their binary counterparts. That will introduce some of precision differences as data_null_ coded manual. Having the summing done as integers on dollarcents would have avoided fractions.

The format Best. (that default) is said to adjust to most applicable resolution. That is all what is said about that.

SAS(R) 9.4 Formats and Informats: Reference More interesting is the SAS(R) 9.4 System Options: Reference, Third Edition decimalconv= system option. It refers to: IEEE Standard for Floating-Point Arithmetic 754-2008. SAS did not conform to that but can now.