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.
5.1 Two tiny tables
Section titled “5.1 Two tiny tables”# Chapter 5: two tiny tables, small enough to check every join by eyelibrary(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 74ae#> # A tibble: 4 x 2#> USUBJID AEDECOD#> <chr> <chr>#> 1 S2 HEADACHE#> 2 S4 DIZZINESS#> 3 S4 NAUSEA#> 4 S5 RASHtibble() 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.
5.2 Stack rows: bind_rows()
Section titled “5.2 Stack rows: bind_rows()”To stack tables is to put the rows of one table under the rows of another.
# 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 frombind_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 NAlate <- 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 keybind_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 80bind_rows(DM = dm, MORE = more, .id = "SOURCE")puts the 2 rows ofmoreunder the 4 rows ofdm. The names before=go into a new columnSOURCE, so each row says where it came from.- A column that only one table has is kept. The rows of the other table get
NAin it.S6has noAGE, and S1 to S4 have noSEX. bind_rows()does not sort. To mix the rows in key order, sort after the stack witharrange().
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.
5.3 Join side by side: the four joins
Section titled “5.3 Join side by side: the four joins”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.
# Chapter 5: the four joins differ only in the rows without a partnerlibrary(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 NAUSEAleft_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 NAUSEAright_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 RASHfull_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, writejoin_by(USUBJID == SUBJ). For a key of two columns, writejoin_by(USUBJID, VISITNUM).- S4 has one row in
dmand two inae. 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 itsAEDECODisNA. S5 has no row indm, so itsAGEisNA. - 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.
# Chapter 5: semi_join() and anti_join() find who is missing wherelibrary(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 74anti_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 71anti_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 studysdtm_dm <- pharmaversesdtm::dmsdtm_ae <- pharmaversesdtm::aeanti_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] 29semi_join(dm, ae)keeps the rows ofdmthat have a partner inae: S2 and S4. S4 comes once, not twice. A semi join never repeats a row.anti_join(dm, ae)keeps the rows ofdmthat have no partner inae: 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.
5.5 Name the key: same-named columns
Section titled “5.5 Name the key: same-named columns”# Chapter 5: name the key, or R joins by every column that has the same namelibrary(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 matchesleft_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 .yleft_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 needleft_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 FThe 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.
5.6 Repeated keys on both sides
Section titled “5.6 Repeated keys on both sides”Subject 01-701-1015 has 3 AE records and 66 concomitant medication records. Join them by subject alone, and see what happens.
# Chapter 5: two tables with repeated keys pair every row with every rowlibrary(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] 3nrow(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] 198Every 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.
5.7 Count the rows before and after
Section titled “5.7 Count the rows before and after”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.
# Chapter 5: count the rows before and after a join, and let R check the keyslibrary(dplyr)
ae <- pharmaversesdtm::aedm <- 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 rowsae_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 brokenae_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.
5.8 Join supplemental qualifiers: SUPPDM
Section titled “5.8 Join supplemental qualifiers: SUPPDM”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.
# Chapter 5: turn SUPPDM qualifiers into columns, then join them to DMlibrary(dplyr)
suppdm <- pharmaversesdtm::suppdmsuppdm |> 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 QNAMflags <- 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 ofQNAM, filled fromQVAL. Chapter 7 explains it.tidyr::names the package, so you do not needlibrary(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 hasNA, notN: 7 on placebo, 10 on the high dose, 3 on the low dose. TurnNAintoNon purpose, when the specification says so.
5.9 Which tool for which job
Section titled “5.9 Which tool for which job”| 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() |
5.10 Summary
Section titled “5.10 Summary”bind_rows()stacks rows..id =records the source. A missing column becomesNA.- The four joins differ only in the rows without a partner: inner, left, right, full.
semi_join()andanti_join()keep or drop rows. They answer “who is missing where?”- Always write
by = join_by(...). Same-named columns that are not keys get.xand.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.
5.11 Exercises
Section titled “5.11 Exercises”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.