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
difference between BEST32. and 14.2
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.
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. ???
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;
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.
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.
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.