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: