SAS | COALESCE Function with Examples

The COALESCE function is employed to pick 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 produce a sample dataset in SAS to know how COALESCE perform.

Example:



filter_none

edit
close

play_arrow

link
brightness_4
code

data temp;
    input roll_no a1-a4;
    cards;12 . 98 52 . 
        23 79 . 67 . 
        14 90 82 88 85 
;run;

chevron_right


Output:

  • COALESCE : First Non-Missing Value
    filter_none

    edit
    close

    play_arrow

    link
    brightness_4
    code

    data exam;
    set temp;
    first_non_miss_val = coalesce(of a1 - a4);
    run;

    chevron_right

    
    

    If you examine the output shown within the image below, you’d realize COALESCE returns 98 in first observation which is the first non-missing value among a1 = ., a2 = 98, a3 = 52, a4 =.

  • COALESCE : Last Non-Missing Value

    Let us suppose you want to find out last non-missing value instead of first. For that, there is no such function available which will return last non-missing value but to achieve that we can reverse the list of variables and calculate the first non-missing value which would be equivalent to last non-missing value. Indirectly, we are considering to evaluate variables from right to left instead of left to right.

    filter_none

    edit
    close

    play_arrow

    link
    brightness_4
    code

    data exam;
    set temp;
    last_non_miss_val = coalesce(of a4-a1);
    run;

    chevron_right

    
    

    Output:

    Note: coalesce(of a4-a1) is equivalent to coalesce(a4, a3, a2, a1).



My Personal Notes arrow_drop_up

Check out this Author's contributed articles.

If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.




Article Tags :

Be the First to upvote.


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.