turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- General Programming
- /
- Proc Compare Question

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

11-30-2012 05:16 PM

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.

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

11-30-2012 05:34 PM

Floating point or rounding errors.

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

All Replies

Solution

11-30-2012
05:34 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

11-30-2012 05:34 PM

Floating point or rounding errors.

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

11-30-2012 06:02 PM

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

Sale_Price Base Sale_Price Compare Diff. %Diff

**$-0.00 $0.00 ** 5.821E-11 -100.000

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

11-30-2012 08:59 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

12-02-2012 10:36 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

12-02-2012 06:47 PM

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