← SASnRJoin Datasets Using SQL

Introduction

Joining subject-level data (ADSL) onto event-level data (ADAE, ADLB) is one of the most frequent operations in clinical programming — we need treatment group, demographics, or flags on every record.
SAS uses PROC SQL with LEFT JOIN, INNER JOIN, or FULL JOIN. R uses dplyr’s left_join() family or base R’s merge().
This lesson demonstrates all three join types on a small ADSL + ADAE pair, showing which records survive in each case.
After running, we compare row counts across the three join types to confirm the expected behaviour.

Join Datasets Using SQL

SAS (Base SAS)+

*==============================================================================;
* Create sample ADSL (subject-level);
*==============================================================================;

data adsl;
    input usubjid $ trt01p $;
    datalines;
SUBJ001 Placebo
SUBJ002 DrugA
SUBJ003 Placebo
SUBJ004 DrugA
;
run;

*==============================================================================;
* Create sample ADAE (event-level, SUBJ004 has no AEs, SUBJ005 not in ADSL);
*==============================================================================;

data adae;
    input usubjid $ aedecod $20. aesev $;
    datalines;
SUBJ001 Headache      MILD
SUBJ001 Nausea        MODERATE
SUBJ002 Fatigue       MILD
SUBJ003 Dizziness     SEVERE
SUBJ005 Rash          MILD
;
run;

*==============================================================================;
* LEFT JOIN: keep all AE records, add treatment from ADSL;
*==============================================================================;

proc sql;
    create table ae_left as
    select a.*,
           b.trt01p
    from adae as a
    left join adsl as b
    on a.usubjid = b.usubjid;
quit;

*==============================================================================;
* INNER JOIN: keep only AEs that match a subject in ADSL;
*==============================================================================;

proc sql;
    create table ae_inner as
    select a.*,
           b.trt01p
    from adae as a
    inner join adsl as b
    on a.usubjid = b.usubjid;
quit;

*==============================================================================;
* FULL JOIN: keep everything from both sides;
*==============================================================================;

proc sql;
    create table ae_full as
    select coalesce(a.usubjid, b.usubjid) as usubjid,
           a.aedecod,
           a.aesev,
           b.trt01p
    from adae as a
    full join adsl as b
    on a.usubjid = b.usubjid;
quit;

proc print data=ae_left;  title "LEFT JOIN";  run;
proc print data=ae_inner; title "INNER JOIN"; run;
proc print data=ae_full;  title "FULL JOIN";  run;

A LEFT JOIN keeps every row from the left table (ADAE) and brings in matching columns from the right table (ADSL). Unmatched AE rows get missing values for treatment.
SUBJ005 has an AE but is not in ADSL — the left join keeps that row with trt01p set to missing.
An INNER JOIN keeps only rows that match on both sides — SUBJ005’s AE is dropped because there is no matching ADSL record.
A FULL JOIN keeps everything from both tables. SUBJ004 (in ADSL, no AEs) appears with missing AE columns; SUBJ005 (AE, not in ADSL) appears with missing treatment.
We use coalesce(a.usubjid, b.usubjid) in the full join to fill the USUBJID from whichever side is non-missing.
The alias a and b keep the SQL readable — a.* means "all columns from the AE table."
After running, we compare: left join has 5 rows, inner join has 4 rows (SUBJ005 dropped), full join has 6 rows (SUBJ004 added).

R (tidyverse)+

library(tidyverse)

#==============================================================================
# Create sample ADSL and ADAE
#==============================================================================

adsl <- tribble(
  ~usubjid,  ~trt01p,
  "SUBJ001", "Placebo",
  "SUBJ002", "DrugA",
  "SUBJ003", "Placebo",
  "SUBJ004", "DrugA"
)

adae <- tribble(
  ~usubjid,  ~aedecod,      ~aesev,
  "SUBJ001", "Headache",    "MILD",
  "SUBJ001", "Nausea",      "MODERATE",
  "SUBJ002", "Fatigue",     "MILD",
  "SUBJ003", "Dizziness",   "SEVERE",
  "SUBJ005", "Rash",        "MILD"
)

#==============================================================================
# LEFT JOIN: keep all AE records, add treatment from ADSL
#==============================================================================

ae_left <- adae %>% left_join(adsl, by = "usubjid")
ae_left

#==============================================================================
# INNER JOIN: keep only AEs with a matching ADSL record
#==============================================================================

ae_inner <- adae %>% inner_join(adsl, by = "usubjid")
ae_inner

#==============================================================================
# FULL JOIN: keep everything from both sides
#==============================================================================

ae_full <- adae %>% full_join(adsl, by = "usubjid")
ae_full

dplyr provides left_join(), inner_join(), and full_join() — each maps directly to the SQL join type of the same name.
adae %>% left_join(adsl, by = "usubjid") is the tidyverse translation of the SQL LEFT JOIN ... ON a.usubjid = b.usubjid.
The by = "usubjid" argument specifies the key column(s). For multiple keys, we pass a vector: by = c("usubjid", "paramcd").
Unmatched rows get NA for the columns from the other table — NA in R serves the same role as SAS missing values.
The pipe %>% makes the left table (ADAE) the first argument, so adae %>% left_join(adsl) reads naturally as "start with AE, bring in ADSL."
After running, we confirm: ae_left has 5 rows, ae_inner has 4 rows, and ae_full has 6 rows — matching the SAS output.

Happy learning!

Search lessons

Introduction

Join Datasets Using SQL