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.
4.1 Sort with arrange()
Section titled “4.1 Sort with arrange()”# 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-30ae |> 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 hasAESEQ3. arrange(USUBJID, AESEQ)sorts by the subject, then by the sequence number inside each subject.NAgoes last, in both directions.arrange(AEENDTC)andarrange(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()”# Chapter 4: distinct() keeps one row per key. Look at what it removeslibrary(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-07distinct(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?
4.3 Groups: the first and the last row
Section titled “4.3 Groups: the first and the last row”group_by(USUBJID) makes a grouped data frame: the next steps work on each subject separately.
# Chapter 4: group_by() and row_number() mark the first and the last row of each subjectlibrary(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 ERYTHEMAInside 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() == 1isTRUEon the first row, androw_number() == n()isTRUEon the last row. first(x)andlast(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()”# Chapter 4: slice_head() and slice_max() keep chosen rows of each group. Watch the tieslibrary(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-07slice_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, andslice_max()keeps all ties.with_ties = FALSEkeeps 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()”# Chapter 4: lag() and lead() read the value of the previous or the next rowlibrary(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 rowslag(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.
4.6 Carry a value: baseline and change
Section titled “4.6 Carry a value: baseline and change”The most common job in ADaM: keep the baseline value, and compute the change at every later visit.
# Chapter 4: carry the baseline value to every row of the subject, then compute the changelibrary(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 NAwt$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 rowsVSBLFL 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?
4.7 Running values: cumsum() and cummax()
Section titled “4.7 Running values: cumsum() and cummax()”# Chapter 4: cumsum() and cummax() give running values inside each subjectlibrary(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 2cumsum(x)is the running total: each row gets the sum of all values up to that row.TRUEcounts as 1. Socumsum(SEVN >= 1)counts the events so far, andcumsum(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 = USUBJIDmakes 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).
# Chapter 4: tidyr::fill() carries the last observation forward (LOCF), with a flaglibrary(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 LOCFSubject 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 leavesVSSTRESNas it was collected. - Flag the filled rows.
DTYPE = "LOCF"is set beforefill(), while you can still see which values are missing. - Fill inside a group.
fill()respectsgroup_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”# Chapter 4: summarise() keeps a group, so the next step works inside it. ungroup() or .by = prevents thatlibrary(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.7summarise() 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.
4.10 Summary
Section titled “4.10 Summary”arrange()sorts.NAgoes 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()andlast()find the first and the last row. slice_head()andslice_max()keep chosen rows.slice_max()keeps ties unlesswith_ties = FALSE.lag()andlead()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 hasNA. cumsum()andcummax()give running values.tidyr::fill()carries the last observation forward.mutate()keeps the rows,summarise()gives one row per group. End groups withungroup(), or use.by.
4.11 Exercises
Section titled “4.11 Exercises”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.