Proc Compare Question

Accepted Solution Solved
Reply
Regular Contributor
Posts: 173
Accepted Solution

Proc Compare Question


Hello Everyone,

I use proc compare to compare two sas datasets.  As a result, proc compare identified a few rows having columns with unequal values.  But when i browse the two datasets and the 'unequal' columns/values side by side the values in question look identical to me.  I have pasted one of the 'unequal' columns/vlaues so you can see the actual message generated by proc compare.  The column is defined as Type number, Length 8, Format Dollar21.2, and Informat Dollar21.2.  in both datasets.  Why does proc compare think they are different?  Thank you for any input anyone may have. Smiley Happy

Sale_Price Base      Sale_Price Compare     Diff.                    %Diff

$31004.85              $31004.85                   3.638E-12          1.173E-14


Accepted Solutions
Solution
‎11-30-2012 05:34 PM
Super User
Posts: 19,822

Re: Proc Compare Question

Floating point  or rounding errors.

you can set the fuzz/criterion options within proc compare (see the docs).

View solution in original post


All Replies
Solution
‎11-30-2012 05:34 PM
Super User
Posts: 19,822

Re: Proc Compare Question

Floating point  or rounding errors.

you can set the fuzz/criterion options within proc compare (see the docs).

Regular Contributor
Posts: 173

Re: Proc Compare Question

Thank you so much Reeza.  I tried the fuzz= and criterion= options.  Both worked nicely!

However, I have a new question.  I ran proc compare again using the fuzz= option and now it identified one row having a column with different values as shown below.  It's obvious to us that  -$-0.00 and $0.00 are the same thing.  Is there an option i can use to get this message not show up?

Thank you so much again Smiley Happy

Sale_Price Base      Sale_Price Compare     Diff.                    %Diff

$-0.00                        $0.00                         5.821E-11          -100.000

Frequent Contributor
Posts: 136

Re: Proc Compare Question

Hi Kevin.

See the proc compare documentation for the CRITERION option:

http://support.sas.com/documentation/cdl/en/proc/61895/HTML/default/viewer.htm#a000146742.htm

I use proc compare occassionally but much more frequently come across the 'machine epsilon' concept (what my operating environment normally regards as close enough to 0 to be accepted as 0) in many stats and IML applications.

Let me know if that helps.

BTW I suspect that you are seeing this because the values were computed in different operating environments and/or applications (probably not SAS). Am I right? Could Microsoft Excel or old cobol compiled modules be involved in your data's provenance? Just a guess....

Cheers.

Damien

Regular Contributor
Posts: 173

Re: Proc Compare Question

Posted in reply to Damien_Mather

Thank you, Damien!

You're right.  The '-0.00' is from Excel.   I browsed the link you provided and it made sense.  I tried the Method = Relative and crierion = 0.001.  Proc Compare still lists the -0.00 vs 0.00 in it's output.  I will continue to play with it.  I really appreciate your suggestion Smiley Happy

Frequent Contributor
Posts: 136

Re: Proc Compare Question

Thanks Kevin.

A further suggestion: drop the RELATIVE parameter value for  the CRITERION option and go with ABSOLUTE instead, owing to how close to 0 your values are. Say Excel is giving you ~ -1E-8 (call it XLEPS, rounds to -0.00 to 2SD), and the other source is giving you 1E-10 (call it OTEPS, rounding to 0.00) then CRITERION=RELATIVE  will evaluate abs(XLEPS-OTEPS)/OTEPS against your CRITERION i.e. abs(-1E-8 - 1E-10)/abs(1E-10) Vs. 1E-3 and find abs(XLEPS-OTEPS)/OTEPS (1.01E2) is STILL way bigger than your CRITERION value (1E-3). The CRITERION=RELATIVE parameter setting is much more useful when the reference value is not close to your operating environment machine epsilon (which SAS documentation often abbreviates as EPS).

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 434 views
  • 0 likes
  • 3 in conversation