A question in SAS/SQL, please help.

Anne — Thu, 26 Apr 2012 18:44:46 GMT

proc sql;

create table Raisin as

select distinct 'Total' as Item, '' as Type, count(GFTAmt) as Count, sum(GFTAmt) format 12.2 as Amt

from A

union all

...

union all

select distinct 'EventType', EventType, count(GFTAmt) , sum(GFTAmt)

from A

group by EventType

;

quit;

Table CRM is created in same way. When I run the following code, error comes.

code 1.

proc compare base=crm compare=Raisin out=result outnoequal outbase outcomp outdif ;

var Count Amt;

title 'Comparison of Variables in Different Data Sets';

id Item Type;

run;

----------The output1 shows:---------------------v

Comparison of Variables in Different Data Sets 14:20 Monday, April 26, 2012 2

The COMPARE Procedure

Comparison of WORK.CRM with WORK.RAISIN

(Method=EXACT)

Values Comparison Summary

Number of Variables Compared with All Observations Equal: 1.

Number of Variables Compared with Some Observations Unequal: 1.

Total Number of Values which Compare Unequal: 1.

Maximum Difference: 2.2737E-13.

Variables with Unequal Values

Variable Type Len Ndif MaxDif

Amt NUM 8 1 227E-15

Value Comparison Results for Variables

__________________________________________________________________________________

|| Base Compare

Item Type || Amt Amt Diff. % Diff

____________ __________________ || _________ _________ _________ _________

EventType General || 1354.11 1354.11 2.274E-13 1.679E-14

__________________________________________________________________________________

code2:

proc sql;
select a.Item, a.type, a.count-b.count as count,
a.Amt ,b.Amt, a.Amt-b.Amt as AmtDiff
from raisin a, crm b
where a.Item=b.Item and a.type=b.type;
quit;

--------------Amt format 12.2 and here is a part of the output2----------------------------v

Item Type count Amt Amt AmtDiff
...
EventType General 0 1354.11 1354.11 2.27E-13
...

--------------------------------------------------------------------------------------------------------------^

My question is that how 2.27E-13 comes?:smileyconfused: how to avoid this format happens?

Please help. Thank you.

Re: A question in SAS/SQL, please help.

PGStats — Thu, 26 Apr 2012 20:41:35 GMT

Anne, formats only affect the printed representation of numbers, not their internal value. If, as it seems, the variable GFTamt should have only 2 decimals, you can avoid small errors due to floating point numbers representation by doing your own rounding. Replace sum(GFTamt) by round(sum(GFTamt), 0.01).

Re: A question in SAS/SQL, please help.

Anne — Fri, 04 May 2012 15:47:20 GMT

Thank you PG, I replaced sum() with round(sum()) and it works well. Thank you so much.

Re: A question in SAS/SQL, please help.

ballardw — Fri, 04 May 2012 18:19:06 GMT

Proc compare also has an OPTION FUZZ so you can set a small value where things are considered "close enough".

I deal with this often and use FUZZ= 1e-8 as the significant digits don't reach anywhere near that.

topic A question in SAS/SQL, please help. in SAS Programming

A question in SAS/SQL, please help.

code2:

Re: A question in SAS/SQL, please help.

Re: A question in SAS/SQL, please help.

Re: A question in SAS/SQL, please help.