Skip to content

Reshaping and lookups

SDTM is long: one row per test. Many derivations need wide data: one row per subject. Many derivations apply the same rule to many columns. And many need a value from another table. This chapter gives you the R tools for these jobs: across(), case_when(), the pivot functions, and four ways to look up a value.

SUPPDM holds the population flags as "Y" or nothing (chapter 5). A table program wants numbers: 1 or 0. That is one rule for four columns.

programs/07_1_across.R
# Chapter 7: across() applies one rule to many columns
library(dplyr)
flags <- pharmaversesdtm::suppdm |>
tidyr::pivot_wider(id_cols = USUBJID, names_from = QNAM, values_from = QVAL) |>
select(USUBJID, SAFETY, ITT, EFFICACY, COMPLT24)
# one rule, four columns: "Y" becomes 1, anything else 0
num <- flags |>
mutate(across(
c(SAFETY, ITT, EFFICACY, COMPLT24),
~ if_else(.x == "Y", 1, 0, missing = 0),
.names = "{.col}N"
))
num |> filter(USUBJID == "01-701-1015")
#> # A tibble: 1 x 9
#> USUBJID SAFETY ITT EFFICACY COMPLT24 SAFETYN ITTN EFFICACYN COMPLT24N
#> <chr> <chr> <chr> <chr> <chr> <dbl> <dbl> <dbl> <dbl>
#> 1 01-701-1015 Y Y Y Y 1 1 1 1
# one rule in summarise(): the sum of every new column
num |> summarise(across(ends_with("N"), sum))
#> # A tibble: 1 x 4
#> SAFETYN ITTN EFFICACYN COMPLT24N
#> <dbl> <dbl> <dbl> <dbl>
#> 1 254 254 234 118

across() goes inside mutate() or summarise(). It takes three arguments:

  1. The columns: c(SAFETY, ITT, EFFICACY, COMPLT24). You can also use a helper such as ends_with("N") or starts_with("COMPLT").
  2. The rule: ~ if_else(.x == "Y", 1, 0, missing = 0). The ~ starts a short function, and .x is the column that the rule works on now. missing = 0 turns an NA into 0.
  3. The names of the new columns: "{.col}N" is the old name with N after it. Without .names, across() replaces the old columns.

In summarise(), the same idea sums all four new columns in one line: 254 subjects in the safety and intent-to-treat populations, 234 in the efficacy population, 118 week-24 completers.

if_else() chooses between two values. case_when() chooses between many.

programs/07_2_case_when.R
# Chapter 7: case_when() chooses a value; the first true condition wins
library(dplyr)
adsl <- pharmaverseadam::adsl |>
filter(SAFFL == "Y") |>
select(USUBJID, AGE)
groups <- adsl |>
mutate(
AGEGR = case_when(
is.na(AGE) ~ "Missing",
AGE < 65 ~ "<65",
AGE <= 80 ~ "65-80",
.default = ">80"
),
WRONG = case_when(
AGE <= 80 ~ "65-80",
AGE < 65 ~ "<65",
.default = ">80"
)
)
groups |> count(AGEGR)
#> # A tibble: 3 x 2
#> AGEGR n
#> <chr> <int>
#> 1 65-80 144
#> 2 <65 33
#> 3 >80 77
groups |> count(WRONG)
#> # A tibble: 2 x 2
#> WRONG n
#> <chr> <int>
#> 1 65-80 177
#> 2 >80 77
groups |> filter(USUBJID == "01-701-1015")
#> # A tibble: 1 x 4
#> USUBJID AGE AGEGR WRONG
#> <chr> <dbl> <chr> <chr>
#> 1 01-701-1015 63 <65 65-80

Each line is condition ~ value. case_when() tests the conditions from top to bottom, and the first true condition wins. .default gives the value when no condition is true.

  • AGEGR tests the missing value first, then < 65, then <= 80. The groups are right: 33, 144 and 77.
  • WRONG tests <= 80 first. That is true for every subject under 65 too, so the line AGE < 65 is never reached. The group <65 is empty, and 65-80 has 177 subjects. Subject 01-701-1015, aged 63, is in the wrong group. R gives no warning: the program is valid, only the order is wrong.

Put the narrow conditions first and the missing value first of all. A missing age makes every comparison NA, and an NA condition is not true. Without is.na(AGE) at the top, a missing age falls through to .default (Exercise 7-2).

A loop repeats a block of code, once for each value in a list. A vectorised function does the same work on a whole column in one call. Most R work is vectorised. Here are both, on the three exposure records of 01-701-1015.

programs/07_3_loop.R
# Chapter 7: a for loop and a vectorised call give the same days
library(dplyr)
ex <- pharmaversesdtm::ex |>
filter(USUBJID == "01-701-1015") |>
mutate(EXSTDT = as.Date(EXSTDTC), EXENDT = as.Date(EXENDTC)) |>
select(USUBJID, EXDOSE, EXSTDT, EXENDT)
# a loop: one row at a time
days <- numeric(nrow(ex))
for (i in seq_len(nrow(ex))) {
days[i] <- as.numeric(ex$EXENDT[i] - ex$EXSTDT[i]) + 1
}
days
#> [1] 15 153 14
# vectorised: the whole column at once
ex <- ex |> mutate(DAYS = as.numeric(EXENDT - EXSTDT) + 1)
ex
#> # A tibble: 3 x 5
#> USUBJID EXDOSE EXSTDT EXENDT DAYS
#> <chr> <dbl> <date> <date> <dbl>
#> 1 01-701-1015 0 2014-01-02 2014-01-16 15
#> 2 01-701-1015 0 2014-01-17 2014-06-18 153
#> 3 01-701-1015 0 2014-06-19 2014-07-02 14
# one row per day of each record: reframe() can return many rows per row
daily <- ex |>
rowwise() |>
reframe(USUBJID, EXDOSE, ADT = seq(EXSTDT, EXENDT, by = "day"))
nrow(daily)
#> [1] 182
  • for (i in seq_len(nrow(ex))) runs the block for i = 1, 2, 3. seq_len(3) makes the numbers 1, 2, 3 (and nothing when there are no rows). Each pass computes the days of one record.
  • mutate(DAYS = ...) computes the same 15, 153 and 14 days on the whole column at once. It is shorter, it is faster, and it cannot forget a row.
  • The last step makes one row per day: 182 rows for the three records. rowwise() makes each row a group, and reframe() can return several rows for each group. seq(EXSTDT, EXENDT, by = "day") is the list of days from the start to the end.

Use a vectorised call when it exists. Use a loop when each pass does something that is not a column: writes a file, prints a table, or runs a model. Chapter 8 shows map() and walk() for these jobs.

Long data has one row per measurement: subject, test, value. Wide data has one row per subject and one column per test. pivot_wider() from the tidyr package turns long into wide.

programs/07_4_wider.R
# Chapter 7: pivot_wider() makes one row per subject from one row per test
library(dplyr)
vs_bl <- pharmaversesdtm::vs |>
filter(
VSBLFL == "Y",
VSTESTCD %in% c("DIABP", "PULSE", "SYSBP"),
VSTPT == "AFTER LYING DOWN FOR 5 MINUTES"
) |>
select(USUBJID, VSTESTCD, VSSTRESN)
vs_bl |> filter(USUBJID == "01-701-1015")
#> # A tibble: 3 x 3
#> USUBJID VSTESTCD VSSTRESN
#> <chr> <chr> <dbl>
#> 1 01-701-1015 DIABP 56
#> 2 01-701-1015 PULSE 56
#> 3 01-701-1015 SYSBP 130
wide <- vs_bl |>
tidyr::pivot_wider(
id_cols = USUBJID,
names_from = VSTESTCD,
values_from = VSSTRESN,
names_prefix = "BL_"
)
wide |> print(n = 3)
#> # A tibble: 253 x 4
#> USUBJID BL_DIABP BL_PULSE BL_SYSBP
#> <chr> <dbl> <dbl> <dbl>
#> 1 01-701-1015 56 56 130
#> 2 01-701-1023 84 68 130
#> 3 01-701-1028 85 62 138
#> # i 250 more rows
c(long = nrow(vs_bl), wide = nrow(wide))
#> long wide
#> 759 253
  • id_cols = USUBJID: one output row per subject.
  • names_from = VSTESTCD: the values of this column become the new column names.
  • values_from = VSSTRESN: the values that fill the new columns.
  • names_prefix = "BL_": text in front of each new name. BL_DIABP is the baseline diastolic blood pressure.

Subject 01-701-1015 had three rows: diastolic 56, pulse 56, systolic 130. Now they are one row. 759 long rows become 253 wide rows, three tests per subject.

The pilot study measures blood pressure and pulse in three positions. Program 07_4 kept one of them with VSTPT. Here is what happens without that filter.

programs/07_5_wider_dup.R
# Chapter 7: without the time point, each test has three baseline values
library(dplyr)
vs_bl <- pharmaversesdtm::vs |>
filter(VSBLFL == "Y", VSTESTCD %in% c("DIABP", "PULSE", "SYSBP")) |>
select(USUBJID, VSTESTCD, VSTPT, VSSTRESN)
wide <- vs_bl |>
tidyr::pivot_wider(
id_cols = USUBJID, names_from = VSTESTCD, values_from = VSSTRESN
)
#> Warning:
#> Values from `VSSTRESN` are not uniquely identified; output will contain
#> list-cols.
#> * Use `values_fn = list` to suppress this warning.
#> * Use `values_fn = {summary_fun}` to summarise duplicates.
#> * Use the following dplyr code to identify duplicates.
#> {data} |>
#> dplyr::summarise(n = dplyr::n(), .by = c(USUBJID, VSTESTCD)) |>
#> dplyr::filter(n > 1L)
wide |> print(n = 3)
#> # A tibble: 253 x 4
#> USUBJID DIABP PULSE SYSBP
#> <chr> <list> <list> <list>
#> 1 01-701-1015 <dbl [3]> <dbl [3]> <dbl [3]>
#> 2 01-701-1023 <dbl [3]> <dbl [3]> <dbl [3]>
#> 3 01-701-1028 <dbl [3]> <dbl [3]> <dbl [3]>
#> # i 250 more rows

Each subject has three values for each test, and a cell can hold only one. pivot_wider() does not choose. It warns, and it puts all three values into the cell as a list-column (<dbl [3]>): a column whose cells hold whole vectors. That is not a table that you can use.

Do not fix it with values_fn = mean or by keeping “the last row”. Decide which value the specification asks for, and filter to it, as 07_4 does. Or keep all of them, with the time point in the name (Exercise 7-3).

programs/07_6_longer.R
# Chapter 7: pivot_longer() turns the columns back into rows
library(dplyr)
wide <- pharmaversesdtm::vs |>
filter(
VSBLFL == "Y",
VSTESTCD %in% c("DIABP", "PULSE", "SYSBP"),
VSTPT == "AFTER LYING DOWN FOR 5 MINUTES"
) |>
tidyr::pivot_wider(
id_cols = USUBJID, names_from = VSTESTCD, values_from = VSSTRESN,
names_prefix = "BL_"
)
long <- wide |>
tidyr::pivot_longer(
cols = starts_with("BL_"),
names_to = "PARAMCD",
names_prefix = "BL_",
values_to = "AVAL"
)
long |> print(n = 6)
#> # A tibble: 759 x 3
#> USUBJID PARAMCD AVAL
#> <chr> <chr> <dbl>
#> 1 01-701-1015 DIABP 56
#> 2 01-701-1015 PULSE 56
#> 3 01-701-1015 SYSBP 130
#> 4 01-701-1023 DIABP 84
#> 5 01-701-1023 PULSE 68
#> 6 01-701-1023 SYSBP 130
#> # i 753 more rows
  • cols = starts_with("BL_"): the columns to turn into rows.
  • names_to = "PARAMCD": the new column that holds the old column names.
  • names_prefix = "BL_": removes this text from the old names, so BL_DIABP becomes DIABP.
  • values_to = "AVAL": the new column that holds the values.

The 253 wide rows become 759 long rows again. This long shape, with PARAMCD and AVAL, is the shape of an ADaM BDS data set (chapter 10).

7.7 Look up a value: a table or a named vector

Section titled “7.7 Look up a value: a table or a named vector”

A lookup finds, for each code, the value that belongs to it. DM has the arm code ARMCD, and you want the dose as a number.

programs/07_7_lookup.R
# Chapter 7: look up a value: a join with a small table, or a named vector
library(dplyr)
dm <- pharmaversesdtm::dm |> select(USUBJID, ARMCD)
# 1. a lookup table, joined by the code
codes <- tribble(
~ARMCD, ~TRT01PN, ~TRT01P,
"Pbo", 0, "Placebo",
"Xan_Lo", 54, "Xanomeline Low Dose",
"Xan_Hi", 81, "Xanomeline High Dose"
)
dm |>
left_join(codes, by = join_by(ARMCD), relationship = "many-to-one") |>
count(ARMCD, TRT01PN, TRT01P)
#> # A tibble: 4 x 4
#> ARMCD TRT01PN TRT01P n
#> <chr> <dbl> <chr> <int>
#> 1 Pbo 0 Placebo 86
#> 2 Scrnfail NA <NA> 52
#> 3 Xan_Hi 81 Xanomeline High Dose 84
#> 4 Xan_Lo 54 Xanomeline Low Dose 84
# 2. a named vector: the names are the codes
dose <- c(Pbo = 0, Xan_Lo = 54, Xan_Hi = 81)
dose["Xan_Lo"]
#> Xan_Lo
#> 54
dm |>
mutate(TRT01PN = unname(dose[ARMCD])) |>
filter(USUBJID == "01-701-1015")
#> # A tibble: 1 x 3
#> USUBJID ARMCD TRT01PN
#> <chr> <chr> <dbl>
#> 1 01-701-1015 Pbo 0

1. A lookup table and a join. tribble() types a small table row by row, so it reads like the specification. A left join adds TRT01PN and TRT01P to every row. The 52 screen failures (Scrnfail) are not in the lookup table, so they get NA. The join shows you that, and relationship = "many-to-one" checks that each code is in the lookup table only once.

2. A named vector. dose <- c(Pbo = 0, Xan_Lo = 54, Xan_Hi = 81) stores three numbers with names. dose["Xan_Lo"] gives 54. dose[ARMCD] looks up the whole column at once. unname() removes the names from the result, so the new column is a plain number. Subject 01-701-1015 gets 0, placebo.

Use the table when the lookup has more than one column, or comes from a file. Use the named vector for a short list of codes in a program.

A factor is a column of categories with a fixed list of allowed values, its levels. The levels decide the order in which the values sort, count and print.

programs/07_8_factor.R
# Chapter 7: a factor gives values an order and a label, the job of a format
library(dplyr)
adsl <- pharmaverseadam::adsl |>
filter(SAFFL == "Y") |>
mutate(AGEGR = case_when(AGE < 65 ~ "<65", AGE <= 80 ~ "65-80", .default = ">80"))
# text sorts character by character: "6" comes before "<"
adsl |> count(AGEGR)
#> # A tibble: 3 x 2
#> AGEGR n
#> <chr> <int>
#> 1 65-80 144
#> 2 <65 33
#> 3 >80 77
# a factor sorts by its levels
adsl |>
mutate(AGEGR = factor(AGEGR, levels = c("<65", "65-80", ">80"))) |>
count(AGEGR)
#> # A tibble: 3 x 2
#> AGEGR n
#> <fct> <int>
#> 1 <65 33
#> 2 65-80 144
#> 3 >80 77
# the position of a level is a number that sorts by severity
sev <- c("MILD", "MODERATE", "SEVERE")
pharmaversesdtm::ae |>
mutate(AESEVN = as.integer(factor(AESEV, levels = sev))) |>
count(AESEVN, AESEV)
#> # A tibble: 3 x 3
#> AESEVN AESEV n
#> <int> <chr> <int>
#> 1 1 MILD 770
#> 2 2 MODERATE 378
#> 3 3 SEVERE 43
# levels and labels from a lookup table: codes in, text out
codes <- tibble(
code = c("Pbo", "Xan_Lo", "Xan_Hi"),
label = c("Placebo", "Xanomeline Low Dose", "Xanomeline High Dose")
)
pharmaversesdtm::dm |>
filter(ARMCD != "Scrnfail") |>
mutate(TRT = factor(ARMCD, levels = codes$code, labels = codes$label)) |>
count(TRT)
#> # A tibble: 3 x 2
#> TRT n
#> <fct> <int>
#> 1 Placebo 86
#> 2 Xanomeline Low Dose 84
#> 3 Xanomeline High Dose 84
  • Text sorts character by character. "65-80" comes before "<65", because the character 6 sorts before <. A table with that order looks wrong.
  • factor(AGEGR, levels = c("<65", "65-80", ">80")) gives the order that you want. count() and every table and plot use it.
  • as.integer() of a factor gives the position of each level: MILD 1, MODERATE 2, SEVERE 3. This is how you make a numeric sort variable such as AESEVN.
  • factor(ARMCD, levels = codes$code, labels = codes$label) turns codes into text in one step, from a lookup table. The labels keep the order of the codes: placebo, low dose, high dose.

Every ADaM data set needs values from ADSL, for example the first dose date TRTSDT on every record. match() is the base R tool for this.

programs/07_9_match.R
# Chapter 7: match() finds the row of each key, a fast lookup on a big table
library(dplyr)
adsl <- pharmaverseadam::adsl |> select(USUBJID, TRTSDT)
vs <- pharmaversesdtm::vs |> select(USUBJID, VSTESTCD, VSDTC)
match(c("01-701-1023", "01-701-1015", "99-999-9999"), adsl$USUBJID)
#> [1] 2 1 NA
vs$TRTSDT <- adsl$TRTSDT[match(vs$USUBJID, adsl$USUBJID)]
c(records = nrow(vs), no_date = sum(is.na(vs$TRTSDT)))
#> records no_date
#> 29643 0
# the same with a join
vs2 <- pharmaversesdtm::vs |>
select(USUBJID, VSTESTCD, VSDTC) |>
left_join(adsl, by = join_by(USUBJID), relationship = "many-to-one")
sum(vs$TRTSDT != vs2$TRTSDT) # dates that differ
#> [1] 0
  • match(x, table) gives, for each value of x, its position in table. 01-701-1023 is row 2 of ADSL, 01-701-1015 row 1, and an unknown subject gives NA.
  • adsl$TRTSDT[match(vs$USUBJID, adsl$USUBJID)] takes the date from the row that matched. All 29643 VS records get a date: none is missing.
  • The left join gives the same dates: 0 differ.

match() does not add columns that you did not ask for, and it never adds rows: one key, one position. The left join is easier to read, and it is the default in this book. Use match() when you need one value from a big table inside a longer expression.

  • across() applies one rule to many columns, in mutate() or summarise().
  • case_when() tests its conditions in order; the first true one wins. Test NA first.
  • Prefer a vectorised call. Use a loop for work that is not a column.
  • pivot_wider() turns long into wide; pivot_longer() turns wide into long. A repeated key gives list-columns and a warning.
  • Look up a value with a join (a table), a named vector, a factor (order and labels), or match().

Exercise 7-1. Add COMPLT8 and COMPLT16 to the columns of across() in program 07_1. How many week-8 and week-16 completers are there?

Exercise 7-2. Make a tibble with the ages 63, NA and 85. Give it two age group columns: one with case_when() without an is.na() test, and one with it. Which group does the missing age get in each?

Exercise 7-3. Turn the baseline blood pressure and pulse of 01-701-1015 into one row, with names_from = c(VSTESTCD, VSTPTNUM). How many value columns do you get?

Exercise 7-4. Use a named vector to turn SEX (F, M) into Female and Male in the safety population. Count both columns together.

The answers are in Answers, Part I (chapters 5 to 8).

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

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

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

© 2026 DataStep Labs. All rights reserved.