Cookies help us display personalized product recommendations and ensure you have great shopping experience.

By using this site, you agree to the Privacy Policy and Terms of Use.
Accept
SmartData CollectiveSmartData Collective
  • Analytics
    AnalyticsShow More
    image fx (67)
    Improving LinkedIn Ad Strategies with Data Analytics
    9 Min Read
    big data and remote work
    Data Helps Speech-Language Pathologists Deliver Better Results
    6 Min Read
    data driven insights
    How Data-Driven Insights Are Addressing Gaps in Patient Communication and Equity
    8 Min Read
    pexels pavel danilyuk 8112119
    Data Analytics Is Revolutionizing Medical Credentialing
    8 Min Read
    data and seo
    Maximize SEO Success with Powerful Data Analytics Insights
    8 Min Read
  • Big Data
  • BI
  • Exclusive
  • IT
  • Marketing
  • Software
Search
© 2008-25 SmartData Collective. All Rights Reserved.
Reading: Coalesce Missing Data to Highlight the Unknown
Share
Notification
Font ResizerAa
SmartData CollectiveSmartData Collective
Font ResizerAa
Search
  • About
  • Help
  • Privacy
Follow US
© 2008-23 SmartData Collective. All Rights Reserved.
SmartData Collective > Analytics > Modeling > Coalesce Missing Data to Highlight the Unknown
Big DataModeling

Coalesce Missing Data to Highlight the Unknown

Editor SDC
Editor SDC
4 Min Read
SHARE

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.

Contents
Coalesce the Missing ValuesIdentify Missing Data when Loading a Dimensional Model

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):

More Read

Know Me and Be Relevant: What I learned from Disney’s Keynote at NCDM
The Salient Aspects of Big Data Management
6 Ways Big Data Can Improve eCommerce For Your Business
Crucial Advantages of Investing in Big Data Management Solutions
Will 6G Force Cell Phone Providers to Redesign Their Data Servers?
  • !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:

Big data solution

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.  

missing data Values

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?

TAGGED:missing data
Share This Article
Facebook Pinterest LinkedIn
Share

Follow us on Facebook

Latest News

image fx (2)
Monitoring Data Without Turning into Big Brother
Big Data Exclusive
image fx (71)
The Power of AI for Personalization in Email
Artificial Intelligence Exclusive Marketing
image fx (67)
Improving LinkedIn Ad Strategies with Data Analytics
Analytics Big Data Exclusive Software
big data and remote work
Data Helps Speech-Language Pathologists Deliver Better Results
Analytics Big Data Exclusive

Stay Connected

1.2kFollowersLike
33.7kFollowersFollow
222FollowersPin

SmartData Collective is one of the largest & trusted community covering technical content about Big Data, BI, Cloud, Analytics, Artificial Intelligence, IoT & more.

ai chatbot
The Art of Conversation: Enhancing Chatbots with Advanced AI Prompts
Chatbots
data-driven web design
5 Great Tips for Using Data Analytics for Website UX
Big Data

Quick Link

  • About
  • Contact
  • Privacy
Follow US
© 2008-25 SmartData Collective. All Rights Reserved.
Go to mobile version
Welcome Back!

Sign in to your account

Username or Email Address
Password

Lost your password?