topic Re: Ratio in SAS Enterprise Guide

Ratio

louise — Wed, 27 Oct 2010 04:13:36 GMT

Hi,

Would like to know how EG generates the resulting values if the expected output is undefined value?

In our scenario, we are computing for ratio with the following given values:

numerator = 15,123,021,740.41

denominator = 0

generated ratio = 1741640597964550%

Although, the data should not have a 0 denominator, just want to ask how EG was able to get the value 1741640597964550%? Moreover, the client tried re-generating the report using same data but the ratio output displayed a different value, 34102446547241500%. Howcome the values are inconsistent?

Thanks!

Re: Ratio

ChrisHemedinger — Wed, 27 Oct 2010 17:40:25 GMT

We'll need more details for how this calculation was done. Using Query Builder? A program with DATA step? A log/output example would help.

EG doesn't perform calculations itself, usually. All of the computations are done within SAS and driven by SAS programs, either written by users or generated in a UI like the Query Builder.

Chris

Re: Ratio

Doc_Duke — Thu, 28 Oct 2010 02:10:05 GMT

Louise,

I'd suggest that you read this article and see if it answers your question

http://support.sas.com/documentation/cdl/en/lrcon/62955/HTML/default/viewer.htm#a000695157.htm

At the heart of the problem is that you are thinking in base-10 and computers work in base-2, so most things are approximations. Integers can be represented exactly, but decimal numbers cannot. Therefore
X=0;
is not the same as
X=0.0;

Re: Ratio

louise — Thu, 28 Oct 2010 08:38:18 GMT

Thank you, Chris. I sent you a separate email. I think it's possible that it's a format issue? I tried summarizing the amounts manually in Excel using the values of the tables used as source by the OLAP cube. It appears that there are two items that did not match because of the decimal values, although when rounded off to two decimals they get the same value. The wierd thing though is that none of them matched the RAW VALUE produced in the EG subtotal.

Excel Manual Summation:
* The optimized value when the Consolidated amount is summarized will equal to 0.00000.
* The original value when the Consolidated amount is summarized will equal to 0.00055.

Query using SAS Base:
* The optimized value when the Consolidated amount is summarized will equal to 0.
* The original value when the Consolidated amount is summarized will equal to 0.0008544922.

EG Subtotal Raw Value:
* The optimized value when the Consolidated amount is summarized will equal to 4.43458557128906E-05.
* The original value when the Consolidated amount is summarized will equal to 0.000868320465087891.

Re: Ratio

louise — Thu, 28 Oct 2010 08:40:29 GMT

Thank you, Doc. I'll check on the link you provided and see if that explains the issue.

Re: Ratio

ChrisHemedinger — Thu, 28 Oct 2010 13:42:23 GMT

Just FYI for the group...

In follow-up e-mail, it turns out this scenario is related to OLAP from within EG. We're following up within the development group with a more detailed analysis.

Chris