BookmarkSubscribeRSS Feed
Drez
Calcite | Level 5

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

5 REPLIES 5
Ksharp
Super User

difference between BEST32.  and  14.2

jakarman
Barite | Level 11

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 --<-----
Drez
Calcite | Level 5

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

data_null__
Jade | Level 19

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
jakarman
Barite | Level 11

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 4532 views
  • 0 likes
  • 4 in conversation