This tutorial explains the usage of COALESCE function and how to use it.


The COALESCE function is used to select the first non-missing value in a list of variables. In other words, it returns the first non-blank value of each row.

Let's create a sample dataset in SAS to understand COALESCE function.

Sample Data
data temp;
input ID x1-x4;
1 . 89 85 .
2 79 . 74 .
3 80 82 86 85
COALESCE : First Non-Missing Value
data want;
set temp;
first_non_miss = coalesce(of x1-x4);
If you look at the output shown in the image below, you would find COALESCE returns 89 in first observation which is the first non-missing value among x1= . , x2=89 , x3=85, x4 = .

We can also use COALESCE function in PROC SQL.
proc sql;
select *, coalesce(x1,x2,x3,x4) as first_non_miss
from temp;

Last Non-Missing Value

Suppose you need to calculate last non-missing value instead of first non-missing value. Unfortunately, there is no such function which returns last non-missing value. To accomplish this task, we can reverse a list of variables and ask SAS to calculate first non-missing value. It would be equivalent to last non-missing value. Indirectly, we are asking SAS to read variables from right to left rather than left to right.
data want;
set temp;
last_non_miss = coalesce(of x4-x1);
Note : coalesce(of x4-x1) is equivalent to coalesce(x4, x3, x2, x1).

Last Non-Missing Value
In this case, COALESCE returns 85 as it is a first non-missing value (read from right to left) among x4= . , x3= 85, x2=89, x1= . .

SAS Tutorials : 100 Free SAS Tutorials

Get Free Email Updates :
*Please confirm your email address by clicking on the link sent to your Email*

Related Posts:

3 Responses to "SAS : COALESCE Function"

Next → ← Prev