Stack datasets

bind_rows()

SAS: SET with several datasets; INDSNAME=; PROC SQL UNION / OUTER UNION CORR

dplyr::bind_rows() stacks tables vertically. Columns are matched by name. Columns present in only one table are filled with NA in the other rows.

Quick reference

library(dplyr)

bind_rows(ae_site_a, ae_site_b)

bind_rows(
  list(SITEA = ae_site_a, SITEB = ae_site_b),
  .id = "SITE"
)
R SAS
bind_rows(a, b) set a b; (stack; align names with rename= if needed)
bind_rows(..., .id = "SRC") set a b indsname=src;
bind_rows() by column name PROC SQL OUTER UNION CORR

Traps: bind_rows() matches columns by name, not position. Character versus numeric on the same name errors in dplyr; fix types before stacking. SAS SET takes character length from the first dataset and can truncate later values; bind_rows() does not truncate.

Worked examples below.

Stack two tables

library(dplyr)

Attaching package: 'dplyr'
The following objects are masked from 'package:stats':

    filter, lag
The following objects are masked from 'package:base':

    intersect, setdiff, setequal, union
ae_site_a <- data.frame(
  USUBJID = c("STD-001-0001", "STD-001-0002"),
  AETERM = c("NAUSEA", "HEADACHE"),
  AESEV = c("MILD", "MODERATE")
)

ae_site_b <- data.frame(
  USUBJID = c("STD-001-0003"),
  AETERM = c("FATIGUE"),
  AESEV = c("MILD"),
  AEREL = c("RELATED")
)

bind_rows(ae_site_a, ae_site_b)
       USUBJID   AETERM    AESEV   AEREL
1 STD-001-0001   NAUSEA     MILD    <NA>
2 STD-001-0002 HEADACHE MODERATE    <NA>
3 STD-001-0003  FATIGUE     MILD RELATED

AEREL is NA for site A rows. SAS set ae_site_a ae_site_b; is the usual analogue when variable names already match.

Mark the source table

bind_rows(
  list(SITEA = ae_site_a, SITEB = ae_site_b),
  .id = "SITE"
)
   SITE      USUBJID   AETERM    AESEV   AEREL
1 SITEA STD-001-0001   NAUSEA     MILD    <NA>
2 SITEA STD-001-0002 HEADACHE MODERATE    <NA>
3 SITEB STD-001-0003  FATIGUE     MILD RELATED

.id adds a column with the list names. SAS:

data all;
  set ae_site_a ae_site_b indsname=src;
  site = src;
run;

INDSNAME= is the dataset name that contributed the row (often WORK.AE_SITE_A style). Rename or scan that value if you need a short code like .id.

ImportantMatch by name, not position

bind_rows() does not line up column 1 with column 1. Rename first when the same concept has different names. Character versus numeric on the same name is an error in dplyr. Fix the type before stacking.

SAS truncates; bind_rows() does not

The first SAS dataset in SET sets character lengths. A later dataset with a longer value is truncated to that length. bind_rows() keeps the full string:

ae_short <- data.frame(
  USUBJID = "STD-001-0001",
  AETERM = "NAUSEA"
)

ae_long <- data.frame(
  USUBJID = "STD-001-0003",
  AETERM = "MYOCARDIAL INFARCTION"
)

bind_rows(ae_short, ae_long)
       USUBJID                AETERM
1 STD-001-0001                NAUSEA
2 STD-001-0003 MYOCARDIAL INFARCTION

AETERM is the full term on both rows. In SAS, set ae_short ae_long; would cut MYOCARDIAL INFARCTION to the length of NAUSEA unless you set length first.

For side-by-side combines on keys, use joins (see Joins), not bind_rows().