← SASnRCOALESCE: Pick the First Non-Missing Value

Introduction

Clinical ADaM derivations often need fallback logic: use the primary value if it exists, otherwise fall back to a secondary or tertiary source.
A common example is deriving the analysis start date — we prefer AESTDTC (adverse event start date), but if that is missing we fall back to RFSTDTC (reference start date).
SAS splits this into two functions: COALESCE() for numeric values and COALESCEC() for character values. Mixing types causes an error.
R has a single coalesce() function from dplyr that handles both types. Base R uses ifelse() chains or a custom function built with Reduce().
This lesson demonstrates both character (date) and numeric (score) fallback scenarios.

COALESCE: Pick the First Non-Missing Value

SAS (Base SAS)+

*==============================================================================;
* Sample data: subjects with missing dates and missing scores
*==============================================================================;

data adae;
  infile datalines dlm='|' dsd missover;
  length USUBJID $7 AESTDTC $10 RFSTDTC $10 TRTSDT $10;
  input USUBJID AESTDTC RFSTDTC TRTSDT;
  datalines4;
101-001|2024-03-15|2024-01-10|2024-01-12
101-002||2024-02-01|2024-02-03
101-003|||2024-03-05
101-004|2024-04-20|2024-03-10|
;;;;
run;

data scores;
  infile datalines dlm='|' dsd missover;
  input USUBJID : $7. SCORE1 SCORE2 SCORE3;
  datalines4;
101-001|85|90|70
101-002|.|88|75
101-003|.|.|60
101-004|92|.|.
;;;;
run;

*==============================================================================;
* COALESCEC for character: pick first non-missing date
*==============================================================================;

data adae_derived;
  set adae;
  ASTDTC = coalescec(AESTDTC, RFSTDTC, TRTSDT);
run;

proc print data=adae_derived noobs;
  title 'Character fallback: COALESCEC';
run;

*==============================================================================;
* COALESCE for numeric: pick first non-missing score
*==============================================================================;

data scores_derived;
  set scores;
  BEST_SCORE = coalesce(SCORE1, SCORE2, SCORE3);
run;

proc print data=scores_derived noobs;
  title 'Numeric fallback: COALESCE';
run;

We create two datasets: ADAE with character date columns (some missing) and SCORES with numeric score columns (some missing).
For character fallback, we use COALESCEC(AESTDTC, RFSTDTC, TRTSDT). It walks left to right through the arguments and returns the first non-blank value.
For 101-001, AESTDTC is populated so COALESCEC returns it. For 101-002, AESTDTC is blank so it falls through to RFSTDTC. For 101-003, both are blank so it reaches TRTSDT.
For numeric fallback, we use COALESCE(SCORE1, SCORE2, SCORE3). It returns the first non-missing (non-dot) numeric value.
SAS requires that all arguments to COALESCE be numeric and all arguments to COALESCEC be character. Mixing types triggers a compile-time error.
If all arguments are missing, both functions return missing — we see this nowhere in our sample data, but it is worth remembering for edge cases.
After running, we check that each subject's derived column contains the correct fallback value based on which sources are populated.

R (tidyverse)+

library(dplyr)

# ==============================================================================
# Sample data: subjects with missing dates and missing scores
# ==============================================================================

adae <- tibble(
  USUBJID = c("101-001", "101-002", "101-003", "101-004"),
  AESTDTC = c("2024-03-15", NA, NA, "2024-04-20"),
  RFSTDTC = c("2024-01-10", "2024-02-01", NA, "2024-03-10"),
  TRTSDT  = c("2024-01-12", "2024-02-03", "2024-03-05", NA)
)

scores <- tibble(
  USUBJID = c("101-001", "101-002", "101-003", "101-004"),
  SCORE1  = c(85, NA, NA, 92),
  SCORE2  = c(90, 88, NA, NA),
  SCORE3  = c(70, 75, 60, NA)
)

# ==============================================================================
# coalesce for character: pick first non-missing date
# ==============================================================================

adae_derived <- adae %>%
  mutate(ASTDTC = coalesce(AESTDTC, RFSTDTC, TRTSDT))

print(adae_derived)

# ==============================================================================
# coalesce for numeric: pick first non-missing score
# ==============================================================================

scores_derived <- scores %>%
  mutate(BEST_SCORE = coalesce(SCORE1, SCORE2, SCORE3))

print(scores_derived)

We build two tibbles: adae with character date columns and scores with numeric columns, using NA where values are missing.
dplyr::coalesce() works the same way for both character and numeric columns — unlike SAS, there is no need for separate functions.
coalesce(AESTDTC, RFSTDTC, TRTSDT) walks left to right and returns the first non-NA value for each row.
For 101-003, both AESTDTC and RFSTDTC are NA, so coalesce reaches the third argument and returns TRTSDT.
The same coalesce(SCORE1, SCORE2, SCORE3) call handles the numeric scores. All arguments must be the same type (all character or all numeric), but we do not need a different function name.
If every argument is NA for a given row, coalesce returns NA.
After running, we verify that each subject's ASTDTC and BEST_SCORE reflect the correct fallback priority.

Happy learning!

Search lessons

Introduction

COALESCE: Pick the First Non-Missing Value