SAS : COALESCE Function

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

COALESCE

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;
cards;
1 . 89 85 .
2 79 . 74 .
3 80 82 86 85
;
run;
COALESCE : First Non-Missing Value
data want;
set temp;
first_non_miss = coalesce(of x1-x4);
run;
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 = .
SAS : COALESCE Function

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

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);
run;
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

About Author:

Deepanshu founded ListenData with a simple objective - Make analytics easy to understand and follow. He has over 7 years of experience in data science and predictive modeling. During his tenure, he has worked with global clients in various domains like banking, Telecom, HR and Health Insurance.

While I love having friends who agree, I only learn from those who don't.

Let's Get Connected: Email | LinkedIn

Get Free Email Updates :
*Please confirm your email address by clicking on the link sent to your Email*
Related Posts:
5 Responses to "SAS : COALESCE Function"
  1. can you include more helpful examples

    ReplyDelete
  2. Replies
    1. I have added more description to this article. Hope it helps!

      Delete
  3. Please add a bit more.It would be great.

    ReplyDelete
  4. example is great

    ReplyDelete

Next → ← Prev