Here is how the data looks, its a dollar amount. I have a mix of other numbers too, so that should not be affected. Any suggestions on how to round off to the nearest penny?
-4.54747E-13
-2.27374E-13
-1.13687E-13
-5.68434E-14
-8.88178E-16
-4.44089E-16
-1.11022E-16
-5.55112E-17
0
2.775558E-17
1.665335E-16
2.220446E-16
4.440892E-16
8.881784E-16
1.332268E-15
4.440892E-15
6.827872E-15
7.105427E-15
Looks like a numeric precision issue. Use the ROUND function to remove these tiny amounts.
data _null_;
MyVar = -4.54747E-13;
MyVarFixed = round(MyVar,0.01);
put _all_;
run;
I would keep a higher precision than two decimals so you still get "correct" results when using your data for calculations.
data have;
MyVar = -4.54747E-13;
MyVarFixed = round(MyVar,1E-12);
put _all_;
run;
Normally when it's a precision issue then it only affects the last decimal of full precision. Are the numbers your using already the product of some calculations done with variables that have such a "precision issue"? If so then round the values as early in the process as you can.
If it's only about printing the values then consider using a format that rounds to two decimals but keep the full precision as internal value for further calculations.
@Patrick wrote:
I would keep a higher precision than two decimals so you still get "correct" results when using your data for calculations.
Not sure I agree when the values are actually dollar (and cents) amounts, as mentioned by the OP.
If there are specific financial transactions or calculations being emulated in the program, it might be closest to real-world operations to round a calculation. For instance, if you want a year of compounded monthly interest, you likely will get a more accurate prediction by rounding-to-the-penny a series of twelve calculations, rather than only rounding the 12-month total. Even then you might not match the real-world procedure of the financial record keeper. For example, do they always round up an exact half-penny value (see the ROUNDE function).
You need to tell use exactly what units those values are in. The largest value is 0.0000000000000071
So if that is supposed to be dollars and cents then everything rounds to 0. Plus most of the values are in the range where precision of data storage is going to impact the "actual" values.
I suspect that you may have a logic error somewhere in what ever you did to create these values as there isn't really any way any of this makes sense in a "dollars and cents" context.
Hello @akulkarni0320 and welcome to the SAS Support Communities!
@akulkarni0320 wrote:
Here is how the data looks, its a dollar amount. I have a mix of other numbers too, so that should not be affected. Any suggestions on how to round off to the nearest penny?
-4.54747E-13
-2.27374E-13
...
7.105427E-15
Seeing these values, I'm almost sure that rounding errors of similar orders of magnitude affect your "mix of other numbers," too. Those other numbers may look like usual dollar amounts (with up to two decimals) in most display formats, but in fact some of them differ from the correct values by, e.g., -4.54747E-13. So you should "clean" those as well using the ROUND function (as others have already suggested) to avoid problems later on.
Choose a rounding unit (second argument of the ROUND function) that is small enough not to alter significant digits, hence <=0.01, but large enough to correct those tiny rounding errors, hence >=1E-12. You could compute the difference between the original value, say, X and the rounded value ROUND(X, 0.01) and then analyze the differences with a quick PROC MEANS step: If the minimum and maximum differences have absolute values, e.g., less than 1E-9, then 0.01 was a suitable rounding unit. If, however, differences like -0.001 or 0.005 occur, then you should use a smaller rounding unit.
The tiny rounding errors typically result from calculations involving numbers with one or more decimals. Using Windows SAS 9.4, the example below reproduces your number list (see variable X in the PROC PRINT output):
data have;
input a b c d;
x=a+b-c-d;
cards;
1337.54 734.53 664.36 1407.71
768.06 5.43 208.55 564.94
135.67 196.89 37.09 295.47
71.71 127.07 19.17 179.61
2.34 1.69 2.20 1.83
1.16 1.94 1.73 1.37
1.77 1.29 2.24 0.82
1.58 1.35 2.74 0.19
1.17 1.29 1.15 1.31
2.99 2.19 5.39 -0.21
1.05 1.05 2.42 -0.32
1.15 1.59 1.80 0.94
1.68 1.06 2.03 0.71
1.98 2.49 1.96 2.51
4.94 2.52 4.68 2.78
19.42 2.10 19.47 2.05
53.31 17.93 70.81 0.43
12.56 13.46 8.94 17.08
13.56 13.46 8.94 17.08
;
data want;
set have;
r=round(x, 1e-10);
run;
proc print data=want;
format x best12.;
run;
Note that I have appended an additional observation where the calculated value X in BEST12. format (and even in BEST32. format) looks as "clean" as the rounded value R=1, but in fact it is greater than 1 (by 7.105427E-15). This could have been avoided by rounding directly after the calculation:
x=round(a+b-c-d, 1e-10);
@PGStats wrote:
Impressive reverse engineering @FreelanceReinh !
Kudos!
Thanks, PG! 🙂
A million random 4-tuples (a, b, c, d) (using a logarithmic scale for the integer parts) were sufficient to get at least one instance of each of the 18 numbers.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.