Skip to content

Groups and order

Clinical data comes in groups. Records belong to a subject. A subject has visits. A visit has measurements. This chapter shows how R sorts rows, works inside each group, and carries a value from one row to the next. You use these tools in almost every ADaM program.

programs/04_1_arrange.R
# Chapter 4: arrange() sorts rows. NA goes last, and text sorts by its codes (upper case first)
library(dplyr)
ae <- pharmaversesdtm::ae |>
filter(USUBJID %in% c("01-701-1015", "01-701-1023")) |>
select(USUBJID, AESEQ, AEDECOD, AESTDTC, AEENDTC)
ae
#> # A tibble: 7 x 5
#> USUBJID AESEQ AEDECOD AESTDTC AEENDTC
#> <chr> <dbl> <chr> <chr> <chr>
#> 1 01-701-1015 1 APPLICATION SITE ERYTHEMA 2014-01-03 <NA>
#> 2 01-701-1015 2 APPLICATION SITE PRURITUS 2014-01-03 <NA>
#> 3 01-701-1015 3 DIARRHOEA 2014-01-09 2014-01-11
#> 4 01-701-1023 3 ATRIOVENTRICULAR BLOCK SECOND DEGREE 2012-08-26 <NA>
#> 5 01-701-1023 1 ERYTHEMA 2012-08-07 2012-08-30
#> 6 01-701-1023 2 ERYTHEMA 2012-08-07 <NA>
#> 7 01-701-1023 4 ERYTHEMA 2012-08-07 2012-08-30
ae |> arrange(USUBJID, AESEQ)
#> # A tibble: 7 x 5
#> USUBJID AESEQ AEDECOD AESTDTC AEENDTC
#> <chr> <dbl> <chr> <chr> <chr>
#> 1 01-701-1015 1 APPLICATION SITE ERYTHEMA 2014-01-03 <NA>
#> 2 01-701-1015 2 APPLICATION SITE PRURITUS 2014-01-03 <NA>
#> 3 01-701-1015 3 DIARRHOEA 2014-01-09 2014-01-11
#> 4 01-701-1023 1 ERYTHEMA 2012-08-07 2012-08-30
#> 5 01-701-1023 2 ERYTHEMA 2012-08-07 <NA>
#> 6 01-701-1023 3 ATRIOVENTRICULAR BLOCK SECOND DEGREE 2012-08-26 <NA>
#> 7 01-701-1023 4 ERYTHEMA 2012-08-07 2012-08-30
ae |> arrange(AEENDTC)
#> # A tibble: 7 x 5
#> USUBJID AESEQ AEDECOD AESTDTC AEENDTC
#> <chr> <dbl> <chr> <chr> <chr>
#> 1 01-701-1023 1 ERYTHEMA 2012-08-07 2012-08-30
#> 2 01-701-1023 4 ERYTHEMA 2012-08-07 2012-08-30
#> 3 01-701-1015 3 DIARRHOEA 2014-01-09 2014-01-11
#> 4 01-701-1015 1 APPLICATION SITE ERYTHEMA 2014-01-03 <NA>
#> 5 01-701-1015 2 APPLICATION SITE PRURITUS 2014-01-03 <NA>
#> 6 01-701-1023 3 ATRIOVENTRICULAR BLOCK SECOND DEGREE 2012-08-26 <NA>
#> 7 01-701-1023 2 ERYTHEMA 2012-08-07 <NA>
ae |> arrange(desc(AEENDTC))
#> # A tibble: 7 x 5
#> USUBJID AESEQ AEDECOD AESTDTC AEENDTC
#> <chr> <dbl> <chr> <chr> <chr>
#> 1 01-701-1015 3 DIARRHOEA 2014-01-09 2014-01-11
#> 2 01-701-1023 1 ERYTHEMA 2012-08-07 2012-08-30
#> 3 01-701-1023 4 ERYTHEMA 2012-08-07 2012-08-30
#> 4 01-701-1015 1 APPLICATION SITE ERYTHEMA 2014-01-03 <NA>
#> 5 01-701-1015 2 APPLICATION SITE PRURITUS 2014-01-03 <NA>
#> 6 01-701-1023 3 ATRIOVENTRICULAR BLOCK SECOND DEGREE 2012-08-26 <NA>
#> 7 01-701-1023 2 ERYTHEMA 2012-08-07 <NA>
letters4 <- tibble(X = c("b", "B", "a", "A"))
letters4 |> arrange(X) |> pull(X)
#> [1] "A" "B" "a" "b"
sort(letters4$X)
#> [1] "a" "A" "b" "B"
  • The rows of a data frame have an order, but it is not always the order you expect. In SDTM AE, the first record of subject 01-701-1023 has AESEQ 3.
  • arrange(USUBJID, AESEQ) sorts by the subject, then by the sequence number inside each subject.
  • NA goes last, in both directions. arrange(AEENDTC) and arrange(desc(AEENDTC)) both put the four events with no end date at the bottom.
  • arrange() sorts text by the codes of its characters: upper case before lower case, "A" "B" "a" "b". This is the “C” sort order. It is the same on every computer. sort() of base R uses the language settings of the computer, so it can give another order: here "a" "A" "b" "B".

Sort with enough columns to make the order unique. If two rows have the same sort keys, their order comes from the input, and a change in the input can change your result.

4.2 Remove duplicates on purpose: distinct()

Section titled “4.2 Remove duplicates on purpose: distinct()”
programs/04_2_distinct.R
# Chapter 4: distinct() keeps one row per key. Look at what it removes
library(dplyr)
ae <- pharmaversesdtm::ae |>
select(USUBJID, AESEQ, AEDECOD, AESEV, AESTDTC)
nrow(ae)
#> [1] 1191
ae_pt <- ae |> distinct(USUBJID, AEDECOD)
nrow(ae_pt)
#> [1] 822
ae_dups <- ae |> filter(duplicated(pick(USUBJID, AEDECOD)))
nrow(ae_dups)
#> [1] 369
ae |>
arrange(USUBJID, AEDECOD, AESTDTC, AESEQ) |>
distinct(USUBJID, AEDECOD, .keep_all = TRUE) |>
filter(USUBJID == "01-701-1023")
#> # A tibble: 2 x 5
#> USUBJID AESEQ AEDECOD AESEV AESTDTC
#> <chr> <dbl> <chr> <chr> <chr>
#> 1 01-701-1023 3 ATRIOVENTRICULAR BLOCK SECOND DEGREE MILD 2012-08-26
#> 2 01-701-1023 1 ERYTHEMA MILD 2012-08-07

distinct(USUBJID, AEDECOD) keeps one row for each combination of subject and preferred term. 1191 AE records become 822 rows. By default, distinct() keeps only the columns that you name.

Never remove rows without a way to see them. duplicated() is TRUE for each row whose key appeared in an earlier row. pick(USUBJID, AEDECOD) gives it the key columns. The filter finds the 369 rows that distinct() removed. 822 + 369 = 1191: every record is accounted for.

.keep_all = TRUE keeps all columns, from the first row of each key. So the sort before distinct() decides which row you keep. Here the earliest start date wins, and AESEQ breaks a tie. Subject 01-701-1023 has three ERYTHEMA records on the same day. The program keeps the one with AESEQ 1.

Exercise 4-1. Keep one row per subject and system organ class (AEBODSYS). How many rows does distinct() remove?

group_by(USUBJID) makes a grouped data frame: the next steps work on each subject separately.

programs/04_3_first_last.R
# Chapter 4: group_by() and row_number() mark the first and the last row of each subject
library(dplyr)
ae <- pharmaversesdtm::ae |>
filter(USUBJID %in% c("01-701-1015", "01-701-1023")) |>
select(USUBJID, AESEQ, AEDECOD) |>
arrange(USUBJID, AESEQ)
ae |>
group_by(USUBJID) |>
mutate(
N_AE = row_number(),
FIRST = row_number() == 1,
LAST = row_number() == n()
) |>
ungroup()
#> # A tibble: 7 x 6
#> USUBJID AESEQ AEDECOD N_AE FIRST LAST
#> <chr> <dbl> <chr> <int> <lgl> <lgl>
#> 1 01-701-1015 1 APPLICATION SITE ERYTHEMA 1 TRUE FALSE
#> 2 01-701-1015 2 APPLICATION SITE PRURITUS 2 FALSE FALSE
#> 3 01-701-1015 3 DIARRHOEA 3 FALSE TRUE
#> 4 01-701-1023 1 ERYTHEMA 1 TRUE FALSE
#> 5 01-701-1023 2 ERYTHEMA 2 FALSE FALSE
#> 6 01-701-1023 3 ATRIOVENTRICULAR BLOCK SECOND DEGREE 3 FALSE FALSE
#> 7 01-701-1023 4 ERYTHEMA 4 FALSE TRUE
ae |>
group_by(USUBJID) |>
summarise(
n_ae = n(),
first_ae = first(AEDECOD),
last_ae = last(AEDECOD)
)
#> # A tibble: 2 x 4
#> USUBJID n_ae first_ae last_ae
#> <chr> <int> <chr> <chr>
#> 1 01-701-1015 3 APPLICATION SITE ERYTHEMA DIARRHOEA
#> 2 01-701-1023 4 ERYTHEMA ERYTHEMA

Inside a group:

  • row_number() numbers the rows from 1. It starts again at 1 for each subject.
  • n() is the number of rows of the group.
  • So row_number() == 1 is TRUE on the first row, and row_number() == n() is TRUE on the last row.
  • first(x) and last(x) give the first and the last value of a column.

The two steps show the difference between mutate() and summarise(). Grouped mutate() keeps every row: 7 rows in, 7 rows out. Grouped summarise() gives one row per group. Subject 01-701-1015 has 3 adverse events and subject 01-701-1023 has 4.

ungroup() at the end removes the groups. Section 4.9 shows why that matters.

Exercise 4-2. How many subjects in pharmaversesdtm::ae have at least one adverse event? Which subject has the most AE records, and how many?

4.4 Keep chosen rows: slice_head() and slice_max()

Section titled “4.4 Keep chosen rows: slice_head() and slice_max()”
programs/04_4_slice.R
# Chapter 4: slice_head() and slice_max() keep chosen rows of each group. Watch the ties
library(dplyr)
ae <- pharmaversesdtm::ae |>
filter(USUBJID %in% c("01-701-1015", "01-701-1023")) |>
mutate(SEVN = match(AESEV, c("MILD", "MODERATE", "SEVERE"))) |>
select(USUBJID, AESEQ, AEDECOD, AESEV, SEVN, AESTDTC)
ae |>
arrange(USUBJID, AESTDTC, AESEQ) |>
slice_head(n = 1, by = USUBJID)
#> # A tibble: 2 x 6
#> USUBJID AESEQ AEDECOD AESEV SEVN AESTDTC
#> <chr> <dbl> <chr> <chr> <int> <chr>
#> 1 01-701-1015 1 APPLICATION SITE ERYTHEMA MILD 1 2014-01-03
#> 2 01-701-1023 1 ERYTHEMA MILD 1 2012-08-07
ae |> slice_max(SEVN, n = 1, by = USUBJID)
#> # A tibble: 4 x 6
#> USUBJID AESEQ AEDECOD AESEV SEVN AESTDTC
#> <chr> <dbl> <chr> <chr> <int> <chr>
#> 1 01-701-1015 1 APPLICATION SITE ERYTHEMA MILD 1 2014-01-03
#> 2 01-701-1015 2 APPLICATION SITE PRURITUS MILD 1 2014-01-03
#> 3 01-701-1015 3 DIARRHOEA MILD 1 2014-01-09
#> 4 01-701-1023 2 ERYTHEMA MODERATE 2 2012-08-07
ae |> slice_max(SEVN, n = 1, by = USUBJID, with_ties = FALSE)
#> # A tibble: 2 x 6
#> USUBJID AESEQ AEDECOD AESEV SEVN AESTDTC
#> <chr> <dbl> <chr> <chr> <int> <chr>
#> 1 01-701-1015 1 APPLICATION SITE ERYTHEMA MILD 1 2014-01-03
#> 2 01-701-1023 2 ERYTHEMA MODERATE 2 2012-08-07
  • slice_head(n = 1, by = USUBJID) keeps the first row of each subject. The sort before it decides which row is first: here the earliest event.
  • match(AESEV, c("MILD", "MODERATE", "SEVERE")) gives the position of each value in the list: 1, 2 or 3. Now the severity is a number that you can compare.
  • slice_max(SEVN, n = 1, by = USUBJID) keeps the row with the highest value. But look at the result: 4 rows, not 2. All three events of 01-701-1015 are MILD, so all three tie for the maximum, and slice_max() keeps all ties.
  • with_ties = FALSE keeps exactly one row per group: the first of the ties.

4.5 The previous and the next row: lag() and lead()

Section titled “4.5 The previous and the next row: lag() and lead()”
programs/04_5_lag.R
# Chapter 4: lag() and lead() read the value of the previous or the next row
library(dplyr)
pharmaversesdtm::vs |>
filter(USUBJID == "01-701-1015", VSTESTCD == "WEIGHT") |>
arrange(VISITNUM) |>
select(VISIT, VSSTRESN) |>
mutate(
PREV = lag(VSSTRESN),
NEXT = lead(VSSTRESN),
CHG_PREV = VSSTRESN - PREV
) |>
print(n = 4)
#> # A tibble: 11 x 5
#> VISIT VSSTRESN PREV NEXT CHG_PREV
#> <chr> <dbl> <dbl> <dbl> <dbl>
#> 1 SCREENING 1 54.0 NA 54.4 NA
#> 2 BASELINE 54.4 54.0 53.1 0.450
#> 3 WEEK 2 53.1 54.4 54.0 -1.36
#> 4 WEEK 4 54.0 53.1 53.1 0.910
#> # i 7 more rows

lag(x) moves the column one row down: each row gets the value of the row before it. The first row gets NA, because no row comes before it. lead(x) moves the column one row up. CHG_PREV is the change since the previous visit: subject 01-701-1015 lost 1.36 kg from BASELINE to WEEK 2.

lag() uses the order of the rows, so sort first. In a data frame with many subjects, use lag() inside a group (.by = USUBJID), or the first row of a subject gets the last value of the subject before it.

The most common job in ADaM: keep the baseline value, and compute the change at every later visit.

programs/04_6_baseline.R
# Chapter 4: carry the baseline value to every row of the subject, then compute the change
library(dplyr)
wt <- pharmaversesdtm::vs |>
filter(USUBJID %in% c("01-701-1015", "01-701-1023"), VSTESTCD == "WEIGHT") |>
select(USUBJID, VISITNUM, VISIT, VSBLFL, VSSTRESN) |>
arrange(USUBJID, VISITNUM)
wt$VSBLFL[1:3] == "Y"
#> [1] NA TRUE NA
wt$VSSTRESN[1:3][wt$VSBLFL[1:3] == "Y"]
#> [1] NA 54.43 NA
wt |>
group_by(USUBJID) |>
mutate(
BASE = VSSTRESN[VSBLFL %in% "Y"],
BASEVIS = VISITNUM[VSBLFL %in% "Y"],
CHG = if_else(VISITNUM > BASEVIS, VSSTRESN - BASE, NA)
) |>
ungroup() |>
select(-BASEVIS) |>
print(n = 5)
#> # A tibble: 15 x 7
#> USUBJID VISITNUM VISIT VSBLFL VSSTRESN BASE CHG
#> <chr> <dbl> <chr> <chr> <dbl> <dbl> <dbl>
#> 1 01-701-1015 1 SCREENING 1 <NA> 54.0 54.4 NA
#> 2 01-701-1015 3 BASELINE Y 54.4 54.4 NA
#> 3 01-701-1015 4 WEEK 2 <NA> 53.1 54.4 -1.36
#> 4 01-701-1015 5 WEEK 4 <NA> 54.0 54.4 -0.450
#> 5 01-701-1015 7 WEEK 6 <NA> 53.1 54.4 -1.36
#> # i 10 more rows

VSBLFL is "Y" on the baseline record and NA on the others. The first two lines show a trap. VSBLFL == "Y" gives NA TRUE NA, not FALSE TRUE FALSE. When you select values with it, each NA selects a missing value: you get NA 54.43 NA instead of the one baseline value 54.43.

%in% asks “is the value in this list?”. It gives FALSE for NA, never NA. So VSSTRESN[VSBLFL %in% "Y"] selects exactly the baseline value. Inside a group, that one value goes to every row of the subject: BASE. Then CHG is the change for the visits after baseline. Before baseline it is NA on purpose: a change from baseline before the baseline has no meaning.

A tibble prints about three digits, so BASE shows as 54.4. The value is still 54.43.

Exercise 4-3. Add PCHG, the percent change from baseline, to the weights of subject 01-701-1015. What must you check before you divide?

programs/04_7_cumulative.R
# Chapter 4: cumsum() and cummax() give running values inside each subject
library(dplyr)
pharmaversesdtm::ae |>
filter(USUBJID %in% c("01-701-1015", "01-701-1023")) |>
mutate(SEVN = match(AESEV, c("MILD", "MODERATE", "SEVERE"))) |>
select(USUBJID, AESEQ, AESEV, SEVN) |>
arrange(USUBJID, AESEQ) |>
mutate(
N_SO_FAR = cumsum(SEVN >= 1),
N_MOD_SO_FAR = cumsum(SEVN >= 2),
WORST_SO_FAR = cummax(SEVN),
.by = USUBJID
)
#> # A tibble: 7 x 7
#> USUBJID AESEQ AESEV SEVN N_SO_FAR N_MOD_SO_FAR WORST_SO_FAR
#> <chr> <dbl> <chr> <int> <int> <int> <int>
#> 1 01-701-1015 1 MILD 1 1 0 1
#> 2 01-701-1015 2 MILD 1 2 0 1
#> 3 01-701-1015 3 MILD 1 3 0 1
#> 4 01-701-1023 1 MILD 1 1 0 1
#> 5 01-701-1023 2 MODERATE 2 2 1 2
#> 6 01-701-1023 3 MILD 1 3 1 2
#> 7 01-701-1023 4 MILD 1 4 1 2
  • cumsum(x) is the running total: each row gets the sum of all values up to that row. TRUE counts as 1. So cumsum(SEVN >= 1) counts the events so far, and cumsum(SEVN >= 2) counts the moderate or severe events so far.
  • cummax(x) is the running maximum: the worst severity so far. For subject 01-701-1023 it goes to 2 at the second event and stays at 2.
  • .by = USUBJID makes the groups for this one step only. The running values start again for each subject.

4.8 Last observation carried forward: fill()

Section titled “4.8 Last observation carried forward: fill()”

The SAP can say that a missing value is replaced by the last value before it. That is last observation carried forward (LOCF).

programs/04_8_fill.R
# Chapter 4: tidyr::fill() carries the last observation forward (LOCF), with a flag
library(dplyr)
library(tidyr)
pharmaversesdtm::vs |>
filter(
USUBJID == "01-703-1279", VSTESTCD == "SYSBP",
VSTPT == "AFTER STANDING FOR 3 MINUTES"
) |>
select(USUBJID, VISITNUM, VISIT, VSSTRESN) |>
arrange(USUBJID, VISITNUM) |>
mutate(AVAL = VSSTRESN, DTYPE = if_else(is.na(AVAL), "LOCF", NA)) |>
group_by(USUBJID) |>
fill(AVAL, .direction = "down") |>
ungroup()
#> # A tibble: 4 x 6
#> USUBJID VISITNUM VISIT VSSTRESN AVAL DTYPE
#> <chr> <dbl> <chr> <dbl> <dbl> <chr>
#> 1 01-703-1279 1 SCREENING 1 140 140 <NA>
#> 2 01-703-1279 2 SCREENING 2 136 136 <NA>
#> 3 01-703-1279 3 BASELINE 120 120 <NA>
#> 4 01-703-1279 4 WEEK 2 NA 120 LOCF

Subject 01-703-1279 has no systolic blood pressure (standing, 3 minutes) at WEEK 2. fill(AVAL, .direction = "down") from the package tidyr replaces each NA with the last value above it: 120, from BASELINE. Three rules:

  • Keep the collected value. The program fills a copy, AVAL, and leaves VSSTRESN as it was collected.
  • Flag the filled rows. DTYPE = "LOCF" is set before fill(), while you can still see which values are missing.
  • Fill inside a group. fill() respects group_by(), so a value never flows from one subject to the next.

4.9 mutate() or summarise(), and the group you forgot

Section titled “4.9 mutate() or summarise(), and the group you forgot”
programs/04_9_ungroup.R
# Chapter 4: summarise() keeps a group, so the next step works inside it. ungroup() or .by = prevents that
library(dplyr)
adsl <- pharmaverseadam::adsl |>
filter(SAFFL == "Y")
adsl |>
group_by(ARM, SEX) |>
summarise(n = n()) |>
mutate(PCT = round(100 * n / sum(n), 1))
#> `summarise()` has regrouped the output.
#> i Summaries were computed grouped by ARM and SEX.
#> i Output is grouped by ARM.
#> i Use `summarise(.groups = "drop_last")` to silence this message.
#> i Use `summarise(.by = c(ARM, SEX))` for per-operation grouping
#> (`?dplyr::dplyr_by`) instead.
#> # A tibble: 6 x 4
#> # Groups: ARM [3]
#> ARM SEX n PCT
#> <chr> <chr> <int> <dbl>
#> 1 Placebo F 53 61.6
#> 2 Placebo M 33 38.4
#> 3 Xanomeline High Dose F 40 47.6
#> 4 Xanomeline High Dose M 44 52.4
#> 5 Xanomeline Low Dose F 50 59.5
#> 6 Xanomeline Low Dose M 34 40.5
adsl |>
summarise(n = n(), .by = c(ARM, SEX)) |>
mutate(PCT = round(100 * n / sum(n), 1))
#> # A tibble: 6 x 4
#> ARM SEX n PCT
#> <chr> <chr> <int> <dbl>
#> 1 Placebo F 53 20.9
#> 2 Placebo M 33 13
#> 3 Xanomeline High Dose M 44 17.3
#> 4 Xanomeline Low Dose M 34 13.4
#> 5 Xanomeline High Dose F 40 15.7
#> 6 Xanomeline Low Dose F 50 19.7

summarise() removes only the last grouping column. After group_by(ARM, SEX) |> summarise(...), the result is still grouped by ARM. dplyr tells you so in a message. The next step, mutate(PCT = 100 * n / sum(n)), then computes sum(n) inside each arm: 61.6% of the placebo subjects are women. That can be what you want, but only if you meant it.

The second step uses .by = c(ARM, SEX). The groups exist for this one summarise() only, so the result has no groups, and sum(n) is the total of all subjects: placebo women are 20.9% of all subjects. Note that .by keeps the groups in the order in which they first appear in the data. group_by() sorts them.

You want Use
a new column on every row, computed per group mutate(..., .by = USUBJID)
one row per group summarise(..., .by = USUBJID)
several steps in the same groups group_by(), the steps, then ungroup()

Exercise 4-4. Change the first step of 04_9 so that the percents are of all 254 subjects. Check that the counts add up to 254 and the percents to 100.

  • arrange() sorts. NA goes last, and text sorts in the “C” order. Sort by enough columns to make the order unique.
  • distinct() removes duplicates. duplicated() shows what it removed. Count both.
  • In a group, row_number(), n(), first() and last() find the first and the last row.
  • slice_head() and slice_max() keep chosen rows. slice_max() keeps ties unless with_ties = FALSE.
  • lag() and lead() read the row before and after. Sort first.
  • To carry a value, compute it for the whole group. Select with %in%, not ==, when the column has NA.
  • cumsum() and cummax() give running values. tidyr::fill() carries the last observation forward.
  • mutate() keeps the rows, summarise() gives one row per group. End groups with ungroup(), or use .by.

Exercises 4-1 to 4-4 are in the sections above. One more:

Exercise 4-5. For each subject in pharmaversesdtm::ae, find the worst severity of all their adverse events. Then count the subjects for each worst severity. This is the core of an “AEs by maximum severity” table.

The answers are in Answers, Part I.

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.