Skip to content

Combining data

Every ADaM data set is built by combining tables. ADSL columns go on every other data set. Adverse events get the treatment dates. Lab values get their reference ranges. This chapter shows how to stack tables and how to join them. It also shows how a join goes wrong without a message, and how you make R check it for you.

The first sections use two tiny tables, so that you can check every row by eye. Then the same ideas run on the pilot study.

programs/05_1_tiny.R
# Chapter 5: two tiny tables, small enough to check every join by eye
library(dplyr)
dm <- tibble(
USUBJID = c("S1", "S2", "S3", "S4"),
AGE = c(63, 64, 71, 74)
)
ae <- tibble(
USUBJID = c("S2", "S4", "S4", "S5"),
AEDECOD = c("HEADACHE", "DIZZINESS", "NAUSEA", "RASH")
)
dm
#> # A tibble: 4 x 2
#> USUBJID AGE
#> <chr> <dbl>
#> 1 S1 63
#> 2 S2 64
#> 3 S3 71
#> 4 S4 74
ae
#> # A tibble: 4 x 2
#> USUBJID AEDECOD
#> <chr> <chr>
#> 1 S2 HEADACHE
#> 2 S4 DIZZINESS
#> 3 S4 NAUSEA
#> 4 S5 RASH

tibble() makes a data frame from columns that you type. Each argument is one column: a name, then the values. Chapter 2 shows it in full.

dm has four subjects, S1 to S4. ae has four records: one for S2, two for S4, and one for S5. S5 is not in dm. Two subjects, S2 and S4, are in both tables. Every program of this chapter that uses the tiny tables makes them again in its first lines, so that it runs on its own.

A key is the column (or the columns) that identifies what a row belongs to. Here the key is USUBJID, the unique subject identifier.

To stack tables is to put the rows of one table under the rows of another.

programs/05_2_bind_rows.R
# Chapter 5: stack tables with bind_rows()
library(dplyr)
dm <- tibble(USUBJID = c("S1", "S2", "S3", "S4"), AGE = c(63, 64, 71, 74))
more <- tibble(USUBJID = c("S0", "S5"), AGE = c(58, 80))
# stack, and record where each row came from
bind_rows(DM = dm, MORE = more, .id = "SOURCE")
#> # A tibble: 6 x 3
#> SOURCE USUBJID AGE
#> <chr> <chr> <dbl>
#> 1 DM S1 63
#> 2 DM S2 64
#> 3 DM S3 71
#> 4 DM S4 74
#> 5 MORE S0 58
#> 6 MORE S5 80
# a column that only one table has: the other rows get NA
late <- tibble(USUBJID = "S6", SEX = "F")
bind_rows(dm, late)
#> # A tibble: 5 x 3
#> USUBJID AGE SEX
#> <chr> <dbl> <chr>
#> 1 S1 63 <NA>
#> 2 S2 64 <NA>
#> 3 S3 71 <NA>
#> 4 S4 74 <NA>
#> 5 S6 NA F
# interleave: stack, then sort by the key
bind_rows(dm, more) |>
arrange(USUBJID)
#> # A tibble: 6 x 2
#> USUBJID AGE
#> <chr> <dbl>
#> 1 S0 58
#> 2 S1 63
#> 3 S2 64
#> 4 S3 71
#> 5 S4 74
#> 6 S5 80
  • bind_rows(DM = dm, MORE = more, .id = "SOURCE") puts the 2 rows of more under the 4 rows of dm. The names before = go into a new column SOURCE, so each row says where it came from.
  • A column that only one table has is kept. The rows of the other table get NA in it. S6 has no AGE, and S1 to S4 have no SEX.
  • bind_rows() does not sort. To mix the rows in key order, sort after the stack with arrange().

The columns must have the same type in every table. If AGE is a number in one table and a text in the other, bind_rows() stops with an error. It does not guess.

To join two tables is to put rows with the same key side by side. The four joins differ in only one thing: what they do with a row that has no partner in the other table.

programs/05_3_joins.R
# Chapter 5: the four joins differ only in the rows without a partner
library(dplyr)
dm <- tibble(USUBJID = c("S1", "S2", "S3", "S4"), AGE = c(63, 64, 71, 74))
ae <- tibble(
USUBJID = c("S2", "S4", "S4", "S5"),
AEDECOD = c("HEADACHE", "DIZZINESS", "NAUSEA", "RASH")
)
inner_join(dm, ae, by = join_by(USUBJID))
#> # A tibble: 3 x 3
#> USUBJID AGE AEDECOD
#> <chr> <dbl> <chr>
#> 1 S2 64 HEADACHE
#> 2 S4 74 DIZZINESS
#> 3 S4 74 NAUSEA
left_join(dm, ae, by = join_by(USUBJID))
#> # A tibble: 5 x 3
#> USUBJID AGE AEDECOD
#> <chr> <dbl> <chr>
#> 1 S1 63 <NA>
#> 2 S2 64 HEADACHE
#> 3 S3 71 <NA>
#> 4 S4 74 DIZZINESS
#> 5 S4 74 NAUSEA
right_join(dm, ae, by = join_by(USUBJID))
#> # A tibble: 4 x 3
#> USUBJID AGE AEDECOD
#> <chr> <dbl> <chr>
#> 1 S2 64 HEADACHE
#> 2 S4 74 DIZZINESS
#> 3 S4 74 NAUSEA
#> 4 S5 NA RASH
full_join(dm, ae, by = join_by(USUBJID))
#> # A tibble: 6 x 3
#> USUBJID AGE AEDECOD
#> <chr> <dbl> <chr>
#> 1 S1 63 <NA>
#> 2 S2 64 HEADACHE
#> 3 S3 71 <NA>
#> 4 S4 74 DIZZINESS
#> 5 S4 74 NAUSEA
#> 6 S5 NA RASH
Join Keeps Rows here
inner_join(dm, ae) only keys in both tables S2, S4, S4
left_join(dm, ae) every row of dm, ae where it matches S1, S2, S3, S4, S4
right_join(dm, ae) every row of ae, dm where it matches S2, S4, S4, S5
full_join(dm, ae) every row of both tables S1, S2, S3, S4, S4, S5
  • by = join_by(USUBJID) names the key. When the key has another name in the second table, write join_by(USUBJID == SUBJ). For a key of two columns, write join_by(USUBJID, VISITNUM).
  • S4 has one row in dm and two in ae. The join repeats the age 74 on both AE rows. This “one to many” join is the normal case.
  • Where a join finds no partner, it writes NA. S1 has no adverse event, so its AEDECOD is NA. S5 has no row in dm, so its AGE is NA.
  • The full join keeps the key of S5. You do not need to combine two key columns: the result has one USUBJID.

The left join is the join you use most. Keep every subject of the population, and add what you find.

5.4 Who is missing where: semi_join() and anti_join()

Section titled “5.4 Who is missing where: semi_join() and anti_join()”

Two more joins add no columns. They only keep or drop rows of the first table.

programs/05_4_semi_anti.R
# Chapter 5: semi_join() and anti_join() find who is missing where
library(dplyr)
dm <- tibble(USUBJID = c("S1", "S2", "S3", "S4"), AGE = c(63, 64, 71, 74))
ae <- tibble(
USUBJID = c("S2", "S4", "S4", "S5"),
AEDECOD = c("HEADACHE", "DIZZINESS", "NAUSEA", "RASH")
)
semi_join(dm, ae, by = join_by(USUBJID)) # DM subjects with an AE
#> # A tibble: 2 x 2
#> USUBJID AGE
#> <chr> <dbl>
#> 1 S2 64
#> 2 S4 74
anti_join(dm, ae, by = join_by(USUBJID)) # DM subjects with no AE
#> # A tibble: 2 x 2
#> USUBJID AGE
#> <chr> <dbl>
#> 1 S1 63
#> 2 S3 71
anti_join(ae, dm, by = join_by(USUBJID)) # AE records with no DM subject
#> # A tibble: 1 x 2
#> USUBJID AEDECOD
#> <chr> <chr>
#> 1 S5 RASH
# the same QC check on the pilot study
sdtm_dm <- pharmaversesdtm::dm
sdtm_ae <- pharmaversesdtm::ae
anti_join(sdtm_ae, sdtm_dm, by = join_by(USUBJID)) |> nrow()
#> [1] 0
sdtm_dm |>
filter(ARMCD != "Scrnfail") |>
anti_join(sdtm_ae, by = join_by(USUBJID)) |>
nrow()
#> [1] 29
  • semi_join(dm, ae) keeps the rows of dm that have a partner in ae: S2 and S4. S4 comes once, not twice. A semi join never repeats a row.
  • anti_join(dm, ae) keeps the rows of dm that have no partner in ae: S1 and S3.
  • anti_join(ae, dm) turns the question around: AE records of a subject that is not in DM. Here that is S5 with RASH.

The last two lines ask the same questions of the pilot study. Every AE record has a subject in DM: the answer is 0 rows. Of the randomised subjects, 29 have no AE record at all.

anti_join() is a favourite QC check. An AE of a subject who is not in DM is a data issue: report it, do not drop it. A randomised subject with no AE may be correct, but a reviewer will ask. You can now answer with a list.

programs/05_5_same_names.R
# Chapter 5: name the key, or R joins by every column that has the same name
library(dplyr)
dm <- pharmaversesdtm::dm |>
select(STUDYID, DOMAIN, USUBJID, AGE, SEX)
ae <- pharmaversesdtm::ae |>
filter(USUBJID == "01-701-1015") |>
select(STUDYID, DOMAIN, USUBJID, AESEQ, AEDECOD)
# no by =: DOMAIN is "AE" on one side and "DM" on the other, so nothing matches
left_join(ae, dm)
#> Joining with `by = join_by(STUDYID, DOMAIN, USUBJID)`
#> # A tibble: 3 x 7
#> STUDYID DOMAIN USUBJID AESEQ AEDECOD AGE SEX
#> <chr> <chr> <chr> <dbl> <chr> <dbl> <chr>
#> 1 CDISCPILOT01 AE 01-701-1015 1 APPLICATION SITE ERYTHEMA NA <NA>
#> 2 CDISCPILOT01 AE 01-701-1015 2 APPLICATION SITE PRURITUS NA <NA>
#> 3 CDISCPILOT01 AE 01-701-1015 3 DIARRHOEA NA <NA>
# by = the key: the other same-named columns get .x and .y
left_join(ae, dm, by = join_by(USUBJID))
#> # A tibble: 3 x 9
#> STUDYID.x DOMAIN.x USUBJID AESEQ AEDECOD STUDYID.y DOMAIN.y AGE SEX
#> <chr> <chr> <chr> <dbl> <chr> <chr> <chr> <dbl> <chr>
#> 1 CDISCPILOT01 AE 01-701-1015 1 APPLIC~ CDISCPIL~ DM 63 F
#> 2 CDISCPILOT01 AE 01-701-1015 2 APPLIC~ CDISCPIL~ DM 63 F
#> 3 CDISCPILOT01 AE 01-701-1015 3 DIARRH~ CDISCPIL~ DM 63 F
# better: take from DM only the columns that you need
left_join(ae, dm |> select(USUBJID, AGE, SEX), by = join_by(USUBJID))
#> # A tibble: 3 x 7
#> STUDYID DOMAIN USUBJID AESEQ AEDECOD AGE SEX
#> <chr> <chr> <chr> <dbl> <chr> <dbl> <chr>
#> 1 CDISCPILOT01 AE 01-701-1015 1 APPLICATION SITE ERYTHEMA 63 F
#> 2 CDISCPILOT01 AE 01-701-1015 2 APPLICATION SITE PRURITUS 63 F
#> 3 CDISCPILOT01 AE 01-701-1015 3 DIARRHOEA 63 F

The first join is wrong, and it does not stop. Without by =, R joins by every column that both tables have. It says so in a message: Joining with by = join_by(STUDYID, DOMAIN, USUBJID). DOMAIN is "AE" in one table and "DM" in the other, so no row matches. Subject 01-701-1015 gets NA for age and sex, although DM has her: a 63-year-old woman.

The second join names the key. Now every AE row finds its subject. But STUDYID and DOMAIN are in both tables and are not the key. R keeps both copies and adds .x (from the first table) and .y (from the second). Nothing is overwritten.

The third join is the habit to keep. Take from the second table only the key and the columns that you need. Then there is nothing to rename.

Subject 01-701-1015 has 3 AE records and 66 concomitant medication records. Join them by subject alone, and see what happens.

programs/05_6_many_to_many.R
# Chapter 5: two tables with repeated keys pair every row with every row
library(dplyr)
ae <- pharmaversesdtm::ae |>
filter(USUBJID == "01-701-1015") |>
select(USUBJID, AEDECOD)
cm <- pharmaversesdtm::cm |>
filter(USUBJID == "01-701-1015") |>
select(USUBJID, CMDECOD)
nrow(ae)
#> [1] 3
nrow(cm)
#> [1] 66
ae_cm <- left_join(ae, cm, by = join_by(USUBJID))
#> Warning message:
#> In `left_join()`:
#> Detected an unexpected many-to-many relationship between `x` and `y`.
#> i Row 1 of `x` matches multiple rows in `y`.
#> i Row 1 of `y` matches multiple rows in `x`.
#> i If a many-to-many relationship is expected, set `relationship =
#> "many-to-many"` to silence this warning.
nrow(ae_cm)
#> [1] 198

Every AE row is paired with every CM row: 3 times 66 is 198 rows. The result is not data. No rule says that DIARRHOEA belongs to any of the 66 medications. dplyr warns: Detected an unexpected many-to-many relationship.

Treat this warning as an error. Almost always the key is not complete. AE and CM records can only be paired by a rule, for example “a medication that started on the day of the AE”. Chapter 11 joins records by dates.

If you really want every pair (it is rare), say so with relationship = "many-to-many". Then the warning goes away, because the reader can see that it is on purpose.

A join can add rows without a warning: a one-to-many join is legal. So count the rows before and after every join. Better still, tell R what you expect, and let it check.

programs/05_7_relationship.R
# Chapter 5: count the rows before and after a join, and let R check the keys
library(dplyr)
ae <- pharmaversesdtm::ae
dm <- pharmaversesdtm::dm |> select(USUBJID, AGE, SEX)
ex <- pharmaversesdtm::ex |> select(USUBJID, EXTRT, EXSTDTC)
# DM has one row per subject: the join keeps the number of AE rows
ae_dm <- left_join(ae, dm, by = join_by(USUBJID), relationship = "many-to-one")
c(before = nrow(ae), after = nrow(ae_dm))
#> before after
#> 1191 1191
# EX has several rows per subject: the same promise is now broken
ae_ex <- left_join(ae, ex, by = join_by(USUBJID), relationship = "many-to-one")
#> Error in `left_join()`:
#> ! Each row in `x` must match at most 1 row in `y`.
#> i Row 1 of `x` matches multiple rows in `y`.
  • relationship = "many-to-one" says: each row of the first table matches at most one row of the second. AE has many rows per subject, DM has one. The promise holds, and AE has 1191 rows before and after the join.
  • EX has several rows per subject (01-701-1015 has three exposure records). The same promise is now false, and R stops with an error. Without relationship =, this join would run and give more rows than AE has, and no message.
relationship = Each row of x matches Each row of y matches
"one-to-one" at most 1 row of y at most 1 row of x
"many-to-one" at most 1 row of y any number
"one-to-many" any number at most 1 row of x
"many-to-many" any number any number

When you add subject-level data (ADSL) to event records, write relationship = "many-to-one". It costs one line, and it turns a silent wrong row count into an error.

SDTM keeps non-standard variables in a separate data set, SUPPDM (supplemental qualifiers for DM). It is long: one row per subject and qualifier. QNAM holds the name of the qualifier and QVAL its value. Before you can join it to DM, you turn it into one row per subject.

programs/05_8_suppdm.R
# Chapter 5: turn SUPPDM qualifiers into columns, then join them to DM
library(dplyr)
suppdm <- pharmaversesdtm::suppdm
suppdm |>
filter(USUBJID == "01-701-1015") |>
select(USUBJID, QNAM, QLABEL, QVAL)
#> # A tibble: 6 x 4
#> USUBJID QNAM QLABEL QVAL
#> <chr> <chr> <chr> <chr>
#> 1 01-701-1015 COMPLT16 Completers of Week 16 Population Flag Y
#> 2 01-701-1015 COMPLT24 Completers of Week 24 Population Flag Y
#> 3 01-701-1015 COMPLT8 Completers of Week 8 Population Flag Y
#> 4 01-701-1015 EFFICACY Efficacy Population Flag Y
#> 5 01-701-1015 ITT Intent to Treat Population Flag Y
#> 6 01-701-1015 SAFETY Safety Population Flag Y
# one row per subject, one column per QNAM
flags <- suppdm |>
tidyr::pivot_wider(id_cols = USUBJID, names_from = QNAM, values_from = QVAL)
flags |> filter(USUBJID == "01-701-1015")
#> # A tibble: 1 x 7
#> USUBJID COMPLT16 COMPLT24 COMPLT8 EFFICACY ITT SAFETY
#> <chr> <chr> <chr> <chr> <chr> <chr> <chr>
#> 1 01-701-1015 Y Y Y Y Y Y
dm <- pharmaversesdtm::dm |>
select(USUBJID, ARM) |>
left_join(flags, by = join_by(USUBJID), relationship = "one-to-one")
c(dm = nrow(dm), flags = nrow(flags))
#> dm flags
#> 306 254
dm |> count(ARM, EFFICACY)
#> # A tibble: 7 x 3
#> ARM EFFICACY n
#> <chr> <chr> <int>
#> 1 Placebo Y 79
#> 2 Placebo <NA> 7
#> 3 Screen Failure <NA> 52
#> 4 Xanomeline High Dose Y 74
#> 5 Xanomeline High Dose <NA> 10
#> 6 Xanomeline Low Dose Y 81
#> 7 Xanomeline Low Dose <NA> 3
  • Subject 01-701-1015 has six rows in SUPPDM: six population flags, all Y.
  • tidyr::pivot_wider() makes one column per value of QNAM, filled from QVAL. Chapter 7 explains it. tidyr:: names the package, so you do not need library(tidyr).
  • The join is one to one: one row per subject on each side. relationship = "one-to-one" checks it.
  • DM has 306 subjects, SUPPDM only 254. The 52 screen failures have no qualifiers, so their flags are NA.
  • SUPPDM writes a flag only when it is Y. A subject who is not in the efficacy population has NA, not N: 7 on placebo, 10 on the high dose, 3 on the low dose. Turn NA into N on purpose, when the specification says so.
You need to Use
Put the rows of two tables under each other bind_rows(), with .id =
Add subject columns (ADSL) to event records left_join(..., relationship = "many-to-one")
Keep only the keys that are in both tables inner_join()
Keep every row of both tables full_join()
Find the rows with a partner, or without one semi_join(), anti_join()
Join a long qualifier table (SUPP—) pivot_wider(), then left_join()
  • bind_rows() stacks rows. .id = records the source. A missing column becomes NA.
  • The four joins differ only in the rows without a partner: inner, left, right, full.
  • semi_join() and anti_join() keep or drop rows. They answer “who is missing where?”
  • Always write by = join_by(...). Same-named columns that are not keys get .x and .y.
  • Repeated keys on both sides give every combination and a warning. Treat the warning as an error.
  • Count the rows before and after a join, and state the relationship = that you expect.

Exercise 5-1. In the tiny tables, run semi_join(ae, dm, by = join_by(USUBJID)). Which rows do you get, and why is S4 there twice now?

Exercise 5-2. How many subjects of the safety population of pharmaverseadam::adsl have no record in pharmaverseadam::adae? How many are in each arm (TRT01A)?

Exercise 5-3. Stack the AE and CM records of 01-701-1015 into one table with the columns USUBJID, TERM and START. Take TERM from AEDECOD and CMDECOD, and START from AESTDTC and CMSTDTC. Add a column DOMAIN with .id =. How many rows come from each domain?

Exercise 5-4. Join the qualifier COMPLT24 of SUPPDM to the safety population of ADSL, as a column COMP24FL. Check the number of rows before and after the join. How many week-24 completers are in each arm?

The answers are in Answers, Part I (chapters 5 to 8).

Found an error or have a question? Write to datastep.studio@gmail.com· Every program runs in DataStep Studio for R, the R extension for VS Code and Positron.

Data: the CDISC pilot study (cdiscpilot01), from the R packagespharmaversesdtm andpharmaverseadam (Apache License 2.0).

R is a project of the R Foundation. SAS and all other SAS Institute Inc. product or service names are registered trademarks or trademarks of SAS Institute Inc. This book is not affiliated with or endorsed by SAS Institute Inc. or the R Foundation. CDISC, SDTM and ADaM are trademarks of the Clinical Data Interchange Standards Consortium.

© 2026 DataStep Labs. All rights reserved.