Skip to content

ADSL, the subject level data set

ADSL has one row per subject. Every other ADaM data set takes its treatment, its population flags and its key dates from ADSL. So an error in ADSL repeats in every table of the study.

In this chapter you build ADSL three times. First by hand, with the dplyr verbs of Part I, as a chain of small steps: each step adds one variable. Then with admiral, the package of the pharmaverse for ADaM. Last, you let diffdf show that the two versions agree, and you compare your ADSL with the reference ADSL of pharmaverseadam.

ADaM is built from SDTM (the Study Data Tabulation Model), the standard form of the collected data. Each SDTM data set is a domain, and each domain has one row per something. Know what one row means before you count anything.

programs/09_1_domains.R
# Chapter 9: what one record of each SDTM domain means. Count the rows and the subjects
library(dplyr)
dm <- pharmaversesdtm::dm
ex <- pharmaversesdtm::ex
ds <- pharmaversesdtm::ds
suppdm <- pharmaversesdtm::suppdm
ae <- pharmaversesdtm::ae
tibble(
domain = c("DM", "EX", "DS", "SUPPDM", "AE"),
rows = c(nrow(dm), nrow(ex), nrow(ds), nrow(suppdm), nrow(ae)),
subjects = c(
n_distinct(dm$USUBJID), n_distinct(ex$USUBJID), n_distinct(ds$USUBJID),
n_distinct(suppdm$USUBJID), n_distinct(ae$USUBJID)
)
)
#> # A tibble: 5 x 3
#> domain rows subjects
#> <chr> <int> <int>
#> 1 DM 306 306
#> 2 EX 591 254
#> 3 DS 850 306
#> 4 SUPPDM 1197 254
#> 5 AE 1191 225
# --SEQ numbers the records of a subject, --DTC is a date as text,
# --DY is the study day
ae |>
filter(USUBJID == "01-701-1015") |>
select(USUBJID, AESEQ, AEDECOD, AESTDTC, AESTDY)
#> # A tibble: 3 x 5
#> USUBJID AESEQ AEDECOD AESTDTC AESTDY
#> <chr> <dbl> <chr> <chr> <dbl>
#> 1 01-701-1015 1 APPLICATION SITE ERYTHEMA 2014-01-03 2
#> 2 01-701-1015 2 APPLICATION SITE PRURITUS 2014-01-03 2
#> 3 01-701-1015 3 DIARRHOEA 2014-01-09 8
ex |>
filter(USUBJID == "01-701-1015") |>
select(USUBJID, EXSEQ, EXTRT, EXDOSE, EXSTDTC, EXENDTC)
#> # A tibble: 3 x 6
#> USUBJID EXSEQ EXTRT EXDOSE EXSTDTC EXENDTC
#> <chr> <dbl> <chr> <dbl> <chr> <chr>
#> 1 01-701-1015 1 PLACEBO 0 2014-01-02 2014-01-16
#> 2 01-701-1015 2 PLACEBO 0 2014-01-17 2014-06-18
#> 3 01-701-1015 3 PLACEBO 0 2014-06-19 2014-07-02

DM has one row per subject: 306 subjects, screen failures included. EX has 591 rows for 254 subjects: one row per dosing interval. AE has 1191 rows for 225 subjects: one row per adverse event.

Three kinds of variables come back in many domains. Their first two letters are the domain, so AESEQ belongs to AE and EXSTDTC to EX:

  • --SEQ numbers the rows of a subject. Subject 01-701-1015 has the AE rows with AESEQ 1, 2 and 3. Together with USUBJID, it is the key of a row. ADaM keeps it, so that every analysis value can be traced back to its source row.
  • --DTC is a date as text, in ISO 8601 form (2014-01-03). Text can hold a partial date, such as 2013-03. Chapter 11 shows what to do with partial dates.
  • --DY is the study day: the number of days from the first dose. The first dose is day 1. There is no day 0.

SUPPDM holds the values that have no standard column in DM. It is tall: one row per subject and value, with the name of the value in QNAM and the value in QVAL.

programs/09_2_suppdm.R
# Chapter 9: SUPPDM is tall. It holds only the "Y" values, so a missing record means "no"
library(dplyr)
suppdm <- pharmaversesdtm::suppdm
suppdm |>
filter(USUBJID == "01-701-1015") |>
select(USUBJID, QNAM, QVAL)
#> # A tibble: 6 x 3
#> USUBJID QNAM QVAL
#> <chr> <chr> <chr>
#> 1 01-701-1015 COMPLT16 Y
#> 2 01-701-1015 COMPLT24 Y
#> 3 01-701-1015 COMPLT8 Y
#> 4 01-701-1015 EFFICACY Y
#> 5 01-701-1015 ITT Y
#> 6 01-701-1015 SAFETY Y
suppdm |>
count(QNAM, QVAL)
#> # A tibble: 6 x 3
#> QNAM QVAL n
#> <chr> <chr> <int>
#> 1 COMPLT16 Y 147
#> 2 COMPLT24 Y 118
#> 3 COMPLT8 Y 190
#> 4 EFFICACY Y 234
#> 5 ITT Y 254
#> 6 SAFETY Y 254
ds <- pharmaversesdtm::ds
ds |>
filter(USUBJID == "01-701-1015") |>
select(USUBJID, DSSEQ, DSCAT, DSDECOD, DSSTDTC)
#> # A tibble: 3 x 5
#> USUBJID DSSEQ DSCAT DSDECOD DSSTDTC
#> <chr> <int> <chr> <chr> <chr>
#> 1 01-701-1015 1 PROTOCOL MILESTONE RANDOMIZED 2014-01-02
#> 2 01-701-1015 2 DISPOSITION EVENT COMPLETED 2014-07-02
#> 3 01-701-1015 3 OTHER EVENT FINAL LAB VISIT 2014-07-02

Subject 01-701-1015 has six rows, all with QVAL = "Y". The pilot stores only the Y values: SAFETY and ITT have 254 rows each, EFFICACY 234 and COMPLT24 118. A missing row means “no”. ADSL needs these values as columns, one row per subject. Chapter 7 shows how pivot_wider() turns rows into columns.

DS (disposition) tells what happened to a subject. Subject 01-701-1015 was randomised, then completed the study. The row with DSCAT = "DISPOSITION EVENT" is the one that says how the subject left.

9.2 The plan: one numbered step per variable

Section titled “9.2 The plan: one numbered step per variable”

Write ADSL as a chain. Each step reads the data frame of the step before and adds one variable, or one pair that belongs together. The steps have numbers with gaps (010, 020, 030, 031), so you can put a new step between two others later.

Step Adds From
010 one row per randomised subject DM
020 TRT01P, TRT01PN, TRT01A, TRT01AN: planned and actual treatment ARM, ACTARM, already in ADSL
030 TRTSDT, the date of the first dose EX
031 TRTEDT, the date of the last dose EX, and RFENDTC for open dose rows
040 SAFFL, ITTFL, EFFFL, COMP24FL: the population flags SUPPDM
050 EOSSTT, DCSREAS: end of study status and reason DS
060, 061 AGEGR1, the age group; RFENDT, the end of study as a date variables already in ADSL

Three rules make the chain:

  1. A small source table for each new variable. It has one row per subject, and only the key and the new variable. Its name says where it comes from: ex_trtsdt, ds_eos.
  2. Always a left join. Every subject of ADSL stays, and no subject is added. So every step has the same number of rows.
  3. A value made from ADSL itself is a mutate(), not a join. Steps 020 and 060 only use columns that ADSL already has.

Why so many steps? Each step is a data frame of its own. When a value is wrong, you look at two neighbouring steps and see which step made it.

programs/09_3_adsl_hand.R
# Chapter 9: ADSL by hand, part 1. Steps 010 to 031: each step adds one variable, or one pair
library(dplyr)
dm <- pharmaversesdtm::dm
ex <- pharmaversesdtm::ex
# Step 010: one row per randomised subject, from DM
adsl_010 <- dm |>
filter(ARMCD != "Scrnfail") |>
select(
STUDYID, USUBJID, SUBJID, SITEID, AGE, AGEU, SEX, RACE, ETHNIC,
ARM, ACTARM, RFSTDTC, RFENDTC
)
# Step 020: planned and actual treatment, text and number. From ADSL itself
dose_of <- function(arm) {
case_when(
arm == "Placebo" ~ 0,
arm == "Xanomeline Low Dose" ~ 54,
arm == "Xanomeline High Dose" ~ 81
)
}
adsl_020 <- adsl_010 |>
mutate(
TRT01P = ARM, TRT01PN = dose_of(ARM),
TRT01A = ACTARM, TRT01AN = dose_of(ACTARM)
)
# Step 030: TRTSDT, the date of the first dose, from EX
ex_dt <- ex |>
mutate(EXSTDT = lubridate::ymd(EXSTDTC), EXENDT = lubridate::ymd(EXENDTC))
ex_trtsdt <- ex_dt |>
summarise(TRTSDT = min(EXSTDT), .by = USUBJID)
adsl_030 <- adsl_020 |>
left_join(ex_trtsdt, by = join_by(USUBJID))
# Step 031: TRTEDT, the date of the last dose. max() without na.rm gives NA
# when one EX record has no end date (an open dose record). Then the end of
# study, RFENDTC, is the end of treatment
ex_trtedt <- ex_dt |>
summarise(EXLAST = max(EXENDT), .by = USUBJID)
adsl_031 <- adsl_030 |>
left_join(ex_trtedt, by = join_by(USUBJID)) |>
mutate(TRTEDT = coalesce(EXLAST, lubridate::ymd(RFENDTC)))
# The build log: every step keeps the same subjects
tibble(
step = c("010", "020", "030", "031"),
rows = c(nrow(adsl_010), nrow(adsl_020), nrow(adsl_030), nrow(adsl_031)),
columns = c(ncol(adsl_010), ncol(adsl_020), ncol(adsl_030), ncol(adsl_031))
)
#> # A tibble: 4 x 3
#> step rows columns
#> <chr> <int> <int>
#> 1 010 254 13
#> 2 020 254 17
#> 3 030 254 18
#> 4 031 254 20
adsl_031 |>
filter(USUBJID == "01-701-1015") |>
select(USUBJID, TRT01P, TRT01AN, TRTSDT, TRTEDT)
#> # A tibble: 1 x 5
#> USUBJID TRT01P TRT01AN TRTSDT TRTEDT
#> <chr> <chr> <dbl> <date> <date>
#> 1 01-701-1015 Placebo 0 2014-01-02 2014-07-02
# Planned and actual treatment differ for some subjects
adsl_031 |>
count(TRT01P, TRT01A)
#> # A tibble: 4 x 3
#> TRT01P TRT01A n
#> <chr> <chr> <int>
#> 1 Placebo Placebo 86
#> 2 Xanomeline High Dose Xanomeline High Dose 72
#> 3 Xanomeline High Dose Xanomeline Low Dose 12
#> 4 Xanomeline Low Dose Xanomeline Low Dose 84
# The open dose records: TRTEDT is RFENDTC
adsl_031 |>
filter(is.na(EXLAST)) |>
select(USUBJID, RFENDTC, EXLAST, TRTEDT)
#> # A tibble: 6 x 4
#> USUBJID RFENDTC EXLAST TRTEDT
#> <chr> <chr> <date> <date>
#> 1 01-704-1233 2013-07-14 NA 2013-07-14
#> 2 01-705-1018 2013-07-12 NA 2013-07-12
#> 3 01-705-1031 2014-05-11 NA 2014-05-11
#> 4 01-705-1303 2014-06-02 NA 2014-06-02
#> 5 01-705-1377 2014-03-07 NA 2014-03-07
#> 6 01-705-1382 2013-05-13 NA 2013-05-13
adsl_031 <- select(adsl_031, -EXLAST)
saveRDS(adsl_031, "data/adsl_031.rds")

Read the program step by step:

  • Step 010 keeps the randomised subjects: ARMCD != "Scrnfail" removes the screen failures. It keeps only the columns that ADSL needs.
  • Step 020 makes the treatment variables. TRT01P is the planned treatment (ARM). TRT01A is the actual treatment (ACTARM). The small function dose_of() turns an arm into its dose, 0, 54 or 81, so that the same rule is written once for both columns. Chapter 8 explains functions. The count shows why there are two variables: 12 subjects were planned for the high dose but took the low dose.
  • Step 030 turns the text dates of EX into real dates with lubridate::ymd(). Then summarise() with .by = USUBJID keeps the earliest start date of each subject (chapter 4). A left join adds it to ADSL.
  • Step 031 finds the last dose. It uses max() without na.rm = TRUE. That is on purpose: see the next section.
  • The build log is a small table with the rows and columns of each step. Every step has 254 rows, so no step lost or gained a subject.

saveRDS() stores one R object in a file, and readRDS() reads it back. The program saves step 031 as data/adsl_031.rds, so that the next program can start from it.

Six subjects have an EX row without an end date. Such an open dose row means that the subject was still taking the study drug when the subject left the study. The rule of this book for TRTEDT:

  • TRTEDT is the latest EX end date of the subject.
  • When the subject has an open dose row, TRTEDT is RFENDTC, the end of the study.

In R, max() of a vector with one NA is NA. So EXLAST is NA exactly for the subjects with an open dose row. coalesce(EXLAST, ymd(RFENDTC)) takes EXLAST when it exists, and the end of study when it does not. For subject 01-704-1233, TRTEDT becomes 2013-07-14.

Step 030 was a left join. Step 031 was a left join. Step 040 needs the same left join again. When the same code comes a third time, write it once, as a function.

programs/09_4_add_vars.R
# Chapter 9: ADSL by hand, part 2. The same join comes a third time: write it once
library(dplyr)
adsl_031 <- readRDS("data/adsl_031.rds")
# Add the columns of `add` to `data`. Each subject must have at most one row in
# both (else: an error), and the function writes one line of the build log
add_vars <- function(data, add, step) {
out <- data |>
left_join(add, by = join_by(USUBJID), relationship = "one-to-one")
new <- setdiff(names(add), names(data))
message(step, ": ", nrow(out), " rows, +", length(new), " variables: ",
paste(new, collapse = " "))
out
}
# Step 040: the population flags, from SUPPDM. No record means "N"
supp <- pharmaversesdtm::suppdm |>
tidyr::pivot_wider(id_cols = USUBJID, names_from = QNAM, values_from = QVAL)
supp_flags <- adsl_031 |>
select(USUBJID) |>
add_vars(supp, "supp_031") |>
transmute(
USUBJID,
SAFFL = if_else(SAFETY %in% "Y", "Y", "N"),
ITTFL = if_else(ITT %in% "Y", "Y", "N"),
EFFFL = if_else(EFFICACY %in% "Y", "Y", "N"),
COMP24FL = if_else(COMPLT24 %in% "Y", "Y", "N")
)
#> supp_031: 254 rows, +6 variables: COMPLT16 COMPLT24 COMPLT8 EFFICACY ITT SAFETY
adsl_040 <- add_vars(adsl_031, supp_flags, "adsl_040")
#> adsl_040: 254 rows, +4 variables: SAFFL ITTFL EFFFL COMP24FL
# Step 050: end of study status and the reason to discontinue, from DS
ds_eos <- pharmaversesdtm::ds |>
filter(DSCAT == "DISPOSITION EVENT") |>
transmute(
USUBJID,
EOSSTT = if_else(DSDECOD == "COMPLETED", "COMPLETED", "DISCONTINUED"),
DCSREAS = if_else(DSDECOD == "COMPLETED", NA, DSDECOD)
)
adsl_050 <- add_vars(adsl_040, ds_eos, "adsl_050")
#> adsl_050: 254 rows, +2 variables: EOSSTT DCSREAS
# Steps 060 and 061: values made from ADSL itself, with mutate()
adsl_hand <- adsl_050 |>
mutate(
AGEGR1 = case_when(AGE < 65 ~ "<65", AGE <= 80 ~ "65-80", AGE > 80 ~ ">80")
) |>
mutate(RFENDT = lubridate::ymd(RFENDTC))
adsl_hand |>
filter(USUBJID == "01-701-1015") |>
select(USUBJID, SAFFL, ITTFL, EFFFL, COMP24FL, EOSSTT, AGEGR1, RFENDT)
#> # A tibble: 1 x 8
#> USUBJID SAFFL ITTFL EFFFL COMP24FL EOSSTT AGEGR1 RFENDT
#> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <date>
#> 1 01-701-1015 Y Y Y Y COMPLETED <65 2014-07-02
adsl_hand |>
count(EOSSTT, DCSREAS)
#> # A tibble: 9 x 3
#> EOSSTT DCSREAS n
#> <chr> <chr> <int>
#> 1 COMPLETED <NA> 110
#> 2 DISCONTINUED ADVERSE EVENT 92
#> 3 DISCONTINUED DEATH 3
#> 4 DISCONTINUED LACK OF EFFICACY 4
#> 5 DISCONTINUED LOST TO FOLLOW-UP 2
#> 6 DISCONTINUED PHYSICIAN DECISION 3
#> 7 DISCONTINUED PROTOCOL VIOLATION 6
#> 8 DISCONTINUED STUDY TERMINATED BY SPONSOR 7
#> 9 DISCONTINUED WITHDRAWAL BY SUBJECT 27
saveRDS(adsl_hand, "data/adsl_hand.rds")

add_vars() does one job, the job of the merge utility of most companies:

  1. The join. left_join() keeps every row of data. The argument relationship = "one-to-one" is a promise: each subject has at most one row on each side. When the promise is broken, R stops with an error. Without it, a second row for a subject would copy the ADSL row in silence (chapter 5 shows how).
  2. The build log. setdiff() lists the columns that only add has. message() writes one line with the step, the rows and the new variables. Read the lines from the top, and you know what each step added.

Then the steps:

  • Step 040 first turns SUPPDM into one row per subject with tidyr::pivot_wider(). The supp_031 line shows the six new columns. Then transmute() makes the four flags. SAFETY %in% "Y" is TRUE for "Y" and FALSE for NA, so a subject without a SUPPDM row gets "N", not NA.
  • Step 050 takes the disposition event of DS: EOSSTT is COMPLETED or DISCONTINUED, and DCSREAS is the reason. 110 subjects completed the study, and 92 left because of an adverse event.
  • Steps 060 and 061 use mutate(): the age group of the pilot SAP (<65, 65-80, >80) and RFENDT, the end of study as a date. Chapter 12 needs RFENDT to censor subjects.

The program writes tidyr::pivot_wider() and lubridate::ymd() with the package name in front. :: uses one function of a package without library().

admiral is the open-source R package of the pharmaverse for ADaM. Each of its derive_*() functions does one derivation that many studies need. You still decide the rules. admiral gives you tested code for the mechanism.

programs/09_5_adsl_admiral.R
# Chapter 9: the same ADSL with admiral. Each derive_*() call is one step of the chain
library(dplyr)
library(admiral)
dm <- pharmaversesdtm::dm
ds <- pharmaversesdtm::ds
suppdm <- pharmaversesdtm::suppdm
ex <- pharmaversesdtm::ex |>
derive_vars_dt(dtc = EXSTDTC, new_vars_prefix = "EXST") |>
derive_vars_dt(dtc = EXENDTC, new_vars_prefix = "EXEN")
dose_of <- function(arm) {
case_when(
arm == "Placebo" ~ 0,
arm == "Xanomeline Low Dose" ~ 54,
arm == "Xanomeline High Dose" ~ 81
)
}
adsl <- dm |>
filter(ARMCD != "Scrnfail") |>
select(
STUDYID, USUBJID, SUBJID, SITEID, AGE, AGEU, SEX, RACE, ETHNIC,
ARM, ACTARM, RFSTDTC, RFENDTC
) |>
mutate(
TRT01P = ARM, TRT01PN = dose_of(ARM),
TRT01A = ACTARM, TRT01AN = dose_of(ACTARM)
) |>
# TRTSDT: the first EX record by start date
derive_vars_merged(
dataset_add = ex, by_vars = exprs(USUBJID),
new_vars = exprs(TRTSDT = EXSTDT),
order = exprs(EXSTDT, EXSEQ), mode = "first"
) |>
# TRTEDT: the last EX end date, or RFENDTC when a record is open
derive_vars_merged(
dataset_add = ex, by_vars = exprs(USUBJID), filter_add = !is.na(EXENDT),
new_vars = exprs(TRTEDT = EXENDT),
order = exprs(EXENDT, EXSEQ), mode = "last"
) |>
derive_var_merged_exist_flag(
dataset_add = ex, by_vars = exprs(USUBJID),
new_var = OPENFL, condition = is.na(EXENDT)
) |>
mutate(
TRTEDT = if_else(OPENFL %in% "Y", convert_dtc_to_dt(RFENDTC), TRTEDT)
) |>
select(-OPENFL) |>
# the population flags: Y when SUPPDM has the record, else N
derive_var_merged_exist_flag(
dataset_add = suppdm, by_vars = exprs(USUBJID), new_var = SAFFL,
condition = QNAM == "SAFETY" & QVAL == "Y",
false_value = "N", missing_value = "N"
) |>
derive_var_merged_exist_flag(
dataset_add = suppdm, by_vars = exprs(USUBJID), new_var = ITTFL,
condition = QNAM == "ITT" & QVAL == "Y",
false_value = "N", missing_value = "N"
) |>
derive_var_merged_exist_flag(
dataset_add = suppdm, by_vars = exprs(USUBJID), new_var = EFFFL,
condition = QNAM == "EFFICACY" & QVAL == "Y",
false_value = "N", missing_value = "N"
) |>
derive_var_merged_exist_flag(
dataset_add = suppdm, by_vars = exprs(USUBJID), new_var = COMP24FL,
condition = QNAM == "COMPLT24" & QVAL == "Y",
false_value = "N", missing_value = "N"
) |>
# end of study status and reason, from the disposition event of DS
derive_vars_merged(
dataset_add = ds, by_vars = exprs(USUBJID),
filter_add = DSCAT == "DISPOSITION EVENT",
new_vars = exprs(
EOSSTT = if_else(DSDECOD == "COMPLETED", "COMPLETED", "DISCONTINUED"),
DCSREAS = if_else(DSDECOD == "COMPLETED", NA, DSDECOD)
)
) |>
mutate(
AGEGR1 = case_when(AGE < 65 ~ "<65", AGE <= 80 ~ "65-80", AGE > 80 ~ ">80"),
RFENDT = convert_dtc_to_dt(RFENDTC)
)
dim(adsl)
#> [1] 254 27
saveRDS(adsl, "data/adsl.rds")
Step By hand admiral
030 summarise(min()), then left_join() derive_vars_merged(..., order = exprs(EXSTDT, EXSEQ), mode = "first")
031 summarise(max()), left_join(), coalesce() derive_vars_merged(..., mode = "last"), then derive_var_merged_exist_flag() finds the open rows
040 pivot_wider(), if_else() derive_var_merged_exist_flag(), once per flag
050 filter(), transmute(), left_join() derive_vars_merged(..., filter_add = DSCAT == "DISPOSITION EVENT")

A few words of admiral:

  • exprs() holds column names and expressions without running them. admiral runs them later, inside the data.
  • derive_vars_dt() turns a --DTC text into a date. new_vars_prefix = "EXST" names the result EXSTDT.
  • derive_vars_merged() is a left join that can first filter the added data (filter_add) and keep one row per subject (order, mode). It warns when the added data has more than one row per subject and order does not choose one.
  • derive_var_merged_exist_flag() sets a flag when the added data has a row that meets condition. false_value and missing_value give "N" for a subject with no such row.

The result has 254 rows and 27 columns. The program saves it as data/adsl.rds: this is the ADSL that chapters 10 to 12 read.

Two programs that follow the same rules must give the same data. diffdf() of the package diffdf compares two data frames row by row. keys names the columns that identify a row.

programs/09_6_adsl_compare.R
# Chapter 9: double programming. The ADSL by hand and the ADSL of admiral must agree
library(diffdf)
adsl_hand <- readRDS("data/adsl_hand.rds")
adsl <- readRDS("data/adsl.rds")
diffdf(adsl_hand, adsl, keys = "USUBJID")
#> No issues were found!

“No issues were found!”: the same rows, the same columns, the same values. When diffdf() finds a difference, it lists the columns and the rows that differ, as the next section shows. Chapter 15 uses diffdf() for the full QC of a study.

pharmaverseadam::adsl is an ADSL of the same study, made with the admiral template. Compare the variables that both ADSLs have.

programs/09_7_adsl_reference.R
# Chapter 9: compare our ADSL with the reference ADSL of pharmaverseadam
library(dplyr)
library(diffdf)
vars <- c("USUBJID", "TRT01P", "TRT01A", "TRTSDT", "TRTEDT", "SAFFL", "EOSSTT")
# zap_label() removes the variable labels: here we compare values, not labels
ours <- readRDS("data/adsl.rds") |>
select(all_of(vars)) |>
haven::zap_label()
ref <- pharmaverseadam::adsl |>
filter(ARMCD != "Scrnfail") |>
select(all_of(vars)) |>
haven::zap_label()
diffdf(ours, ref, keys = "USUBJID")
#> Warning message:
#> In diffdf(ours, ref, keys = "USUBJID") :
#> Not all Values Compared Equal
#> Differences found between the objects!
#>
#> Summary of BASE and COMPARE
#> ==================================================================
#> PROPERTY BASE COMP
#> ------------------------------------------------------------------
#> Name ours ref
#> Class "tbl_df, tbl, data.frame" "tbl_df, tbl, data.frame"
#> Rows(#) 254 254
#> Columns(#) 7 7
#> ------------------------------------------------------------------
#>
#>
#> Not all Values Compared Equal
#> =============================
#> Variable No of Differences
#> -----------------------------
#> TRTEDT 6
#> -----------------------------
#>
#>
#> ===============================================
#> VARIABLE USUBJID BASE COMPARE
#> -----------------------------------------------
#> TRTEDT 01-704-1233 2013-07-14 2013-04-04
#> TRTEDT 01-705-1018 2013-07-12 <NA>
#> TRTEDT 01-705-1031 2014-05-11 2013-12-18
#> TRTEDT 01-705-1303 2014-06-02 2013-12-30
#> TRTEDT 01-705-1377 2014-03-07 2014-01-25
#> TRTEDT 01-705-1382 2013-05-13 <NA>
#> -----------------------------------------------
#>

haven::zap_label() removes the labels of the columns: a label is a short text that describes a column, such as “Date of First Exposure to Treatment”. Here you compare values, so the labels must not count.

Six values of TRTEDT differ, and they are the six subjects with an open dose row of section 9.3. The reference takes the latest end date that exists and ignores the open row. So for 01-704-1233 it gives 2013-04-04, and for 01-705-1018 and 01-705-1382 it gives no date at all, because their only end dates are missing. Your rule follows the SAP of this book. Neither program has a bug: the two programs follow two different rules. When a QC comparison finds a difference, find the rule that made it before you change any code.

Three more differences are not in the comparison, and each one is a rule:

  • The reference keeps the 52 screen failures. Your ADSL keeps only the randomised subjects.
  • The reference uses the age groups of the admiral template. Your AGEGR1 uses the groups of the pilot SAP.
  • The reference has no ITTFL, EFFFL or COMP24FL.
  • SDTM has one domain per topic. Know what one row means: a subject (DM), a dosing interval (EX), an event (AE).
  • Build ADSL as a chain of numbered steps. Each step adds one variable with a left join or a mutate(), and keeps every subject.
  • max() gives NA when one value is NA. Decide what a missing value means before you write na.rm = TRUE.
  • When the same code comes a third time, write a function. relationship = "one-to-one" lets the join check the keys.
  • admiral does the same steps with derive_vars_merged(), derive_vars_dt() and derive_var_merged_exist_flag().
  • diffdf() shows that two programs agree. When they do not, find the rule behind each difference.

Exercise 9-1. Add TRTDURD, the duration of treatment in days: TRTEDT - TRTSDT + 1. Why + 1? What is TRTDURD for 01-701-1015?

Exercise 9-2. Add WEIGHTBL, the weight at baseline, as step 070. Take the VS rows with VSTESTCD == "WEIGHT" and VSBLFL == "Y", join them with the promise of add_vars(), and round to one decimal. Which subject has no WEIGHTBL?

Exercise 9-3. Join EX to ADSL with left_join(adsl, ex, by = join_by(USUBJID)). How many rows do you get, and why? How many subjects have more than one EX row? What happens with relationship = "one-to-one"?

Exercise 9-4. Use the simple rule “TRTEDT is the latest EX end date that exists”: max(EXENDT, na.rm = TRUE). Which subjects get another TRTEDT? What does R say about two of them?

The answers are in Answers, Part II.

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.