← SASnRCompare datasets

Introduction

In clinical programming, we routinely compare two versions of the same dataset — for example the production DM and the independently programmed QC DM — to prove they match.
A good comparison should answer three questions:
- Do both datasets have the same variables (names, types, lengths, labels)?
- For subjects common to both datasets, do the variable values match?
- Which subjects are in only one dataset and not the other?
SAS uses PROC COMPARE with BASE= and COMPARE=. The log and listing together answer all three questions, and the return code (&sysinfo) can be interrogated downstream in automation.
R has no single built-in equivalent, so we reach for a helper package. diffdf and arsenal::comparedf() are the two popular choices in clinical work — both print a structured report very similar in spirit to PROC COMPARE output.
The examples below build two small DM-like datasets, then run the same comparison in SAS, tidyverse R (using diffdf) and base R (using arsenal).
Before running, note the differences we deliberately seeded:
- Subject 1005 has sex = F in dm but sex = M in qc_dm.
- Subjects 1006 and 1007 are in dm but missing from qc_dm.
- Subject 1008 is in qc_dm but not in dm.
After running, confirm the report flags exactly these three discrepancies. If the report shows more or fewer, revisit the code.

Compare datasets

SAS (Base SAS)+

*==============================================================================;
* Step 1: production DM (7 subjects)                                          ;
*==============================================================================;
data dm;
  infile cards truncover dlm="|";
  length domain $2 usubjid $5 sex $3;
  input usubjid $ sex $ age;
  domain = "DM";
  label usubjid = "Unique Subject Identifier";
  cards;
1001|M|72
1002|F|65
1003|F|45
1004|M|40
1005|F|35
1006|F|6
1007|F|5
;
run;

*==============================================================================;
* Step 2: QC DM (6 subjects) — one mismatch, two missing, one extra          ;
*==============================================================================;
data qc_dm;
  infile cards truncover dlm="|";
  length domain $2 usubjid $5 sex $3;
  input usubjid $ sex $ age;
  domain = "DM";
  cards;
1001|M|72
1002|F|65
1003|F|45
1004|M|40
1005|M|35
1008|M|2
;
run;

*==============================================================================;
* Step 3: compare                                                             ;
*==============================================================================;
proc compare base = dm compare = qc_dm listall;
  id usubjid;
run;

*------------------------------------------------------------------------------;
* Step 4 (optional): inspect the return code                                  ;
*------------------------------------------------------------------------------;
%put NOTE: PROC COMPARE sysinfo = &sysinfo;

To compare two datasets in SAS, we run PROC COMPARE with base= and compare= arguments. The one named on base= is the reference.
Every section of the report then reads as “how does COMPARE differ from BASE”, so we need to keep that framing in mind when explaining the output to a reviewer.
We typically add an ID statement so SAS knows which set of variables identifies a row. Without it, PROC COMPARE lines up records by position, which is rarely what we want.
With id usubjid, SAS matches on subject and then reports value mismatches per variable for the matched rows.
For the most thorough listing, we add the listall option. That asks for every available report — variables only in one side, observations only in one side, and the value-by-value comparison.
Verbose, but it leaves nothing to assumptions.
Things we confirm in the listing:
- Variables summary — are all expected variables present in both datasets with matching types and lengths?
- Observation summary — how many rows are in BASE only, in COMPARE only, and in both?
- Value comparison results — for matched rows, which variables differ and for which subjects?
When PROC COMPARE sits inside a validation macro, we can also check the return code &sysinfo.
A value of 0 means everything matched; any non-zero value means at least one discrepancy.
Useful when we want the program to fail automatically on any mismatch.

R (tidyverse)+

library(tidyverse)
library(diffdf)  # install.packages("diffdf") if needed

#==============================================================================
# Step 1: production DM
#==============================================================================
dm <- tribble(
  ~usubjid, ~sex, ~age,
  "1001",   "M",  72,
  "1002",   "F",  65,
  "1003",   "F",  45,
  "1004",   "M",  40,
  "1005",   "F",  35,
  "1006",   "F",   6,
  "1007",   "F",   5
) %>%
  mutate(domain = "DM") %>%
  relocate(domain, .before = usubjid)

#==============================================================================
# Step 2: QC DM — one mismatch, two missing, one extra
#==============================================================================
qc_dm <- tribble(
  ~usubjid, ~sex, ~age,
  "1001",   "M",  72,
  "1002",   "F",  65,
  "1003",   "F",  45,
  "1004",   "M",  40,
  "1005",   "M",  35,
  "1008",   "M",   2
) %>%
  mutate(domain = "DM") %>%
  relocate(domain, .before = usubjid)

#==============================================================================
# Step 3: compare with diffdf (clinical-programming friendly)
#==============================================================================
diff_report <- diffdf(
  base    = dm,
  compare = qc_dm,
  keys    = "usubjid"
)

print(diff_report)

For a SAS-to-R style comparison in tidyverse-friendly code, we can reach for diffdf.
It is a standalone R package from openpharma (not part of tidyverse itself), written specifically for clinical programming work.
Its output is deliberately modelled on PROC COMPARE, which makes it friendly for reviewers already used to reading SAS listings.
To tell diffdf() which set of columns identifies a row, we pass keys = "usubjid". This is the direct analogue of the SAS ID statement.
Without keys, rows are matched positionally — the same caveat as PROC COMPARE.
When we print(diff_report), the output is split into sections: variables only in one side, rows only in one side, and value-level mismatches per variable.
Exact section names appear in that console output; we note them down the first time we run the code so later runs are quick to scan.
diff_report is also an object we can inspect programmatically with str() or subscripting.
For automated checks inside a validation script, we test whether any issues were found — the object’s own print method says “No issues were found” when the two inputs match, and lists the issues otherwise.
This plays the same role as checking &sysinfo after SAS PROC COMPARE.
After running, we confirm three things in the printed report:
- exactly one value difference for 1005,
- exactly two rows in BASE only (1006, 1007),
- exactly one row in COMPARE only (1008).

Happy learning!

Search lessons

Introduction

Compare datasets