Reading SAS7BDAT and XPT files

Lesson 2 — Clinical Programming with R

Lesson 2 of 12 Intermediate ~70 min

Learning objectives

Read SAS datasets and transport files while preserving metadata
Handle haven_labelled columns with a consistent study-wide policy
Diagnose and fix encoding problems
Read large datasets efficiently
Write XPT files that meet transport constraints
Build a reliable, validating import layer for a study

The three SAS formats

Format	Extension	What it is	Read with
SAS dataset	`.sas7bdat`	Native, platform-specific	`haven::read_sas()`
Transport V5	`.xpt`	The submission format	`haven::read_xpt()`
Transport V8	`.xpt`	Longer names, still transport	`haven::read_xpt()`
Format catalog	`.sas7bcat`	Value label definitions	`read_sas(catalog_file=)`

The V5 transport format is what regulators require for submission datasets, and it carries constraints that shape a great deal of what follows: variable names ≤ 8 characters, labels ≤ 40, character values ≤ 200 bytes, no long variable names, ASCII only. See xportr.

Reading

library(haven)

dm  <- read_sas("data/raw/sdtm/dm.sas7bdat")
ae  <- read_xpt("data/raw/sdtm/ae.xpt")
lb  <- read_sas("lb.sas7bdat", catalog_file = "formats.sas7bcat")

read_sas() returns a tibble. Metadata survives as attributes:

str(dm$AGE)
#>  num [1:306] 45 52 38 61 29 ...
#>  - attr(*, "label")= chr "Age"
#>  - attr(*, "format.sas")= chr "BEST"

attributes(dm)$label   # dataset label, if present

Extracting the metadata

library(dplyr); library(purrr)

sas_metadata <- function(dat) {
  tibble::tibble(
    variable   = names(dat),
    label      = map_chr(dat, ~ attr(.x, "label")      %||% NA_character_),
    format     = map_chr(dat, ~ attr(.x, "format.sas") %||% NA_character_),
    class      = map_chr(dat, ~ paste(class(.x), collapse = ", ")),
    type       = map_chr(dat, typeof),
    n_missing  = map_int(dat, ~ sum(is.na(.x))),
    n_distinct = map_int(dat, ~ dplyr::n_distinct(.x, na.rm = TRUE)),
    max_width  = map_int(dat, ~ if (is.character(.x))
                                  max(nchar(.x, type = "bytes"), na.rm = TRUE)
                                else NA_integer_)
  )
}

sas_metadata(dm)
#> # A tibble: 24 x 8
#>   variable label                     format class     type  n_missing n_distinct max_width
#>   <chr>    <chr>                     <chr>  <chr>     <chr>     <int>      <int>     <int>
#> 1 STUDYID  Study Identifier          NA     character chr           0          1         9
#> 2 USUBJID  Unique Subject Identifier NA     character chr           0        306        14
#> 3 AGE      Age                       BEST   numeric   dbl           2         46        NA

max_width in bytes is what matters for XPT — a non-ASCII character can be several bytes, so nchar(type = "chars") will understate it.

`haven_labelled` columns

When a SAS variable has a user-defined format, haven returns a haven_labelled vector: the underlying value plus a lookup of value labels.

class(dm$RACEN)
#> [1] "haven_labelled" "double"

attr(dm$RACEN, "labels")
#> WHITE BLACK ASIAN OTHER
#>     1     2     3     4

as.numeric(dm$RACEN)   # the codes
as_factor(dm$RACEN)    # a factor with the labels

Three policies, and the study must pick one:

# A. Keep them — maximum fidelity, but many functions do not handle them
dm

# B. Convert to factor — good for tables and plots
dm_f <- dm |> mutate(across(where(is.labelled), as_factor))

# C. Strip to plain types — simplest, loses the value labels
dm_p <- dm |> zap_labels() |> zap_formats()

Choose once, write it down

Mixing policies across programs produces datasets that compare unequal for reasons nobody can locate — a diffdf comparison will report differences in class on variables whose values are identical. Put the decision in the study’s programming conventions document.

A common workable policy: strip formats but keep labels, then reapply everything with xportr at the end.

dm <- read_sas(path) |> zap_formats()   # keep 'label', drop 'format.sas'

Useful haven helpers:

zap_labels(x)      # remove value labels (the labelled class)
zap_label(x)       # remove the variable label
zap_formats(x)     # remove format.sas
zap_widths(x)      # remove display widths
zap_empty(x)       # "" -> NA for character
as_factor(x, levels = c("default", "labels", "values", "both"))
labelled(x, labels = c(Yes = 1, No = 0), label = "Response")

Encoding

Non-UTF-8 source data is common from European and Asian sites, and it shows up in investigator names, verbatim AE terms and comment fields.

ae <- read_sas("ae.sas7bdat")

# Diagnose
all(validUTF8(ae$AETERM))
#> [1] FALSE

bad <- which(!validUTF8(ae$AETERM))
ae$AETERM[head(bad)]
#> [1] "Kopfschmerzen mit \xdcbelkeit"

Fix at read time if you know the encoding:

ae <- read_sas("ae.sas7bdat", encoding = "latin1")
ae <- read_sas("ae.sas7bdat", encoding = "WINDOWS-1252")
ae <- read_sas("ae.sas7bdat", encoding = "SHIFT-JIS")

Or repair afterwards:

ae$AETERM <- iconv(ae$AETERM, from = "latin1", to = "UTF-8")

# When the encoding is unknown
guess <- stringi::stri_enc_detect(ae$AETERM[bad[1]])
guess[[1]]$Encoding[1]
#> [1] "ISO-8859-1"

XPT V5 is ASCII

Transport V5 does not support non-ASCII characters. Any accented character in a submission dataset must be transliterated or the file is non-conformant:

ae$AETERM <- iconv(ae$AETERM, "UTF-8", "ASCII//TRANSLIT")
#> "Kopfschmerzen mit Ubelkeit"

Do this deliberately and document it — silently mangling a verbatim term is a data integrity problem, and //TRANSLIT behaviour differs between platforms. Check what actually changed:

changed <- which(original != transliterated)

Large files

read_sas() supports column and row selection, applied while reading:

# Structure only — no data
read_sas("lb.sas7bdat", n_max = 0) |> names()

# Only the columns you need
lb <- read_sas(
  "lb.sas7bdat",
  col_select = c(USUBJID, LBTESTCD, LBSTRESN, LBSTRESU, LBDTC, VISITNUM, LBBLFL)
)

# A sample for development
lb_dev <- read_sas("lb.sas7bdat", n_max = 10000)

Reading only the needed columns on a 60-variable, 2-million-row lab dataset can be the difference between 90 seconds and 8 seconds.

Convert once, read many

# In a one-off preparation script
library(arrow)

for (f in list.files("data/raw/sdtm", pattern = "\\.sas7bdat$", full.names = TRUE)) {
  d <- haven::read_sas(f)
  write_parquet(d, file.path("data/cache",
                             paste0(tools::file_path_sans_ext(basename(f)), ".parquet")))
}

Parquet reads an order of magnitude faster and preserves types exactly. Labels are not preserved by Parquet, so keep the metadata separately:

meta <- map(sdtm_files, ~ sas_metadata(read_sas(.x, n_max = 0)))
saveRDS(meta, "data/cache/metadata.rds")

Alternatively, saveRDS() preserves attributes perfectly and is fast enough for most study-sized data.

Very large data

# Query without loading
ds <- arrow::open_dataset("data/cache/lb.parquet")
alt <- ds |>
  dplyr::filter(LBTESTCD == "ALT") |>
  dplyr::select(USUBJID, LBSTRESN, LBDTC) |>
  dplyr::collect()

# Or push it to a database
con <- DBI::dbConnect(duckdb::duckdb())
duckdb::duckdb_register_arrow(con, "lb", ds)
DBI::dbGetQuery(con, "SELECT USUBJID, AVG(LBSTRESN) FROM lb
                      WHERE LBTESTCD = 'ALT' GROUP BY USUBJID")

Writing XPT

library(haven)

write_xpt(adsl, "data/submission/adsl.xpt", version = 5, name = "ADSL")
write_xpt(adsl, "adsl.xpt", version = 8)     # longer names allowed

V5 constraints, all of which write_xpt() will silently violate or error on:

Constraint	V5 limit
Dataset name	8 characters
Variable name	8 characters, uppercase, start with a letter
Variable label	40 characters
Dataset label	40 characters
Character value	200 bytes
Encoding	ASCII
Numeric	8-byte float only

Do not try to satisfy these by hand. xportr checks and applies all of them from a specification — see xportr.

library(xportr)

adsl |>
  xportr_type(spec)   |>
  xportr_length(spec) |>
  xportr_label(spec)  |>
  xportr_order(spec)  |>
  xportr_format(spec) |>
  xportr_df_label(spec) |>
  xportr_write("data/submission/adsl.xpt", strict_checks = TRUE)

A study import layer

Put one function per domain in R/import.R. The reading is trivial; the value is in the contract checks.

library(haven); library(dplyr); library(cli)

#' Read an SDTM domain with validation
#'
#' @param domain Two-letter domain code, e.g. "DM".
#' @param path Directory containing the SDTM files.
#' @param required Character vector of variables that must be present.
#' @param key Variables that should uniquely identify a record.
#' @return A tibble with formats zapped and labels retained.
read_sdtm <- function(domain,
                      path     = study_paths()$sdtm,
                      required = NULL,
                      key      = NULL) {

  file <- file.path(path, paste0(tolower(domain), ".sas7bdat"))
  if (!file.exists(file)) {
    file <- file.path(path, paste0(tolower(domain), ".xpt"))
  }
  if (!file.exists(file)) {
    cli_abort(c("SDTM domain {.val {domain}} not found.",
                "i" = "Looked in {.path {path}}."))
  }

  dat <- if (tools::file_ext(file) == "xpt") read_xpt(file) else read_sas(file)
  dat <- zap_formats(dat)            # study policy: keep labels, drop formats

  # --- Contract checks ------------------------------------------------------

  if (!is.null(required)) {
    missing <- setdiff(required, names(dat))
    if (length(missing) > 0) {
      cli_abort(c(
        "{domain} is missing required variable{?s}.",
        "x" = "Missing: {.var {missing}}",
        "i" = "Present: {.var {head(names(dat), 10)}}"
      ))
    }
  }

  if (!is.null(key)) {
    dupes <- dat |> count(across(all_of(key))) |> filter(n > 1)
    if (nrow(dupes) > 0) {
      cli_abort(c(
        "{domain} has duplicate records on the key {.var {key}}.",
        "x" = "{nrow(dupes)} duplicated key combination{?s}.",
        "i" = "First: {.val {paste(dupes[1, key], collapse = ' / ')}}"
      ))
    }
  }

  # --- Non-fatal warnings ---------------------------------------------------

  bad_utf8 <- vapply(dat, \(x) is.character(x) && !all(validUTF8(x[!is.na(x)])),
                     logical(1))
  if (any(bad_utf8)) {
    cli_warn(c("{domain} has invalid UTF-8 in {.var {names(dat)[bad_utf8]}}.",
               "i" = "Specify {.arg encoding} or repair with {.fn iconv}."))
  }

  no_label <- vapply(dat, \(x) is.null(attr(x, "label")), logical(1))
  if (any(no_label)) {
    cli_warn("{domain}: {sum(no_label)} variable{?s} without a label: {.var {names(dat)[no_label]}}")
  }

  cli_alert_success(
    "{domain}: {nrow(dat)} record{?s}, {ncol(dat)} variable{?s} from {.path {basename(file)}}"
  )
  dat
}

Used:

dm <- read_sdtm("DM",
                required = c("STUDYID", "USUBJID", "AGE", "SEX", "ARM", "RFSTDTC"),
                key      = "USUBJID")

ae <- read_sdtm("AE",
                required = c("STUDYID", "USUBJID", "AETERM", "AEDECOD", "AESTDTC"),
                key      = c("USUBJID", "AESEQ"))

lb <- read_sdtm("LB",
                required = c("USUBJID", "LBTESTCD", "LBSTRESN", "LBDTC"),
                key      = c("USUBJID", "LBSEQ"))

The key check on USUBJID/--SEQ catches a large fraction of the data issues that would otherwise surface as a mysterious row-count change in an ADaM dataset three programs later.

Coming from SAS

SAS	R
`libname sdtm "path";`	`path <- "path"` (no library concept)
`data dm; set sdtm.dm; run;`	`dm <- read_sas("path/dm.sas7bdat")`
`proc contents data=dm;`	`sas_metadata(dm)` or `str(dm)`
`proc cimport infile="dm.xpt";`	`read_xpt("dm.xpt")`
`proc copy in=sdtm out=work;`	`map(files, read_sas)`
`label age = "Age";`	`attr(dm$AGE, "label") <- "Age"`
`format racen racef.;`	`haven::labelled(racen, labels = c(...))`
`proc cport / xport=`	`write_xpt(version = 5)`

The conceptual difference: SAS libraries are persistent named locations, resolved at run time. R has objects in memory and file paths. Nothing is “assigned” to a library — you read into a variable and that variable is the dataset.

Common mistakes

Mistake	Consequence	Fix
Inconsistent `haven_labelled` policy	Datasets compare unequal on class	One study-wide policy
Assuming labels survive `mutate()`	Labels silently lost	Reapply with `xportr`
Ignoring encoding	Mojibake in verbatim terms; XPT non-conformant	`encoding=` or `iconv()`
Reading all columns of a huge domain	Slow, memory heavy	`col_select`
`nchar()` in characters not bytes	XPT length violations	`nchar(type = "bytes")`
No key check after import	Row multiplication later	Check `USUBJID`/`--SEQ` uniqueness
Hand-writing XPT constraints	Non-conformant files	Use `xportr`

Exercise 2.1 — Domain inventory

Write sdtm_inventory(path) that scans a directory of SDTM files and returns one row per domain with: domain, file size, record count, variable count, whether USUBJID is present, the number of distinct subjects, and whether --SEQ uniquely identifies records.

Show solution

library(haven); library(dplyr); library(purrr); library(fs)

sdtm_inventory <- function(path) {
  files <- dir_ls(path, regexp = "\\.(sas7bdat|xpt)$")

  map(files, function(f) {
    domain <- toupper(path_ext_remove(path_file(f)))

    dat <- tryCatch(
      if (path_ext(f) == "xpt") read_xpt(f) else read_sas(f),
      error = function(e) NULL
    )

    if (is.null(dat)) {
      return(tibble::tibble(
        domain = domain, file = path_file(f),
        size_mb = round(file_size(f) / 1024^2, 2),
        n_records = NA_integer_, n_variables = NA_integer_,
        has_usubjid = NA, n_subjects = NA_integer_,
        seq_var = NA_character_, seq_unique = NA,
        status = "READ FAILED"
      ))
    }

    seq_var <- grep(paste0("^", domain, "SEQ$"), names(dat), value = TRUE)

    seq_unique <- if (length(seq_var) == 1 && "USUBJID" %in% names(dat)) {
      !anyDuplicated(dat[, c("USUBJID", seq_var)])
    } else NA

    tibble::tibble(
      domain      = domain,
      file        = path_file(f),
      size_mb     = round(as.numeric(file_size(f)) / 1024^2, 2),
      n_records   = nrow(dat),
      n_variables = ncol(dat),
      has_usubjid = "USUBJID" %in% names(dat),
      n_subjects  = if ("USUBJID" %in% names(dat)) n_distinct(dat$USUBJID)
                    else NA_integer_,
      seq_var     = if (length(seq_var) == 1) seq_var else NA_character_,
      seq_unique  = seq_unique,
      status      = "OK"
    )
  }) |>
    list_rbind() |>
    arrange(domain)
}

sdtm_inventory("data/raw/sdtm")
#> # A tibble: 12 x 10
#>   domain file         size_mb n_records n_variables has_usubjid n_subjects seq_var seq_unique status
#>   <chr>  <chr>          <dbl>     <int>       <int> <lgl>            <int> <chr>   <lgl>      <chr>
#> 1 AE     ae.sas7bdat     2.41      1847          31 TRUE               218 AESEQ   TRUE       OK
#> 2 DM     dm.sas7bdat     0.18       306          24 TRUE               306 NA      NA         OK
#> 3 LB     lb.sas7bdat    48.2      124903          38 TRUE              298 LBSEQ   TRUE       OK

DM correctly has no --SEQ — it is one record per subject, so USUBJID alone is the key. seq_unique = FALSE on any other domain is a data issue worth raising with data management immediately.

For a large directory, reading only the structure is much faster:

dat <- read_sas(f, n_max = 0)     # variables and attributes only

but then n_records and n_subjects are unavailable. A two-pass approach — structure for everything, full read only where the counts matter — is a reasonable compromise.

Exercise 2.2 — Round-trip a dataset through XPT

Read a .sas7bdat, write it as XPT V5, read it back, and produce a report of everything that changed. Explain each difference.

Show solution

library(haven); library(dplyr); library(purrr)

compare_roundtrip <- function(path, tmp = tempfile(fileext = ".xpt")) {

  original <- read_sas(path)
  write_xpt(original, tmp, version = 5)
  returned <- read_xpt(tmp)

  meta <- function(d) {
    tibble::tibble(
      variable  = names(d),
      label     = map_chr(d, ~ attr(.x, "label") %||% NA_character_),
      class     = map_chr(d, ~ class(.x)[1]),
      max_bytes = map_int(d, ~ if (is.character(.x))
                            max(c(0L, nchar(.x, type = "bytes")), na.rm = TRUE)
                          else NA_integer_)
    )
  }

  full_join(meta(original), meta(returned),
            by = "variable", suffix = c("_before", "_after")) |>
    mutate(
      name_truncated  = nchar(variable) > 8,
      label_changed   = !identical(label_before, label_after),
      label_truncated = !is.na(label_before) & nchar(label_before) > 40,
      class_changed   = class_before != class_after,
      value_truncated = !is.na(max_bytes_before) & max_bytes_before > 200
    ) |>
    filter(name_truncated | label_changed | class_changed | value_truncated)
}

compare_roundtrip("data/raw/sdtm/dm.sas7bdat")

The differences you will typically see, and why:

Difference	Cause
Label truncated to 40 characters	V5 label limit
Variable name truncated to 8	V5 name limit — collisions are possible
`haven_labelled` → `numeric`	V5 has no concept of value labels
`Date` → `numeric`	V5 stores dates as numbers plus a format
Character values cut at 200 bytes	V5 character limit
Accented characters mangled	V5 is ASCII
Trailing spaces removed or added	SAS pads character values to their declared length

The dangerous one is name truncation causing collisions. TREATMENTSTART and TREATMENTSTOP both truncate to TREATMEN, and one silently overwrites the other. Check for it explicitly:

check_v5_names <- function(d) {
  short <- toupper(substr(names(d), 1, 8))
  dup   <- short[duplicated(short)]
  if (length(dup) > 0) {
    cli::cli_abort(c(
      "Variable names collide when truncated to 8 characters for XPT V5.",
      "x" = "Colliding stem{?s}: {.val {unique(dup)}}",
      "i" = "Affected: {.var {names(d)[short %in% dup]}}"
    ))
  }
  invisible(TRUE)
}

And on values:

check_v5_widths <- function(d) {
  too_long <- map_lgl(d, ~ is.character(.x) &&
                        any(nchar(.x, type = "bytes") > 200, na.rm = TRUE))
  if (any(too_long)) {
    cli::cli_abort("Character values exceed 200 bytes in {.var {names(d)[too_long]}}")
  }
  invisible(TRUE)
}

In practice you would not hand-roll these — xportr_write(strict_checks = TRUE) performs all of them and more. But running the round-trip comparison once on a real dataset is the fastest way to understand why xportr exists, and what it is protecting you from.

Recap

read_sas() / read_xpt() keep labels and formats as attributes
Pick one haven_labelled policy for the whole study and document it
Diagnose encoding with validUTF8(); XPT V5 is ASCII only
Use col_select and n_max on large domains; cache to Parquet or RDS
Measure character widths in bytes, not characters
Validate keys (USUBJID, --SEQ) at import, not three programs later
Use xportr for transport constraints — 8-character name collisions are silent

Next: SDTM programming in R.