← SASnRRetain - Concatenate character results into a single value in a group

Introduction

Some reports ask for all of a subject's results joined into a single string — for example, "Y;N;Y" for a subject with three visits — collapsing many rows into one.
The output has one row per subject, with a long character column holding the joined values.
SAS does this with RETAIN + CATX in a DATA step, then keeps only the last row of each subject.
Tidyverse uses group_by() %>% summarize(str_flatten(...)) to build the concatenated string in one step.
Base R uses aggregate() with a paste(..., collapse = ";") helper to achieve the same effect.
This lesson joins each subject's non-missing results into one semicolon-separated string; missing values are dropped from the join automatically.

Retain - Concatenate character results into a single value in a group

SAS (Base SAS)+

*==============================================================================;
* Create the input dataset;
*==============================================================================;
data concat;
    input usubjid visitnum result$;
    datalines;
101 1 Y
101 2 N
101 3 Y
102 1 Y
102 2 N
102 3 .
102 4 N
102 5 Y
;
run;

*==============================================================================;
*Concatenate non-missing results into a single string;
*==============================================================================;

proc sort data=concat;
   by usubjid visitnum;
run;

data concat01;
   set concat;
   by usubjid visitnum;

   length all_results $32767;

   retain all_results;

   if first.usubjid then all_results=result;
   else if not missing(result) then all_results=catx(";",all_results,result);

   if last.usubjid;

   keep usubjid all_results;
run;

To collapse a character column across a subject into a single semicolon-separated string, we use RETAIN together with CATX and keep only the last row per subject.
We give all_results a generous length ($32767) up front so the catx build has room to grow — the default length would truncate long concatenations.
if first.usubjid then all_results = result; primes the string with the first value of the subject.
On every subsequent row, catx(";", all_results, result) appends the current result with a semicolon separator, ignoring empty values automatically.
if last.usubjid; is a subsetting if — it keeps only the final row per subject, which by now holds the fully-built string.
We keep usubjid all_results; to drop the per-row columns and return one row per subject with the concatenated results.
After running, we check that each subject appears once in concat01 and all_results is a single string joining all non-missing results with semicolons.

R (tidyverse)+

library(tidyverse)
library(stringr)

#==============================================================================
# Create the input dataset using tribble
#==============================================================================

concat <- tribble(
  ~usubjid, ~visitnum, ~result,
  101, 1, "Y",
  101, 2, "N",
  101, 3, "Y",
  102, 1, "Y",
  102, 2, "N",
  102, 3, NA,
  102, 4, "N",
  102, 5, "Y"
)

#==============================================================================
# Concatenate non-missing results into a single string
#==============================================================================

concat01 <- concat %>%
  arrange(usubjid, visitnum) %>%
  group_by(usubjid) %>%
  summarize(
    all_results = str_flatten(result, collapse=";",na.rm = TRUE)
  ) %>% ungroup()

For a tidyverse equivalent, we group by subject and use summarize() with str_flatten() to collapse the character column in one step.
arrange(usubjid, visitnum) ensures the values are concatenated in the intended order, the same way PROC SORT does for the SAS version.
group_by(usubjid) scopes the summary so we get one output row per subject.
str_flatten(result, collapse = ";", na.rm = TRUE) is the workhorse — it joins every value with the ; separator and drops NAs from the join.
na.rm = TRUE is what gives us the SAS catx behaviour of skipping missing values automatically.
We finish with ungroup() to remove the grouping metadata so downstream code is not surprised.
After running, we verify concat01 has one row per subject and all_results is the semicolon-joined string of non-NA results.

Happy learning!

Search lessons

Introduction

Retain - Concatenate character results into a single value in a group