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

 

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Social media & sharing icons powered by UltimatelySocial
%d bloggers like this: