DATA Step, Macro, Functions and more

A question in SAS/SQL, please help.

New Contributor
Posts: 3

A question in SAS/SQL, please help.

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



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;


----------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


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



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;

--------------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.

Esteemed Advisor
Posts: 5,526

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

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).


New Contributor
Posts: 3

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

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

Super User
Posts: 13,532

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

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.

Ask a Question
Discussion stats
  • 3 replies
  • 1 like
  • 3 in conversation