Skip to content

BDS data sets: ADVS and ADLB

Most analysis data sets of a study have the basic data structure (BDS): one row per subject, parameter and analysis time point. Vital signs, laboratory values and questionnaires all use it. The same derivations come back in every BDS data set, so learn them once, here, on body weight.

Variable Meaning
PARAMCD, PARAM the parameter, what was measured: WEIGHT, “Weight (kg)”
AVAL, ADT, ADY the analysis value, its date and its study day
AVISIT, AVISITN the analysis visit, as text and as a number
ABLFL, BASE the baseline row, and its value on every row of the subject
CHG, PCHG change and percent change from baseline

The programs read data/adsl.rds from chapter 9, so run the programs of chapter 9 first.

programs/10_1_advs_weight.R
# Chapter 10: ADVS for body weight by hand, one derivation per step
library(dplyr)
adsl <- readRDS("data/adsl.rds") |>
select(USUBJID, TRTSDT)
# Step 010: one row per weight measurement, with the first dose from ADSL
advs_010 <- pharmaversesdtm::vs |>
filter(VSTESTCD == "WEIGHT") |>
select(STUDYID, USUBJID, VSSEQ, VISIT, VSDTC, VSSTRESN) |>
left_join(adsl, by = join_by(USUBJID), relationship = "many-to-one") |>
mutate(PARAMCD = "WEIGHT", PARAM = "Weight (kg)", AVAL = VSSTRESN)
# Step 020: the date and the study day. There is no day 0
advs_020 <- advs_010 |>
mutate(
ADT = lubridate::ymd(VSDTC),
ADY = as.numeric(ADT - TRTSDT) + if_else(ADT >= TRTSDT, 1, 0)
)
# Step 030: the analysis visit. Screening and unscheduled visits get none
advs_030 <- advs_020 |>
mutate(
AVISIT = case_when(
VISIT == "BASELINE" ~ "Baseline",
startsWith(VISIT, "WEEK") ~ stringr::str_to_title(VISIT)
),
AVISITN = case_when(
VISIT == "BASELINE" ~ 0,
startsWith(VISIT, "WEEK") ~
as.numeric(stringr::str_extract(VISIT, "[0-9]+"))
)
)
# Step 040: ABLFL, the last non-missing value on or before the first dose
advs_040 <- advs_030 |>
arrange(USUBJID, ADT, VSSEQ) |>
mutate(
can_be_base = !is.na(AVAL) & ADT <= TRTSDT,
ABLFL = if_else(row_number() %in% last(which(can_be_base)), "Y", NA),
.by = USUBJID
)
# Step 050: BASE, the baseline value on every row of the subject
advs_050 <- advs_040 |>
mutate(BASE = first(AVAL[ABLFL %in% "Y"]), .by = USUBJID)
# Step 060: change and percent change, for the visits after baseline
advs_weight <- advs_050 |>
mutate(
CHG = if_else(AVISITN > 0, AVAL - BASE, NA),
PCHG = if_else(AVISITN > 0, CHG / BASE * 100, NA)
) |>
select(
STUDYID, USUBJID, VSSEQ, PARAMCD, PARAM, VISIT, AVISIT, AVISITN, ADT, ADY,
TRTSDT, AVAL, ABLFL, BASE, CHG, PCHG
)
advs_weight |>
filter(USUBJID == "01-701-1015") |>
select(AVISIT, ADT, ADY, AVAL, ABLFL, BASE, CHG, PCHG)
#> # A tibble: 11 x 8
#> AVISIT ADT ADY AVAL ABLFL BASE CHG PCHG
#> <chr> <date> <dbl> <dbl> <chr> <dbl> <dbl> <dbl>
#> 1 <NA> 2013-12-26 -7 54.0 <NA> 54.4 NA NA
#> 2 Baseline 2014-01-02 1 54.4 Y 54.4 NA NA
#> 3 Week 2 2014-01-16 15 53.1 <NA> 54.4 -1.36 -2.50
#> 4 Week 4 2014-01-30 29 54.0 <NA> 54.4 -0.450 -0.827
#> 5 Week 6 2014-02-12 42 53.1 <NA> 54.4 -1.36 -2.50
#> 6 Week 8 2014-03-05 63 53.1 <NA> 54.4 -1.36 -2.50
#> 7 Week 12 2014-03-26 84 53.1 <NA> 54.4 -1.36 -2.50
#> 8 Week 16 2014-05-07 126 53.1 <NA> 54.4 -1.36 -2.50
#> 9 Week 20 2014-05-21 140 53.1 <NA> 54.4 -1.36 -2.50
#> 10 Week 24 2014-06-18 168 53.1 <NA> 54.4 -1.36 -2.50
#> 11 Week 26 2014-07-02 182 53.5 <NA> 54.4 -0.910 -1.67
# Baselines that do not come from the visit called BASELINE
advs_weight |>
filter(ABLFL == "Y", VISIT != "BASELINE") |>
select(USUBJID, VISIT, ADT, AVAL)
#> # A tibble: 1 x 4
#> USUBJID VISIT ADT AVAL
#> <chr> <chr> <date> <dbl>
#> 1 01-702-1082 SCREENING 1 2013-07-03 54.4
saveRDS(advs_weight, "data/advs_weight.rds")

Each step adds one derivation:

  • Step 010 keeps the weight rows of VS and joins TRTSDT from ADSL. relationship = "many-to-one" says: many VS rows, one ADSL row per subject. AVAL is the numeric result VSSTRESN.
  • Step 020 makes the date and the study day. ADT - TRTSDT is a number of days. The first dose is day 1, so the program adds 1 to every day on or after the first dose. The day before is -1, not 0. The screening weight of 01-701-1015 has ADY = -7, and Week 2 has ADY = 15.
  • Step 030 makes the analysis visit. BASELINE becomes Baseline with AVISITN = 0, and WEEK 2 becomes Week 2 with AVISITN = 2. str_extract(VISIT, "[0-9]+") takes the digits of the visit name. Screening and unscheduled visits get no analysis visit.
  • Step 040 flags the baseline row. Then step 050 puts its value, BASE, on every row, and step 060 computes CHG and PCHG.

The SAP of the study defines the baseline. Write its text next to the code:

Baseline is the last non-missing value on or before the day of the first dose.

Step 040 does exactly that, for each subject (.by = USUBJID):

  1. Sort the rows by date (arrange(USUBJID, ADT, VSSEQ)). VSSEQ breaks a tie, so the result is the same in every run.
  2. can_be_base is TRUE for a row with a value, on or before the first dose.
  3. which(can_be_base) gives the row numbers where it is TRUE, and last() the last of them. That row gets ABLFL = "Y".

Step 050 then takes AVAL[ABLFL %in% "Y"]: the value of the baseline row. first() makes it one value, or NA when a subject has no baseline row.

Look at the second result. Subject 01-702-1082 has no weight at the visit called BASELINE. The SAP rule takes the screening weight instead. A rule “baseline is the visit called BASELINE” would give this subject no baseline and no change.

CHG and PCHG are computed only for the analysis visits after baseline: AVISITN > 0. On the baseline row and before it they stay NA. That is a choice of the specification. Other studies put CHG = 0 on the baseline row; exercise 10-2 shows it. For 01-701-1015, BASE is 54.4 and Week 2 has CHG = -1.36.

10.2 Blood pressure: the same steps, so write functions

Section titled “10.2 Blood pressure: the same steps, so write functions”

Blood pressure and pulse need the same derivations as weight, with one difference. The pilot measures them in three positions: ATPTN 815 (lying 5 minutes), 816 (standing 1 minute) and 817 (standing 3 minutes). Each position has its own baseline. So the group is not the subject, but the subject, the parameter and the position.

Steps 010 to 060 would come back for each test. Write them once, as two functions:

programs/10_2_advs_function.R
# Chapter 10: blood pressure needs the same steps, per position. Write them once
library(dplyr)
adsl <- readRDS("data/adsl.rds") |>
select(USUBJID, TRTSDT)
params <- tibble(
VSTESTCD = c("SYSBP", "DIABP", "PULSE", "WEIGHT"),
PARAMCD = c("SYSBP", "DIABP", "PULSE", "WEIGHT"),
PARAM = c(
"Systolic Blood Pressure (mmHg)", "Diastolic Blood Pressure (mmHg)",
"Pulse Rate (beats/min)", "Weight (kg)"
)
)
# Steps 010 to 030: the BDS rows of some VS tests, with dates and visits
vs_to_bds <- function(vs, adsl, params) {
vs |>
inner_join(params, by = join_by(VSTESTCD), relationship = "many-to-one") |>
left_join(adsl, by = join_by(USUBJID), relationship = "many-to-one") |>
mutate(
AVAL = VSSTRESN, ATPTN = VSTPTNUM, ATPT = VSTPT,
ADT = lubridate::ymd(VSDTC),
ADY = as.numeric(ADT - TRTSDT) + if_else(ADT >= TRTSDT, 1, 0),
AVISIT = case_when(
VISIT == "BASELINE" ~ "Baseline",
startsWith(VISIT, "WEEK") ~ stringr::str_to_title(VISIT)
),
AVISITN = case_when(
VISIT == "BASELINE" ~ 0,
startsWith(VISIT, "WEEK") ~
as.numeric(stringr::str_extract(VISIT, "[0-9]+"))
)
)
}
# Steps 040 to 060: ABLFL, BASE, CHG and PCHG in each group of `by`.
# `order` says which row is the last one
derive_baseline <- function(data, by, order) {
data |>
arrange(pick(all_of(order))) |>
mutate(
can_be_base = !is.na(AVAL) & ADT <= TRTSDT,
ABLFL = if_else(row_number() %in% last(which(can_be_base)), "Y", NA),
BASE = first(AVAL[ABLFL %in% "Y"]),
.by = all_of(by)
) |>
mutate(
CHG = if_else(AVISITN > 0, AVAL - BASE, NA),
PCHG = if_else(AVISITN > 0, CHG / BASE * 100, NA)
) |>
select(-can_be_base)
}
advs_hand <- pharmaversesdtm::vs |>
vs_to_bds(adsl, params) |>
derive_baseline(
by = c("USUBJID", "PARAMCD", "ATPTN"), order = c("ADT", "VSSEQ")
) |>
select(
STUDYID, USUBJID, VSSEQ, PARAMCD, PARAM, ATPTN, ATPT, VISIT, AVISIT,
AVISITN, ADT, ADY, TRTSDT, AVAL, ABLFL, BASE, CHG, PCHG
) |>
arrange(USUBJID, PARAMCD, ADT, ATPTN)
advs_hand |>
count(PARAMCD, ABLFL)
#> # A tibble: 8 x 3
#> PARAMCD ABLFL n
#> <chr> <chr> <int>
#> 1 DIABP Y 762
#> 2 DIABP <NA> 7445
#> 3 PULSE Y 762
#> 4 PULSE <NA> 7442
#> 5 SYSBP Y 762
#> 6 SYSBP <NA> 7446
#> 7 WEIGHT Y 254
#> 8 WEIGHT <NA> 1796
# One baseline per position: systolic blood pressure of 01-701-1015
advs_hand |>
filter(USUBJID == "01-701-1015", PARAMCD == "SYSBP", ABLFL == "Y") |>
select(PARAMCD, ATPTN, ATPT, AVAL, BASE)
#> # A tibble: 3 x 5
#> PARAMCD ATPTN ATPT AVAL BASE
#> <chr> <dbl> <chr> <dbl> <dbl>
#> 1 SYSBP 815 AFTER LYING DOWN FOR 5 MINUTES 130 130
#> 2 SYSBP 816 AFTER STANDING FOR 1 MINUTE 121 121
#> 3 SYSBP 817 AFTER STANDING FOR 3 MINUTES 131 131
# Baselines that do not come from the visit called BASELINE
advs_hand |>
filter(ABLFL == "Y", VISIT != "BASELINE") |>
count(USUBJID, VISIT)
#> # A tibble: 2 x 3
#> USUBJID VISIT n
#> <chr> <chr> <int>
#> 1 01-702-1082 SCREENING 1 1
#> 2 01-718-1150 SCREENING 1 9
# The test: the function gives the same weight rows as program 10_1
weight_10_1 <- readRDS("data/advs_weight.rds")
weight_10_2 <- advs_hand |>
filter(PARAMCD == "WEIGHT") |>
select(-ATPTN, -ATPT)
diffdf::diffdf(weight_10_1, weight_10_2, keys = c("USUBJID", "VSSEQ"))
#> No issues were found!
saveRDS(advs_hand, "data/advs_hand.rds")
  • vs_to_bds() does steps 010 to 030 for the VS tests in params. inner_join() with params keeps only those tests and adds PARAMCD and PARAM.
  • derive_baseline() does steps 040 to 060 in each group of by. order says which row is “last”. Inside a function, the column names come as text, so the function uses all_of(by) and pick(all_of(order)). Chapter 8 explains these helpers.
  • One call does all four parameters. Weight has no position (ATPTN is NA), and NA is one group of its own. So the same by works for weight and for blood pressure.

The count shows 762 baseline rows for each blood pressure parameter: 254 subjects times three positions. Weight has 254. Subject 01-701-1015 has three systolic baselines, one per position: 130, 121 and 131.

The last lines are a test: the function must give the same weight rows as program 10_1. diffdf() finds no issues. Write such a test each time you replace code with a function.

The second result lists the baselines that do not come from the visit called BASELINE. Subject 01-718-1150 has no BASELINE visit for blood pressure. The SAP rule takes the last value before the first dose, from SCREENING 1, for all 9 pairs of parameter and position.

programs/10_3_advs_admiral.R
# Chapter 10: the same ADVS with admiral
library(dplyr)
library(admiral)
adsl <- readRDS("data/adsl.rds")
params <- tibble(
VSTESTCD = c("SYSBP", "DIABP", "PULSE", "WEIGHT"),
PARAMCD = c("SYSBP", "DIABP", "PULSE", "WEIGHT"),
PARAM = c(
"Systolic Blood Pressure (mmHg)", "Diastolic Blood Pressure (mmHg)",
"Pulse Rate (beats/min)", "Weight (kg)"
)
)
advs <- pharmaversesdtm::vs |>
filter(VSTESTCD %in% params$VSTESTCD) |>
derive_vars_merged(
dataset_add = adsl, by_vars = exprs(USUBJID), new_vars = exprs(TRTSDT)
) |>
derive_vars_merged_lookup(
dataset_add = params, by_vars = exprs(VSTESTCD),
new_vars = exprs(PARAMCD, PARAM)
) |>
derive_vars_dt(dtc = VSDTC, new_vars_prefix = "A") |>
derive_vars_dy(reference_date = TRTSDT, source_vars = exprs(ADT)) |>
mutate(
AVAL = VSSTRESN, ATPTN = VSTPTNUM, ATPT = VSTPT,
AVISIT = case_when(
VISIT == "BASELINE" ~ "Baseline",
startsWith(VISIT, "WEEK") ~ stringr::str_to_title(VISIT)
),
AVISITN = case_when(
VISIT == "BASELINE" ~ 0,
startsWith(VISIT, "WEEK") ~
as.numeric(stringr::str_extract(VISIT, "[0-9]+"))
)
) |>
# ABLFL: the last row of each group among the rows that can be baseline
restrict_derivation(
derivation = derive_var_extreme_flag,
args = params(
by_vars = exprs(USUBJID, PARAMCD, ATPTN), order = exprs(ADT, VSSEQ),
new_var = ABLFL, mode = "last"
),
filter = !is.na(AVAL) & ADT <= TRTSDT
) |>
derive_var_base(
by_vars = exprs(USUBJID, PARAMCD, ATPTN), source_var = AVAL, new_var = BASE
) |>
restrict_derivation(derivation = derive_var_chg, filter = AVISITN > 0) |>
restrict_derivation(derivation = derive_var_pchg, filter = AVISITN > 0) |>
select(
STUDYID, USUBJID, VSSEQ, PARAMCD, PARAM, ATPTN, ATPT, VISIT, AVISIT,
AVISITN, ADT, ADY, TRTSDT, AVAL, ABLFL, BASE, CHG, PCHG
) |>
arrange(USUBJID, PARAMCD, ADT, ATPTN)
#> All `VSTESTCD` are mapped.
dim(advs)
#> [1] 26669 18
saveRDS(advs, "data/advs.rds")
Step By hand admiral
010 left_join() with ADSL, inner_join() with params derive_vars_merged(), derive_vars_merged_lookup()
020 ymd(), ADT - TRTSDT, plus 1 derive_vars_dt(), derive_vars_dy()
040 last(which(can_be_base)) restrict_derivation(derive_var_extreme_flag, ..., filter = ...)
050 first(AVAL[ABLFL %in% "Y"]) derive_var_base()
060 if_else(AVISITN > 0, ...) restrict_derivation(derive_var_chg, filter = AVISITN > 0)

Two admiral ideas are new:

  • derive_var_extreme_flag() flags the first or the last row of each group (by_vars), in the order of order. mode = "last" gives the last row.
  • restrict_derivation() runs a derivation on some rows only. The filter says which rows: here the rows that can be baseline, !is.na(AVAL) & ADT <= TRTSDT. The other rows stay as they are. Exercise 10-4 shows what goes wrong without the filter.

derive_vars_merged_lookup() writes “All VSTESTCD are mapped.”: every test found its row in params. The result has 26669 rows, one per VS row of the four tests.

programs/10_4_advs_compare.R
# Chapter 10: ADVS by hand against admiral, then against the reference ADVS
library(dplyr)
library(diffdf)
# admiral drops the labels that the SDTM columns bring: compare the values only
advs_hand <- readRDS("data/advs_hand.rds") |>
haven::zap_label()
advs <- readRDS("data/advs.rds") |>
haven::zap_label()
diffdf(advs_hand, advs, keys = c("USUBJID", "VSSEQ"))
#> No issues were found!
# The reference has derived rows too (DTYPE is not missing)
pharmaverseadam::advs |>
filter(PARAMCD %in% advs$PARAMCD) |>
count(DTYPE)
#> # A tibble: 3 x 2
#> DTYPE n
#> <chr> <int>
#> 1 AVERAGE 10254
#> 2 LOV 1890
#> 3 <NA> 26669
# Compare the rows that come from VS
vars <- c(
"USUBJID", "VSSEQ", "AVISIT", "AVISITN", "ADT", "ADY", "AVAL", "ABLFL",
"BASE", "CHG", "PCHG"
)
ref <- pharmaverseadam::advs |>
filter(PARAMCD %in% advs$PARAMCD, is.na(DTYPE)) |>
select(all_of(vars)) |>
haven::zap_label()
ours <- advs |>
select(all_of(vars))
diffdf(ours, ref, keys = c("USUBJID", "VSSEQ"))
#> No issues were found!

The first comparison: the ADVS by hand and the ADVS of admiral agree. Both data frames first go through haven::zap_label(). Your ADVS by hand keeps the labels of the SDTM columns, and admiral drops some of them. Labels are attributes, not values. The specification sets the final labels (chapter 15).

The reference ADVS has more rows than yours. Its column DTYPE marks a derived row: a row that the ADaM programmer adds, without an SDTM row of its own. The reference has 10254 AVERAGE rows (the mean of the values of one visit) and 1890 LOV rows (the last value on treatment, as an “End of Treatment” visit). Add such rows only when the SAP asks for them. On the 26669 rows that come from VS, your ADVS and the reference agree: the same visits, baselines and changes.

ADLB has the same structure: each lab test is a parameter, and the same baseline rule applies. Two things are new. A lab value has a normal range: ANRLO and ANRHI are its lower and upper limits. ANRIND says where the value is: LOW, NORMAL or HIGH. BNRIND is ANRIND of the baseline row, on every row.

programs/10_5_adlb_hand.R
# Chapter 10: ADLB chemistry by hand: the function of 10_2 again, and normal ranges
library(dplyr)
adsl <- readRDS("data/adsl.rds") |>
select(USUBJID, TRTSDT)
# derive_baseline() of program 10_2, not changed
derive_baseline <- function(data, by, order) {
data |>
arrange(pick(all_of(order))) |>
mutate(
can_be_base = !is.na(AVAL) & ADT <= TRTSDT,
ABLFL = if_else(row_number() %in% last(which(can_be_base)), "Y", NA),
BASE = first(AVAL[ABLFL %in% "Y"]),
.by = all_of(by)
) |>
mutate(
CHG = if_else(AVISITN > 0, AVAL - BASE, NA),
PCHG = if_else(AVISITN > 0, CHG / BASE * 100, NA)
) |>
select(-can_be_base)
}
adlb_hand <- pharmaversesdtm::lb |>
filter(LBCAT == "CHEMISTRY") |>
left_join(adsl, by = join_by(USUBJID), relationship = "many-to-one") |>
mutate(
PARAMCD = LBTESTCD, PARAM = LBTEST, AVAL = LBSTRESN,
ADT = lubridate::ymd(substr(LBDTC, 1, 10)),
ADY = as.numeric(ADT - TRTSDT) + if_else(ADT >= TRTSDT, 1, 0),
week = startsWith(VISIT, "WEEK"),
AVISIT = if_else(week, stringr::str_to_title(VISIT), NA),
AVISITN = if_else(
week, as.numeric(stringr::str_extract(VISIT, "[0-9]+")), NA
),
# the normal range of the lab, and where the value is in it
ANRLO = LBSTNRLO, ANRHI = LBSTNRHI,
ANRIND = case_when(
AVAL < ANRLO ~ "LOW",
AVAL > ANRHI ~ "HIGH",
!is.na(AVAL) ~ "NORMAL"
)
) |>
derive_baseline(by = c("USUBJID", "PARAMCD"), order = c("ADT", "LBSEQ")) |>
# BNRIND: ANRIND of the baseline row, on every row
mutate(BNRIND = first(ANRIND[ABLFL %in% "Y"]), .by = c(USUBJID, PARAMCD)) |>
select(
STUDYID, USUBJID, LBSEQ, PARAMCD, PARAM, VISIT, AVISIT, AVISITN, ADT, ADY,
TRTSDT, AVAL, ANRLO, ANRHI, ANRIND, ABLFL, BASE, BNRIND, CHG, PCHG
) |>
arrange(USUBJID, PARAMCD, ADT, LBSEQ)
adlb_hand |>
filter(USUBJID == "01-701-1015", PARAMCD == "ALT") |>
select(VISIT, ADY, AVAL, ANRHI, ANRIND, ABLFL, BASE, BNRIND, CHG)
#> # A tibble: 10 x 9
#> VISIT ADY AVAL ANRHI ANRIND ABLFL BASE BNRIND CHG
#> <chr> <dbl> <dbl> <dbl> <chr> <chr> <dbl> <chr> <dbl>
#> 1 SCREENING 1 -7 27 34 NORMAL Y 27 NORMAL NA
#> 2 WEEK 2 15 41 34 HIGH <NA> 27 NORMAL 14
#> 3 WEEK 4 29 18 34 NORMAL <NA> 27 NORMAL -9
#> 4 WEEK 6 42 26 34 NORMAL <NA> 27 NORMAL -1
#> 5 WEEK 8 63 22 34 NORMAL <NA> 27 NORMAL -5
#> 6 WEEK 12 84 27 34 NORMAL <NA> 27 NORMAL 0
#> 7 WEEK 16 126 17 34 NORMAL <NA> 27 NORMAL -10
#> 8 WEEK 20 140 21 34 NORMAL <NA> 27 NORMAL -6
#> 9 WEEK 24 168 23 34 NORMAL <NA> 27 NORMAL -4
#> 10 WEEK 26 182 23 34 NORMAL <NA> 27 NORMAL -4
# The chemistry labs have no BASELINE visit. Where does the baseline of ALT
# come from?
adlb_hand |>
filter(PARAMCD == "ALT", ABLFL == "Y") |>
count(VISIT)
#> # A tibble: 4 x 2
#> VISIT n
#> <chr> <int>
#> 1 SCREENING 1 230
#> 2 UNSCHEDULED 1.1 18
#> 3 UNSCHEDULED 1.2 4
#> 4 UNSCHEDULED 1.3 2
saveRDS(adlb_hand, "data/adlb_hand.rds")
  • The program copies derive_baseline() of program 10_2 without a change. A function that works on two domains is a function you can trust. Chapter 19 shows how to keep such functions in one place, so that you do not copy them.
  • LBDTC has a time part after the date. substr(LBDTC, 1, 10) keeps the first ten characters: the date.
  • ANRIND uses case_when(): below the lower limit is LOW, above the upper limit is HIGH, every other value is NORMAL.
  • BNRIND uses the same idea as BASE: the value of the baseline row, on every row of the group.

The chemistry labs have no BASELINE visit in the pilot. For ALT, 230 baselines come from SCREENING 1. Some subjects had the test again after screening and before the first dose, at an unscheduled visit. The SAP rule takes that later value: 18 baselines come from UNSCHEDULED 1.1, 4 from 1.2 and 2 from 1.3.

Subject 01-701-1015 had a normal ALT at baseline (27, upper limit 34). At Week 2 the value was 41: ANRIND = "HIGH", CHG = 14.

10.6 ADLB with admiral, and the comparisons

Section titled “10.6 ADLB with admiral, and the comparisons”
programs/10_6_adlb_admiral.R
# Chapter 10: the same ADLB with admiral
library(dplyr)
library(admiral)
adsl <- readRDS("data/adsl.rds")
adlb <- pharmaversesdtm::lb |>
filter(LBCAT == "CHEMISTRY") |>
derive_vars_merged(
dataset_add = adsl, by_vars = exprs(USUBJID), new_vars = exprs(TRTSDT)
) |>
derive_vars_dt(dtc = LBDTC, new_vars_prefix = "A") |>
derive_vars_dy(reference_date = TRTSDT, source_vars = exprs(ADT)) |>
mutate(
PARAMCD = LBTESTCD, PARAM = LBTEST, AVAL = LBSTRESN,
week = startsWith(VISIT, "WEEK"),
AVISIT = if_else(week, stringr::str_to_title(VISIT), NA),
AVISITN = if_else(
week, as.numeric(stringr::str_extract(VISIT, "[0-9]+")), NA
),
ANRLO = LBSTNRLO, ANRHI = LBSTNRHI
) |>
derive_var_anrind() |>
restrict_derivation(
derivation = derive_var_extreme_flag,
args = params(
by_vars = exprs(USUBJID, PARAMCD), order = exprs(ADT, LBSEQ),
new_var = ABLFL, mode = "last"
),
filter = !is.na(AVAL) & ADT <= TRTSDT
) |>
derive_var_base(
by_vars = exprs(USUBJID, PARAMCD), source_var = AVAL, new_var = BASE
) |>
derive_var_base(
by_vars = exprs(USUBJID, PARAMCD), source_var = ANRIND, new_var = BNRIND
) |>
restrict_derivation(derivation = derive_var_chg, filter = AVISITN > 0) |>
restrict_derivation(derivation = derive_var_pchg, filter = AVISITN > 0) |>
select(
STUDYID, USUBJID, LBSEQ, PARAMCD, PARAM, VISIT, AVISIT, AVISITN, ADT, ADY,
TRTSDT, AVAL, ANRLO, ANRHI, ANRIND, ABLFL, BASE, BNRIND, CHG, PCHG
) |>
arrange(USUBJID, PARAMCD, ADT, LBSEQ)
dim(adlb)
#> [1] 32740 20
saveRDS(adlb, "data/adlb.rds")

derive_var_anrind() makes ANRIND from AVAL, ANRLO and ANRHI. derive_var_base() copies the baseline value of any column: once for AVAL into BASE, once for ANRIND into BNRIND. The result has 32740 rows, the chemistry rows of LB.

programs/10_7_adlb_compare.R
# Chapter 10: ADLB by hand against admiral, then against the reference ADLB
library(dplyr)
library(diffdf)
adlb_hand <- readRDS("data/adlb_hand.rds") |>
haven::zap_label()
adlb <- readRDS("data/adlb.rds") |>
haven::zap_label()
diffdf(adlb_hand, adlb, keys = c("USUBJID", "LBSEQ"))
#> No issues were found!
# The reference: the rows of LB chemistry, by the same key
vars <- c(
"USUBJID", "LBSEQ", "ADT", "ADY", "AVAL", "ANRLO", "ANRHI", "ANRIND",
"ABLFL", "BASE", "BNRIND"
)
ref <- pharmaverseadam::adlb |>
filter(LBCAT == "CHEMISTRY", is.na(DTYPE)) |>
select(all_of(vars)) |>
haven::zap_label()
ours <- adlb |>
select(all_of(vars))
diffdf(ours, ref, keys = c("USUBJID", "LBSEQ"))
#> No issues were found!
# CHG: the reference gives every visit an AVISITN (from VISITNUM), so it
# computes CHG at unscheduled visits too. Count the rows with a CHG in one
# data set only
chg <- inner_join(
adlb |> select(USUBJID, LBSEQ, VISIT, CHG),
pharmaverseadam::adlb |> select(USUBJID, LBSEQ, CHG_REF = CHG),
by = join_by(USUBJID, LBSEQ)
)
chg |>
filter(is.na(CHG) != is.na(CHG_REF)) |>
count(
visit = stringr::word(VISIT, 1),
ours = !is.na(CHG),
reference = !is.na(CHG_REF)
)
#> # A tibble: 3 x 4
#> visit ours reference n
#> <chr> <lgl> <lgl> <int>
#> 1 AMBUL FALSE TRUE 18
#> 2 RETRIEVAL FALSE TRUE 18
#> 3 UNSCHEDULED FALSE TRUE 819

The ADLB by hand and the ADLB of admiral agree. The reference agrees on the dates, values, normal ranges, baselines and BNRIND.

CHG is different, and the last result shows where. The reference gives every visit an AVISITN, from VISITNUM: also the unscheduled visits, the retrieval visit and the ECG visits. So it computes CHG there too: on 819 unscheduled rows, 18 retrieval rows and 18 ECG rows. Some of these unscheduled rows are before the first dose, and some are baseline rows (section 10.5). Your ADLB computes CHG only at the scheduled weeks. The SAP decides which rule is right. Write the rule down, and the next programmer knows why the numbers differ.

A shift table counts subjects by their range at baseline and their range after baseline. It shows, for example, how many subjects with a normal ALT at baseline had a high ALT later.

programs/10_8_shift.R
# Chapter 10: a shift table for ALT, from the baseline range to the worst range
library(dplyr)
adlb <- readRDS("data/adlb.rds")
# the worst ANRIND of each subject after baseline: HIGH before LOW before NORMAL
worst <- adlb |>
filter(PARAMCD == "ALT", AVISITN > 0, !is.na(ANRIND)) |>
summarise(
BNRIND = first(BNRIND),
WORST = case_when(
any(ANRIND == "HIGH") ~ "HIGH",
any(ANRIND == "LOW") ~ "LOW",
.default = "NORMAL"
),
.by = USUBJID
)
worst |>
filter(USUBJID == "01-701-1015")
#> # A tibble: 1 x 3
#> USUBJID BNRIND WORST
#> <chr> <chr> <chr>
#> 1 01-701-1015 NORMAL HIGH
worst |>
count(BNRIND, WORST) |>
tidyr::pivot_wider(names_from = WORST, values_from = n, values_fill = 0) |>
select(BNRIND, LOW, NORMAL, HIGH)
#> # A tibble: 3 x 4
#> BNRIND LOW NORMAL HIGH
#> <chr> <int> <int> <int>
#> 1 HIGH 0 1 10
#> 2 LOW 0 1 0
#> 3 NORMAL 6 207 21
  • The program keeps the ALT rows after baseline (AVISITN > 0) with a value.
  • summarise(.by = USUBJID) makes one row per subject. WORST is HIGH when any row is high, else LOW when any row is low, else NORMAL.
  • count() and pivot_wider() make the table: one row per BNRIND, one column per WORST.

207 subjects stayed normal. 21 subjects went from normal to high, and 01-701-1015 is one of them. Chapter 13 turns counts like these into a table for the report.

  • BDS has one row per subject, parameter and time point. The same derivations come back in every BDS data set.
  • The study day has no day 0: add 1 on and after the first dose.
  • The baseline follows the SAP: the last non-missing value on or before the first dose, in each group. Do not take it from the visit name.
  • mutate() with .by puts the baseline value on every row of the group.
  • When the same steps come back with another key, write a function, and test it against the hand-written version.
  • admiral does the same with derive_var_extreme_flag() inside restrict_derivation(), derive_var_base(), derive_var_chg() and derive_var_pchg().
  • When your data set and the reference differ, find the rule: here derived rows (DTYPE) and CHG at unscheduled visits.

Exercise 10-1. Add ANL01FL to ADVS: "Y" on the last row of each subject, parameter, position and analysis visit. How many groups have more than one row? How many rows of each parameter get ANL01FL = "Y"?

Exercise 10-2. Your specification wants CHG on every row with AVAL and BASE, also at baseline and before it. How many weight rows have a CHG now, and how many with the rule of this chapter? What is CHG on the baseline row?

Exercise 10-3. Write the shift table of program 10_8 as a function shift_table(adlb, paramcd). Call it for ALT and for AST. How many subjects with a normal AST at baseline had a high AST later?

Exercise 10-4. In program 10_3, call derive_var_extreme_flag() for weight directly, without restrict_derivation() and its filter. From which visits do the “baselines” come now?

The answers are in Answers, Part II.

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

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

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

© 2026 DataStep Labs. All rights reserved.