SAS : COALESCE Function

Live Online Training : SAS Programming with 50+ Case Studies

- Explain Programming Concepts in Simple English
- Live Projects
- Scenario Based Questions
- Job Placement Assistance
- Get 20% off till July 14, 2017

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 close to 7 years of experience in data science and predictive modeling. During his tenure, he has worked with global clients in various domains like retail and commercial banking, Telecom, HR and Automotive.


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:

4 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

Next → ← Prev