Help using Base SAS procedures

format w.d and rounding?

Reply
New Contributor
Posts: 3

format w.d and rounding?

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

Super User
Posts: 9,676

Re: format w.d and rounding?

difference between BEST32.  and  14.2

Valued Guide
Posts: 3,208

Re: format w.d and rounding?

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. 

---->-- ja karman --<-----
New Contributor
Posts: 3

Re: format w.d and rounding?

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.  ???

Respected Advisor
Posts: 3,777

Re: format w.d and rounding?

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
Valued Guide
Posts: 3,208

Re: format w.d and rounding?

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.

---->-- ja karman --<-----
Ask a Question
Discussion stats
  • 5 replies
  • 523 views
  • 0 likes
  • 4 in conversation