SAS Day 15: Proc Compare
Problem: Suppose we need to compare the datasets Prod and QC.
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.
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.
Proc Compare Base=a Compare=b listall criterion=0.0001; var x y z; with a b c; run;
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! ☺