Summarise
Every program in this book looks at data, counts it and summarises it. In R, a few functions do most of that work: glimpse() to look, count() to count, summarise() for statistics, and group_by() to do it per arm. This chapter builds the numbers at the top of every safety table: the big N, the percent of the arm, and the subjects with at least one adverse event.
6.1 Look at a data frame
Section titled “6.1 Look at a data frame”Before you use a data frame, look at it. How many rows and columns? Which types? What do the first rows look like?
# Chapter 6: look at a data frame before you use itlibrary(dplyr)
adsl <- pharmaverseadam::adsl |> select(STUDYID, USUBJID, ARM, TRT01A, AGE, SEX, RACE, SAFFL, TRTSDT)
dim(adsl)#> [1] 306 9names(adsl)#> [1] "STUDYID" "USUBJID" "ARM" "TRT01A" "AGE" "SEX" "RACE"#> [8] "SAFFL" "TRTSDT"glimpse(adsl)#> Rows: 306#> Columns: 9#> $ STUDYID <chr> "CDISCPILOT01", "CDISCPILOT01", "CDISCPILOT01", "CDISCPILOT01"~#> $ USUBJID <chr> "01-701-1015", "01-701-1023", "01-701-1028", "01-701-1033", "0~#> $ ARM <chr> "Placebo", "Placebo", "Xanomeline High Dose", "Xanomeline Low ~#> $ TRT01A <chr> "Placebo", "Placebo", "Xanomeline High Dose", "Xanomeline Low ~#> $ AGE <dbl> 63, 64, 71, 74, 77, 85, 59, 68, 81, 84, 52, 84, 81, 57, 75, 57~#> $ SEX <chr> "F", "M", "M", "M", "F", "F", "F", "M", "F", "M", "M", "M", "F~#> $ RACE <chr> "WHITE", "WHITE", "WHITE", "WHITE", "WHITE", "WHITE", "WHITE",~#> $ SAFFL <chr> "Y", "Y", "Y", "Y", "Y", "Y", "N", "Y", "Y", "Y", "Y", "Y", "Y~#> $ TRTSDT <date> 2014-01-02, 2012-08-05, 2013-07-19, 2014-03-18, 2014-07-01, 2~
# the labels, as PROC CONTENTS lists themsapply(adsl, attr, "label")#> STUDYID USUBJID#> "Study Identifier" "Unique Subject Identifier"#> ARM TRT01A#> "Description of Planned Arm" "Actual Treatment for Period 01"#> AGE SEX#> "Age" "Sex"#> RACE SAFFL#> "Race" "Safety Population Flag"#> TRTSDT#> "Date of First Exposure to Treatment"
adsl |> filter(SAFFL == "Y") |> select(USUBJID, TRT01A, AGE, SEX, RACE) |> print(n = 5)#> # A tibble: 254 x 5#> USUBJID TRT01A AGE SEX RACE#> <chr> <chr> <dbl> <chr> <chr>#> 1 01-701-1015 Placebo 63 F WHITE#> 2 01-701-1023 Placebo 64 M WHITE#> 3 01-701-1028 Xanomeline High Dose 71 M WHITE#> 4 01-701-1033 Xanomeline Low Dose 74 M WHITE#> 5 01-701-1034 Xanomeline High Dose 77 F WHITE#> # i 249 more rowsdim()gives the number of rows and columns: 306 and 9.names()gives the column names, in order.glimpse()lists every column on one line: its name, its type (<chr>text,<dbl>number,<date>date) and its first values. It is the fastest look at a wide data frame.- A label is a longer description of a column. The pharmaverse data sets keep the CDISC label as an attribute of each column.
sapply(adsl, attr, "label")reads the label of every column. print(n = 5)prints five rows of a tibble. The last line tells you how many rows are not shown: 249 of the 254 subjects in the safety population.
Subject 01-701-1015 is the first row: a 63-year-old white woman on placebo.
6.2 Count rows: count()
Section titled “6.2 Count rows: count()”# Chapter 6: count rows per value with count(), and add a percentlibrary(dplyr)
adsl <- pharmaverseadam::adsl |> filter(SAFFL == "Y")
adsl |> count(SEX)#> # A tibble: 2 x 2#> SEX n#> <chr> <int>#> 1 F 143#> 2 M 111
adsl |> count(SEX) |> mutate(pct = round(100 * n / sum(n), 1))#> # A tibble: 2 x 3#> SEX n pct#> <chr> <int> <dbl>#> 1 F 143 56.3#> 2 M 111 43.7
adsl |> count(TRT01A, SEX)#> # A tibble: 6 x 3#> TRT01A SEX n#> <chr> <chr> <int>#> 1 Placebo F 53#> 2 Placebo M 33#> 3 Xanomeline High Dose F 35#> 4 Xanomeline High Dose M 37#> 5 Xanomeline Low Dose F 55#> 6 Xanomeline Low Dose M 41
# count() keeps NA as a value of its ownpharmaversesdtm::vs |> filter(VISIT == "BASELINE") |> count(VSPOS)#> # A tibble: 3 x 2#> VSPOS n#> <chr> <int>#> 1 STANDING 1518#> 2 SUPINE 759#> 3 <NA> 506count(SEX)gives one row per value, and the number of rows inn: 143 women and 111 men in the safety population.count()returns a data frame. So a percent is onemutate()away.sum(n)is the total of the column: 254.count(TRT01A, SEX)counts each combination. This is the long form of a cross table: one row per cell. On placebo there are 53 women and 33 men.count()countsNAas a value. At baseline, 506 vital-sign records have no position (VSPOS). Weight and temperature are measured in no position.count()shows them as their own row, so you see them.
6.3 Statistics: summarise()
Section titled “6.3 Statistics: summarise()”# Chapter 6: summary statistics per group with summarise()library(dplyr)
pharmaverseadam::adsl |> filter(SAFFL == "Y") |> group_by(TRT01A) |> summarise( n = n(), mean = round(mean(AGE), 1), sd = round(sd(AGE), 1), median = median(AGE), q1 = quantile(AGE, 0.25), q3 = quantile(AGE, 0.75), min = min(AGE), max = max(AGE) )#> # A tibble: 3 x 9#> TRT01A n mean sd median q1 q3 min max#> <chr> <int> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>#> 1 Placebo 86 75.2 8.6 76 69.2 81.8 52 89#> 2 Xanomeline High Dose 72 73.8 7.9 75.5 70.5 79 56 88#> 3 Xanomeline Low Dose 96 76 8.1 78 71 82 51 88
# one missing value makes the mean missing, unless you say na.rm = TRUEpharmaversesdtm::vs |> filter(VSTESTCD == "PULSE", VISIT == "WEEK 2") |> summarise( n = n(), n_missing = sum(is.na(VSSTRESN)), mean = mean(VSSTRESN), mean_known = round(mean(VSSTRESN, na.rm = TRUE), 1) )#> # A tibble: 1 x 4#> n n_missing mean mean_known#> <int> <int> <dbl> <dbl>#> 1 747 1 NA 74.6summarise() makes one row per group. Each line inside it makes one column:
| Function | Gives |
|---|---|
n() |
the number of rows |
mean(), sd() |
the mean and the standard deviation |
median() |
the median |
quantile(x, 0.25) |
a quantile: here the first quartile, Q1 |
min(), max() |
the smallest and the largest value |
The placebo arm has 86 subjects with a mean age of 75.2 years, from 52 to 89.
A missing value spreads. At week 2, one of the 747 pulse records has no value. mean() of a column with one NA is NA: R does not know the missing value, so it does not know the mean. na.rm = TRUE (“NA remove”) computes the mean of the known values: 74.6. Every summary function takes na.rm. Decide on purpose whether your table needs it, and report the number of missing values next to the statistic.
6.4 The big N
Section titled “6.4 The big N”Every table header shows the number of subjects per arm: the big N. It is the denominator of every percent in the table. Compute it once, from the population, and use it everywhere.
# Chapter 6: the big N per arm, computed oncelibrary(dplyr)
adsl <- pharmaverseadam::adsl |> filter(SAFFL == "Y")
bign <- adsl |> count(TRT01A, name = "BIGN")bign#> # A tibble: 3 x 2#> TRT01A BIGN#> <chr> <int>#> 1 Placebo 86#> 2 Xanomeline High Dose 72#> 3 Xanomeline Low Dose 96
# the column headers of a tablebign |> mutate(header = paste0(TRT01A, " (N=", BIGN, ")"))#> # A tibble: 3 x 3#> TRT01A BIGN header#> <chr> <int> <chr>#> 1 Placebo 86 Placebo (N=86)#> 2 Xanomeline High Dose 72 Xanomeline High Dose (N=72)#> 3 Xanomeline Low Dose 96 Xanomeline Low Dose (N=96)
# one number by name: a named vectorn_by_arm <- pull(bign, BIGN, name = TRT01A)n_by_arm["Placebo"]#> Placebo#> 86
# planned arm and actual treatment are not the same for every subjectadsl |> count(ARM, TRT01A)#> # A tibble: 4 x 3#> ARM TRT01A n#> <chr> <chr> <int>#> 1 Placebo Placebo 86#> 2 Xanomeline High Dose Xanomeline High Dose 72#> 3 Xanomeline High Dose Xanomeline Low Dose 12#> 4 Xanomeline Low Dose Xanomeline Low Dose 84count(TRT01A, name = "BIGN")counts the subjects of the safety population per actual treatment: 86, 72 and 96.paste0()glues text and numbers together into the column headers:Placebo (N=86).pull(bign, BIGN, name = TRT01A)turns the column into a named vector: a vector whose values have names.n_by_arm["Placebo"]then gives 86, by name, not by position.
The last count shows why a table must say which treatment variable it uses. 12 subjects were randomised to the high dose (ARM) but received the low dose (TRT01A). A safety table counts by the actual treatment. An efficacy table usually counts by the randomised arm.
6.5 A group statistic on every row
Section titled “6.5 A group statistic on every row”summarise() gives one row per group. Sometimes you want the group statistic on every row instead: the big N of the subject’s arm, or the mean of the arm. Use mutate() after group_by().
# Chapter 6: put a group statistic on every row with group_by() and mutate()library(dplyr)
adsl <- pharmaverseadam::adsl |> filter(SAFFL == "Y")
with_n <- adsl |> group_by(TRT01A) |> mutate(BIGN = n(), MEANAGE = round(mean(AGE), 1)) |> ungroup() |> select(USUBJID, TRT01A, AGE, BIGN, MEANAGE)with_n |> filter(USUBJID == "01-701-1015")#> # A tibble: 1 x 5#> USUBJID TRT01A AGE BIGN MEANAGE#> <chr> <chr> <dbl> <int> <dbl>#> 1 01-701-1015 Placebo 63 86 75.2
# percent of the arm: count, then divide by the arm totaladsl |> count(TRT01A, SEX) |> group_by(TRT01A) |> mutate(pct = round(100 * n / sum(n), 1)) |> ungroup()#> # A tibble: 6 x 4#> TRT01A SEX n pct#> <chr> <chr> <int> <dbl>#> 1 Placebo F 53 61.6#> 2 Placebo M 33 38.4#> 3 Xanomeline High Dose F 35 48.6#> 4 Xanomeline High Dose M 37 51.4#> 5 Xanomeline Low Dose F 55 57.3#> 6 Xanomeline Low Dose M 41 42.7- After
group_by(TRT01A),n()andmean(AGE)insidemutate()work on each arm. Every row gets the value of its own arm. Subject 01-701-1015 getsBIGN86 and the mean age of the placebo arm, 75.2. - The percent of the arm is the same idea:
count()first, thengroup_by(TRT01A)andsum(n)per arm. On placebo, 61.6% are women. ungroup()removes the groups, so that the next step works on the whole table again (chapter 4).
6.6 Subjects with at least one TEAE
Section titled “6.6 Subjects with at least one TEAE”The first row of every adverse event table is the number of subjects with at least one treatment-emergent adverse event (TEAE), as a percent of the big N. In ADAE, TRTEMFL == "Y" marks a TEAE.
# Chapter 6: subjects with at least one TEAE, as a percent of the big Nlibrary(dplyr)
adsl <- pharmaverseadam::adsl |> filter(SAFFL == "Y")adae <- pharmaverseadam::adae |> filter(SAFFL == "Y", TRTEMFL == "Y")
bign <- adsl |> count(TRT01A, name = "BIGN")n_teae <- adae |> group_by(TRT01A) |> summarise(n = n_distinct(USUBJID))
bign |> left_join(n_teae, by = join_by(TRT01A)) |> mutate(pct = round(100 * n / BIGN, 1))#> # A tibble: 3 x 4#> TRT01A BIGN n pct#> <chr> <int> <int> <dbl>#> 1 Placebo 86 65 75.6#> 2 Xanomeline High Dose 72 68 94.4#> 3 Xanomeline Low Dose 96 84 87.5The program has three steps:
- The big N comes from ADSL, the population. Never from ADAE: a subject without an AE is not in ADAE, but is in the denominator.
- The count comes from ADAE:
n_distinct(USUBJID)counts each subject once, however many events they had. - A left join of the counts to the big N keeps every arm, also an arm where nobody had an event.
65 of the 86 subjects on placebo had at least one TEAE: 75.6%.
6.7 Subjects and records per system organ class
Section titled “6.7 Subjects and records per system organ class”An AE table counts subjects per system organ class (SOC), the top level of the MedDRA dictionary (AESOC). One subject can have several events in one SOC.
# Chapter 6: TEAEs per system organ class: subjects and recordslibrary(dplyr)
teae <- pharmaverseadam::adae |> filter(SAFFL == "Y", TRTEMFL == "Y")
teae |> filter(USUBJID == "01-701-1015") |> select(USUBJID, AESOC, AEDECOD)#> # A tibble: 3 x 3#> USUBJID AESOC AEDECOD#> <chr> <chr> <chr>#> 1 01-701-1015 GENERAL DISORDERS AND ADMINISTRATION SITE CONDITIONS APPLICATION ~#> 2 01-701-1015 GENERAL DISORDERS AND ADMINISTRATION SITE CONDITIONS APPLICATION ~#> 3 01-701-1015 GASTROINTESTINAL DISORDERS DIARRHOEA
teae |> group_by(AESOC) |> summarise(subjects = n_distinct(USUBJID), records = n()) |> arrange(desc(subjects)) |> print(n = 5)#> # A tibble: 23 x 3#> AESOC subjects records#> <chr> <int> <int>#> 1 GENERAL DISORDERS AND ADMINISTRATION SITE CONDITIONS 108 288#> 2 SKIN AND SUBCUTANEOUS TISSUE DISORDERS 98 256#> 3 NERVOUS SYSTEM DISORDERS 53 92#> 4 GASTROINTESTINAL DISORDERS 51 84#> 5 CARDIAC DISORDERS 40 86#> # i 18 more rowsSubject 01-701-1015 has 3 TEAEs. Two of them are in the same SOC, general disorders and administration site conditions. In that SOC she is 1 subject and 2 records.
Over all arms, the same SOC has 108 subjects and 288 records. The table reports 108. n() counts records, n_distinct(USUBJID) counts subjects. Write both while you develop: when the two numbers are equal for every row, ask yourself if you counted the right thing.
6.8 Set operations on keys
Section titled “6.8 Set operations on keys”A set operation compares two lists of keys: which keys are in either list, in both, or in one but not the other.
# Chapter 6: union(), intersect() and setdiff() on the subject keyslibrary(dplyr)
ae_subj <- pharmaversesdtm::ae |> distinct(USUBJID)cm_subj <- pharmaversesdtm::cm |> distinct(USUBJID)
c(ae = nrow(ae_subj), cm = nrow(cm_subj))#> ae cm#> 225 229
nrow(union(ae_subj, cm_subj)) # an AE or a medication#> [1] 244nrow(intersect(ae_subj, cm_subj)) # an AE and a medication#> [1] 210nrow(setdiff(ae_subj, cm_subj)) # an AE, but no medication#> [1] 15
setdiff(ae_subj, cm_subj) |> head(3)#> # A tibble: 3 x 1#> USUBJID#> <chr>#> 1 01-703-1175#> 2 01-703-1335#> 3 01-704-1135distinct(USUBJID) makes one row per subject: 225 subjects have an AE and 229 have a concomitant medication.
| Function | Keeps | Here |
|---|---|---|
union(a, b) |
keys in a or b |
244 |
intersect(a, b) |
keys in both | 210 |
setdiff(a, b) |
keys in a but not in b |
15 |
The functions work on data frames with the same columns, and on plain vectors. setdiff() is not symmetric: setdiff(cm_subj, ae_subj) asks another question (Exercise 6-4). On one key column, setdiff(a, b) gives the same subjects as anti_join(a, b) of chapter 5.
6.9 Find text: str_detect()
Section titled “6.9 Find text: str_detect()”To keep the rows whose text contains a word, use str_detect() from the stringr package (chapter 3). It is easy to get wrong in two ways.
# Chapter 6: find text with str_detect(), and the regular expression traplibrary(dplyr)library(stringr)
cm <- pharmaversesdtm::cm
# the terms are in capitals: lower case finds nothingcm |> filter(str_detect(CMTRT, "codeine")) |> nrow()#> [1] 0cm |> filter(str_detect(CMTRT, "CODEINE")) |> distinct(CMTRT)#> # A tibble: 3 x 1#> CMTRT#> <chr>#> 1 TYLENOL W/CODEINE NO. 3#> 2 PROMETHAZINE HCL W/CODEINE#> 3 TYLENOL W/CODEINE NO. 4cm |> filter(str_detect(CMTRT, regex("codeine", ignore_case = TRUE))) |> nrow()#> [1] 7
# a pattern is a regular expression: "." means "any character"cm |> filter(str_detect(CMTRT, ".")) |> nrow()#> [1] 7510cm |> filter(str_detect(CMTRT, fixed("."))) |> distinct(CMTRT)#> # A tibble: 3 x 1#> CMTRT#> <chr>#> 1 TYLENOL W/CODEINE NO. 3#> 2 ASPIRIN (E.C.)#> 3 TYLENOL W/CODEINE NO. 4- Case. The terms are in capitals.
"codeine"finds 0 rows,"CODEINE"finds three different medications.regex("codeine", ignore_case = TRUE)ignores the case: 7 rows. - Special characters. The pattern is a regular expression (chapter 3). In it,
.means “any character”. Sostr_detect(CMTRT, ".")is true for every one of the 7510 rows.fixed(".")searches for a real period: three medications have one.
Other characters with a special meaning are ( ) [ ] { } + * ? ^ $ | \. When you search for a plain text, wrap it in fixed().
6.10 Summary
Section titled “6.10 Summary”- Look first:
dim(),names(),glimpse(), the labels,print(n = 5). count()counts rows per value and keepsNAas a value. A percent is onemutate()away.summarise()withmean(),sd(),median(),quantile(),min(),max(). A missing value givesNAunlessna.rm = TRUE.- The big N comes from the population (ADSL), never from the events.
group_by()+mutate()puts a group statistic on every row.- Count subjects with
n_distinct(USUBJID), records withn(). union(),intersect(),setdiff()compare lists of keys.str_detect()searches with a regular expression. Usefixed()for plain text.
6.11 Exercises
Section titled “6.11 Exercises”Exercise 6-1. Print the first five subjects of the placebo arm of the safety population. Who is the fifth subject?
Exercise 6-2. Count RACE by TRT01A in the safety population. How many White subjects are in the placebo arm?
Exercise 6-3. Change program 06_6 to count the subjects with at least one serious TEAE (AESER == "Y"). What do you get for placebo, and why do you need coalesce()?
Exercise 6-4. How many subjects have a concomitant medication but no AE?
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.