R has six named join functions where SAS has one MERGE statement plus a set of conventions. Once you know which name maps to which IN= pattern, joins get shorter and much harder to get silently wrong.
Quick reference
library(dplyr)# --- the six joins ------------------------------------------------------inner_join(dm, ae, by ="subjid") # rows matching on BOTH sidesleft_join( dm, ae, by ="subjid") # all of dm, annotated from ae <- the usual oneright_join(dm, ae, by ="subjid") # all of ae (just swap the args instead)full_join( dm, ae, by ="subjid") # everything from either sideanti_join( dm, ae, by ="subjid") # in dm, NOT in ae <- "who is missing?"semi_join( dm, ae, by ="subjid") # filter dm to matches, add no columns# --- keys ---------------------------------------------------------------left_join(dm, ae, by =c("subjid"="SUBJID")) # different names, left firstleft_join(lb, visits, by =c("subjid", "visit")) # both keys must match# --- state what you expect; the join aborts if the data disagrees -------left_join(dm, ae, by ="subjid", relationship ="one-to-many")# "one-to-one" | "one-to-many" | "many-to-one" | "many-to-many"
R
SAS DATA step
SQL
inner_join()
if a and b;
INNER JOIN
left_join()
if a;
LEFT JOIN
right_join()
if b;
RIGHT JOIN
full_join()
no subsetting if
FULL JOIN
anti_join()
if a and not b;
WHERE ... NOT IN
semi_join()
if a and b; then dedupe
WHERE EXISTS
Three traps: NA after a join means "no match", not "missing value". inner_join() throws away your data errors and your event-free subjects together, without saying so. And a duplicate key on the right silently multiplies rows, which is what relationship = is for.
The rest of this page is the same material worked through slowly, with runnable examples and the output they produce. Skip it if the block above was what you came for.
The example tables
Two small tables with deliberate mismatches, used throughout this page.
library(dplyr)
Attaching package: 'dplyr'
The following objects are masked from 'package:stats':
filter, lag
The following objects are masked from 'package:base':
intersect, setdiff, setequal, union
The mismatches matter, because they are what the different joins disagree about:
subject 001 has two AEs, so this is one-to-many
subjects 004 and 005 are in dm with no AEs
subject 006 has an AE but is not in dm, a data error you want to catch
The six joins
inner_join(): rows matching in both
proc sql;
select * from dm inner join ae on dm.subjid = ae.subjid;
quit;
/* or, DATA step */
data out;
merge dm(in=a) ae(in=b);
by subjid;
if a and b;
run;
inner_join(dm, ae, by ="subjid")
subjid sex trt aeterm sev
1 001 M Placebo Headache Mild
2 001 M Placebo Nausea Moderate
3 002 F Active Rash Severe
4 003 F Active Fatigue Mild
Four rows. Dropped: 004 and 005 (no AE), and 006 (not in dm). Note that the data error and the legitimately event-free subjects vanish together and without comment. That is exactly why inner_join() makes a poor default.
left_join(): keep everything on the left
data out;
merge dm(in=a) ae;
by subjid;
if a;
run;
left_join(dm, ae, by ="subjid")
subjid sex trt aeterm sev
1 001 M Placebo Headache Mild
2 001 M Placebo Nausea Moderate
3 002 F Active Rash Severe
4 003 F Active Fatigue Mild
5 004 M Placebo <NA> <NA>
6 005 M Active <NA> <NA>
This is the workhorse, because most real joins are "start from my subject list and annotate it". Six rows: 001 twice because it has two AEs, and 004/005 once each with NA in the AE columns.
ImportantNA from a join means "no match", not "missing value"
R gives you the same NA for both, and only context tells you which you have. Here it means the subject had no adverse event. It does not mean the term was left blank on the CRF, and it does not that the AE extract failed to load. If that distinction matters downstream, record it explicitly with a flag column rather than relying on the NA.
right_join(): keep everything on the right
right_join(dm, ae, by ="subjid")
subjid sex trt aeterm sev
1 001 M Placebo Headache Mild
2 001 M Placebo Nausea Moderate
3 002 F Active Rash Severe
4 003 F Active Fatigue Mild
5 006 <NA> <NA> Dizziness Mild
Identical to left_join(ae, dm, by = "subjid") apart from column order, and rare in practice, because swapping the arguments reads better than making the reader track a mirror image. Subject 006 shows up here with NA demographics.
full_join(): keep everything from both
data out;
merge dm ae;
by subjid;
run;
full_join(dm, ae, by ="subjid")
subjid sex trt aeterm sev
1 001 M Placebo Headache Mild
2 001 M Placebo Nausea Moderate
3 002 F Active Rash Severe
4 003 F Active Fatigue Mild
5 004 M Placebo <NA> <NA>
6 005 M Active <NA> <NA>
7 006 <NA> <NA> Dizziness Mild
Seven rows: every subjid from either side. Useful when you are hunting data-quality problems and do not yet know which side they are on.
anti_join(): left rows with no match on the right
data out;
merge dm(in=a) ae(in=b);
by subjid;
if a and not b;
run;
The QC workhorse. "Who is missing?" is one line in both directions:
# Subjects with no adverse events at allanti_join(dm, ae, by ="subjid")
subjid sex trt
1 004 M Placebo
2 005 M Active
# AE rows referencing a subject who is not in DM (a data error)anti_join(ae, dm, by ="subjid")
subjid aeterm sev
1 006 Dizziness Mild
Running both directions after any join is a cheap habit that catches key problems before they reach an analysis dataset.
semi_join(): filter the left by existence on the right
proc sql;
select * from dm where subjid in (select subjid from ae);
quit;
semi_join(dm, ae, by ="subjid")
subjid sex trt
1 001 M Placebo
2 002 F Active
3 003 F Active
Three rows, and only dm's columns. Nothing is merged in. Compare with inner_join(dm, ae), which returned four rows because 001 was duplicated by its two AEs. semi_join() filters; inner_join() filters and merges. When you only want to subset, semi_join() cannot change your row count behind your back.
Keys that are named differently
SAS forces you to rename before merging. R does not:
A row matches only when both keys match, exactly like BY subjid visit.
Row-count hygiene
The most common join bug in clinical data is a right-hand table with duplicate keys, which quietly multiplies your rows. In SAS you find out by reading the log. In R, state the shape you expect and let the join fail if the data disagrees:
nrow(dm)
[1] 5
nrow(left_join(dm, ae, by ="subjid"))
[1] 6
Six rows from five subjects. Here that is correct, because one subject has two AEs, but you should have to say so:
left_join(dm, ae, by ="subjid", relationship ="one-to-many")
subjid sex trt aeterm sev
1 001 M Placebo Headache Mild
2 001 M Placebo Nausea Moderate
3 002 F Active Rash Severe
4 003 F Active Fatigue Mild
5 004 M Placebo <NA> <NA>
6 005 M Active <NA> <NA>
relationship = (dplyr 1.1 and later) takes "one-to-one", "one-to-many", "many-to-one" or "many-to-many", and aborts when the data violates the claim:
left_join(dm, ae, by ="subjid", relationship ="one-to-one")
Error in `left_join()`:
! Each row in `x` must match at most 1 row in `y`.
ℹ Row 1 of `x` matches multiple rows in `y`.
That error is the point. Get into the habit of declaring the relationship on every join. It turns a silent row explosion into a loud failure at the line that caused it.
Base R: merge()
Still common in older code, and the closest single function to a DATA step MERGE:
merge(dm, ae, by ="subjid") # inner (the default)
subjid sex trt aeterm sev
1 001 M Placebo Headache Mild
2 001 M Placebo Nausea Moderate
3 002 F Active Rash Severe
4 003 F Active Fatigue Mild
merge(dm, ae, by ="subjid", all.x =TRUE) # left
subjid sex trt aeterm sev
1 001 M Placebo Headache Mild
2 001 M Placebo Nausea Moderate
3 002 F Active Rash Severe
4 003 F Active Fatigue Mild
5 004 M Placebo <NA> <NA>
6 005 M Active <NA> <NA>
merge(dm, ae, by ="subjid", all.y =TRUE) # right
subjid sex trt aeterm sev
1 001 M Placebo Headache Mild
2 001 M Placebo Nausea Moderate
3 002 F Active Rash Severe
4 003 F Active Fatigue Mild
5 006 <NA> <NA> Dizziness Mild
merge(dm, ae, by ="subjid", all =TRUE) # full
subjid sex trt aeterm sev
1 001 M Placebo Headache Mild
2 001 M Placebo Nausea Moderate
3 002 F Active Rash Severe
4 003 F Active Fatigue Mild
5 004 M Placebo <NA> <NA>
6 005 M Active <NA> <NA>
7 006 <NA> <NA> Dizziness Mild
Readable enough, but there is no merge() equivalent of anti_join() or semi_join(), no relationship = check, and all.x = TRUE says less at a glance than left_join. Prefer dplyr for new work; recognise merge() when you inherit it.