SAS Proc Compare

SAS Day 15: Proc Compare

Problem: Suppose we need to compare the datasets Prod and QC.

Background:

We use Proc Compare to compare the value of two datasets. The report will give us a summary of two datasets.  Proc compare has many options, the ones I like the most are: Criterion, Var…. With, Listall, Id, Out.

Criterion: Set the compare decimal places.
E.g. criterion=0.001.

Var…With: Compare the attributes with different names.
E.g.  Var aval; With aval1;

Listall: List all the comparison results.

Id: Specify the compared order.
E.g: Id usubjid visitnum visdtc;

Out: Output a dataset for compared results.

Basic Syntax:

Proc Compare Base=a Compare=b listall criterion=0.0001;
var x y z;
with a b c;
run;

Solution:

Output:

Obviously, Prod and QC dataset did not match. So we need to go back and digging what caused the discrepancies and fix it!

• HTML Proc Compare Output
  If you would like a fancy HTML output for future references, we can use ODS output

  ods html file="/output location/compareresult.html";
  proc compare base=base compare=c;
  title "Compare Result";
  run;
  ods html close;

• Additional Trick
  If the data was read from a different source, such as Excel, there might be hidden special characters.
  We will use the combination of Length, Compress, and Lowcase function to check the special characters and the Substr the common character part.

  l=length(compress(lowcase(a)));
  new=substr(a,1,x);
  * compare the new variable

   

Thanks Yajnes, Cindy, and 77 for the reference code and idea.

Happy Coding! ☺