Articles
SAS PROC COMPARE Interview Questions: Match Records, Set Tolerances, and Explain Differences
Prepare SAS PROC COMPARE interview answers: match IDs, justify numeric tolerances, separate metadata from values, and retain reconciliation evidence.
Two SAS datasets can contain the same business records and still produce alarming differences when compared in observation order. Conversely, matching rounded displays can hide a real difference in stored values. A useful PROC COMPARE interview answer starts with record identity, then defines what “equal” means.
Match the right records before interpreting their differences. This guide works through an original reconciliation fixture, a tolerance decision, and an evidence-based sign-off. Official SAS behavior is linked where introduced; the worked examples are preparation exercises, not reported employer questions.
The goal is to explain why a result is credible, rather than memorize a long option list.
What should you establish before running PROC COMPARE?
Ask what one row represents: a customer, a transaction, a visit, or one observation per customer and date. A customer identifier alone is insufficient if a customer can have several visits. The comparison key must represent the declared grain.
Our preparation recommendation is to write a short comparison contract before selecting options. Name the base snapshot, comparison snapshot, key, required variables, accepted numeric differences, and treatment of unmatched rows. Include the extraction cutoff when either dataset can change during validation.
SAS's official comparison concepts distinguish dataset and variable attributes, observation matching, and value comparisons. These are separate questions. A value-only check cannot establish that a required variable is present or that the record population is complete.
In an interview, make the business decision explicit: “I am validating whether the new calculation reproduces the approved snapshot at transaction grain.” That statement gives you a reason to reject duplicate transaction keys, investigate missing records, and justify an amount tolerance.
Why can the same records compare differently after sorting?
Consider our original fixture. The base rows are (101,100.004), (102,200.000), and (103,50.000). The comparison rows arrive as (102,200.000), (101,100.005), and (104,50.000). Each pair contains a transaction ID and amount.
Comparing positions pairs transaction 101 with 102 and 102 with 101. Those large differences are artifacts of the pairing. The final rows have the same amount but different identities, which is a population issue rather than evidence of equality.
The official ID statement documentation says normal ID matching uses common ID variables of the same type; sort both datasets by the common ID variables. Without ID, matching is positional. ID ... NOTSORTED is also positional and requires corresponding ID values to agree; it is not an arbitrary-order key lookup.
proc sort data=base_snapshot out=base_by_id;
by transaction_id;
run;
proc sort data=new_snapshot out=new_by_id;
by transaction_id;
run;
proc compare base=base_by_id compare=new_by_id
method=absolute criterion=0.01;
id transaction_id;
var amount;
run;
This snippet assumes the inputs already passed key checks. It was reviewed against the documentation, not executed in a SAS environment. The original fixture's keyed pairs and decimal differences were checked separately in Python; that check does not substitute for running this SAS program.
The intended interpretation is two matched IDs, one base-only ID, and one comparison-only ID. The amount difference for 101 is 0.001. Transaction 102 has no amount difference. The two records with amount 50 are not a match because their IDs differ.
What do duplicate keys make ambiguous?
Add two rows for transaction 101 to one input. Which comparison row should each match? A technical procedure can produce output while your business identity remains undefined.
The ID documentation warns about successive duplicate IDs and describes one-to-one comparison of duplicates. With unsorted data, its duplicate detection covers successive occurrences. Neither behavior establishes a business rule for selecting a record.
Our proposed precondition is to count records per full key in each input and require uniqueness when the declared grain is one record per key. If repeats are valid, extend the key with the missing grain dimension, such as line number or effective date. If repeats are invalid, retain their evidence and resolve them before comparing values.
Do not use a deduplication option merely to make the warning disappear. Keeping one arbitrary row can remove the very defect the comparison was intended to expose. If the requirement is “latest approved version,” state how approval, effective time, and ties are resolved before forming the two snapshots.
A good follow-up answer distinguishes a duplicate business record from two legitimate versions. Their values may be identical, but the operational problem is different. Explain who authorizes the selection rule and whether excluded versions remain auditable.
How do you choose an absolute or relative tolerance?
Under the documented METHOD=ABSOLUTE, numeric inequality depends on whether the absolute difference exceeds CRITERION=. METHOD=EXACT requires exact equality. The method and criterion belong in the comparison contract, not in an improvised effort to reduce the error count.
For our fixture, a 0.01 absolute amount tolerance accepts the 0.001 difference for transaction 101. That is a conditional example, not a recommendation that every currency pipeline should accept a cent of error. You must explain the calculation, storage representation, rounding point, and downstream effect.
| Original test | Decision under an exact decimal 0.01 contract |
|---|---|
| 100.004 versus 100.005 | Difference 0.001; accepted |
| 100.00 versus 100.01 | Difference exactly 0.01; accepted |
| 100.00 versus 100.0101 | Difference 0.0101; rejected |
| 0 versus 0.005 | Accepted numerically, but verify whether zero has a separate business meaning |
These boundary calculations were executed using Python Decimal. SAS numeric values use their own machine representation, so test the actual runtime near a boundary rather than assuming a decimal fixture proves the binary calculation's result.
Relative comparison can be useful across different magnitudes, but specify its documented formula and near-zero handling before use. SAS supports RELATIVE(delta); do not silently replace it with an unrelated library's isclose rule. An accepted difference in a large total can still hide an unacceptable error in a small record.
Our inference is that per-variable policies are clearer when units differ. An amount, date, rate, and count should not inherit one unexplained threshold. Use separate comparisons or explicitly normalized validation views when the equality contracts differ.
Why can identical displays hide unequal values?
The concepts documentation states that PROC COMPARE checks unformatted values. Our amounts 100.004 and 100.005 can look similar under a coarse display while remaining different stored numbers. Changing the print format is not a correction to the underlying calculation.
Ask whether the required equivalence concerns raw values, rounded settlement amounts, or display strings. Those are different validation targets. If settlement rounding is part of the requirement, construct an explicit rounded validation view and retain the raw comparison as evidence.
Variable metadata also matters. A changed label may be harmless for one downstream job, while a changed type or character length can break a consumer. Our sign-off recommendation is to classify each attribute difference against the interface contract instead of accepting or rejecting all metadata differences together.
Do not infer schema equivalence from equal values in a small sample. A shortened character field might contain the sample safely while truncating a longer future value. Include a boundary fixture that reaches the required length and inspect the actual attributes.
How should missing values affect the verdict?
The official PROC COMPARE options include NOMISSBASE, NOMISSCOMP, and NOMISSING, which can make missing values compare equal to otherwise different values in the specified input. That changes the equality contract.
For our reconciliation exercise, a missing amount is unresolved data, not a wildcard. Accepting it against 100 would conceal a completeness defect. If a migration intentionally fills a previously empty optional field, describe that permitted transformation separately and count the affected records.
Distinguish a missing field value from a missing record. An ID absent from one dataset is a population mismatch; an existing matched row with an empty amount is a value-completeness problem. A tolerance cannot repair either identity or completeness.
If both snapshots contain missing amounts, matching missing values still does not establish that the calculation is correct. A separate required-field rule may reject both. A comparison answers whether two inputs agree under a policy, not whether either input is valid in the business domain.
How do you retain differences without losing context?
The official output-dataset example combines base values, comparison values, and differences. Retaining the originals beside a difference makes a reviewer able to inspect the reason for the verdict.
proc compare base=base_by_id compare=new_by_id
method=absolute criterion=0.01
out=compare_evidence
outbase outcomp outdif outnoequal noprint;
id transaction_id;
var amount;
run;
%let compare_status = &sysinfo;
The results reference explains _TYPE_ and _OBS_. Interpret them with the selected options, not as a custom one-row-per-error schema. OUTNOEQUAL suppresses rows judged equal; within retained difference rows, .E can represent a variable judged equal. Do not treat that marker as a new source-system missing amount.
SAS's SYSINFO support sample describes combined return-code flags. Capture the value immediately after PROC COMPARE, before another step can change it. Decode the documented flags rather than comparing the entire result to one magic number.
Our proposed acceptance report combines the captured status, population counts, duplicate-key results, difference evidence, and approved exceptions. An empty selected value-difference output alone is insufficient for sign-off: the selection may omit a required field, and metadata or unmatched-record evidence may live elsewhere.
Practise explaining the reconciliation decision
Apply PROC COMPARE where its documented matching and comparison model fits; explain other tools when the exercise requires different matching logic.
| Question | What to explain |
|---|---|
| Reconcile Records Across Sources | Establish grain and account for records on only one side |
| Reconcile Monthly Bank-Account Balances Across Two Datasets | Separate balance differences from a missing account or month |
| Reconcile ledgers with SQL/Python and late events | Fix the snapshot cutoff before interpreting late-event differences |
| Match Payments to Invoices by Memo ID, Exact Amount, then Amount Tolerance | Justify an amount tolerance after establishing correspondence |
| Validate order book data across multiple databases | Compare a shared data contract and retain evidence for reviewers |
Finish with a concise verdict for the original fixture: “After validating unique transaction IDs and sorting, IDs 101 and 102 match. The amount difference for 101 meets the stated tolerance. IDs 103 and 104 remain population exceptions, so I cannot approve complete equivalence.”
Then change one assumption: perhaps 104 is a permitted new transaction, or the required tolerance is exact. Explain how the verdict changes and which evidence remains unchanged. That is the interview skill behind the options.
Keep correspondence, numeric acceptance, and overall sign-off as separate decisions; a clean comparison is useful only when its scope represents the actual requirement.
Sources and Further Reading
- SAS: PROC COMPARE concepts and equality criteria
- SAS: ID statement, sorting and duplicate behavior
- SAS: PROC COMPARE options
- SAS: output-dataset comparison example
- SAS: results, output variables and return codes
- SAS: interpreting SYSINFO flags
References checked October 4, 2026. SAS snippets were documentation-reviewed; original decimal fixtures were executed separately. No SAS runtime execution or employer question frequency is claimed.