09-25-2014 02:31 PM
When SAS uses the exp or log function, how many decimal places does it use for the e in the computations?
Also, does anyone know how Excel treats this same issue?
I am trying to deconstruct and replicate an online calculator for risk into a SAS program. I believe the online calculator was originally constructed in Exel, however there is an online version as well, both always providing the same results. A student and I have been working on this project for three weeks, to near perfect success. However, the calculator is off by a tenth of a percent for about 3% of our sample (sometimes lower or higher). The calculation is fairly long with multiple logging, exponentiations and many coefficients. I was thinking pershaps there is a difference between Excel and SAS in regards to the rounding of e in the calculations that may influence the results (account for the difference).
Also, if there is a difference in the number of decimal places and I wanted to change the number of places SAS uses in the exp or log function, can anyone propose an easy way to do this?
Thanks in advance for any help,
09-25-2014 02:59 PM
https://support.sas.com/techsup/technote/ts654.pdf 101 numeric precision. Based on the hardware IEE IEEE floating point - Wikipedia, the free encyclopedia binary64 double point.
Both SAS and Excel are not working with decimal places but in the digital age that is binary.
Doing some calculations in a wrong order you can easily create truncation errors that will increasingly grow. There is not notification with that. part of how numeric processing works. Some slider experience or working with a sextant is bringing the insights.
There is an escape for that when you have a recent SAs version. You could used proc DS2 and define numeric in a decimal way. I expect problem with the scientific mathematic functions as these cannot use the hardware but must software coded versions.
09-25-2014 03:36 PM
I saw this online during my searches, but was hoping someone could present it to me with less jargon.
Big picture, are they different or not and how do I make them the same. I can run a bunch of numbers in Excel and then in SAS, but this does not tell me what is happening and by chance they could appear the same even if there was a difference (due to insufficient sampling).
Thanks for enduring my ignorance!!!
09-25-2014 03:57 PM
I believe once I get a solution on places Excel uses (e.g., 15 decimals), I can then use the following
x.xxxxxxxxxxxxxxx**(x2); => perhaps may require round function to ensure it use this
x = value in Excel
x2 an inserted number from my project
data show; val=x.xxxxxxxxxxxxxxx; b2 = log2(val); v2 = log(val) / log(2); put b2= ; put v2=; run;
Though, I first still need to confirm or dismisss similarities between SAS and Excel, before figuring out the above code.
09-25-2014 04:27 PM
You are really missing how calculations are done. In the old days (some years before 1970) computers where very unusual. The calculators where to be build yet.
Doing and learning calculations you needed a slider (scientific) books with log-tables and some standard numbers with pen and pencil.
The understanding what you did with some algorithme was necessary for getting acceptable results. There were many good results.
The fundamentals have not changed but most people obviously do not learn those anymore. I will give you some Intel links:
- Looking for fast exponential function (a question on using exp and got an answer with accuracy hints)
- Exponential Functions (part of intels C complier used to build a lot of software)
09-26-2014 12:59 AM
You may find the COMPARE procedure useful when comparing numeric columns between different tables (SAS versus Excel) that you think should be "identical".
COMPARE (using the EXACT method) will report the maximum difference found between the columns and if it is "small" it uses exponential notation to report this. If it reports the difference as 1* e(-14) or less then you know the values are essentially the same and it is floating point precision that is the culprit. If you then switch to the ABSOLUTE method, COMPARE will usually report the same columns as being identical.