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
- /
- SAS Procedures
- /
- format w.d and rounding?

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

10-03-2014 10:27 AM

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

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

10-03-2014 10:55 AM

difference between BEST32. and 14.2

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

10-03-2014 11:58 AM

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

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

10-03-2014 03:00 PM

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

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

10-03-2014 03:41 PM

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;

x=-

x=-

x=-

x=

x=

x=

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

10-04-2014 02:46 AM

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