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

The

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

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= . .

**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;If you look at the output shown in the image below, you would find

set temp;

first_non_miss =coalesce(of x1-x4);

run;

**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 |

can you include more helpful examples

ReplyDeleteDidnt get this

ReplyDeleteI have added more description to this article. Hope it helps!

DeletePlease add a bit more.It would be great.

ReplyDeleteexample is great

ReplyDeleteLoved the course so far. Everything is explained in a very simple & detailed way making it easier to understand. One thing I would like to add here is that coalesce can be used to replace missing values with some specific value as well.

ReplyDeleteYes we can do the same...

Delete