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

DeleteCOALESCE for numeric variables and COALESCEC is for character variables.

ReplyDelete