How to Use the COALESCE Function in SAS (With Examples)

You can use the COALESCE function in SAS to return the first non-missing value in each row of a dataset.

The following example shows how to use this function in practice.

Example: How to Use COALESCE in SAS

Suppose we have the following dataset in SAS that contains some missing values:

/*create dataset*/
data original_data;
    input team $ points rebounds assists;
    datalines;
Warriors 25 8 7
Wizards . 12 6
Rockets . . 5
Celtics 24 . 5
Thunder . 14 5
Spurs 33 19 .
Nets . . .
Mavericks . 8 10
Kings . . 9
Pelicans . 23 6
;
run;

/*view dataset*/
proc print data=original_data;

We can use the COALESCE function to create a new column that returns the first non-missing value in each row among the points, rebounds, and assists columns:

/*create new dataset*/
data new_data;
    set original_data;
    first_non_missing = coalesce(points, rebounds, assists);
run;

/*view new dataset*/
proc print data=new_data;

Here’s how the value in the first_non_missing column was chosen:

• First row: The first non-missing value among points, rebounds, and assists was 25.
• Second row: The first non-missing value among points, rebounds, and assists was 12.
• Third row: The first non-missing value among points, rebounds, and assists was 5.

And so on.

Note #1: If all values are missing (like in row 7) then the COALESCE function will simply return a missing value.

Note #2: The COALESCE function only works with numeric variables. If you’d instead like to return the first non-missing value among a list of character variables, use the COALESCEC function.

Additional Resources

The following tutorials explain how to perform other common tasks in SAS:

How to Normalize Data in SAS
How to Replace Characters in a String in SAS
How to Replace Missing Values with Zero in SAS
How to Remove Duplicates in SAS