Importing SAS, CSV and Excel files

Lesson 4 — R Programming

Lesson 4 of 12 Beginner to intermediate ~75 min

Learning objectives

Read .sas7bdat and .xpt files with haven and keep the metadata
Handle SAS labels, formats and haven_labelled columns
Read CSV files with explicit column types instead of guessing
Read Excel workbooks, including merged headers and multiple sheets
Diagnose encoding, locale and date-parsing problems
Write an import layer that fails loudly rather than silently

The import layer is where studies go wrong

Almost every reproducibility problem starts at import. A column read as character in one run and numeric in another; a date parsed as US format on one laptop and European on another; a decimal comma turning 3,5 into NA. The fix is always the same: specify types explicitly, never rely on guessing.

Reading SAS files

haven reads all three SAS formats.

library(haven)

dm  <- read_sas("data/raw/dm.sas7bdat")     # native SAS dataset
ae  <- read_xpt("data/raw/ae.xpt")          # SAS transport (V5/V8)
cat <- read_sas("dm.sas7bdat", catalog_file = "formats.sas7bcat")

read_sas() returns a tibble in which SAS metadata survives as attributes:

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

Pull the labels out into a data dictionary:

library(dplyr)
library(purrr)

dictionary <- tibble(
  variable = names(dm),
  label    = map_chr(dm, ~ attr(.x, "label")      %||% NA_character_),
  format   = map_chr(dm, ~ attr(.x, "format.sas") %||% NA_character_),
  type     = map_chr(dm, ~ class(.x)[1])
)

dictionary
#> # A tibble: 24 x 4
#>   variable label                      format   type
#>   <chr>    <chr>                      <chr>    <chr>
#> 1 STUDYID  Study Identifier           NA       character
#> 2 USUBJID  Unique Subject Identifier  NA       character
#> 3 AGE      Age                        BEST     numeric
#> 4 SEX      Sex                        NA       character

`haven_labelled` columns

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

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

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

These are useful but not all functions handle them. Three ways forward:

library(haven)

as_factor(dm$RACEN)            # -> factor with the value labels
zap_labels(dm$RACEN)           # -> plain numeric, labels discarded
zap_labels(zap_formats(dm))    # -> whole dataset stripped

# Convert every labelled column at once
dm_f <- dm |> mutate(across(where(is.labelled), as_factor))

Decide once, document it

Whether you keep haven_labelled, convert to factor, or strip to plain types is an architectural decision for the whole study. Mixing approaches across programs produces datasets that compare unequal for reasons nobody can find. Write it into the study’s programming conventions.

Character encoding

SAS datasets from European or Asian sites frequently arrive in a non-UTF-8 encoding, producing mojibake in investigator names or verbatim AE terms.

ae <- read_sas("ae.sas7bdat", encoding = "latin1")

# Diagnose
Encoding(ae$AETERM[1:5])
validUTF8(ae$AETERM)  |> all()
#> [1] FALSE           <- something is wrong

# Repair after the fact
ae$AETERM <- iconv(ae$AETERM, from = "latin1", to = "UTF-8")

Large files

read_sas() supports column and row selection, which avoids loading a 2 GB lab dataset in full:

lb <- read_sas(
  "lb.sas7bdat",
  col_select = c(USUBJID, LBTESTCD, LBSTRESN, LBDTC, VISITNUM),
  n_max      = Inf
)

# Peek at structure without reading everything
read_sas("lb.sas7bdat", n_max = 0) |> names()

Reading CSV

Use readr, and use the col_types argument. Always.

library(readr)

# Guessing — fine for exploration, not for production
dm <- read_csv("data/raw/dm.csv")
#> Rows: 306 Columns: 24
#> -- Column specification --------------------------
#> chr (18): STUDYID, USUBJID, SEX, RACE, ARM, ...
#> dbl  (5): AGE, HEIGHT, WEIGHT, BMI, SITEID
#> date (1): RFSTDTC

Copy that specification and make it explicit:

dm <- read_csv(
  "data/raw/dm.csv",
  col_types = cols(
    STUDYID  = col_character(),
    USUBJID  = col_character(),
    SUBJID   = col_character(),   # keeps leading zeros
    SITEID   = col_character(),   # "007" must not become 7
    AGE      = col_integer(),
    SEX      = col_factor(levels = c("M", "F")),
    RFSTDTC  = col_date(format = "%Y-%m-%d"),
    .default = col_character()
  )
)

.default = col_character() is a deliberate choice: any column you did not name arrives as text, unchanged, and you convert it consciously. That is much safer than letting the guesser decide.

Leading zeros

SITEID values like "007" read as numeric become 7, and the join to the site reference table then finds nothing. Subject IDs, site IDs, visit codes and country codes should almost always be col_character().

The guessing trap

readr guesses types from the first 1000 rows by default:

# Row 1500 contains "N/A" in a numeric column
x <- read_csv("labs.csv")
#> Warning: One or more parsing issues...

problems(x)
#> # A tibble: 3 x 5
#>     row   col expected actual file
#>   <int> <int> <chr>    <chr>  <chr>
#> 1  1500     4 a double N/A    labs.csv

Always check problems() after a guessed read. Options:

read_csv("labs.csv", guess_max = Inf)              # slow but thorough
read_csv("labs.csv", na = c("", "NA", "N/A", "."))  # declare the sentinels
read_csv("labs.csv", col_types = cols(RESULT = col_character()))  # handle later

Delimiters and locales

read_csv2("data.csv")     # ; separator, decimal comma (much of Europe)
read_tsv("data.tsv")
read_delim("data.txt", delim = "|")

read_delim(
  "data_de.csv",
  delim  = ";",
  locale = locale(decimal_mark = ",", grouping_mark = ".",
                  date_format = "%d.%m.%Y", encoding = "latin1")
)

A file where 3,5 means three-and-a-half and 1.234 means one thousand two hundred thirty-four will parse to complete nonsense without the right locale, and it will do it quietly.

Writing

write_csv(dm, "data/derived/dm.csv")
write_csv(dm, "dm.csv", na = "")          # blank instead of "NA"
write_excel_csv(dm, "dm.csv")             # BOM so Excel gets UTF-8 right

# For R-to-R handoff, prefer a binary format — types are preserved exactly
saveRDS(dm, "data/derived/dm.rds")
dm <- readRDS("data/derived/dm.rds")

arrow::write_parquet(dm, "dm.parquet")    # fast, typed, cross-language

Reading Excel

library(readxl)

excel_sheets("data/raw/lab_ranges.xlsx")
#> [1] "Chemistry" "Haematology" "Notes"

chem <- read_excel("data/raw/lab_ranges.xlsx", sheet = "Chemistry")
chem <- read_excel("lab_ranges.xlsx", sheet = 1, range = "A3:F120")
chem <- read_excel("lab_ranges.xlsx", skip = 2, n_max = 100)

Excel-specific problems and their fixes:

# 1. Types vary within a column -> force character, convert later
read_excel("f.xlsx", col_types = "text")

# 2. Explicit types per column
read_excel("f.xlsx",
           col_types = c("text", "text", "numeric", "date", "skip"))

# 3. Excel dates arrive as serial numbers when the cell is formatted oddly
as.Date(45000, origin = "1899-12-30")
#> [1] "2023-03-15"

# 4. Merged header cells produce NA column names
janitor::clean_names(chem)

Read every sheet into one tibble:

library(purrr)

path <- "data/raw/lab_ranges.xlsx"

all_sheets <- excel_sheets(path) |>
  set_names() |>
  map(~ read_excel(path, sheet = .x, col_types = "text")) |>
  list_rbind(names_to = "sheet")

Excel is not a data format

It is a presentation format with data in it. Merged cells, colour-coded meaning, footnotes in the last row, and numbers stored as text are normal. Read everything as text, inspect, then convert. Never trust an Excel file’s types.

A defensive import function

Put all of this together into one function per dataset, in R/import.R:

library(readr)
library(dplyr)
library(cli)

import_dm <- function(path) {
  if (!file.exists(path)) {
    cli_abort("Demographics file not found: {.path {path}}")
  }

  dm <- read_csv(
    path,
    col_types = cols(
      STUDYID  = col_character(),
      USUBJID  = col_character(),
      SITEID   = col_character(),
      AGE      = col_integer(),
      SEX      = col_character(),
      ARM      = col_character(),
      RFSTDTC  = col_character(),   # keep ISO text, parse deliberately
      .default = col_character()
    )
  )

  # Contract checks — fail here, not three programs later
  required <- c("STUDYID", "USUBJID", "SITEID", "AGE", "SEX", "ARM")
  missing  <- setdiff(required, names(dm))
  if (length(missing) > 0) {
    cli_abort("Missing required column{?s}: {.var {missing}}")
  }

  if (anyDuplicated(dm$USUBJID) > 0) {
    dups <- dm$USUBJID[duplicated(dm$USUBJID)]
    cli_abort("Duplicate USUBJID: {.val {unique(dups)}}")
  }

  bad_sex <- setdiff(unique(na.omit(dm$SEX)), c("M", "F", "U"))
  if (length(bad_sex) > 0) {
    cli_warn("Unexpected SEX value{?s}: {.val {bad_sex}}")
  }

  cli_alert_success("Imported {nrow(dm)} subject{?s} from {.path {basename(path)}}")
  dm
}

The value is not the reading — it is the six lines of validation after it. An import that fails on the day the data changes is worth a great deal more than one that quietly produces a wrong table.

Choosing a reader

Source	Function	Notes
`.sas7bdat`	`haven::read_sas()`	Keeps labels and formats
`.xpt`	`haven::read_xpt()`	V5 and V8 transport
`.sav` (SPSS)	`haven::read_sav()`
`.dta` (Stata)	`haven::read_dta()`
`.csv`	`readr::read_csv()`	Specify `col_types`
`.csv`, huge	`data.table::fread()`	Very fast
`.xlsx` / `.xls`	`readxl::read_excel()`	Read as text first
`.parquet`	`arrow::read_parquet()`	Typed, compressed, fast
`.rds`	`readRDS()`	R-only, exact round trip
Database	`DBI` + `dbplyr`	Push work to the server
JSON / API	`jsonlite`, `httr2`	Then `tidyr::unnest()`

Common mistakes

Mistake	Consequence	Fix
Relying on type guessing	Types differ between runs	Explicit `col_types`
Reading IDs as numeric	Leading zeros lost, joins fail	`col_character()`
Ignoring `problems()`	Silent `NA`s	Check it after every guessed read
Wrong locale	Decimal comma → `NA`	`locale(decimal_mark = ",")`
Trusting Excel types	Mixed columns, serial dates	`col_types = "text"`
No validation after import	Errors surface far downstream	Contract checks in the import function
`read.csv()` (base)	Slower, older defaults	`readr::read_csv()`

Exercise 4.1 — Write a strict CSV spec

A vitals CSV has columns STUDYID, USUBJID, SITEID, VISITNUM, VSTESTCD, VSORRES, VSORRESU, VSDTC. VSORRES occasionally contains "NOT DONE". SITEID values look like "007". VSDTC is ISO 8601 but sometimes only "2026-03". Write the read_csv() call.

Show solution

vs <- read_csv(
  "data/raw/vs.csv",
  col_types = cols(
    STUDYID  = col_character(),
    USUBJID  = col_character(),
    SITEID   = col_character(),   # preserve "007"
    VISITNUM = col_double(),
    VSTESTCD = col_character(),
    VSORRES  = col_character(),   # "NOT DONE" means this cannot be numeric
    VSORRESU = col_character(),
    VSDTC    = col_character()    # partial dates cannot be col_date()
  ),
  na = c("", "NA")                # do NOT treat "NOT DONE" as NA — it is information
)

Two judgement calls worth stating explicitly:

VSORRES stays character. Creating a numeric VSSTRESN is a derivation, done in a later program where you can record how "NOT DONE" was handled.
VSDTC stays character because col_date() cannot represent "2026-03". Partial dates are handled in Dates, strings and factors.

Exercise 4.2 — Extract a data dictionary from a SAS file

Write sas_dictionary(path) returning a tibble with variable, label, format, type, n_missing and n_distinct for every column of a .sas7bdat file.

Show solution

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

sas_dictionary <- function(path) {
  dat <- read_sas(path)

  tibble::tibble(
    variable   = names(dat),
    label      = map_chr(dat, ~ attr(.x, "label")      %||% NA_character_),
    format     = map_chr(dat, ~ attr(.x, "format.sas") %||% NA_character_),
    type       = map_chr(dat, ~ class(.x)[1]),
    n_missing  = map_int(dat, ~ sum(is.na(.x))),
    n_distinct = map_int(dat, ~ dplyr::n_distinct(.x, na.rm = TRUE))
  )
}

sas_dictionary("data/raw/dm.sas7bdat")
#> # A tibble: 24 x 6
#>   variable label                     format type      n_missing n_distinct
#>   <chr>    <chr>                     <chr>  <chr>         <int>      <int>
#> 1 STUDYID  Study Identifier          NA     character         0          1
#> 2 USUBJID  Unique Subject Identifier NA     character         0        306
#> 3 AGE      Age                       BEST   numeric           2         46

%||% is from rlang (and base R since 4.4): a %||% b returns b when a is NULL. It is the idiomatic way to supply a default for a missing attribute.

To read only metadata on a very large file, read_sas(path, n_max = 0) gives you the columns and attributes without the data — though n_missing and n_distinct then require the full read.

Exercise 4.3 — Diagnose a broken import

A colleague’s script produces a lab dataset where LBSTRESN is character on their machine and numeric on yours, from the same file. Nothing in the code differs. Explain what is happening and give two fixes.

Show solution

Cause. The read is relying on type guessing. readr guesses from the first guess_max rows (1000 by default). If the file has been re-extracted and now contains a non-numeric sentinel — "N/A", "<LLOQ", "." — within the first 1000 rows on one machine’s copy but not the other’s, the guessed type differs. Row ordering differences between extracts are enough to cause this.

Fix 1 — specify the type.

lb <- read_csv("lb.csv", col_types = cols(LBSTRESN = col_double()))

Anything unparseable becomes NA and appears in problems(lb), so it is visible rather than silent.

Fix 2 — read as character and convert deliberately.

lb <- read_csv("lb.csv", col_types = cols(.default = col_character()))

lb <- lb |>
  mutate(
    lbstresn_num  = suppressWarnings(as.numeric(LBSTRESN)),
    lbstresn_flag = case_when(
      is.na(LBSTRESN)                          ~ "MISSING",
      !is.na(lbstresn_num)                     ~ "NUMERIC",
      grepl("^<", LBSTRESN)                    ~ "BELOW_LLOQ",
      grepl("^>", LBSTRESN)                    ~ "ABOVE_ULOQ",
      .default                                 ~ "NON_NUMERIC"
    )
  )

Fix 2 is what you want in clinical work: the reason a value is not numeric is itself analysable information, and discarding it loses signal about assay limits.

A third, non-technical fix: add stopifnot(is.numeric(lb$LBSTRESN)) to the import function so the failure is loud and immediate.

Recap

haven::read_sas() / read_xpt() preserve labels and formats as attributes
haven_labelled columns need a study-wide decision: keep, as_factor(), or zap_labels()
Always give readr explicit col_types; check problems() after any guessed read
IDs and site codes are character, never numeric
Read Excel as text, then convert; expect merged headers and serial dates
Validate immediately after import — the import function is the right place to fail

Next: Data manipulation with dplyr.

The import layer is where studies go wrong

Reading SAS files

haven_labelled columns

Character encoding

Large files

Reading CSV

The guessing trap

Delimiters and locales

Writing

Reading Excel

A defensive import function

Choosing a reader

Common mistakes

`haven_labelled` columns