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.
9.1 What SDTM looks like
Section titled “9.1 What SDTM looks like”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.
# Chapter 9: what one record of each SDTM domain means. Count the rows and the subjectslibrary(dplyr)
dm <- pharmaversesdtm::dmex <- pharmaversesdtm::exds <- pharmaversesdtm::dssuppdm <- pharmaversesdtm::suppdmae <- 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 dayae |> 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-02DM 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:
--SEQnumbers the rows of a subject. Subject 01-701-1015 has the AE rows withAESEQ1, 2 and 3. Together withUSUBJID, it is the key of a row. ADaM keeps it, so that every analysis value can be traced back to its source row.--DTCis a date as text, in ISO 8601 form (2014-01-03). Text can hold a partial date, such as2013-03. Chapter 11 shows what to do with partial dates.--DYis 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.
# 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::dsds |> 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-02Subject 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:
- 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. - Always a left join. Every subject of ADSL stays, and no subject is added. So every step has the same number of rows.
- 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.
9.3 Steps 010 to 031: treatment and dates
Section titled “9.3 Steps 010 to 031: treatment and dates”# Chapter 9: ADSL by hand, part 1. Steps 010 to 031: each step adds one variable, or one pairlibrary(dplyr)
dm <- pharmaversesdtm::dmex <- pharmaversesdtm::ex
# Step 010: one row per randomised subject, from DMadsl_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 itselfdose_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 EXex_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 treatmentex_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 subjectstibble( 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 subjectsadsl_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 RFENDTCadsl_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.
TRT01Pis the planned treatment (ARM).TRT01Ais the actual treatment (ACTARM). The small functiondose_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(). Thensummarise()with.by = USUBJIDkeeps 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()withoutna.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.
An open dose row
Section titled “An open dose row”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:
TRTEDTis the latest EX end date of the subject.- When the subject has an open dose row,
TRTEDTisRFENDTC, 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.
9.4 The third join: write a function
Section titled “9.4 The third join: write a function”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.
# Chapter 9: ADSL by hand, part 2. The same join comes a third time: write it oncelibrary(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 logadd_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 SAFETYadsl_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 DSds_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:
- The join.
left_join()keeps every row ofdata. The argumentrelationship = "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). - The build log.
setdiff()lists the columns that onlyaddhas.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(). Thesupp_031line shows the six new columns. Thentransmute()makes the four flags.SAFETY %in% "Y"isTRUEfor"Y"andFALSEforNA, so a subject without a SUPPDM row gets"N", notNA. - Step 050 takes the disposition event of DS:
EOSSTTisCOMPLETEDorDISCONTINUED, andDCSREASis 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) andRFENDT, the end of study as a date. Chapter 12 needsRFENDTto 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().
9.5 The same ADSL with admiral
Section titled “9.5 The same ADSL with admiral”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.
# Chapter 9: the same ADSL with admiral. Each derive_*() call is one step of the chainlibrary(dplyr)library(admiral)
dm <- pharmaversesdtm::dmds <- pharmaversesdtm::dssuppdm <- pharmaversesdtm::suppdmex <- 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 27saveRDS(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--DTCtext into a date.new_vars_prefix = "EXST"names the resultEXSTDT.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 andorderdoes not choose one.derive_var_merged_exist_flag()sets a flag when the added data has a row that meetscondition.false_valueandmissing_valuegive"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.
9.6 Double programming: do the two agree?
Section titled “9.6 Double programming: do the two agree?”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.
# Chapter 9: double programming. The ADSL by hand and the ADSL of admiral must agreelibrary(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.
9.7 Compare with the reference ADSL
Section titled “9.7 Compare with the reference ADSL”pharmaverseadam::adsl is an ADSL of the same study, made with the admiral template. Compare the variables that both ADSLs have.
# Chapter 9: compare our ADSL with the reference ADSL of pharmaverseadamlibrary(dplyr)library(diffdf)
vars <- c("USUBJID", "TRT01P", "TRT01A", "TRTSDT", "TRTEDT", "SAFFL", "EOSSTT")
# zap_label() removes the variable labels: here we compare values, not labelsours <- 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
AGEGR1uses the groups of the pilot SAP. - The reference has no
ITTFL,EFFFLorCOMP24FL.
9.8 Summary
Section titled “9.8 Summary”- 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()givesNAwhen one value isNA. Decide what a missing value means before you writena.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()andderive_var_merged_exist_flag(). diffdf()shows that two programs agree. When they do not, find the rule behind each difference.
9.9 Exercises
Section titled “9.9 Exercises”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.