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. 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):
- !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.
Never miss a post!Get the latest BI Notes post in your Inbox when a new post is released! Click here for free subscription. You email address is kept private and you can unsubscribe anytime. Go ahead ... join us!
Latest posts by Steve Overton (see all)
- Need to Migrate Users, Groups, and Roles between SAS Environments? - November 5, 2013
- Big Data Loading with PostgreSQL available in SAS 9.4 - October 22, 2013
- Everything Still Starts and Ends with Data in SAS Visual Analytics - July 30, 2013
- Coding: Quick Tip for Exploiting and Identifying SAS Formats - July 15, 2013
- Describe Your Table in SAS to Write the SQL Code - May 22, 2013