Table of Contents
The COALESCE function in SAS is a useful tool for handling missing values in a dataset. It allows the user to replace null or missing values with a specified value, either from a list of variables or a single value. This function is particularly helpful when dealing with large datasets where missing values can affect the accuracy of analysis results.
To use the COALESCE function, the user must specify the variables or values to be evaluated and the replacement value. The function will then compare the specified variables or values and return the first non-missing value. This can be used in various scenarios such as creating a new variable with a default value, imputing missing values, or merging multiple datasets with different variable names.
For example, in a dataset containing information on monthly sales, the COALESCE function can be used to replace missing values in the “sales” variable with a default value of 0. This ensures that all observations have a value in the “sales” variable, making it easier to perform calculations and analysis.
In another scenario, when merging two datasets with different variable names, the COALESCE function can be used to create a new variable that combines the values from both datasets. This can prevent the loss of information and ensure that all data is included in the final dataset.
In summary, the COALESCE function in SAS is a versatile tool that can be used in various ways to handle missing values and enhance data analysis. Its application can result in more accurate and complete results, making it a valuable function for SAS users.
Use the COALESCE Function in SAS (With Examples)
You can use the COALESCE function in SAS to return the first non-missing value in each row of a dataset.
The following example shows how to use this function in practice.
Example: How to Use COALESCE in SAS
Suppose we have the following dataset in SAS that contains some missing values:
/*create dataset*/
data original_data;
input team $ points rebounds assists;
datalines;
Warriors 25 8 7
Wizards . 12 6
Rockets . . 5
Celtics 24 . 5
Thunder . 14 5
Spurs 33 19 .
Nets . . .
Mavericks . 8 10
Kings . . 9
Pelicans . 23 6
;
run;
/*view dataset*/
proc printdata=original_data;
We can use the COALESCE function to create a new column that returns the first non-missing value in each row among the points, rebounds, and assists columns:
/*create new dataset*/
data new_data;
set original_data;
first_non_missing = coalesce(points, rebounds, assists);
run;
/*view new dataset*/
proc printdata=new_data;
Here’s how the value in the first_non_missing column was chosen:
- First row: The first non-missing value among points, rebounds, and assists was 25.
- Second row: The first non-missing value among points, rebounds, and assists was 12.
- Third row: The first non-missing value among points, rebounds, and assists was 5.
And so on.
Note #1: If all values are missing (like in row 7) then the COALESCE function will simply return a missing value.
Note #2: The COALESCE function only works with numeric variables. If you’d instead like to return the first non-missing value among a list of character variables, use the COALESCEC function.
Additional Resources
The following tutorials explain how to perform other common tasks in SAS:
Cite this article
stats writer (2024). How can I use the COALESCE function in SAS, and what are some examples of its application?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-can-i-use-the-coalesce-function-in-sas-and-what-are-some-examples-of-its-application/
stats writer. "How can I use the COALESCE function in SAS, and what are some examples of its application?." PSYCHOLOGICAL SCALES, 1 Jul. 2024, https://scales.arabpsychology.com/stats/how-can-i-use-the-coalesce-function-in-sas-and-what-are-some-examples-of-its-application/.
stats writer. "How can I use the COALESCE function in SAS, and what are some examples of its application?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-can-i-use-the-coalesce-function-in-sas-and-what-are-some-examples-of-its-application/.
stats writer (2024) 'How can I use the COALESCE function in SAS, and what are some examples of its application?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-can-i-use-the-coalesce-function-in-sas-and-what-are-some-examples-of-its-application/.
[1] stats writer, "How can I use the COALESCE function in SAS, and what are some examples of its application?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, July, 2024.
stats writer. How can I use the COALESCE function in SAS, and what are some examples of its application?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.