Obtain the first non-missing value in SAS

This article answers the question of how to obtain the first non-missing value of a list of values in SAS. For example, you have a table with 5 columns and want the value of the first non-empty column. However, the content of the columns can change. So, the first column with a non-missing value might change too. In this case, the SAS functions COALESCE and COALESCEC are very useful. This article explains the syntax of both functions and shows some examples.

The COALESCE and COALESCEC syntax

The COALESCE function returns the first non-missing value from a list of numeric arguments. Whereas the COALESCEC function returns the first non-missing value of a list of character arguments. Both functions check the value of each argument in the order they appear. If it encounters a non-missing value, then this value is returned. However, if all values are missing, SAS returns a missing value.

COALESCE(argument 1<, argument 2, …, argument n>)

COALESCEC(argument 1<, argument 2, …, argument n>)

The arguments can be constants, variables of expression.

Examples

We use two tables to show how the COALESCE and COALESCEC functions work. The first table consists of four numeric columns with some missing value. While the second table also contains four columns, but this time with character values. Also, this table has missing values.

data work.ds_num;
	infile datalines dlm=',' missover;
	input col1 col2 col3 col4;
	datalines;
1, 2, 3, 4
1,  , 3, 4
 , 2, 3, 
 ,  ,  , 4
 ,  ,  ,  
;
run;

data work.ds_char;
	infile datalines dlm=',' missover;
	input col1 $ col2 $ col3 $ col4 $;
	datalines;
a, b, c, d
a,  , c, d
 , b, c, 
 ,  ,  , d
 ,  ,  ,  
;
run;

Both the COALESCE and COALESCEC function searches for the first non-missing value of the four columns. Observe that the fifth row only has missing values.

data work.coalesce;
	set work.ds_num;
	
	coalesce = coalesce(col1, col2, col3, col4);
run;

data work.coalescec;
	set work.ds_char;
	
	coalescec = coalescec(col1, col2, col3, col4);
run;

As you can see in the figures above, in SAS you can obtain the first non-missing value using the COALESCE or COALESCEC function.