Table of Contents
The COALESCE function in SAS is used to return the first non-missing value among a list of values. It is an efficient way to check for the presence of missing values in the data. It can be used to fill specific values or to replace missing values with user-defined values. Examples of how to use the COALESCE function include using it to check for the presence of missing values in a variable, replacing missing values with a desired value, and filling specific values with a user-defined value.
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.
The following tutorials explain how to perform other common tasks in SAS: