Coalescing refers to the process of checking the value of each input parameter in the order in which they are listed and returns the first non-missing value. In SAS, the behavior of each of the coalesce functions depends on the processing context, either in a data step or in a PROC SQL statement. These differences are important to acknowledge, especially when the code is migrated from PROC SQL to a data step.

Suppose, for example, that the following piece of code needs to be modified into a data step:

proc sql;
  create table foo2 as
  select coalesce(colum1, column2) as coalesce_column
  from foo;
quit;

The intuitive approach would be to simply rewrite the code as follows:

data foo2;
  set foo;
  coalesce_column = coalesce(colum1, column2);
run;

Although this approach seems to be correct at first glance, it may cause a potential issue: the coalesce function in the context of a data step treats parameters as numeric values. More specifically, if column1 and column2 are characters, the automatic SAS conversion kicks in by trying to convert these values to numerics. If such a conversion fails, a null value will be passed to the function. Simply switching to the character-compatible function, coalescec, may still have some side-effects as we will demonstrate in the following sections.