topic Re: Why 2 identical SAS tables show a difference in values when using PROC COMPARE? in SAS Programming

Why 2 identical SAS tables show a difference in values when using PROC COMPARE?

mvalsamis — Wed, 04 Jun 2025 11:06:05 GMT

I have two identical tables, WORK.ONE and WORK.TWO (this is only for simplicity). The code to create them and assign them indexes is the following:

data work.one;
  length id $2 product $5 price 8;
  input id $ product $ price;
  datalines;
  A3 Desk 500
  A4 Table 1000
  B1 Bed 1500
  ;
run;

data work.two;
  length id $2 product $5 price 8;
  input id $ product $ price;
  datalines;
  A3 Desk 500
  A4 Table 1000
  B1 Bed 1500
  ;
run;

proc datasets lib=work;
  modify one;
  index create id;
run;

proc datasets lib=work;
  modify two;
  index create id;
run;

I want to compare them using PROC COMPARE as follows:

proc compare b=one c=two;
  id id;
run;

I get the following results, again simplified:

Variables with Unequal Values

Variable Type Len Ndif MaxDif

price NUM $2 1 3.5E-20

Value Comparison Results for Variables

id || Base Compare Diff. %Diff

price price

-----------------------------------------------------------------------

A4 || 1000 1000 3.5E-20 1.3E-20

Although the values are identical, they are not recognised like that. Obviously the difference tends to zero, but I need it to be shown as exactly equal. Do you have any idea of how I can fix this? Thanks a lot in advance.

Re: Why 2 identical SAS tables show a difference in values when using PROC COMPARE?

PaigeMiller — Wed, 04 Jun 2025 11:30:07 GMT

Clearly the difference is very small: 3.5E-20. Why does this happen? Because computers cannot represent some non-integer numbers with infinite precision, and so these will show as different. (I do not get the results you show from your code, indicating that your real-world data that you ran PROC COMPARE on did not have integers for PRICE)

To avoid this problem, use the FUZZ= option in PROC COMPARE.

Re: Why 2 identical SAS tables show a difference in values when using PROC COMPARE?

Kathryn_SAS — Wed, 04 Jun 2025 11:39:56 GMT

Another option to account for numeric precision issues is to use the ROUND function on the variable (Price) in advance in each data set.

data one;
set one;
price=round(price,.01);
run;

data two;
set two;
price=round(price,.01);
run;

Re: Why 2 identical SAS tables show a difference in values when using PROC COMPARE?

mvalsamis — Wed, 04 Jun 2025 11:41:10 GMT

Yes, this example is oversimplified, and if you compare the two tables, they will be exactly identical. The actual tables I am working in have 24 numeric variables, which have decimals.