Coalesce

Prefer one value, fall back to another

SAS: COALESCE, coalescec, IFN / IFC, specify-then-coded

SAS COALESCE / coalescec skip missing values. dplyr::coalesce() skips NA only. Blank "" is a value, not missing.

Quick reference

library(dplyr)

coalesce(x, y)                    # first non-NA argument; later args are fallbacks
coalesce(na_if(spy, ""), coded)   # specify, then coded. blanks are not NA

na_if(x, "")                      # "" -> NA so coalesce will skip it
na_if(x, "NA")                    # the literal string "NA" is not missing either
R SAS
dplyr::coalesce() COALESCE (numeric), coalescec (character)
na_if(x, "") SAS already treats blank as missing
if_else(cond, a, b) IFN / IFC

Three traps: coalesce() skips NA only, not "". A specify field of "" therefore beats the coded value. Convert blanks with na_if() first, then prefer specify over coded: coalesce(na_if(spy, ""), coded).

Worked examples below.

The example table

Specify versus coded location. Four rows; four states of the specify field.

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
cm <- data.frame(
  USUBJID = c("STD-001-0001", "STD-001-0002", "STD-001-0003", "STD-001-0004"),
  LOCSPY  = c("Left deltoid", "", NA, "Right thigh, anterior"),
  LOC     = c("ARM", "ARM", "LEG", "THIGH")
)

cm
       USUBJID                LOCSPY   LOC
1 STD-001-0001          Left deltoid   ARM
2 STD-001-0002                         ARM
3 STD-001-0003                  <NA>   LEG
4 STD-001-0004 Right thigh, anterior THIGH
  • STD-001-0001: specify present; overrides ARM
  • STD-001-0002: specify is ""; use coded
  • STD-001-0003: specify is NA; use coded
  • STD-001-0004: specify present; overrides THIGH

coalesce() skips NA

loc = coalescec(locspy, loc);
cm |>
  mutate(LOC_OUT = coalesce(LOCSPY, LOC))
       USUBJID                LOCSPY   LOC               LOC_OUT
1 STD-001-0001          Left deltoid   ARM          Left deltoid
2 STD-001-0002                         ARM                      
3 STD-001-0003                  <NA>   LEG                   LEG
4 STD-001-0004 Right thigh, anterior THIGH Right thigh, anterior

STD-001-0003 is correct: NA is skipped, LEG is used. STD-001-0002 is not. "" is not NA, so coalesce() keeps it and does not use coded ARM.

Importantcoalesce() does not treat "" as missing

SAS character missing is blank. R character missing is NA. coalesce() follows R. A specify field of "" is present, so it overrides the coded term. Convert blanks with na_if(), then coalesce.

The fix: na_if() then coalesce()

cm |>
  mutate(LOC_OUT = coalesce(na_if(LOCSPY, ""), LOC))
       USUBJID                LOCSPY   LOC               LOC_OUT
1 STD-001-0001          Left deltoid   ARM          Left deltoid
2 STD-001-0002                         ARM                   ARM
3 STD-001-0003                  <NA>   LEG                   LEG
4 STD-001-0004 Right thigh, anterior THIGH Right thigh, anterior

Specify is used when it has text. Coded is used when specify is "" or NA.

Specify-overrides-coded is coalesce(na_if(spy, ""), coded). Reversed arguments coalesce(coded, na_if(spy, "")) keep ARM on STD-001-0001 and drop the specify text.

na_if() on its own

na_if(cm$LOCSPY, "")
[1] "Left deltoid"          NA                      NA                     
[4] "Right thigh, anterior"

"" becomes NA. Other strings are unchanged. NA remains NA.

The characters "NA" are not missing. If an export stored that string, convert it with na_if(x, "NA"). That is separate from na_if(x, "").

IFN / IFC

SAS IFN (numeric) and IFC (character) are a condition and two (or three) results. That mapping is if_else(), not coalesce():

loc = ifc(missing(locspy) or locspy = "", loc, locspy);
cm |>
  mutate(
    LOC_OUT = if_else(
      is.na(na_if(LOCSPY, "")),
      LOC,
      LOCSPY
    )
  )
       USUBJID                LOCSPY   LOC               LOC_OUT
1 STD-001-0001          Left deltoid   ARM          Left deltoid
2 STD-001-0002                         ARM                   ARM
3 STD-001-0003                  <NA>   LEG                   LEG
4 STD-001-0004 Right thigh, anterior THIGH Right thigh, anterior

Same result as coalesce(na_if(LOCSPY, ""), LOC). Use coalesce() for first non-missing. Use if_else() when the rule is a named condition.