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
    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
    data analytics for trademark registration
    Optimizing Trademark Registration with Data Analytics
    6 Min Read
    data analytics for finding zip codes
    Unlocking Zip Code Insights with Data Analytics
    6 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

Big Data: Coming to a Little Screen Near You
The Power of Big Data and Analytics in Digital Signage
Big Data Becomes Pivotal to the Construction Industry
Revealed: The Top 5 Big Data Use Cases Your CEO Will Love
Big Data Creates Greater Divide Between CDN & Traditional Web Hosting
  • !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

accountant using ai
AI Improves Integrity in Corporate Accounting
Exclusive
ai and law enforcement
Forensic AI Technology is Doing Wonders for Law Enforcement
Artificial Intelligence Exclusive
langgraph and genai
LangGraph Orchestrator Agents: Streamlining AI Workflow Automation
Artificial Intelligence Exclusive
ai fitness app
Will AI Replace Personal Trainers? A Data-Driven Look at the Future of Fitness Careers
Artificial Intelligence 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 chatbots
AI Chatbots Can Help Retailers Convert Live Broadcast Viewers into Sales!
Chatbots
AI and chatbots
Chatbots and SEO: How Can Chatbots Improve Your SEO Ranking?
Artificial Intelligence Chatbots Exclusive

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?