Coalesce Missing Data to Highlight the Unknown

Missing data can be a pain; having missing data and not knowing where it is can be even more of a pain. Here is a quick tip for potentially handling missing values during an ETL process, or during any data processing step, and how to quickly spot them. Mileage may vary depending on the business requirements for processing your data.

Missing data can be a pain; having missing data and not knowing where it is can be even more of a pain. Here is a quick tip for potentially handling missing values during an ETL process, or during any data processing step, and how to quickly spot them. Mileage may vary depending on the business requirements for processing your data.

Coalesce the Missing Values

The coalesce function (alternatively the coalesceC function for character values) is very useful for selectively loading a field depending on the state of data.  The parameters are simple.  Just reference variables in your data or explicit hard-coded values and the coalesce function picks the first non-missing value for that observation.  It selects based on the order variables are entered, from left to right.

coalesce( [first variable], [second variable], .... , [Nth variable])

Sometimes I hard-code the following values at the end of the coalesce parameter list to ensure something gets entered (depending on requirements):

• !UNKNOWN
• !MISSING
• !HEY LOOK AT ME

Using these standardized values can help the business spot missing values very quickly, especially if you use a special character such as the exclamation point which sorts missing values at the top when viewing in ascending order.  

The following code fills missing values of ‘DeathCause’ in the SASHELP.HEART dataset:

data out; set SASHELP.HEART; DeathCause = coalesceC(DeathCause, '!UNKNOWN'); run;

The missing values are converted to !UNKNOWN:

Identify Missing Data when Loading a Dimensional Model

Coalescing missing foreign key values can also be useful when loading a dimensional model.  In a star schema, categorical values are stored in dimension tables with corresponding foreign keys that references these values from fact tables.   The purpose of foreign keys is to describe the factual numeric values contained in the fact table by joining to the related dimension table.  A good best practice is to always load explicit non-NULL foreign key values to ensure numeric data is always identified and because your DBA may not like NULL values within integrity constraints.  If a numeric value truly has a missing dimension, you can use the coalesce function to stage a “zero” value for the foreign key in a fact table.  You could also use a value of “-1″ as the “missing” foreign key value.  This also acts as a “catch all” to make sure the ETL process completes with no errors due to attempting to insert a missing or NULL value in a fact table. 

This is an example DIMENSION table I’m using to reference address locations in a fact table.  

The fact table can reference the ‘address_key’ of 0 for anything that is missing or unknown.

These are two ways I’ve used the COALESCE() and COALESCEC() functions.  Do you have any other uses?