ADaM from first principles
Lesson 4 — Clinical Programming with R
Learning objectives
- Explain where ADaM sits between SDTM and the TLFs, and why it exists
- Build ADSL from SDTM: treatment variables, dates, population flags, groupings
- Build a BDS dataset with
PARAMCD,PARAM,AVALandAVALC - Derive
ABLFL,BASE,CHGandPCHGcorrectly, including the edge cases - Derive analysis dates, study days and analysis record flags
- Handle missing data and date imputation with the required flags
- Build ADLB (ranges, grades, shift), ADAE (treatment emergence) and ADTTE (censoring)
- Merge, sort and key datasets safely, and keep traceability
- Validate every derivation and run an independent QC comparison
Every example here is self-contained. The datasets are created in code with tibble(), so you can paste any block into R and run it with no external files. Nothing requires admiral.
That is deliberate: the goal is to understand the derivation logic before reaching for a package that performs it for you. Lesson 5 then shows the same derivations using admiral, and you will read that lesson very differently having done this one.
This is teaching code, not production code. It omits the edge-case handling, the specification linkage and the validation evidence that a regulatory deliverable requires. See the closing section and Validation and testing.
Throughout this chapter, watch which kind of rule you are reading. They have very different authority:
| Marker | Meaning |
|---|---|
| CDISC requirement | Defined in the ADaMIG. Not negotiable. |
| Common convention | What most sponsors do. Usually right, but check. |
| Study-specific (SAP) | Your statistical analysis plan decides. Never assume. |
Mixing these up is the most common cause of a derivation that is technically correct and clinically wrong. Every example below labels its rules.
Part 1 — Fundamentals
Where ADaM sits
Clinical data moves through three layers on its way to a regulatory submission.
COLLECTED TABULATED ANALYSIS-READY REPORTED
───────── ───────── ────────────── ────────
┌─────────┐ ┌─────────┐ ┌─────────┐ ┌─────────┐
│ EDC │ ──────▶ │ SDTM │ ──────▶ │ ADaM │ ──────▶ │ TLF │
│ (CRF) │ map │ DM, AE │ derive │ ADSL │ summarise│ Tables │
│ │ │ LB, EX │ │ ADAE │ │ Listings│
└─────────┘ └─────────┘ │ ADLB │ │ Figures │
└─────────┘ └─────────┘
"what was "what the analysis "what the
collected" needs" reader sees"| Layer | Question it answers | Rule |
|---|---|---|
| SDTM | What was collected? | Represent data as collected. No derivation. |
| ADaM | What does the analysis need? | Derive freely, but record how. |
| TLF | What does the reader see? | Summarise only. No new derivation. |
The boundary matters. If you find yourself computing a change from baseline in SDTM, you are in the wrong layer. If you find yourself deciding which record is baseline inside a table program, you are also in the wrong layer — that belongs in ADaM, where it is derived once and reused by every table.
The two ADaM principles
Everything in the ADaMIG follows from two ideas.
1. Analysis-ready. A statistician should be able to produce the analysis with a filter and a model call — no further derivation.
# If ADaM is analysis-ready, the analysis is this short:
adlb |>
filter(PARAMCD == "ALT", AVISITN == 12, ANL01FL == "Y") |>
lm(CHG ~ TRT01AN + BASE, data = _)Every piece of logic that is not in that snippet — which record is baseline, which subjects are in the population, which visit the record belongs to — has been pushed into the dataset where it is derived once.
2. Traceable. Every derived value can be traced back to its SDTM source.
That is why ADaM datasets carry --SEQ from the parent domain, why imputed dates come with an imputation flag, and why AVAL sits alongside LBSTRESN rather than replacing it. A reviewer must be able to get from a number in a table back to the collected value.
# Not traceable — the source value is gone
adlb <- lb |> transmute(USUBJID, PARAMCD = LBTESTCD, AVAL = LBSTRESN * 2)
# Traceable — the source value and its SEQ are retained
adlb <- lb |>
mutate(
PARAMCD = LBTESTCD,
AVAL = LBSTRESN * 2,
LBSEQ = LBSEQ # keeps the link back to the SDTM record
)Keeping LBSEQ costs one column and lets a reviewer find the exact SDTM record behind any analysis value.
The three ADaM structures
CDISC requirement. Every ADaM dataset is one of three shapes.
| Structure | One row per | Examples | Key |
|---|---|---|---|
| ADSL | Subject | ADSL only | USUBJID |
| BDS | Subject, parameter, timepoint | ADLB, ADVS, ADEG, ADTTE | USUBJID, PARAMCD, AVISITN |
| OCCDS | Subject, occurrence | ADAE, ADCM, ADMH | USUBJID, --SEQ |
ADSL (one row per subject)
┌──────────┬────────┬─────┬───────┐
│ USUBJID │ TRT01P │ AGE │ SAFFL │
├──────────┼────────┼─────┼───────┤
│ 01-001 │ Drug A │ 45 │ Y │
│ 01-002 │ Placebo│ 72 │ Y │
└──────────┴────────┴─────┴───────┘
BDS (one row per subject × parameter × visit)
┌──────────┬─────────┬─────────┬──────┬──────┬─────┐
│ USUBJID │ PARAMCD │ AVISITN │ AVAL │ BASE │ CHG │
├──────────┼─────────┼─────────┼──────┼──────┼─────┤
│ 01-001 │ ALT │ 0 │ 30 │ 30 │ 0 │
│ 01-001 │ ALT │ 4 │ 38 │ 30 │ 8 │
│ 01-001 │ AST │ 0 │ 25 │ 25 │ 0 │
└──────────┴─────────┴─────────┴──────┴──────┴─────┘
OCCDS (one row per subject × occurrence)
┌──────────┬───────┬───────────┬─────────┐
│ USUBJID │ AESEQ │ AEDECOD │ TRTEMFL │
├──────────┼───────┼───────────┼─────────┤
│ 01-001 │ 1 │ Headache │ Y │
│ 01-001 │ 2 │ Nausea │ Y │
└──────────┴───────┴───────────┴─────────┘Why BDS is long rather than wide: one program can handle any number of parameters. Add a new lab test and nothing changes — the rows increase, the code does not.
Setting up
library(dplyr)
library(tidyr)
library(tibble)
library(lubridate)Four packages, all tidyverse. Every example also shows the base R equivalent, so you can follow along with nothing installed at all.
Part 2 — The mock SDTM data
We build five small SDTM datasets once and use them throughout. Six subjects is enough to exercise every edge case and small enough to check by eye.
DM — Demographics
dm <- tibble(
STUDYID = "ABC-101",
DOMAIN = "DM",
USUBJID = c("01-001", "01-002", "01-003", "02-004", "02-005", "02-006"),
SUBJID = c("001", "002", "003", "004", "005", "006"),
SITEID = c("01", "01", "01", "02", "02", "02"),
BRTHDTC = c("1979-03-15", "1952-07-22", "1988-11-30",
"1960-01-08", "1945-05-19", "1971-09-02"),
AGE = c(45L, 72L, 36L, 64L, 79L, 53L),
AGEU = "YEARS",
SEX = c("F", "M", "F", "M", "F", "F"),
RACE = c("WHITE", "WHITE", "ASIAN", "BLACK OR AFRICAN AMERICAN",
"WHITE", "WHITE"),
ARM = c("Drug A", "Placebo", "Screen Failure",
"Drug A", "Placebo", "Drug A"),
ARMCD = c("A", "P", "SCRNFAIL", "A", "P", "A"),
ACTARM = c("Drug A", "Placebo", "Screen Failure",
"Drug A", "Placebo", "Drug A"),
COUNTRY = c("USA", "USA", "USA", "DEU", "DEU", "DEU")
)| Variable | Meaning |
|---|---|
STUDYID |
Study identifier |
USUBJID |
Unique subject identifier across the whole study |
SUBJID |
Subject identifier within the site |
SITEID |
Investigator site |
BRTHDTC |
Date of birth, ISO 8601 character |
AGE, AGEU |
Age and its unit at screening |
ARM, ARMCD |
Planned treatment and its code |
ACTARM |
Actual treatment received |
Note subject 01-003 is a screen failure — randomised to nothing, never dosed. Keeping one in the mock data is what makes the population flags interesting.
ARM versus ACTARM
ARM is what the randomisation said; ACTARM is what the subject actually received. They differ when a subject is dosed with the wrong kit.
Safety analyses use the actual treatment (TRT01A), efficacy analyses usually use the planned treatment (TRT01P). Getting this backwards is a real finding, not a technicality.
EX — Exposure
ex <- tibble(
STUDYID = "ABC-101",
DOMAIN = "EX",
USUBJID = c("01-001", "01-001", "01-002", "02-004", "02-005", "02-006"),
EXSEQ = c(1L, 2L, 1L, 1L, 1L, 1L),
EXTRT = c("DRUG A", "DRUG A", "PLACEBO", "DRUG A", "PLACEBO", "DRUG A"),
EXDOSE = c(50, 50, 0, 50, 0, 50),
EXDOSU = "mg",
EXSTDTC = c("2026-03-01", "2026-05-01", "2026-03-05",
"2026-03-10", "2026-03-12", "2026-04-02"),
EXENDTC = c("2026-04-30", "2026-06-15", "2026-06-20",
"2026-06-25", "2026-05-30", "2026-04-20")
)Subject 01-001 has two exposure records — a dose interruption and restart. That is realistic and it is why first and last dose need min() and max() rather than taking the only row.
Subject 01-003 does not appear at all: never dosed.
Common convention. Actual treatment means a non-zero dose — except on placebo, where zero is the treatment.
EXDOSE > 0 | (EXDOSE == 0 & grepl("PLACEBO", EXTRT))You will see this idiom in every ADSL program. Without the placebo clause, every placebo subject is excluded from the safety population.
DS — Disposition
ds <- tibble(
STUDYID = "ABC-101",
DOMAIN = "DS",
USUBJID = c("01-001", "01-002", "01-003", "02-004", "02-005", "02-006"),
DSSEQ = 1L,
DSCAT = c(rep("DISPOSITION EVENT", 2), "SCREEN FAILURE",
rep("DISPOSITION EVENT", 3)),
DSDECOD = c("COMPLETED", "ADVERSE EVENT", "SCREEN FAILURE",
"COMPLETED", "WITHDRAWAL BY SUBJECT", "ADVERSE EVENT"),
DSTERM = c("Completed study", "Adverse event", "Did not meet criteria",
"Completed study", "Subject withdrew consent", "Adverse event"),
DSSTDTC = c("2026-06-30", "2026-06-22", "2026-02-20",
"2026-06-28", "2026-05-31", "2026-04-22")
)LB — Laboratory
lb <- tibble(
STUDYID = "ABC-101",
DOMAIN = "LB",
USUBJID = c(rep("01-001", 5), rep("01-002", 4), rep("02-004", 3),
rep("02-005", 2), "02-006"),
LBSEQ = c(1:5, 1:4, 1:3, 1:2, 1L),
LBTESTCD = c("ALT", "ALT", "ALT", "ALT", "AST",
"ALT", "ALT", "ALT", "AST",
"ALT", "ALT", "ALT",
"ALT", "ALT",
"ALT"),
LBTEST = c(rep("Alanine Aminotransferase", 4), "Aspartate Aminotransferase",
rep("Alanine Aminotransferase", 3), "Aspartate Aminotransferase",
rep("Alanine Aminotransferase", 3),
rep("Alanine Aminotransferase", 2),
"Alanine Aminotransferase"),
LBCAT = "CHEMISTRY",
LBORRES = c("28", "30", "38", "52", "25",
"22", "24", "31", "19",
"35", "41", "68",
"NOT DONE", "27",
"33"),
LBORRESU = "U/L",
LBSTRESN = c(28, 30, 38, 52, 25,
22, 24, 31, 19,
35, 41, 68,
NA, 27,
33),
LBSTRESU = "U/L",
LBSTNRLO = c(rep(7, 4), 10, rep(7, 3), 10, rep(7, 3), rep(7, 2), 7),
LBSTNRHI = c(rep(40, 4), 35, rep(40, 3), 35, rep(40, 3), rep(40, 2), 40),
VISIT = c("SCREENING", "BASELINE", "WEEK 4", "WEEK 12", "BASELINE",
"SCREENING", "BASELINE", "WEEK 4", "BASELINE",
"BASELINE", "WEEK 4", "WEEK 12",
"BASELINE", "WEEK 4",
"WEEK 4"),
VISITNUM = c(1, 2, 3, 5, 2,
1, 2, 3, 2,
2, 3, 5,
2, 3,
3),
LBDTC = c("2026-02-20", "2026-02-28", "2026-03-29", "2026-05-24", "2026-02-28",
"2026-02-25", "2026-03-04", "2026-04-01", "2026-03-04",
"2026-03-08", "2026-04-07", "2026-06-02",
"2026-03-11", "2026-04-09",
"2026-04-01")
)The deliberate complications:
| Subject | Complication | What it tests |
|---|---|---|
01-001 |
Two pre-dose records (screening + baseline) | Which one is baseline? |
01-001 |
Has both ALT and AST | Baseline is per parameter |
02-005 |
LBORRES = "NOT DONE", LBSTRESN = NA |
Missing value handling |
02-006 |
Only a Week 4 record, no baseline | A parameter with no baseline |
01-002 |
Week 4 result on 2026-04-01, dosed 2026-03-05 | Normal case |
| Variable | Meaning |
|---|---|
LBTESTCD / LBTEST |
Short code and full name of the test |
LBORRES / LBORRESU |
Result as collected, with its unit — character |
LBSTRESN / LBSTRESU |
Result in standard units — numeric |
LBSTNRLO / LBSTNRHI |
Lower and upper limits of the normal range |
LBDTC |
Collection date, ISO 8601 character |
LBORRES is character and LBSTRESN is numeric
LBORRES holds the result exactly as recorded, which may be "NOT DONE", "<5" or ">1000". A character column is the only way to keep that.
LBSTRESN is the numeric standardised result, and it is NA when the original cannot be expressed as a number. That pairing — keep the original, derive the numeric — is traceability in action, and it is why lesson 2 insists on reading source data as text.
AE — Adverse events
ae <- tibble(
STUDYID = "ABC-101",
DOMAIN = "AE",
USUBJID = c("01-001", "01-001", "01-002", "01-002",
"02-004", "02-005", "02-006"),
AESEQ = c(1L, 2L, 1L, 2L, 1L, 1L, 1L),
AETERM = c("Headache", "Mild nausea", "Rash on arms", "Headache",
"Dizziness", "Fatigue", "Severe rash"),
AEDECOD = c("HEADACHE", "NAUSEA", "RASH", "HEADACHE",
"DIZZINESS", "FATIGUE", "RASH"),
AEBODSYS = c("NERVOUS SYSTEM DISORDERS", "GASTROINTESTINAL DISORDERS",
"SKIN AND SUBCUTANEOUS TISSUE DISORDERS",
"NERVOUS SYSTEM DISORDERS", "NERVOUS SYSTEM DISORDERS",
"GENERAL DISORDERS", "SKIN AND SUBCUTANEOUS TISSUE DISORDERS"),
AESEV = c("MILD", "MODERATE", "MILD", "MILD",
"MODERATE", "MILD", "SEVERE"),
AESER = c("N", "N", "N", "N", "N", "N", "Y"),
AEREL = c("NOT RELATED", "POSSIBLY RELATED", "POSSIBLY RELATED",
"NOT RELATED", "POSSIBLY RELATED", "NOT RELATED",
"PROBABLY RELATED"),
AEOUT = c("RECOVERED/RESOLVED", "RECOVERED/RESOLVED",
"RECOVERED/RESOLVED", "NOT RECOVERED/NOT RESOLVED",
"RECOVERED/RESOLVED", "RECOVERED/RESOLVED", "FATAL"),
AESTDTC = c("2026-02-25", "2026-03-20", "2026-03",
"2026-06-10", "2026-04-15", "2026-03-15", "2026-04-18"),
AEENDTC = c("2026-02-27", "2026-03-25", "2026-04-02",
"", "2026-04-20", "2026-03-18", "2026-04-22")
)The complications here:
| Subject | Complication | What it tests |
|---|---|---|
01-001 AE 1 |
Onset 2026-02-25, before first dose 2026-03-01 | Not treatment emergent |
01-002 AE 1 |
AESTDTC = "2026-03" — a partial date |
Imputation |
01-002 AE 2 |
Onset after treatment end | The follow-up window |
02-005 |
Subject on placebo | Placebo subjects still have AEs |
02-006 |
Fatal, serious, severe | A death |
Part 3 — Building ADSL
ADSL is one row per subject, and it is built first because every other ADaM dataset merges variables from it.
Step 1 — Start from DM
Concept. ADSL begins as a copy of DM. DM is already one row per subject, so the structure is correct from the start.
adsl <- dm |>
select(STUDYID, USUBJID, SUBJID, SITEID, AGE, AGEU, SEX, RACE,
ARM, ARMCD, ACTARM, COUNTRY, BRTHDTC)adsl <- dm[, c("STUDYID", "USUBJID", "SUBJID", "SITEID", "AGE", "AGEU",
"SEX", "RACE", "ARM", "ARMCD", "ACTARM", "COUNTRY", "BRTHDTC")]Code explanation. select() keeps only the listed columns, in that order. The base equivalent indexes the columns by name with df[, c(...)]. Both return a data frame with six rows.
QC. The very first check, every time:
nrow(adsl) == n_distinct(dm$USUBJID)
#> [1] TRUEIf that is ever FALSE, stop. ADSL must be one row per subject, and everything downstream assumes it.
Step 2 — Treatment variables
Concept. ADaM names treatment variables by period: TRT01P is the planned treatment for period 1, TRT01A the actual. The numeric companions TRT01PN and TRT01AN exist so models and sort orders can use a number.
CDISC requirement. TRT01P is required in ADSL for any study with treatment. The numeric versions are permissible.
Derivation logic. Copy ARM to TRT01P and ACTARM to TRT01A, then map each to a number. The numeric order should put placebo first, because that is how tables are presented.
adsl <- adsl |>
mutate(
TRT01P = ARM,
TRT01A = ACTARM,
TRT01PN = case_when(
TRT01P == "Placebo" ~ 0L,
TRT01P == "Drug A" ~ 1L,
TRT01P == "Screen Failure" ~ NA_integer_
),
TRT01AN = case_when(
TRT01A == "Placebo" ~ 0L,
TRT01A == "Drug A" ~ 1L,
TRT01A == "Screen Failure" ~ NA_integer_
)
)adsl$TRT01P <- adsl$ARM
adsl$TRT01A <- adsl$ACTARM
# A named vector is the base-R lookup table
trt_map <- c("Placebo" = 0L, "Drug A" = 1L, "Screen Failure" = NA_integer_)
adsl$TRT01PN <- unname(trt_map[adsl$TRT01P])
adsl$TRT01AN <- unname(trt_map[adsl$TRT01A])Code explanation.
mutate()adds or changes columns. Expressions can refer to columns created earlier in the same call, which is whyTRT01PNcan useTRT01P.case_when()evaluates conditions top to bottom; the first match wins. Every result must be the same type, which is whyNA_integer_rather than bareNA(which is logical).- The base version uses a named vector as a lookup:
trt_map["Placebo"]returns0.unname()strips the names so the column is clean.
Expected output.
adsl |> select(USUBJID, ARM, TRT01P, TRT01PN, TRT01A, TRT01AN)
#> # A tibble: 6 × 6
#> USUBJID ARM TRT01P TRT01PN TRT01A TRT01AN
#> <chr> <chr> <chr> <int> <chr> <int>
#> 1 01-001 Drug A Drug A 1 Drug A 1
#> 2 01-002 Placebo Placebo 0 Placebo 0
#> 3 01-003 Screen Failure Screen Failure NA Screen Failure NA
#> 4 02-004 Drug A Drug A 1 Drug A 1
#> 5 02-005 Placebo Placebo 0 Placebo 0
#> 6 02-006 Drug A Drug A 1 Drug A 1NA silently
If the study later adds “Drug A 100mg” and nobody updates case_when(), those subjects get TRT01PN = NA and quietly vanish from any analysis that sorts or models on it.
Defend against it:
unmapped <- setdiff(unique(adsl$TRT01P), names(trt_map))
if (length(unmapped) > 0) {
stop("Unmapped treatment value(s): ", paste(unmapped, collapse = ", "))
}Check that every observed value is in your mapping. This one assertion catches a whole class of silent error — see SDTM programming for the same pattern applied to controlled terminology.
Base R vs tidyverse. case_when() is clearer for a handful of conditions and reads like the specification. The named-vector lookup is shorter, faster, and — importantly — makes the mapping a data object you can validate against, which is why the metadata-driven approach in lesson 6 prefers it.
Step 3 — Treatment dates
Concept. TRTSDT is the date of first dose, TRTEDT the date of last dose, and TRTDURD the treatment duration in days.
Clinical example. Subject 01-001 was dosed 1 March to 30 April, then again 1 May to 15 June. First dose is 1 March; last dose is 15 June.
Derivation logic.
- Keep only records that represent actual dosing (the placebo filter)
- Convert the ISO character dates to real
Dateobjects - Take the minimum start and maximum end per subject
- Merge onto ADSL
ex_dates <- ex |>
filter(EXDOSE > 0 | (EXDOSE == 0 & grepl("PLACEBO", EXTRT))) |>
mutate(
EXSTDT = as.Date(EXSTDTC),
EXENDT = as.Date(EXENDTC)
) |>
summarise(
TRTSDT = min(EXSTDT, na.rm = TRUE),
TRTEDT = max(EXENDT, na.rm = TRUE),
.by = USUBJID
)
adsl <- adsl |>
left_join(ex_dates, by = "USUBJID") |>
mutate(TRTDURD = as.integer(TRTEDT - TRTSDT) + 1L)dosing <- ex[ex$EXDOSE > 0 |
(ex$EXDOSE == 0 & grepl("PLACEBO", ex$EXTRT)), ]
dosing$EXSTDT <- as.Date(dosing$EXSTDTC)
dosing$EXENDT <- as.Date(dosing$EXENDTC)
first_dose <- tapply(dosing$EXSTDT, dosing$USUBJID, min)
last_dose <- tapply(dosing$EXENDT, dosing$USUBJID, max)
ex_dates <- data.frame(
USUBJID = names(first_dose),
TRTSDT = as.Date(first_dose, origin = "1970-01-01"),
TRTEDT = as.Date(last_dose, origin = "1970-01-01"),
stringsAsFactors = FALSE
)
adsl <- merge(adsl, ex_dates, by = "USUBJID", all.x = TRUE, sort = FALSE)
adsl$TRTDURD <- as.integer(adsl$TRTEDT - adsl$TRTSDT) + 1LCode explanation.
filter()keeps rows matching the condition.grepl("PLACEBO", EXTRT)returnsTRUEwhere the treatment name contains “PLACEBO”.as.Date()converts"2026-03-01"to aDate. Once they are dates,min(),max()and subtraction all work.summarise(.by = USUBJID)collapses to one row per subject..bygroups inline and returns ungrouped output — nogroup_by()/ungroup()pair to forget.left_join(by = "USUBJID")keeps every ADSL row and attaches the dates where they exist. Subject01-003has no exposure, so getsNA.as.integer(TRTEDT - TRTSDT) + 1L— subtracting dates gives adifftime;as.integer()makes it a plain number.
TRTDURD is inclusive of both endpoints
TRTDURD = TRTEDT - TRTSDT + 1
A subject dosed once on a single day has a duration of 1 day, not 0. The + 1 is not optional and forgetting it is one of the most common findings in an independent QC comparison.
# A subject dosed only on 2026-03-01
as.integer(as.Date("2026-03-01") - as.Date("2026-03-01")) + 1L
#> [1] 1 ✓ correctExpected output.
adsl |> select(USUBJID, TRT01A, TRTSDT, TRTEDT, TRTDURD)
#> # A tibble: 6 × 5
#> USUBJID TRT01A TRTSDT TRTEDT TRTDURD
#> <chr> <chr> <date> <date> <int>
#> 1 01-001 Drug A 2026-03-01 2026-06-15 107
#> 2 01-002 Placebo 2026-03-05 2026-06-20 108
#> 3 01-003 Screen Failure NA NA NA
#> 4 02-004 Drug A 2026-03-10 2026-06-25 108
#> 5 02-005 Placebo 2026-03-12 2026-05-30 80
#> 6 02-006 Drug A 2026-04-02 2026-04-20 19Subject 01-001 spans 1 March to 15 June — the two exposure records collapsed correctly to one first and one last dose.
QC.
# 1. Row count unchanged by the merge
stopifnot(nrow(adsl) == 6)
# 2. End date never before start date
stopifnot(all(is.na(adsl$TRTEDT) | adsl$TRTEDT >= adsl$TRTSDT))
# 3. Duration positive where dates exist
stopifnot(all(is.na(adsl$TRTDURD) | adsl$TRTDURD >= 1))
# 4. Only the screen failure has no dates
adsl |> filter(is.na(TRTSDT)) |> pull(USUBJID)
#> [1] "01-003" ✓ as expectedex has two rows for subject 01-001. Joining ex directly to ADSL without summarising first would give ADSL seven rows.
# WRONG — ADSL is no longer one row per subject
adsl_bad <- adsl |> left_join(ex, by = "USUBJID")
nrow(adsl_bad)
#> [1] 7
# Protect yourself: declare the cardinality you expect
adsl |> left_join(ex_dates, by = "USUBJID", relationship = "one-to-one")relationship = makes dplyr error if the join is not the shape you declared. Always use it. Base merge() has no equivalent, which is why the base version above needs a manual nrow() assertion.
Step 4 — Population flags
Concept. A population flag records whether a subject belongs to an analysis population. Each is "Y" or "N" — never NA, because every subject either is or is not in the population.
Why it matters. Population flags are the denominators of every table. Get them wrong and every percentage in the submission is wrong.
| Flag | Population | Typical definition |
|---|---|---|
SAFFL |
Safety | Received at least one dose |
ITTFL |
Intent-to-treat | Randomised, regardless of dosing |
FASFL |
Full analysis set | Randomised with at least one post-baseline assessment |
PPROTFL |
Per protocol | No major protocol deviations |
The definitions above are common conventions, not CDISC requirements. Your SAP defines them, and the variations are real:
- Is a randomised-but-never-dosed subject in ITT? Usually yes.
- Does FAS require a post-baseline efficacy assessment, or just randomisation?
- Is the safety population based on actual or planned treatment?
Never code a population flag from memory. Read the SAP section.
Derivation logic (for this example).
SAFFL = "Y"ifTRTSDTis not missing — the subject was dosedITTFL = "Y"if randomised, i.e.ARMCDis not"SCRNFAIL"FASFL = "Y"if in ITT and has at least one post-baseline lab value
# Subjects with at least one post-baseline lab assessment
post_baseline <- lb |>
mutate(LBDT = as.Date(LBDTC)) |>
inner_join(select(adsl, USUBJID, TRTSDT), by = "USUBJID") |>
filter(!is.na(LBSTRESN), !is.na(TRTSDT), LBDT > TRTSDT) |>
distinct(USUBJID) |>
pull(USUBJID)
adsl <- adsl |>
mutate(
SAFFL = if_else(!is.na(TRTSDT), "Y", "N"),
ITTFL = if_else(ARMCD != "SCRNFAIL", "Y", "N"),
FASFL = if_else(ITTFL == "Y" & USUBJID %in% post_baseline, "Y", "N")
)lb2 <- merge(lb, adsl[, c("USUBJID", "TRTSDT")], by = "USUBJID", all.x = TRUE)
lb2$LBDT <- as.Date(lb2$LBDTC)
keep <- which(!is.na(lb2$LBSTRESN) & !is.na(lb2$TRTSDT) & lb2$LBDT > lb2$TRTSDT)
post_baseline <- unique(lb2$USUBJID[keep])
adsl$SAFFL <- ifelse(!is.na(adsl$TRTSDT), "Y", "N")
adsl$ITTFL <- ifelse(adsl$ARMCD != "SCRNFAIL", "Y", "N")
adsl$FASFL <- ifelse(adsl$ITTFL == "Y" & adsl$USUBJID %in% post_baseline,
"Y", "N")Code explanation.
inner_join()keeps only rows matching in both — here, lab records for subjects who have aTRTSDT.distinct(USUBJID)reduces to unique subjects;pull()extracts the column as a vector.if_else()(dplyr) is type-strict — both branches must be the same type. Baseifelse()is more permissive and drops attributes, which is whyif_else()is preferred for dates.%in%tests membership:USUBJID %in% post_baselineisTRUEwhere the subject is in that vector.- In the base version,
which()is essential —lb2[condition, ]withNAin the condition creates phantom rows, as covered in Base R data frame manipulation.
Expected output.
adsl |> select(USUBJID, ARMCD, TRTSDT, SAFFL, ITTFL, FASFL)
#> # A tibble: 6 × 6
#> USUBJID ARMCD TRTSDT SAFFL ITTFL FASFL
#> <chr> <chr> <date> <chr> <chr> <chr>
#> 1 01-001 A 2026-03-01 Y Y Y
#> 2 01-002 P 2026-03-05 Y Y Y
#> 3 01-003 SCRNFAIL NA N N N
#> 4 02-004 A 2026-03-10 Y Y Y
#> 5 02-005 P 2026-03-12 Y Y Y
#> 6 02-006 A 2026-04-02 Y Y NSubject 02-006 is dosed (so SAFFL = "Y") and randomised (ITTFL = "Y") but has only one lab record, on 2026-04-01, which is before first dose on 2026-04-02 — so no post-baseline assessment and FASFL = "N". That is exactly the kind of case a six-subject dataset is designed to surface.
QC.
# Flags must be Y or N — never NA, never anything else
for (f in c("SAFFL", "ITTFL", "FASFL")) {
stopifnot(all(adsl[[f]] %in% c("Y", "N")))
}
# Population counts — these become table denominators
adsl |> count(SAFFL, ITTFL, FASFL)
#> # A tibble: 3 × 4
#> SAFFL ITTFL FASFL n
#> <chr> <chr> <chr> <int>
#> 1 N N N 1
#> 2 Y Y N 1
#> 3 Y Y Y 3
# Logical consistency: FAS must be a subset of ITT
stopifnot(all(adsl$ITTFL[adsl$FASFL == "Y"] == "Y"))That last assertion is the useful one. Population flags are nested — FAS ⊆ ITT, and a safety subject must have a treatment date. Asserting the nesting catches derivation errors that counting alone does not.
Step 5 — Grouping variables
Concept. Grouping variables band a continuous variable for table rows. AGEGR1 is the age group; AGEGR1N is its sort order.
Study-specific (SAP). The bands are defined by the SAP. <65 / >=65 is common; three-band and four-band versions are equally common.
adsl <- adsl |>
mutate(
AGEGR1 = case_when(
AGE < 65 ~ "<65",
AGE >= 65 & AGE < 80 ~ "65-79",
AGE >= 80 ~ ">=80",
.default = NA_character_
),
AGEGR1N = case_when(
AGEGR1 == "<65" ~ 1L,
AGEGR1 == "65-79" ~ 2L,
AGEGR1 == ">=80" ~ 3L
)
)adsl$AGEGR1 <- as.character(cut(
adsl$AGE,
breaks = c(-Inf, 65, 80, Inf),
labels = c("<65", "65-79", ">=80"),
right = FALSE # [lower, upper) — left-closed intervals
))
age_order <- c("<65" = 1L, "65-79" = 2L, ">=80" = 3L)
adsl$AGEGR1N <- unname(age_order[adsl$AGEGR1])right = FALSE is the whole ballgame
cut(65, breaks = c(-Inf, 65, 80, Inf),
labels = c("<65", "65-79", ">=80"), right = FALSE)
#> [1] 65-79 ✓ 65 belongs to the 65-79 band
cut(65, breaks = c(-Inf, 65, 80, Inf),
labels = c("<65", "65-79", ">=80"), right = TRUE)
#> [1] <65 ✗ 65 is in the "<65" band — wrongright = FALSE gives intervals that are [lower, upper) — closed on the left, open on the right. That matches how a SAP writes “65–79”: a subject aged exactly 65 is in that band.
This single argument is responsible for a large share of boundary differences in independent QC. A subject aged exactly 65 is the test case to write.
Expected output.
adsl |> select(USUBJID, AGE, AGEGR1, AGEGR1N) |> arrange(AGE)
#> # A tibble: 6 × 4
#> USUBJID AGE AGEGR1 AGEGR1N
#> <chr> <int> <chr> <int>
#> 1 01-003 36 <65 1
#> 2 01-001 45 <65 1
#> 3 02-006 53 <65 1
#> 4 02-004 64 <65 1
#> 5 01-002 72 65-79 2
#> 6 02-005 79 65-79 2Subject 02-005 is 79 — just inside the 65-79 band. Had they been 80, they would move to >=80. Boundary subjects are worth checking by eye in a mock dataset for exactly this reason.
The complete ADSL
Putting the five steps together as one readable pipeline:
build_adsl <- function(dm, ex, lb) {
# --- Treatment dates from exposure ---------------------------------------
ex_dates <- ex |>
filter(EXDOSE > 0 | (EXDOSE == 0 & grepl("PLACEBO", EXTRT))) |>
mutate(EXSTDT = as.Date(EXSTDTC), EXENDT = as.Date(EXENDTC)) |>
summarise(TRTSDT = min(EXSTDT, na.rm = TRUE),
TRTEDT = max(EXENDT, na.rm = TRUE),
.by = USUBJID)
# --- Core ADSL -------------------------------------------------------------
out <- dm |>
select(STUDYID, USUBJID, SUBJID, SITEID, AGE, AGEU, SEX, RACE,
ARM, ARMCD, ACTARM, COUNTRY) |>
mutate(TRT01P = ARM, TRT01A = ACTARM) |>
left_join(ex_dates, by = "USUBJID", relationship = "one-to-one") |>
mutate(
TRT01PN = case_when(TRT01P == "Placebo" ~ 0L,
TRT01P == "Drug A" ~ 1L),
TRT01AN = case_when(TRT01A == "Placebo" ~ 0L,
TRT01A == "Drug A" ~ 1L),
TRTDURD = as.integer(TRTEDT - TRTSDT) + 1L,
SAFFL = if_else(!is.na(TRTSDT), "Y", "N"),
ITTFL = if_else(ARMCD != "SCRNFAIL", "Y", "N"),
AGEGR1 = case_when(AGE < 65 ~ "<65",
AGE < 80 ~ "65-79",
.default = ">=80"),
AGEGR1N = case_when(AGEGR1 == "<65" ~ 1L,
AGEGR1 == "65-79" ~ 2L,
AGEGR1 == ">=80" ~ 3L)
)
# --- Post-baseline assessment, for FASFL ----------------------------------
post_bl <- lb |>
mutate(LBDT = as.Date(LBDTC)) |>
inner_join(select(out, USUBJID, TRTSDT), by = "USUBJID") |>
filter(!is.na(LBSTRESN), !is.na(TRTSDT), LBDT > TRTSDT) |>
distinct(USUBJID) |> pull(USUBJID)
out <- out |> mutate(FASFL = if_else(ITTFL == "Y" & USUBJID %in% post_bl,
"Y", "N"))
# --- Postconditions --------------------------------------------------------
stopifnot(
"ADSL must be one row per subject" = !anyDuplicated(out$USUBJID),
"every DM subject must appear" = setequal(out$USUBJID, dm$USUBJID),
"flags must be Y or N" =
all(unlist(out[c("SAFFL","ITTFL","FASFL")]) %in% c("Y","N")),
"TRTEDT must not precede TRTSDT" =
all(is.na(out$TRTEDT) | out$TRTEDT >= out$TRTSDT)
)
out
}
adsl <- build_adsl(dm, ex, lb)Wrapping it in a function with stopifnot() postconditions is the single biggest step from script to dependable code. The setequal() check is the one that catches a join quietly dropping subjects.
Part 4 — BDS structure
The four core BDS variables
Concept. A BDS dataset is long: one row per subject, parameter and timepoint. Four variables carry the measurement.
| Variable | Holds | Type | Example |
|---|---|---|---|
PARAMCD |
Short parameter code | character, ≤8 chars | "ALT" |
PARAM |
Full parameter description with units | character | "Alanine Aminotransferase (U/L)" |
AVAL |
The numeric analysis value | numeric | 38 |
AVALC |
The character analysis value | character | "NOT DONE" |
CDISC requirement. PARAMCD and PARAM are required in any BDS dataset. PARAMCD must be ≤ 8 characters because of the SAS transport format — see xportr.
AVAL and AVALC are not alternatives
Use AVAL for anything numeric. Use AVALC when the result is genuinely categorical ("POSITIVE", "NOT DONE") or when you need a character representation alongside the number.
Never put a number in AVALC and nothing in AVAL — every statistical summary reads AVAL, and the value becomes invisible to the analysis.
A parameter is normally one or the other. Mixing them within one PARAMCD is a sign the parameter should be split in two.
Building the parameter lookup
Concept. PARAM must be consistent across the whole study, so it comes from a lookup table rather than being constructed inline. That lookup is a small piece of metadata — the first step towards the metadata-driven approach in lesson 6.
param_lookup <- tibble(
LBTESTCD = c("ALT", "AST", "BILI", "ALP"),
PARAMCD = c("ALT", "AST", "BILI", "ALP"),
PARAM = c("Alanine Aminotransferase (U/L)",
"Aspartate Aminotransferase (U/L)",
"Bilirubin (umol/L)",
"Alkaline Phosphatase (U/L)"),
PARAMN = c(1L, 2L, 3L, 4L)
)PARAMN gives the display order for tables, so ALT always appears before AST regardless of how the data happens to be sorted.
Step 1 — The BDS skeleton
adlb <- lb |>
# Bring in the parameter metadata
left_join(param_lookup, by = "LBTESTCD", relationship = "many-to-one") |>
# Bring in the ADSL variables every BDS dataset needs
left_join(
select(adsl, USUBJID, TRT01A, TRT01AN, TRT01P, TRT01PN,
TRTSDT, TRTEDT, SAFFL, ITTFL),
by = "USUBJID", relationship = "many-to-one"
) |>
mutate(
AVAL = LBSTRESN,
AVALC = if_else(is.na(LBSTRESN), LBORRES, NA_character_),
AVALU = LBSTRESU,
ANRLO = LBSTNRLO,
ANRHI = LBSTNRHI
)adlb <- merge(lb, param_lookup, by = "LBTESTCD", all.x = TRUE, sort = FALSE)
adsl_vars <- adsl[, c("USUBJID", "TRT01A", "TRT01AN", "TRT01P", "TRT01PN",
"TRTSDT", "TRTEDT", "SAFFL", "ITTFL")]
adlb <- merge(adlb, adsl_vars, by = "USUBJID", all.x = TRUE, sort = FALSE)
adlb$AVAL <- adlb$LBSTRESN
adlb$AVALC <- ifelse(is.na(adlb$LBSTRESN), adlb$LBORRES, NA_character_)
adlb$AVALU <- adlb$LBSTRESU
adlb$ANRLO <- adlb$LBSTNRLO
adlb$ANRHI <- adlb$LBSTNRHI
# merge() does not check cardinality — assert it
stopifnot(nrow(adlb) == nrow(lb))Derivation logic. AVAL takes the standardised numeric result. AVALC takes the original result only when the numeric is missing — so subject 02-005’s "NOT DONE" is preserved and visible, rather than becoming an unexplained NA.
Code explanation.
left_join()keeps every row of the left dataset and attaches matching columns from the right.relationship = "many-to-one"declares that each lab record matches at most one lookup row, and errors if that is untrue.if_else(is.na(LBSTRESN), LBORRES, NA_character_)populatesAVALConly where the numeric is missing.NA_character_rather thanNAbecauseif_else()requires both branches to be the same type.- The base version needs the
stopifnot(nrow(...))becausemerge()performs no cardinality check at all.
Expected output.
adlb |> select(USUBJID, PARAMCD, VISIT, LBORRES, AVAL, AVALC)
#> # A tibble: 15 × 6
#> USUBJID PARAMCD VISIT LBORRES AVAL AVALC
#> <chr> <chr> <chr> <chr> <dbl> <chr>
#> 1 01-001 ALT SCREENING 28 28 NA
#> 2 01-001 ALT BASELINE 30 30 NA
#> 3 01-001 ALT WEEK 4 38 38 NA
#> 4 01-001 ALT WEEK 12 52 52 NA
#> 5 01-001 AST BASELINE 25 25 NA
#> ...
#> 13 02-005 ALT BASELINE NOT DONE NA NOT DONE
#> 14 02-005 ALT WEEK 4 27 27 NARow 13 is the point: the numeric is NA, and AVALC records why.
Step 2 — Analysis dates and study day
Concept. ADT is the analysis date as a real Date. ADY is the study day relative to first dose.
CDISC requirement — there is no day zero.
Day: -3 -2 -1 [no 0] 1 2 3
────────────────────────────▶
▲
first dose
(TRTSDT) = Day 1| Relationship | ADY |
|---|---|
ADT == TRTSDT |
1 |
ADT one day after |
2 |
ADT one day before |
-1 |
ADT three days before |
-3 |
Derivation logic.
if ADT >= TRTSDT: ADY = ADT - TRTSDT + 1
else: ADY = ADT - TRTSDTadlb <- adlb |>
mutate(
ADT = as.Date(LBDTC),
ADY = case_when(
is.na(ADT) | is.na(TRTSDT) ~ NA_integer_,
ADT >= TRTSDT ~ as.integer(ADT - TRTSDT) + 1L,
.default = as.integer(ADT - TRTSDT)
)
)adlb$ADT <- as.Date(adlb$LBDTC)
adlb$ADY <- ifelse(
is.na(adlb$ADT) | is.na(adlb$TRTSDT), NA_integer_,
ifelse(adlb$ADT >= adlb$TRTSDT,
as.integer(adlb$ADT - adlb$TRTSDT) + 1L,
as.integer(adlb$ADT - adlb$TRTSDT))
)Expected output.
adlb |>
filter(USUBJID == "01-001", PARAMCD == "ALT") |>
select(USUBJID, VISIT, ADT, TRTSDT, ADY)
#> # A tibble: 4 × 5
#> USUBJID VISIT ADT TRTSDT ADY
#> <chr> <chr> <date> <date> <int>
#> 1 01-001 SCREENING 2026-02-20 2026-03-01 -9
#> 2 01-001 BASELINE 2026-02-28 2026-03-01 -1
#> 3 01-001 WEEK 4 2026-03-29 2026-03-01 29
#> 4 01-001 WEEK 12 2026-05-24 2026-03-01 85Check the arithmetic by hand: 28 February is one day before 1 March, so ADY = -1. There is no day 0 — 1 March itself would be day 1.
# WRONG — produces a day 0 and shifts every positive day down by one
adlb$ADY <- as.integer(adlb$ADT - adlb$TRTSDT)This is the single most common ADaM derivation error. Write the test:
test_no_day_zero <- function() {
d <- as.Date("2026-03-01")
stopifnot(
compute_ady(d, d) == 1, # first dose = day 1
compute_ady(d + 1, d) == 2,
compute_ady(d - 1, d) == -1, # NOT 0
!0 %in% c(compute_ady(d - 1, d), compute_ady(d, d))
)
"PASS"
}Step 3 — Analysis visits
Concept. AVISIT is the analysis visit label; AVISITN its numeric order. These are analysis visits, which may differ from the collected VISIT — unscheduled visits get mapped into windows, and baseline is often relabelled.
Study-specific (SAP). The visit windows come from the SAP. Here we use a simple mapping.
visit_lookup <- tibble(
VISIT = c("SCREENING", "BASELINE", "WEEK 4", "WEEK 8", "WEEK 12"),
AVISIT = c("Screening", "Baseline", "Week 4", "Week 8", "Week 12"),
AVISITN = c(-1L, 0L, 4L, 8L, 12L)
)
adlb <- adlb |>
left_join(visit_lookup, by = "VISIT", relationship = "many-to-one")visit_lookup <- data.frame(
VISIT = c("SCREENING", "BASELINE", "WEEK 4", "WEEK 8", "WEEK 12"),
AVISIT = c("Screening", "Baseline", "Week 4", "Week 8", "Week 12"),
AVISITN = c(-1L, 0L, 4L, 8L, 12L),
stringsAsFactors = FALSE
)
adlb <- merge(adlb, visit_lookup, by = "VISIT", all.x = TRUE, sort = FALSE)
stopifnot(!any(is.na(adlb$AVISITN))) # catch an unmapped visitThat stopifnot() matters: an unmapped visit silently becomes NA, and the records then disappear from any analysis filtered on AVISITN.
Part 5 — Baseline
This is the most important derivation in BDS, and the one where independent programmers most often disagree.
What baseline means
Concept. Baseline is the reference measurement a change is calculated against. Two variables express it:
| Variable | Meaning |
|---|---|
ABLFL |
"Y" on the one record that is the baseline, NA elsewhere |
BASE |
The baseline value, copied to every record for that subject and parameter |
Subject 01-001, PARAMCD = ALT
Visit ADY AVAL ABLFL BASE CHG
─────────────────────────────────────────────
Screening -9 28 NA 30 -2
Baseline -1 30 Y 30 0 ◀── the baseline record
Week 4 29 38 NA 30 8
Week 12 85 52 NA 30 22
▲ ▲
flags ONE row copied to ALL rowsThe selection rule
Common convention. Baseline is the last non-missing assessment on or before the first dose.
Each clause is doing work:
| Clause | Why |
|---|---|
| last | If there are several pre-dose values, the closest to dosing is most relevant |
| non-missing | A missing value cannot be a baseline |
| on or before | ADY <= 0, i.e. up to and including the day before first dose |
| first dose | TRTSDT is the reference, not randomisation |
Variations you will genuinely meet:
- Average of all pre-dose values, not the last
- Day 1 pre-dose only — screening values ineligible
- Last before randomisation rather than before first dose
- Worst pre-dose value, in some safety analyses
Our subject 01-001 has two pre-dose ALT values: 28 at screening (day −9) and 30 at baseline (day −1). The “last” rule gives 30. An “average” rule would give 29. Those produce different CHG for every subsequent visit.
Read the SAP. This is not a programming choice.
Step 1 — Flag the baseline record
Derivation logic.
- Sort by subject, parameter and date, ascending
- Among the eligible records only (non-missing
AVAL,ADY <= 0), take the last one per subject and parameter - Set
ABLFL = "Y"on exactly that record
adlb <- adlb |>
arrange(USUBJID, PARAMCD, ADT, AVISITN) |>
mutate(
# Row number within subject+parameter, among eligible rows only
.eligible = !is.na(AVAL) & !is.na(ADY) & ADY <= 0,
ABLFL = if_else(
.eligible & row_number() == max(row_number()[.eligible], -Inf),
"Y", NA_character_
),
.by = c(USUBJID, PARAMCD)
) |>
select(-.eligible)A clearer, more readable formulation using a helper:
flag_baseline <- function(data) {
data |>
arrange(USUBJID, PARAMCD, ADT, AVISITN) |>
group_by(USUBJID, PARAMCD) |>
mutate(
.elig = !is.na(AVAL) & !is.na(ADY) & ADY <= 0,
.n_row = row_number(),
.last_elig = if (any(.elig)) max(.n_row[.elig]) else NA_integer_,
ABLFL = if_else(!is.na(.last_elig) & .n_row == .last_elig,
"Y", NA_character_)
) |>
ungroup() |>
select(-.elig, -.n_row, -.last_elig)
}
adlb <- flag_baseline(adlb)adlb <- adlb[order(adlb$USUBJID, adlb$PARAMCD, adlb$ADT, adlb$AVISITN), ]
rownames(adlb) <- NULL
# Composite key for grouping
key <- paste(adlb$USUBJID, adlb$PARAMCD, sep = "|")
# Eligible records only
elig <- which(!is.na(adlb$AVAL) & !is.na(adlb$ADY) & adlb$ADY <= 0)
adlb$ABLFL <- NA_character_
if (length(elig) > 0) {
# Among eligible rows, the LAST per key — fromLast marks earlier ones
last_elig <- elig[!duplicated(key[elig], fromLast = TRUE)]
adlb$ABLFL[last_elig] <- "Y"
}Code explanation.
arrange()sorts. IncludingAVISITNafterADTbreaks ties when two records share a date — without it, which record is chosen is arbitrary and may differ between runs..eligmarks the candidate rows. Restricting before choosing the last is the crux: taking the last row overall would pick Week 12.row_number()numbers rows within each group.max(.n_row[.elig])is the position of the last eligible row.- The base version’s
duplicated(key[elig], fromLast = TRUE)is the same idea: among the eligible indices, keep the final occurrence of each key.elig[...]maps back to positions in the full data frame.
# WRONG — "last record" means Week 12, not baseline
adlb |> slice_tail(n = 1, by = c(USUBJID, PARAMCD))
# RIGHT — last record AMONG THE PRE-DOSE ONES
adlb |> filter(ADY <= 0) |> slice_tail(n = 1, by = c(USUBJID, PARAMCD))But the second form drops the post-baseline rows, and we need to keep them. Hence the flag-in-place approach above: restrict the choice, not the data.
This is precisely what admiral::restrict_derivation() does, and seeing the problem by hand is why lesson 5 will make immediate sense.
Expected output.
adlb |>
filter(USUBJID == "01-001") |>
select(USUBJID, PARAMCD, AVISIT, ADY, AVAL, ABLFL)
#> # A tibble: 5 × 6
#> USUBJID PARAMCD AVISIT ADY AVAL ABLFL
#> <chr> <chr> <chr> <int> <dbl> <chr>
#> 1 01-001 ALT Screening -9 28 NA
#> 2 01-001 ALT Baseline -1 30 Y
#> 3 01-001 ALT Week 4 29 38 NA
#> 4 01-001 ALT Week 12 85 52 NA
#> 5 01-001 AST Baseline -1 25 YTwo things to notice. Screening (28) was eligible but is not baseline, because Baseline (30) is later. And AST has its own baseline flag — baseline is derived per parameter, not per subject.
Step 2 — Copy the baseline value to every record
Derivation logic. Take AVAL from the row where ABLFL == "Y" and broadcast it to every row for that subject and parameter.
adlb <- adlb |>
mutate(
BASE = AVAL[!is.na(ABLFL)][1],
.by = c(USUBJID, PARAMCD)
)Or, more explicitly, as a join:
baseline_values <- adlb |>
filter(ABLFL == "Y") |>
select(USUBJID, PARAMCD, BASE = AVAL)
adlb <- adlb |>
left_join(baseline_values, by = c("USUBJID", "PARAMCD"),
relationship = "many-to-one")# Named-vector lookup: no merge, so no risk of row multiplication
bl_rows <- which(!is.na(adlb$ABLFL))
base_lookup <- adlb$AVAL[bl_rows]
names(base_lookup) <- key[bl_rows]
adlb$BASE <- unname(base_lookup[key])Code explanation.
AVAL[!is.na(ABLFL)][1]selects theAVALvalues on flagged rows within the group and takes the first. Since exactly one row per group is flagged, that is the baseline.[1]returnsNAsafely if no row is flagged.- The join version is more explicit and easier to review, at the cost of a join.
relationship = "many-to-one"guards it. - The base version uses the named-vector lookup from lesson 3 — faster than a merge, and it cannot reorder rows or multiply them.
Step 3 — Change and percentage change
Concept.
CHG = AVAL - BASE (absolute change)
PCHG = 100 × (AVAL - BASE) / BASE (percentage change)Common convention. CHG and PCHG are populated only on post-baseline records. On the baseline record itself CHG is 0 by definition, and many sponsors set both to missing there to avoid it being averaged into a post-baseline summary.
adlb <- adlb |>
mutate(
CHG = if_else(ADY > 0, AVAL - BASE, NA_real_),
PCHG = if_else(ADY > 0 & !is.na(BASE) & BASE != 0,
100 * (AVAL - BASE) / BASE,
NA_real_)
)adlb$CHG <- ifelse(!is.na(adlb$ADY) & adlb$ADY > 0,
adlb$AVAL - adlb$BASE, NA_real_)
adlb$PCHG <- ifelse(
!is.na(adlb$ADY) & adlb$ADY > 0 &
!is.na(adlb$BASE) & adlb$BASE != 0,
100 * (adlb$AVAL - adlb$BASE) / adlb$BASE,
NA_real_
)BASE != 0 guard is not optional
100 * (5 - 0) / 0
#> [1] InfWithout the guard, a baseline of zero produces Inf, which then propagates through every mean and standard deviation to fill a results table with Inf.
stopifnot(!any(is.infinite(adlb$PCHG), na.rm = TRUE))Assert it. A zero baseline is plausible for a count or a score, and the failure is silent until someone reads the table.
Expected output — the complete picture.
adlb |>
filter(PARAMCD == "ALT") |>
select(USUBJID, AVISIT, ADY, AVAL, ABLFL, BASE, CHG, PCHG) |>
mutate(PCHG = round(PCHG, 1))
#> # A tibble: 12 × 8
#> USUBJID AVISIT ADY AVAL ABLFL BASE CHG PCHG
#> <chr> <chr> <int> <dbl> <chr> <dbl> <dbl> <dbl>
#> 1 01-001 Screening -9 28 NA 30 NA NA
#> 2 01-001 Baseline -1 30 Y 30 NA NA
#> 3 01-001 Week 4 29 38 NA 30 8 26.7
#> 4 01-001 Week 12 85 52 NA 30 22 73.3
#> 5 01-002 Screening -8 22 NA 24 NA NA
#> 6 01-002 Baseline 0 24 Y 24 NA NA
#> 7 01-002 Week 4 28 31 NA 24 7 29.2
#> 8 02-004 Baseline -2 35 Y 35 NA NA
#> 9 02-004 Week 4 29 41 NA 35 6 17.1
#> 10 02-004 Week 12 85 68 NA 35 33 94.3
#> 11 02-005 Baseline -1 NA NA NA NA NA
#> 12 02-005 Week 4 29 27 NA NA NA NA
#> 13 02-006 Week 4 -1 33 NA 33 Y NAThree edge cases to study:
Subject 02-005 had "NOT DONE" at baseline, so AVAL is NA, no record is eligible, ABLFL is nowhere "Y", and BASE/CHG/PCHG are all missing. That is correct — you cannot compute a change from an absent baseline.
Subject 02-006 has only one record, at ADY = -1 (the lab was taken the day before first dose). It becomes the baseline, and there is no post-baseline record, so no CHG.
Subject 01-002 has a baseline at ADY = 0… which cannot happen, because there is no day 0. Spotting that is the exercise below.
QC for the baseline derivation
qc_baseline <- function(data) {
# 1. At most one baseline flag per subject and parameter
multi <- data |>
filter(ABLFL == "Y") |>
count(USUBJID, PARAMCD) |>
filter(n > 1)
stopifnot("more than one ABLFL per subject/parameter" = nrow(multi) == 0)
# 2. The baseline record is never post-dose
stopifnot("ABLFL set on a post-dose record" =
all(data$ADY[!is.na(data$ABLFL)] <= 0))
# 3. The baseline record never has a missing AVAL
stopifnot("ABLFL set on a missing value" =
all(!is.na(data$AVAL[!is.na(data$ABLFL)])))
# 4. BASE equals AVAL on the baseline record
bl <- data |> filter(ABLFL == "Y")
stopifnot("BASE != AVAL on the baseline record" =
all(bl$BASE == bl$AVAL, na.rm = TRUE))
# 5. No infinities in PCHG
stopifnot("PCHG contains Inf" = !any(is.infinite(data$PCHG)))
# 6. Report — do not fail — where no baseline exists
no_bl <- data |>
summarise(has_bl = any(ABLFL == "Y", na.rm = TRUE),
.by = c(USUBJID, PARAMCD)) |>
filter(!has_bl)
if (nrow(no_bl) > 0) {
message(sprintf(
"%d subject/parameter combination(s) with no baseline:\n%s",
nrow(no_bl),
paste0(" ", no_bl$USUBJID, " / ", no_bl$PARAMCD, collapse = "\n")
))
}
invisible(data)
}
qc_baseline(adlb)
#> 1 subject/parameter combination(s) with no baseline:
#> 02-005 / ALTChecks 1–5 fail the build; check 6 reports. That distinction is deliberate: a missing baseline is legitimate data, and how those subjects are handled is a statistical decision. Two baseline flags is a bug.
Part 6 — Analysis flags
ANL01FL — one record per analysis cell
Concept. A subject may have more than one record for the same visit — an unscheduled repeat, a re-test, a duplicate load. An analysis needs exactly one. ANL01FL = "Y" marks the record to use.
Why it matters. Without it, a mean at Week 4 would include a subject twice and be quietly wrong.
Study-specific (SAP). Which record to pick is an SAP decision. Common rules: the last by date; the one from the scheduled visit; the worst value for a safety analysis.
Derivation logic (here). Last non-missing record per subject, parameter and analysis visit.
adlb <- adlb |>
arrange(USUBJID, PARAMCD, AVISITN, ADT, LBSEQ) |>
group_by(USUBJID, PARAMCD, AVISITN) |>
mutate(
.elig = !is.na(AVAL),
.n = row_number(),
.last = if (any(.elig)) max(.n[.elig]) else NA_integer_,
ANL01FL = if_else(!is.na(.last) & .n == .last, "Y", NA_character_)
) |>
ungroup() |>
select(-.elig, -.n, -.last)adlb <- adlb[order(adlb$USUBJID, adlb$PARAMCD, adlb$AVISITN,
adlb$ADT, adlb$LBSEQ), ]
rownames(adlb) <- NULL
key3 <- paste(adlb$USUBJID, adlb$PARAMCD, adlb$AVISITN, sep = "|")
elig <- which(!is.na(adlb$AVAL))
adlb$ANL01FL <- NA_character_
if (length(elig) > 0) {
last <- elig[!duplicated(key3[elig], fromLast = TRUE)]
adlb$ANL01FL[last] <- "Y"
}QC — the whole point of the flag.
# Exactly one flagged record per analysis cell
adlb |>
filter(ANL01FL == "Y") |>
count(USUBJID, PARAMCD, AVISITN) |>
filter(n > 1)
#> # A tibble: 0 × 4 ✓ no duplicates
# A summary now cannot double-count
adlb |>
filter(PARAMCD == "ALT", AVISITN == 4, ANL01FL == "Y", SAFFL == "Y") |>
summarise(n = n(), mean_aval = mean(AVAL), .by = TRT01A)The flag only helps if you use it.
# Wrong — may double-count a subject with a repeat assessment
adlb |> filter(PARAMCD == "ALT", AVISITN == 4) |> summarise(mean(AVAL))
# Right
adlb |> filter(PARAMCD == "ALT", AVISITN == 4, ANL01FL == "Y") |>
summarise(mean(AVAL))Every BDS analysis should filter on an analysis flag. If a table program does not, ask why.
Part 7 — Missing data and imputation
The principle
CDISC requirement. SDTM keeps data as collected — a partial date stays partial. ADaM may impute, but every imputed value must carry a flag saying so.
| Variable | Holds |
|---|---|
ASTDT |
The analysis start date, possibly imputed |
ASTDTF |
Which date part was imputed: "D", "M", "Y", or NA |
ASTTMF |
Which time part was imputed |
Flag values (CDISC requirement).
ASTDTF |
Meaning |
|---|---|
NA |
Nothing imputed — the date was complete |
"D" |
Day imputed (input was "2026-03") |
"M" |
Month and day imputed (input was "2026") |
"Y" |
Everything imputed (input was missing) |
A reviewer must be able to identify which values were derived rather than collected. An imputed date with no flag is indistinguishable from a collected one, and that breaks traceability — the second ADaM principle.
Imputing a partial date
Clinical example. Subject 01-002 has AESTDTC = "2026-03" — the month is known, the day is not. First dose was 2026-03-05.
Study-specific (SAP). The imputation rule is an SAP decision. A common conservative convention for AE start dates: impute to the earliest possible date, because an earlier onset is more likely to be classified treatment-emergent, which is the cautious direction for safety.
impute_dtc_start <- function(dtc) {
d <- substr(dtc, 1, 10)
tibble(
dtc = dtc,
precision = case_when(
grepl("^\\d{4}-\\d{2}-\\d{2}$", d) ~ "complete",
grepl("^\\d{4}-\\d{2}$", d) ~ "month",
grepl("^\\d{4}$", d) ~ "year",
.default ~ "missing"
)
) |>
mutate(
imputed = case_when(
precision == "complete" ~ as.Date(d),
precision == "month" ~ as.Date(paste0(d, "-01")),
precision == "year" ~ as.Date(paste0(d, "-01-01")),
.default ~ as.Date(NA)
),
flag = case_when(
precision == "complete" ~ NA_character_,
precision == "month" ~ "D",
precision == "year" ~ "M",
.default ~ "Y"
)
)
}
impute_dtc_start(c("2026-03-15", "2026-03", "2026", ""))
#> # A tibble: 4 × 4
#> dtc precision imputed flag
#> <chr> <chr> <date> <chr>
#> 1 2026-03-15 complete 2026-03-15 NA
#> 2 2026-03 month 2026-03-01 D
#> 3 2026 year 2026-01-01 M
#> 4 "" missing NA Yimpute_dtc_start <- function(dtc) {
d <- substr(dtc, 1, 10)
complete <- grepl("^\\d{4}-\\d{2}-\\d{2}$", d)
month <- grepl("^\\d{4}-\\d{2}$", d)
year <- grepl("^\\d{4}$", d)
imputed <- as.Date(rep(NA_character_, length(d)))
imputed[complete] <- as.Date(d[complete])
imputed[month] <- as.Date(paste0(d[month], "-01"))
imputed[year] <- as.Date(paste0(d[year], "-01-01"))
flag <- rep("Y", length(d))
flag[complete] <- NA_character_
flag[month] <- "D"
flag[year] <- "M"
data.frame(dtc = dtc, imputed = imputed, flag = flag,
stringsAsFactors = FALSE)
}Constraining the imputation
Common convention. A naive imputation can produce an impossible date. If an AE is recorded as “March 2026” and first dose was 5 March, imputing to 1 March places the onset before treatment — which may or may not be what the SAP intends.
The usual constraint: never impute earlier than first dose.
apply_min_date <- function(imputed, flag, min_date) {
# Only constrain where imputation actually happened
constrain <- !is.na(flag) & !is.na(imputed) & !is.na(min_date)
imputed[constrain] <- pmax(imputed[constrain], min_date[constrain])
imputed
}Worked comparison for subject 01-002:
| Rule | ASTDT |
TRTEMFL |
|---|---|---|
| Naive: earliest possible | 2026-03-01 | "N" — before first dose on 03-05 |
Constrained to TRTSDT |
2026-03-05 | "Y" — on first dose |
The same data, two defensible rules, opposite safety conclusions. That is why admiral::derive_vars_dt() exposes a min_dates argument rather than choosing for you, and why this must come from the SAP.
ASTDTF records the precision of the source, not the effect of the constraint. A reviewer needs to know the day was unknown regardless of what the rule then did with it.
flag <- compute_flag(dtc) # from the SOURCE precision
imputed <- compute_imputed(dtc)
imputed <- apply_min_date(imputed, flag, trtsdt) # constrain AFTERPart 8 — ADLB: ranges, grades and shifts
Normal range indicator
Concept. ANRIND classifies a result against its reference range.
CDISC requirement. The permitted values are "LOW", "NORMAL" and "HIGH" (some studies add "ABNORMAL" for tests with no numeric range).
Derivation logic.
if AVAL < ANRLO → "LOW"
if AVAL > ANRHI → "HIGH"
otherwise → "NORMAL"Common convention. The range limits are inclusive — a value exactly equal to ANRHI is "NORMAL", not "HIGH".
adlb <- adlb |>
mutate(
ANRIND = case_when(
is.na(AVAL) ~ NA_character_,
!is.na(ANRLO) & AVAL < ANRLO ~ "LOW",
!is.na(ANRHI) & AVAL > ANRHI ~ "HIGH",
.default ~ "NORMAL"
)
)adlb$ANRIND <- with(adlb, ifelse(
is.na(AVAL), NA_character_,
ifelse(!is.na(ANRLO) & AVAL < ANRLO, "LOW",
ifelse(!is.na(ANRHI) & AVAL > ANRHI, "HIGH", "NORMAL"))
))is.na(AVAL) branch must come first
case_when() takes the first matching condition. A missing AVAL compared with a limit gives NA, which is not TRUE, so it would fall through to .default and be labelled "NORMAL".
For a lab dataset that is badly wrong — a not-done assessment reported as normal. Putting the missing check first is the fix, and it is worth a test:
stopifnot(all(is.na(adlb$ANRIND[is.na(adlb$AVAL)])))Baseline reference range indicator
Concept. BNRIND is the ANRIND of the baseline record, copied to every record — the input to a shift table.
adlb <- adlb |>
mutate(BNRIND = ANRIND[!is.na(ABLFL)][1],
.by = c(USUBJID, PARAMCD))Same broadcast pattern as BASE, applied to the categorical indicator.
Toxicity grades
Concept. ATOXGR grades the severity of a laboratory abnormality, typically using NCI CTCAE criteria.
Real CTCAE grading depends on the parameter, the direction of abnormality, the subject’s baseline, and sometimes the upper limit of normal as a multiplier. The example below is a simplified illustration for ALT only, using multiples of the upper limit of normal.
Production grading uses a metadata table of criteria per parameter, and packages exist for it. Do not generalise this snippet.
Illustrative rule — ALT, CTCAE v5 style:
| Grade | Criterion |
|---|---|
| 0 | AVAL <= ANRHI |
| 1 | > 1× ULN to <= 3× ULN |
| 2 | > 3× ULN to <= 5× ULN |
| 3 | > 5× ULN to <= 20× ULN |
| 4 | > 20× ULN |
adlb <- adlb |>
mutate(
.uln_ratio = if_else(!is.na(ANRHI) & ANRHI > 0, AVAL / ANRHI, NA_real_),
ATOXGR = case_when(
PARAMCD != "ALT" ~ NA_character_, # illustrative: ALT only
is.na(.uln_ratio) ~ NA_character_,
.uln_ratio <= 1 ~ "0",
.uln_ratio <= 3 ~ "1",
.uln_ratio <= 5 ~ "2",
.uln_ratio <= 20 ~ "3",
.default ~ "4"
)
) |>
select(-.uln_ratio)Expected output.
adlb |>
filter(PARAMCD == "ALT", !is.na(AVAL)) |>
select(USUBJID, AVISIT, AVAL, ANRHI, ANRIND, ATOXGR)
#> # A tibble: 11 × 6
#> USUBJID AVISIT AVAL ANRHI ANRIND ATOXGR
#> <chr> <chr> <dbl> <dbl> <chr> <chr>
#> 1 01-001 Screening 28 40 NORMAL 0
#> 2 01-001 Baseline 30 40 NORMAL 0
#> 3 01-001 Week 4 38 40 NORMAL 0
#> 4 01-001 Week 12 52 40 HIGH 1
#> 5 02-004 Week 12 68 40 HIGH 1ALT of 52 against an upper limit of 40 is a ratio of 1.3 — above normal, so ANRIND = "HIGH", and within 1–3× ULN, so grade 1.
Shift tables
Concept. A shift table cross-tabulates baseline category against post-baseline category, showing how subjects moved.
Clinical example. “How many subjects who were normal at baseline became high on treatment?”
shift_data <- adlb |>
filter(PARAMCD == "ALT", SAFFL == "Y", ANL01FL == "Y",
AVISITN > 0, !is.na(BNRIND), !is.na(ANRIND))
shift_table <- shift_data |>
mutate(
BNRIND = factor(BNRIND, levels = c("LOW", "NORMAL", "HIGH")),
ANRIND = factor(ANRIND, levels = c("LOW", "NORMAL", "HIGH"))
) |>
count(TRT01A, BNRIND, ANRIND, .drop = FALSE) |>
pivot_wider(names_from = ANRIND, values_from = n, values_fill = 0)keep <- which(adlb$PARAMCD == "ALT" & adlb$SAFFL == "Y" &
adlb$ANL01FL == "Y" & adlb$AVISITN > 0 &
!is.na(adlb$BNRIND) & !is.na(adlb$ANRIND))
sd <- adlb[keep, ]
sd$BNRIND <- factor(sd$BNRIND, levels = c("LOW", "NORMAL", "HIGH"))
sd$ANRIND <- factor(sd$ANRIND, levels = c("LOW", "NORMAL", "HIGH"))
shift_table <- table(Baseline = sd$BNRIND, Postbaseline = sd$ANRIND)
addmargins(shift_table)Expected output.
shift_table
#> Postbaseline
#> Baseline LOW NORMAL HIGH
#> LOW 0 0 0
#> NORMAL 0 2 2
#> HIGH 0 0 0Two subjects stayed normal; two moved from normal to high.
factor(levels = ) and .drop = FALSE are what make this correct
Without them, a category with no subjects simply does not appear — so the LOW row and column vanish entirely, and the table no longer matches the shell in the SAP.
A blank cell reads as not evaluated; 0 reads as evaluated, none occurred. Declaring the factor levels guarantees every expected row and column exists.
Part 9 — ADAE: treatment emergence
What treatment-emergent means
Concept. A treatment-emergent adverse event (TEAE) is one that started after treatment began, and therefore could plausibly be caused by it. TRTEMFL flags them.
Common convention.
TRTEMFL = "Y" if ASTDT >= TRTSDT
and ASTDT <= TRTEDT + follow-up windowThe follow-up window is typically 30 days, and it is study-specific.
TRTSDT TRTEDT TRTEDT+30
│ │ │
──────────┼───────────────────────────┼──────────────┼──────────▶
│ │ │
not TEAE │◀────────── TEAE ─────────────────────────▶│ not TEAEBuilding ADAE
WINDOW <- 30 # study-specific: from the SAP
adae <- ae |>
left_join(
select(adsl, USUBJID, TRT01A, TRT01AN, TRTSDT, TRTEDT, SAFFL),
by = "USUBJID", relationship = "many-to-one"
) |>
# Impute partial start dates, constrained to first dose
mutate(
.imp = impute_dtc_start(AESTDTC),
ASTDTF = .imp$flag,
ASTDT = apply_min_date(.imp$imputed, .imp$flag, TRTSDT),
AENDT = as.Date(na_if(substr(AEENDTC, 1, 10), "")),
ASTDY = case_when(
is.na(ASTDT) | is.na(TRTSDT) ~ NA_integer_,
ASTDT >= TRTSDT ~ as.integer(ASTDT - TRTSDT) + 1L,
.default ~ as.integer(ASTDT - TRTSDT)
),
TRTEMFL = case_when(
is.na(TRTSDT) ~ "N", # never dosed
is.na(ASTDT) ~ "Y", # conservative
ASTDT < TRTSDT ~ "N",
is.na(TRTEDT) ~ "Y", # ongoing treatment
ASTDT <= TRTEDT + WINDOW ~ "Y",
.default ~ "N"
),
AESEVN = case_when(AESEV == "MILD" ~ 1L,
AESEV == "MODERATE" ~ 2L,
AESEV == "SEVERE" ~ 3L)
) |>
select(-.imp)WINDOW <- 30
adae <- merge(ae, adsl[, c("USUBJID","TRT01A","TRT01AN","TRTSDT","TRTEDT","SAFFL")],
by = "USUBJID", all.x = TRUE, sort = FALSE)
stopifnot(nrow(adae) == nrow(ae))
imp <- impute_dtc_start(adae$AESTDTC)
adae$ASTDTF <- imp$flag
adae$ASTDT <- apply_min_date(imp$imputed, imp$flag, adae$TRTSDT)
end <- substr(adae$AEENDTC, 1, 10)
adae$AENDT <- as.Date(ifelse(end == "", NA_character_, end))
adae$ASTDY <- ifelse(
is.na(adae$ASTDT) | is.na(adae$TRTSDT), NA_integer_,
ifelse(adae$ASTDT >= adae$TRTSDT,
as.integer(adae$ASTDT - adae$TRTSDT) + 1L,
as.integer(adae$ASTDT - adae$TRTSDT))
)
adae$TRTEMFL <- with(adae, ifelse(
is.na(TRTSDT), "N",
ifelse(is.na(ASTDT), "Y",
ifelse(ASTDT < TRTSDT, "N",
ifelse(is.na(TRTEDT), "Y",
ifelse(ASTDT <= TRTEDT + WINDOW, "Y", "N"))))
))
sev_map <- c(MILD = 1L, MODERATE = 2L, SEVERE = 3L)
adae$AESEVN <- unname(sev_map[adae$AESEV])Expected output.
adae |> select(USUBJID, AEDECOD, AESTDTC, ASTDT, ASTDTF, TRTSDT, ASTDY, TRTEMFL)
#> # A tibble: 7 × 8
#> USUBJID AEDECOD AESTDTC ASTDT ASTDTF TRTSDT ASTDY TRTEMFL
#> <chr> <chr> <chr> <date> <chr> <date> <int> <chr>
#> 1 01-001 HEADACHE 2026-02-25 2026-02-25 NA 2026-03-01 -4 N
#> 2 01-001 NAUSEA 2026-03-20 2026-03-20 NA 2026-03-01 20 Y
#> 3 01-002 RASH 2026-03 2026-03-05 D 2026-03-05 1 Y
#> 4 01-002 HEADACHE 2026-06-10 2026-06-10 NA 2026-03-05 98 Y
#> 5 02-004 DIZZINESS 2026-04-15 2026-04-15 NA 2026-03-10 37 Y
#> 6 02-005 FATIGUE 2026-03-15 2026-03-15 NA 2026-03-12 4 Y
#> 7 02-006 RASH 2026-04-18 2026-04-18 NA 2026-04-02 17 YRow 1 is the pre-treatment event — ASTDY = -4, correctly not emergent.
Row 3 is the imputation case. "2026-03" would naively impute to 2026-03-01, which precedes first dose on 03-05. The min_dates constraint pulled it to 03-05, so it becomes day 1 and is emergent. ASTDTF = "D" records that the day was imputed.
Occurrence flags
Concept. An AE table counts subjects, not events. A subject with three headaches counts once. Occurrence flags mark the first occurrence so a table program can simply count flagged rows.
| Flag | Marks the first occurrence per |
|---|---|
AOCCFL |
Subject — for the “any TEAE” row |
AOCCSFL |
Subject and system organ class |
AOCCPFL |
Subject and preferred term |
flag_first <- function(data, keys, flag_name) {
data |>
arrange(USUBJID, ASTDT, AESEQ) |>
group_by(across(all_of(keys))) |>
mutate(
.elig = TRTEMFL == "Y",
.n = row_number(),
.first = if (any(.elig)) min(.n[.elig]) else NA_integer_,
"{flag_name}" := if_else(!is.na(.first) & .n == .first,
"Y", NA_character_)
) |>
ungroup() |>
select(-.elig, -.n, -.first)
}
adae <- adae |>
flag_first(c("USUBJID"), "AOCCFL") |>
flag_first(c("USUBJID", "AEBODSYS"), "AOCCSFL") |>
flag_first(c("USUBJID", "AEDECOD"), "AOCCPFL")flag_first_base <- function(data, key_cols, flag_name) {
data <- data[order(data$USUBJID, data$ASTDT, data$AESEQ), ]
rownames(data) <- NULL
key <- do.call(paste, c(data[key_cols], sep = "|"))
elig <- which(data$TRTEMFL == "Y")
data[[flag_name]] <- NA_character_
if (length(elig) > 0) {
first <- elig[!duplicated(key[elig])] # NOT fromLast — we want first
data[[flag_name]][first] <- "Y"
}
data
}
adae <- flag_first_base(adae, "USUBJID", "AOCCFL")
adae <- flag_first_base(adae, c("USUBJID", "AEBODSYS"), "AOCCSFL")
adae <- flag_first_base(adae, c("USUBJID", "AEDECOD"), "AOCCPFL")Using the flags — an incidence table in three lines:
denom <- adsl |> filter(SAFFL == "Y") |> count(TRT01A, name = "N")
adae |>
filter(SAFFL == "Y", TRTEMFL == "Y", AOCCPFL == "Y") |>
count(TRT01A, AEDECOD, name = "n") |>
left_join(denom, by = "TRT01A") |>
mutate(pct = round(100 * n / N, 1))
#> # A tibble: 6 × 5
#> TRT01A AEDECOD n N pct
#> <chr> <chr> <int> <int> <dbl>
#> 1 Drug A DIZZINESS 1 3 33.3
#> 2 Drug A NAUSEA 1 3 33.3
#> 3 Drug A RASH 1 3 33.3
#> 4 Placebo FATIGUE 1 2 50
#> 5 Placebo HEADACHE 1 2 50
#> 6 Placebo RASH 1 2 50count(adae$TRT01A) counts events. The denominator must be the number of subjects in the population, which lives in ADSL.
This is the most common error in AE table programming, and deriving the occurrence flags in ADaM is what makes the table program short enough to get right. See TLF generation.
Part 10 — ADTTE: time to event
The structure
Concept. ADTTE is a BDS dataset where AVAL is a duration and CNSR says whether the event happened.
| Variable | Meaning |
|---|---|
AVAL |
Time to event or censoring, in AVALU units |
CNSR |
0 = event occurred, 1 = censored |
STARTDT |
The time origin |
ADT |
The event or censoring date |
EVNTDESC |
What happened, in words |
SRCDOM, SRCVAR, SRCSEQ |
Traceability to the source record |
CNSR = 0 means the event occurred
This is the opposite of the convention in survival, lifelines and most other software, where event = 1 means the event occurred.
library(survival)
# WRONG — inverted, the curve will rise
survfit(Surv(AVAL, CNSR) ~ TRT01A, data = adtte)
# RIGHT
survfit(Surv(AVAL, 1 - CNSR) ~ TRT01A, data = adtte)Getting this backwards produces a survival curve that goes up, or — worse — one that looks plausible and is exactly wrong. Assert it:
stopifnot(all(adtte$CNSR %in% c(0, 1)))
stopifnot(all(!is.na(adtte$ADT[adtte$CNSR == 0]))) # events need a dateBuilding ADTTE
Clinical example. Time to first treatment-emergent rash. Subjects who never had one are censored at their last known assessment.
Derivation logic.
- Define the event: first TEAE with
AEDECOD == "RASH" - Define the censoring: the last date we know anything about the subject
- For each subject, take the event if there is one, otherwise the censor
AVAL= days fromSTARTDTtoADT, inclusive
# --- 1. Events -------------------------------------------------------------
events <- adae |>
filter(TRTEMFL == "Y", AEDECOD == "RASH", !is.na(ASTDT)) |>
arrange(USUBJID, ASTDT, AESEQ) |>
slice_head(n = 1, by = USUBJID) |>
transmute(
USUBJID, ADT = ASTDT, CNSR = 0L,
EVNTDESC = "First treatment-emergent rash",
SRCDOM = "ADAE", SRCVAR = "ASTDT", SRCSEQ = AESEQ
)
# --- 2. Censoring ---------------------------------------------------------
censors <- adsl |>
filter(SAFFL == "Y") |>
transmute(
USUBJID, ADT = TRTEDT, CNSR = 1L,
EVNTDESC = "Last known assessment (treatment end)",
SRCDOM = "ADSL", SRCVAR = "TRTEDT", SRCSEQ = NA_integer_
)
# --- 3. Event takes precedence over censoring ----------------------------
adtte <- bind_rows(events, censors) |>
arrange(USUBJID, CNSR) |> # CNSR 0 sorts before 1
slice_head(n = 1, by = USUBJID) |>
left_join(
select(adsl, USUBJID, TRT01A, TRT01AN, TRTSDT, SAFFL),
by = "USUBJID", relationship = "one-to-one"
) |>
mutate(
STARTDT = TRTSDT,
PARAMCD = "TTRASH",
PARAM = "Time to First Treatment-Emergent Rash (days)",
AVAL = as.integer(ADT - STARTDT) + 1L,
AVALU = "DAYS"
) |>
select(USUBJID, TRT01A, TRT01AN, PARAMCD, PARAM, STARTDT, ADT,
AVAL, AVALU, CNSR, EVNTDESC, SRCDOM, SRCVAR, SRCSEQ, SAFFL)Code explanation.
slice_head(n = 1, by = USUBJID)after sorting takes the first row per subject — the earliest rash.bind_rows()stacks the event and censor candidates, thenarrange(USUBJID, CNSR)puts events (CNSR = 0) before censors (CNSR = 1), soslice_head()keeps the event when one exists. That is a neat way to express “event takes precedence” without a join.SRCDOM/SRCVAR/SRCSEQrecord where the date came from — this is traceability, and it is a CDISC expectation for ADTTE.
Expected output.
adtte |> select(USUBJID, TRT01A, STARTDT, ADT, AVAL, CNSR, EVNTDESC)
#> # A tibble: 5 × 7
#> USUBJID TRT01A STARTDT ADT AVAL CNSR EVNTDESC
#> <chr> <chr> <date> <date> <int> <int> <chr>
#> 1 01-001 Drug A 2026-03-01 2026-06-15 107 1 Last known assessment…
#> 2 01-002 Placebo 2026-03-05 2026-03-05 1 0 First treatment-emerg…
#> 3 02-004 Drug A 2026-03-10 2026-06-25 108 1 Last known assessment…
#> 4 02-005 Placebo 2026-03-12 2026-05-30 80 1 Last known assessment…
#> 5 02-006 Drug A 2026-04-02 2026-04-18 17 0 First treatment-emerg…Two events (CNSR = 0), three censored. Subject 01-003 is absent because they are not in the safety population.
Analysis:
library(survival)
fit <- survfit(Surv(AVAL, 1 - CNSR) ~ TRT01A, data = adtte)
summary(fit)
survdiff(Surv(AVAL, 1 - CNSR) ~ TRT01A, data = adtte)QC.
stopifnot(
"CNSR must be 0 or 1" = all(adtte$CNSR %in% c(0L, 1L)),
"one row per subject" = !anyDuplicated(adtte$USUBJID),
"AVAL must be positive" = all(adtte$AVAL >= 1),
"events need a date" = all(!is.na(adtte$ADT[adtte$CNSR == 0])),
"ADT must not precede STARTDT" = all(adtte$ADT >= adtte$STARTDT),
"traceability populated" = all(!is.na(adtte$SRCDOM))
)Part 11 — Merging, keys and traceability
Keys
Concept. The key is the set of variables that uniquely identifies a record. Knowing it tells you what a safe merge looks like.
| Dataset | Key |
|---|---|
| ADSL | USUBJID |
| ADAE | USUBJID, AESEQ |
| ADLB | USUBJID, PARAMCD, AVISITN, ADT |
| ADTTE | USUBJID, PARAMCD |
check_key <- function(data, key, name = "dataset") {
dupes <- data |> count(across(all_of(key))) |> filter(n > 1)
if (nrow(dupes) > 0) {
stop(sprintf("%s: %d duplicate key combination(s) on %s",
name, nrow(dupes), paste(key, collapse = " + ")),
call. = FALSE)
}
message(sprintf("%s: key %s is unique across %d records",
name, paste(key, collapse = " + "), nrow(data)))
invisible(data)
}
check_key(adsl, "USUBJID", "ADSL")
#> ADSL: key USUBJID is unique across 6 records
check_key(adae, c("USUBJID", "AESEQ"), "ADAE")
#> ADAE: key USUBJID + AESEQ is unique across 7 recordsRun this on every dataset you build. It takes one line and catches the problem that causes the most downstream damage.
Safe merging
nrow(adsl) #> 6
nrow(left_join(adsl, ae, by = "USUBJID")) #> 8A left join on a non-unique right-hand key multiplies rows. ADSL is no longer one row per subject, and every percentage computed from it is wrong.
dplyr protects you if you ask:
left_join(adsl, ae, by = "USUBJID", relationship = "many-to-one")
#> Error in `left_join()`:
#> ! Each row in `x` must match at most 1 row in `y`.
left_join(adsl, ae_summary, by = "USUBJID", unmatched = "error")
#> errors if any ADSL subject has no matchBase merge() protects you not at all — you must assert:
out <- merge(adsl, ae_summary, by = "USUBJID", all.x = TRUE, sort = FALSE)
stopifnot(nrow(out) == nrow(adsl))A reusable safe merge for base R:
safe_merge <- function(x, y, by, expect = "many-to-one") {
if (anyDuplicated(y[, by, drop = FALSE])) {
stop("Right-hand side has duplicate keys on ",
paste(by, collapse = ", "), " — the merge would multiply rows.",
call. = FALSE)
}
out <- merge(x, y, by = by, all.x = TRUE, sort = FALSE)
if (nrow(out) != nrow(x)) {
stop("Merge changed the row count: ", nrow(x), " -> ", nrow(out),
call. = FALSE)
}
out
}Sorting and record order
Common convention. ADaM datasets are delivered sorted by their key.
adsl <- adsl |> arrange(USUBJID)
adae <- adae |> arrange(USUBJID, AESEQ)
adlb <- adlb |> arrange(USUBJID, PARAMN, AVISITN, ADT)Any derivation using “first” or “last” depends on the sort. If two records tie on every sort key, which one is chosen is arbitrary and may differ between runs or platforms.
Always sort down to a unique key — typically --SEQ:
# Not deterministic: two AEs on the same date with the same term
arrange(adae, USUBJID, ASTDT)
# Deterministic
arrange(adae, USUBJID, ASTDT, AEDECOD, AESEQ)A non-deterministic sort is one of the hardest QC differences to diagnose, because it reproduces inconsistently.
Traceability variables
CDISC requirement. Keep the link back to SDTM.
| Variable | Records |
|---|---|
--SEQ from the parent |
Which SDTM record this came from |
SRCDOM, SRCVAR, SRCSEQ |
Source domain, variable and sequence |
ASTDTF, ASTTMF |
That a date was imputed |
| Original value retained | LBSTRESN next to AVAL |
# Traceable: a reviewer can get from AVAL back to the LB record
adlb |> select(USUBJID, LBSEQ, LBTESTCD, LBSTRESN, PARAMCD, AVAL, BASE, CHG)Part 12 — Metadata, specifications and Define-XML
The specification as data
Concept. The ADaM specification lists every variable with its label, type, length and derivation. If it is stored as data rather than prose, the code can read it, apply it and check against it.
adsl_spec <- tibble(
variable = c("STUDYID", "USUBJID", "AGE", "AGEU", "SEX",
"TRT01P", "TRT01PN", "TRTSDT", "TRTDURD", "SAFFL"),
label = c("Study Identifier", "Unique Subject Identifier",
"Age", "Age Units", "Sex",
"Planned Treatment for Period 01",
"Planned Treatment for Period 01 (N)",
"Date of First Exposure", "Total Treatment Duration (Days)",
"Safety Population Flag"),
type = c("text", "text", "integer", "text", "text",
"text", "integer", "date", "integer", "text"),
length = c(20L, 30L, 8L, 6L, 1L, 40L, 8L, 8L, 8L, 1L),
origin = c("Assigned", "Predecessor", "Predecessor", "Predecessor",
"Predecessor", "Predecessor", "Derived", "Derived",
"Derived", "Derived"),
codelist = c(NA, NA, NA, NA, "SEX", "TRT01P", NA, NA, NA, "NY")
)Origin types (CDISC requirement).
| Origin | Means |
|---|---|
Predecessor |
Copied unchanged from another dataset — names the source |
Derived |
Computed — requires a method description |
Assigned |
Set by the sponsor, not computed |
Collected |
Recorded on the CRF |
Protocol |
Specified in the protocol |
Predecessor is the one that carries traceability in ADaM. A variable copied from SDTM should be Predecessor with the source named — not Derived.
Checking the dataset against its specification
check_against_spec <- function(data, spec, name = "dataset") {
problems <- character()
missing <- setdiff(spec$variable, names(data))
if (length(missing)) {
problems <- c(problems,
sprintf("Specified but absent: %s", paste(missing, collapse = ", ")))
}
extra <- setdiff(names(data), spec$variable)
if (length(extra)) {
problems <- c(problems,
sprintf("Present but unspecified: %s", paste(extra, collapse = ", ")))
}
# Character lengths, measured in BYTES for the transport format
for (i in seq_len(nrow(spec))) {
v <- spec$variable[i]
if (!v %in% names(data) || spec$type[i] != "text") next
actual <- suppressWarnings(
max(nchar(as.character(data[[v]]), type = "bytes"), na.rm = TRUE))
if (is.finite(actual) && actual > spec$length[i]) {
problems <- c(problems, sprintf(
"%s: %d bytes exceeds specified length %d — values WILL be truncated",
v, actual, spec$length[i]))
}
}
if (length(problems)) {
stop(sprintf("%s does not conform to its specification:\n %s",
name, paste(problems, collapse = "\n ")), call. = FALSE)
}
message(sprintf("%s conforms to its specification (%d variables)",
name, nrow(spec)))
invisible(data)
}Checking lengths before writing matters: transport-format truncation is silent, as lesson 11 explains.
Applying labels
apply_labels <- function(data, spec) {
for (i in seq_len(nrow(spec))) {
v <- spec$variable[i]
if (v %in% names(data)) {
attr(data[[v]], "label") <- spec$label[i]
}
}
data
}
adsl_labelled <- apply_labels(adsl, adsl_spec)
attr(adsl_labelled$TRTDURD, "label")
#> [1] "Total Treatment Duration (Days)"x <- adsl_labelled |> mutate(AGE = AGE + 1)
attr(x$AGE, "label")
#> NULLAs covered in lesson 2, R drops attributes through most operations. Apply labels last, immediately before writing — which is exactly what xportr is for.
Define-XML, in one paragraph
Define-XML is the machine-readable data definition document that accompanies a submission. It describes every dataset, variable, codelist and derivation — and it is generated from the specification above, not written separately. That is the whole argument for keeping the spec as data: one artefact produces both the dataset conformance and the define. See lesson 10.
Part 13 — Automated validation and independent QC
Three layers of checking
┌─────────────────────────────────────────────────┐
│ Independent QC │ slow, authoritative
│ A second programmer, working from the SPEC │
├─────────────────────────────────────────────────┤
│ Dataset checks │ fast, every run
│ Keys, ranges, flags, logical consistency │
├─────────────────────────────────────────────────┤
│ Unit tests │ fastest, every change
│ Derivation functions against known inputs │
└─────────────────────────────────────────────────┘Layer 1 — Unit tests on derivation functions
Extract the logic into functions, then test them.
compute_ady <- function(adt, trtsdt) {
ifelse(is.na(adt) | is.na(trtsdt), NA_integer_,
ifelse(adt >= trtsdt,
as.integer(adt - trtsdt) + 1L,
as.integer(adt - trtsdt)))
}
# --- Tests (run with testthat in a real project) -------------------------
d <- as.Date("2026-03-01")
stopifnot(
"first dose is day 1" = compute_ady(d, d) == 1L,
"day after is day 2" = compute_ady(d + 1L, d) == 2L,
"day before is day -1" = compute_ady(d - 1L, d) == -1L,
"there is no day zero" = !0L %in% c(compute_ady(d - 1L, d),
compute_ady(d, d)),
"missing propagates" = is.na(compute_ady(as.Date(NA), d)),
"vectorised" = length(compute_ady(c(d, d + 5L), d)) == 2L
)The "there is no day zero" test is the valuable one. It encodes a CDISC rule in a form that fails loudly if someone later “simplifies” the function.
See Testing with testthat for the full framework.
Layer 2 — Dataset-level checks
validate_adsl <- function(data) {
checks <- list(
one_row_per_subject = !anyDuplicated(data$USUBJID),
usubjid_populated = all(!is.na(data$USUBJID) & data$USUBJID != ""),
flags_valid = all(unlist(data[c("SAFFL","ITTFL","FASFL")])
%in% c("Y","N")),
saffl_needs_trtsdt = all(data$SAFFL == "N" | !is.na(data$TRTSDT)),
fas_subset_of_itt = all(data$ITTFL[data$FASFL == "Y"] == "Y"),
trtedt_after_trtsdt = all(is.na(data$TRTEDT) | is.na(data$TRTSDT) |
data$TRTEDT >= data$TRTSDT),
trtdurd_positive = all(is.na(data$TRTDURD) | data$TRTDURD >= 1),
age_plausible = all(is.na(data$AGE) | (data$AGE >= 0 & data$AGE <= 120))
)
results <- tibble(
check = names(checks),
passed = unlist(checks)
)
failed <- results |> filter(!passed)
if (nrow(failed) > 0) {
stop("ADSL validation failed:\n ",
paste(failed$check, collapse = "\n "), call. = FALSE)
}
results
}
validate_adsl(adsl)
#> # A tibble: 8 × 2
#> check passed
#> <chr> <lgl>
#> 1 one_row_per_subject TRUE
#> 2 usubjid_populated TRUE
#> ...Returning a table of results — rather than just failing — gives you something to put in a validation report.
Layer 3 — Independent QC comparison
Concept. A second programmer writes the derivation independently from the specification, not from the production code. The two outputs are compared.
If the QC programmer reads the production code first, they inherit its misreadings of the specification — which is the main thing double programming exists to catch.
compare_datasets <- function(prod, qc, keys, tolerance = 1e-8) {
# Structure
only_prod <- setdiff(names(prod), names(qc))
only_qc <- setdiff(names(qc), names(prod))
common <- intersect(names(prod), names(qc))
common_nonkey <- setdiff(common, keys)
merged <- merge(prod[common], qc[common], by = keys,
suffixes = c(".prod", ".qc"))
diffs <- lapply(common_nonkey, function(v) {
p <- merged[[paste0(v, ".prod")]]
q <- merged[[paste0(v, ".qc")]]
differs <- if (is.numeric(p) && is.numeric(q)) {
!(abs(p - q) < tolerance | (is.na(p) & is.na(q)))
} else {
!(as.character(p) == as.character(q) | (is.na(p) & is.na(q)))
}
differs[is.na(differs)] <- TRUE
if (any(differs)) {
data.frame(
merged[differs, keys, drop = FALSE],
variable = v,
prod = as.character(p[differs]),
qc = as.character(q[differs]),
stringsAsFactors = FALSE
)
}
})
value_diffs <- do.call(rbind, Filter(Negate(is.null), diffs))
list(
n_prod = nrow(prod),
n_qc = nrow(qc),
only_in_prod = only_prod,
only_in_qc = only_qc,
value_diffs = value_diffs,
match = length(only_prod) == 0 && length(only_qc) == 0 &&
nrow(prod) == nrow(qc) && is.null(value_diffs)
)
}result <- compare_datasets(adsl_prod, adsl_qc, keys = "USUBJID")
if (result$match) {
message("MATCH — all values equal")
} else {
print(result$value_diffs)
}
#> USUBJID variable prod qc
#> 1 01-001 TRTDURD 106 107
#> 2 01-002 TRTDURD 107 108Reading a difference. Every TRTDURD differs by exactly 1 — a clean, systematic offset. That is the signature of a convention difference, not a calculation error: one programmer used TRTEDT - TRTSDT, the other added the inclusive + 1. The ADaMIG says inclusive, so the first is wrong.
| Difference pattern | Usual cause |
|---|---|
| Systematic ±1 on a day variable | The no-day-zero or inclusive-duration rule |
| Differences only on boundary values | right = TRUE/FALSE in a band |
| Differences only where a date is partial | Imputation convention |
| Last decimal only | Rounding; floating point |
| A handful of subjects | A genuine edge case — the valuable finding |
| Different row counts | Population definition disagreement |
In practice, most differences are resolved by amending the specification. That is the most valuable output of the exercise, and it is covered fully in Validation and testing.
From teaching code to production code
Everything above is correct for the examples given and is not production code. The gap:
| This chapter | A regulatory deliverable also needs |
|---|---|
| Rules chosen for illustration | Every rule traced to an SAP section |
stopifnot() inline |
A documented validation plan with recorded results |
| Six subjects | Edge cases from the real data, found and handled |
| One imputation convention | The convention the SAP specifies, with the flag |
| A comparison function | Independent programming by a second person |
| Hand-written derivations | Tested library functions where they exist |
| Labels applied in R | xportr and a conformant XPT |
| Spec as a tibble | A controlled specification and a define.xml |
| Code in a chapter | Version control, review, change control |
Two honest conclusions:
Use admiral for real work. derive_vars_dt(), derive_var_trtdurd() and restrict_derivation() implement exactly what we wrote by hand, with edge cases you have not thought of and a test suite thousands of people exercise. Writing derivations from scratch for a submission means re-deriving that for free. Lesson 5 is next for that reason.
But understanding this first is not wasted. You now know what restrict_derivation() is for, why min_dates exists, and what goes wrong without them. That is the difference between calling a function and knowing whether the answer is right.
Exercises
Exercise 4.1 — Find the bug (beginner)
Look again at the ALT output for subject 01-002:
#> 6 01-002 Baseline 0 24 Y 24 NA NAADY is 0. Explain why that is impossible, find the cause in the mock data, and say what the correct ADY should be.
Show solution
Why it is impossible. CDISC defines study day with no day zero: the day of first dose is day 1, and the day before is day −1. A value of 0 cannot occur if ADY is derived correctly.
Finding the cause.
adlb |>
filter(USUBJID == "01-002", AVISIT == "Baseline") |>
select(USUBJID, LBDTC, ADT, TRTSDT, ADY)
#> USUBJID LBDTC ADT TRTSDT ADY
#> 1 01-002 2026-03-04 2026-03-04 2026-03-05 0The lab was collected on 2026-03-04 and first dose was 2026-03-05 — the lab is one day before first dose.
as.integer(as.Date("2026-03-04") - as.Date("2026-03-05"))
#> [1] -1So the correct answer is ADY = -1, and our derivation gives 0. The bug is in the derivation, not the data.
Where exactly. Look at the case_when():
ADY = case_when(
is.na(ADT) | is.na(TRTSDT) ~ NA_integer_,
ADT >= TRTSDT ~ as.integer(ADT - TRTSDT) + 1L,
.default ~ as.integer(ADT - TRTSDT)
)That logic is actually correct — 2026-03-04 >= 2026-03-05 is FALSE, so it takes the .default branch and returns -1.
So why did the output show 0? Because the printed output in Part 4 was produced before ADY was recomputed after the dates were merged — a stale intermediate. Re-run the pipeline in order and it resolves:
adlb <- adlb |>
mutate(
ADT = as.Date(LBDTC),
ADY = case_when(
is.na(ADT) | is.na(TRTSDT) ~ NA_integer_,
ADT >= TRTSDT ~ as.integer(ADT - TRTSDT) + 1L,
.default ~ as.integer(ADT - TRTSDT)
)
)
adlb |> filter(USUBJID == "01-002", AVISIT == "Baseline") |> pull(ADY)
#> [1] -1 ✓The real lesson. This is a pipeline ordering bug, and it is extremely common: a variable derived from another variable that had not been created yet, producing NA or a wrong value silently.
Two defences:
# 1. Assert the invariant — this would have caught it immediately
stopifnot("ADY must never be 0" = !any(adlb$ADY == 0, na.rm = TRUE))
# 2. Build in one pipeline so ordering is explicit and visible
adlb <- lb |>
left_join(adsl_vars, by = "USUBJID") |> # TRTSDT must exist first
mutate(ADT = as.Date(LBDTC)) |> # then ADT
mutate(ADY = compute_ady(ADT, TRTSDT)) # then ADYADY != 0 assertion is worth adding to every BDS validation function.
Exercise 4.2 — An alternative baseline rule (intermediate)
The SAP is amended: baseline is now the average of all non-missing pre-dose assessments, not the last one. Rewrite the baseline derivation, show how subject 01-001’s ALT results change, and explain what happens to ABLFL.
Show solution
derive_baseline_average <- function(data) {
data |>
arrange(USUBJID, PARAMCD, ADT, AVISITN) |>
mutate(
# BASE is now a computed average, not a copied value
BASE = mean(AVAL[!is.na(AVAL) & !is.na(ADY) & ADY <= 0], na.rm = TRUE),
BASE = if_else(is.nan(BASE), NA_real_, BASE),
.by = c(USUBJID, PARAMCD)
) |>
mutate(
CHG = if_else(ADY > 0, AVAL - BASE, NA_real_),
PCHG = if_else(ADY > 0 & !is.na(BASE) & BASE != 0,
100 * (AVAL - BASE) / BASE, NA_real_)
)
}
adlb_avg <- derive_baseline_average(adlb)Subject 01-001, ALT — the two rules compared:
adlb |>
filter(USUBJID == "01-001", PARAMCD == "ALT") |>
select(AVISIT, ADY, AVAL, BASE_last = BASE, CHG_last = CHG) |>
left_join(
adlb_avg |>
filter(USUBJID == "01-001", PARAMCD == "ALT") |>
select(AVISIT, BASE_avg = BASE, CHG_avg = CHG),
by = "AVISIT"
)
#> AVISIT ADY AVAL BASE_last CHG_last BASE_avg CHG_avg
#> 1 Screening -9 28 30 NA 29 NA
#> 2 Baseline -1 30 30 NA 29 NA
#> 3 Week 4 29 38 30 8 29 9
#> 4 Week 12 85 52 30 22 29 23The pre-dose values are 28 (screening) and 30 (baseline visit).
- Last rule:
BASE = 30, so Week 4 change is38 - 30 = 8 - Average rule:
BASE = (28 + 30) / 2 = 29, so Week 4 change is38 - 29 = 9
Every post-baseline CHG shifts by 1, and every PCHG changes too. On a real study with hundreds of subjects, a different baseline rule moves the treatment effect estimate.
What happens to ABLFL. This is the interesting part.
ABLFL flags the record that is the baseline. Under an averaging rule, no single record is the baseline — the baseline is a computed value that exists on no row.
Three options, in order of preference:
# Option 1 (preferred): do not populate ABLFL at all
# Honest: there is no baseline record. Document it in the define.
# Downside: any program filtering ABLFL == "Y" finds nothing.
# Option 2: flag all contributing records
adlb_avg <- adlb_avg |>
mutate(ABLFL = if_else(!is.na(AVAL) & ADY <= 0, "Y", NA_character_),
.by = c(USUBJID, PARAMCD))
# Downside: BREAKS the ADaM convention that ABLFL identifies ONE record.
# A program doing slice(ABLFL == "Y") now gets two rows per subject.
# Option 3: add a derived baseline RECORD with DTYPE
adlb_avg_rows <- adlb_avg |>
filter(!is.na(AVAL), ADY <= 0) |>
summarise(
AVAL = mean(AVAL), AVISIT = "Baseline", AVISITN = 0L,
ADY = 0L, ABLFL = "Y", DTYPE = "AVERAGE",
.by = c(USUBJID, PARAMCD, PARAM, TRT01A, TRT01AN)
)
adlb_final <- bind_rows(adlb_avg, adlb_avg_rows)Option 3 is the CDISC-conformant answer. ADaM provides DTYPE (derivation type) precisely for records that were derived rather than collected. A derived baseline record carries DTYPE = "AVERAGE", ABLFL = "Y", and analyses exclude derived records when they want collected ones only:
adlb_final |> filter(is.na(DTYPE)) # collected records only
adlb_final |> filter(ABLFL == "Y") # the derived baselineNote the ADY = 0 in option 3 — which we have just established is impossible for a collected record. For a derived record with no real date it is a reasonable placeholder, but check what your SAP and define expect; some sponsors leave ADY missing on derived records instead.
ABLFL, and possibly the row structure of the dataset. This is why baseline derivation is the single most reviewed part of a BDS specification.
Exercise 4.3 — Mini-project: a complete ADaM pipeline (advanced)
Build an end-to-end pipeline that takes the five SDTM datasets, produces ADSL, ADLB and ADAE, validates each, and emits one summary table. Requirements:
- Each dataset built by its own function with postconditions
- Every derivation unit-tested
- A key-uniqueness check on each output
- One demographics table and one AE incidence table
- A run log recording what was produced and whether checks passed
Show solution
#-------------------------------------------------------------------------------
# Mini-project: SDTM -> ADaM -> summary tables, with validation
# Self-contained: run after creating dm, ex, ds, lb, ae from Part 2
#-------------------------------------------------------------------------------
library(dplyr); library(tidyr); library(tibble)
# ==============================================================
# 1. Reusable derivation functions (each unit-testable)
# ==============================================================
compute_ady <- function(adt, trtsdt) {
ifelse(is.na(adt) | is.na(trtsdt), NA_integer_,
ifelse(adt >= trtsdt, as.integer(adt - trtsdt) + 1L,
as.integer(adt - trtsdt)))
}
compute_trtdurd <- function(trtsdt, trtedt) {
ifelse(is.na(trtsdt) | is.na(trtedt), NA_integer_,
as.integer(trtedt - trtsdt) + 1L)
}
band_age <- function(age) {
as.character(cut(age, c(-Inf, 65, 80, Inf),
labels = c("<65", "65-79", ">=80"), right = FALSE))
}
classify_range <- function(aval, lo, hi) {
ifelse(is.na(aval), NA_character_,
ifelse(!is.na(lo) & aval < lo, "LOW",
ifelse(!is.na(hi) & aval > hi, "HIGH", "NORMAL")))
}
# ==============================================================
# 2. Unit tests — run before anything else
# ==============================================================
run_unit_tests <- function() {
d <- as.Date("2026-03-01")
tests <- list(
ady_first_dose_is_1 = compute_ady(d, d) == 1L,
ady_no_day_zero = compute_ady(d - 1L, d) == -1L,
ady_handles_na = is.na(compute_ady(as.Date(NA), d)),
trtdurd_inclusive = compute_trtdurd(d, d) == 1L,
trtdurd_two_days = compute_trtdurd(d, d + 1L) == 2L,
age_band_65_is_middle = band_age(65) == "65-79",
age_band_64_is_low = band_age(64) == "<65",
age_band_80_is_high = band_age(80) == ">=80",
range_equal_hi_normal = classify_range(40, 7, 40) == "NORMAL",
range_above_hi_high = classify_range(41, 7, 40) == "HIGH",
range_na_stays_na = is.na(classify_range(NA, 7, 40))
)
failed <- names(tests)[!unlist(tests)]
if (length(failed)) stop("Unit tests failed:\n ",
paste(failed, collapse = "\n "), call. = FALSE)
message(sprintf("Unit tests: %d passed", length(tests)))
invisible(TRUE)
}
# ==============================================================
# 3. Dataset builders
# ==============================================================
build_adsl <- function(dm, ex, lb) {
ex_dates <- ex |>
filter(EXDOSE > 0 | (EXDOSE == 0 & grepl("PLACEBO", EXTRT))) |>
summarise(TRTSDT = min(as.Date(EXSTDTC), na.rm = TRUE),
TRTEDT = max(as.Date(EXENDTC), na.rm = TRUE),
.by = USUBJID)
trt_map <- c("Placebo" = 0L, "Drug A" = 1L)
out <- dm |>
select(STUDYID, USUBJID, SUBJID, SITEID, AGE, AGEU, SEX, RACE,
ARM, ARMCD, ACTARM, COUNTRY) |>
mutate(TRT01P = ARM, TRT01A = ACTARM) |>
left_join(ex_dates, by = "USUBJID", relationship = "one-to-one") |>
mutate(
TRT01PN = unname(trt_map[TRT01P]),
TRT01AN = unname(trt_map[TRT01A]),
TRTDURD = compute_trtdurd(TRTSDT, TRTEDT),
SAFFL = if_else(!is.na(TRTSDT), "Y", "N"),
ITTFL = if_else(ARMCD != "SCRNFAIL", "Y", "N"),
AGEGR1 = band_age(AGE),
AGEGR1N = c("<65" = 1L, "65-79" = 2L, ">=80" = 3L)[AGEGR1] |> unname()
)
post_bl <- lb |>
inner_join(select(out, USUBJID, TRTSDT), by = "USUBJID") |>
filter(!is.na(LBSTRESN), !is.na(TRTSDT), as.Date(LBDTC) > TRTSDT) |>
distinct(USUBJID) |> pull(USUBJID)
out <- out |>
mutate(FASFL = if_else(ITTFL == "Y" & USUBJID %in% post_bl, "Y", "N")) |>
arrange(USUBJID)
stopifnot(
"ADSL: not one row per subject" = !anyDuplicated(out$USUBJID),
"ADSL: subjects lost" = setequal(out$USUBJID, dm$USUBJID),
"ADSL: bad flag values" =
all(unlist(out[c("SAFFL","ITTFL","FASFL")]) %in% c("Y","N")),
"ADSL: FAS not subset of ITT" = all(out$ITTFL[out$FASFL == "Y"] == "Y")
)
out
}
build_adlb <- function(lb, adsl, param_lookup, visit_lookup) {
out <- lb |>
left_join(param_lookup, by = "LBTESTCD", relationship = "many-to-one") |>
left_join(visit_lookup, by = "VISIT", relationship = "many-to-one") |>
left_join(select(adsl, USUBJID, TRT01A, TRT01AN, TRTSDT, TRTEDT,
SAFFL, ITTFL),
by = "USUBJID", relationship = "many-to-one") |>
mutate(
AVAL = LBSTRESN,
AVALC = if_else(is.na(LBSTRESN), LBORRES, NA_character_),
AVALU = LBSTRESU,
ANRLO = LBSTNRLO, ANRHI = LBSTNRHI,
ADT = as.Date(LBDTC),
ADY = compute_ady(ADT, TRTSDT),
ANRIND = classify_range(AVAL, ANRLO, ANRHI)
) |>
arrange(USUBJID, PARAMCD, ADT, AVISITN)
# Baseline flag: last eligible pre-dose record
out <- out |>
group_by(USUBJID, PARAMCD) |>
mutate(
.elig = !is.na(AVAL) & !is.na(ADY) & ADY <= 0,
.n = row_number(),
.last = if (any(.elig)) max(.n[.elig]) else NA_integer_,
ABLFL = if_else(!is.na(.last) & .n == .last, "Y", NA_character_)
) |>
ungroup() |>
select(-.elig, -.n, -.last)
# Baseline value, change, analysis flag
out <- out |>
mutate(BASE = AVAL[!is.na(ABLFL)][1],
BNRIND = ANRIND[!is.na(ABLFL)][1],
.by = c(USUBJID, PARAMCD)) |>
mutate(
CHG = if_else(ADY > 0, AVAL - BASE, NA_real_),
PCHG = if_else(ADY > 0 & !is.na(BASE) & BASE != 0,
100 * (AVAL - BASE) / BASE, NA_real_)
) |>
group_by(USUBJID, PARAMCD, AVISITN) |>
mutate(
.elig = !is.na(AVAL), .n = row_number(),
.last = if (any(.elig)) max(.n[.elig]) else NA_integer_,
ANL01FL = if_else(!is.na(.last) & .n == .last, "Y", NA_character_)
) |>
ungroup() |>
select(-.elig, -.n, -.last)
stopifnot(
"ADLB: row count changed" = nrow(out) == nrow(lb),
"ADLB: ADY must never be 0" = !any(out$ADY == 0, na.rm = TRUE),
"ADLB: multiple baselines" =
nrow(count(filter(out, ABLFL == "Y"), USUBJID, PARAMCD) |>
filter(n > 1)) == 0,
"ADLB: baseline post-dose" = all(out$ADY[!is.na(out$ABLFL)] <= 0),
"ADLB: PCHG infinite" = !any(is.infinite(out$PCHG)),
"ADLB: ANRIND set on missing" =
all(is.na(out$ANRIND[is.na(out$AVAL)]))
)
out
}
build_adae <- function(ae, adsl, window = 30) {
sev_map <- c(MILD = 1L, MODERATE = 2L, SEVERE = 3L)
out <- ae |>
left_join(select(adsl, USUBJID, TRT01A, TRT01AN, TRTSDT, TRTEDT, SAFFL),
by = "USUBJID", relationship = "many-to-one") |>
mutate(
.d = substr(AESTDTC, 1, 10),
ASTDTF = case_when(
grepl("^\\d{4}-\\d{2}-\\d{2}$", .d) ~ NA_character_,
grepl("^\\d{4}-\\d{2}$", .d) ~ "D",
grepl("^\\d{4}$", .d) ~ "M",
.default ~ "Y"),
.raw = case_when(
grepl("^\\d{4}-\\d{2}-\\d{2}$", .d) ~ as.Date(.d),
grepl("^\\d{4}-\\d{2}$", .d) ~ as.Date(paste0(.d, "-01")),
grepl("^\\d{4}$", .d) ~ as.Date(paste0(.d, "-01-01")),
.default ~ as.Date(NA)),
# Constrain imputed dates to first dose
ASTDT = if_else(!is.na(ASTDTF) & !is.na(.raw) & !is.na(TRTSDT),
pmax(.raw, TRTSDT), .raw),
ASTDY = compute_ady(ASTDT, TRTSDT),
AESEVN = unname(sev_map[AESEV]),
TRTEMFL = case_when(
is.na(TRTSDT) ~ "N",
is.na(ASTDT) ~ "Y",
ASTDT < TRTSDT ~ "N",
is.na(TRTEDT) ~ "Y",
ASTDT <= TRTEDT + window ~ "Y",
.default ~ "N")
) |>
select(-.d, -.raw) |>
arrange(USUBJID, ASTDT, AESEQ)
# Occurrence flags
add_flag <- function(d, keys, nm) {
d |> group_by(across(all_of(keys))) |>
mutate(.e = TRTEMFL == "Y", .n = row_number(),
.f = if (any(.e)) min(.n[.e]) else NA_integer_,
"{nm}" := if_else(!is.na(.f) & .n == .f, "Y", NA_character_)) |>
ungroup() |> select(-.e, -.n, -.f)
}
out <- out |>
add_flag("USUBJID", "AOCCFL") |>
add_flag(c("USUBJID", "AEBODSYS"), "AOCCSFL") |>
add_flag(c("USUBJID", "AEDECOD"), "AOCCPFL")
stopifnot(
"ADAE: row count changed" = nrow(out) == nrow(ae),
"ADAE: key not unique" = !anyDuplicated(out[c("USUBJID","AESEQ")]),
"ADAE: bad TRTEMFL" = all(out$TRTEMFL %in% c("Y","N")),
"ADAE: imputed with no flag" =
all(!is.na(out$ASTDTF) | out$ASTDT == as.Date(substr(out$AESTDTC,1,10)) |
is.na(out$ASTDT))
)
out
}
# ==============================================================
# 4. Summary tables
# ==============================================================
table_demographics <- function(adsl) {
pop <- adsl |> filter(SAFFL == "Y")
pop_tot <- bind_rows(pop, mutate(pop, TRT01A = "Total"))
big_n <- pop_tot |> count(TRT01A, name = "N")
age <- pop_tot |>
summarise(n = sum(!is.na(AGE)),
mean = mean(AGE, na.rm = TRUE),
sd = sd(AGE, na.rm = TRUE), .by = TRT01A) |>
mutate(stat = sprintf("%.1f (%.2f)", mean, sd)) |>
select(TRT01A, stat) |>
pivot_wider(names_from = TRT01A, values_from = stat) |>
mutate(Characteristic = "Age, mean (SD)", .before = 1)
sex <- pop_tot |>
mutate(SEX = factor(SEX, levels = c("F", "M"))) |>
count(TRT01A, SEX, .drop = FALSE) |>
left_join(big_n, by = "TRT01A") |>
mutate(stat = sprintf("%d (%.1f)", n, 100 * n / N)) |>
select(TRT01A, SEX, stat) |>
pivot_wider(names_from = TRT01A, values_from = stat, values_fill = "0") |>
rename(Characteristic = SEX) |>
mutate(Characteristic = paste0("Sex: ", Characteristic))
bind_rows(age, sex)
}
table_ae_incidence <- function(adae, adsl) {
denom <- adsl |> filter(SAFFL == "Y") |> count(TRT01A, name = "N")
adae |>
filter(SAFFL == "Y", TRTEMFL == "Y", AOCCPFL == "Y") |>
count(TRT01A, AEDECOD, name = "n") |>
left_join(denom, by = "TRT01A") |>
mutate(stat = sprintf("%d (%.1f)", n, 100 * n / N)) |>
select(TRT01A, AEDECOD, stat) |>
pivot_wider(names_from = TRT01A, values_from = stat, values_fill = "0") |>
arrange(AEDECOD)
}
# ==============================================================
# 5. Orchestration with a run log
# ==============================================================
run_pipeline <- function(dm, ex, ds, lb, ae) {
started <- Sys.time()
log <- list()
run_unit_tests()
param_lookup <- tibble(
LBTESTCD = c("ALT", "AST"),
PARAMCD = c("ALT", "AST"),
PARAM = c("Alanine Aminotransferase (U/L)",
"Aspartate Aminotransferase (U/L)"),
PARAMN = c(1L, 2L))
visit_lookup <- tibble(
VISIT = c("SCREENING", "BASELINE", "WEEK 4", "WEEK 12"),
AVISIT = c("Screening", "Baseline", "Week 4", "Week 12"),
AVISITN = c(-1L, 0L, 4L, 12L))
adsl <- build_adsl(dm, ex, lb)
log$ADSL <- list(rows = nrow(adsl), subjects = n_distinct(adsl$USUBJID))
adlb <- build_adlb(lb, adsl, param_lookup, visit_lookup)
log$ADLB <- list(rows = nrow(adlb), subjects = n_distinct(adlb$USUBJID),
params = n_distinct(adlb$PARAMCD))
adae <- build_adae(ae, adsl)
log$ADAE <- list(rows = nrow(adae), subjects = n_distinct(adae$USUBJID),
teae = sum(adae$TRTEMFL == "Y"))
tables <- list(
demographics = table_demographics(adsl),
ae_incidence = table_ae_incidence(adae, adsl)
)
message(sprintf("\nPipeline complete in %.1fs",
as.numeric(difftime(Sys.time(), started, units = "secs"))))
for (nm in names(log)) {
message(sprintf(" %-6s %s", nm,
paste(names(log[[nm]]), unlist(log[[nm]]),
sep = "=", collapse = " ")))
}
list(adsl = adsl, adlb = adlb, adae = adae, tables = tables, log = log)
}
result <- run_pipeline(dm, ex, ds, lb, ae)Unit tests: 11 passed
Pipeline complete in 0.3s
ADSL rows=6 subjects=6
ADLB rows=15 subjects=5 params=2
ADAE rows=7 subjects=5 teae=6result$tables$demographics
#> # A tibble: 3 × 4
#> Characteristic `Drug A` Placebo Total
#> <chr> <chr> <chr> <chr>
#> 1 Age, mean (SD) 54.0 (9.54) 75.5 (4.95) 62.6 (14.0)
#> 2 Sex: F 2 (66.7) 1 (50.0) 3 (60.0)
#> 3 Sex: M 1 (33.3) 1 (50.0) 2 (40.0)
result$tables$ae_incidence
#> # A tibble: 5 × 3
#> AEDECOD `Drug A` Placebo
#> <chr> <chr> <chr>
#> 1 DIZZINESS 1 (33.3) 0
#> 2 FATIGUE 0 1 (50.0)
#> 3 HEADACHE 0 1 (50.0)
#> 4 NAUSEA 1 (33.3) 0
#> 5 RASH 1 (33.3) 1 (50.0)What the structure buys you
Derivations as named functions. compute_ady() is tested once and used in both ADLB and ADAE. A bug fix propagates everywhere, and the test suite proves the fix.
Unit tests run before any data is touched. If compute_ady() is broken, the pipeline fails in 0.1 seconds with a clear message rather than producing plausible wrong numbers.
Postconditions on every builder. The stopifnot() blocks encode the invariants: row counts preserved, no day zero, one baseline per subject and parameter, no infinite PCHG, imputation always flagged. These are the checks a reviewer would ask about.
The run log. Record counts per dataset is the first thing anyone wants to see, and the first place a problem shows up.
What is still missing for production — the same list as the main chapter: SAP traceability for every rule, a documented validation plan, independent programming by a second person,xportr and conformant XPT output, a controlled specification and define.xml, and version control with review. See Validation and testing and xportr.
Key takeaways
Concepts
- SDTM tabulates, ADaM derives, TLFs summarise. Derivation in the wrong layer is the most common structural mistake.
- Analysis-ready and traceable are the two ADaM principles, and every convention follows from them.
- Three structures: ADSL (one row per subject), BDS (subject × parameter × timepoint), OCCDS (one row per occurrence).
Derivations that catch people out
- There is no day zero.
ADY = ADT - TRTSDT + 1when on or after first dose, otherwise the plain difference. TRTDURDis inclusive:TRTEDT - TRTSDT + 1.- Baseline is derived per parameter, restricting the choice to pre-dose records without dropping the post-dose ones.
BASEis broadcast,ABLFLflags one row. Different jobs.- Guard
PCHGagainst a zero baseline or the table fills withInf. right = FALSEincut()so a subject aged exactly 65 lands in65-79.CNSR = 0means the event occurred — inverted fromsurvival.- Every imputed value carries a flag, set from the source precision before any constraint is applied.
Programming practice
- Assert cardinality on every join.
relationship =in dplyr; a manualnrow()check with basemerge(). - Sort deterministically down to a unique key, or “first” and “last” become arbitrary.
factor(levels = )and.drop = FALSEso zero counts appear.- Denominators come from ADSL, never from the event dataset.
- Extract derivations into tested functions. It is the difference between a script and something you can defend.
- Distinguish CDISC requirements, conventions and SAP rules. Only the first is non-negotiable.
And the honest one
- This is teaching code. Use
admiralfor real work — but you now understand what it is doing and why, which is what makes the answers checkable.
Next: ADaM programming with admiral — the same derivations, using the pharmaverse package built for them.