We use cookies, including third-party cookies from Google to serve personalized ads through AdSense, to operate this site and understand how it is used. By continuing to browse, you accept this use. See our Privacy Policy and Terms of Use for details, including how to opt out of personalized advertising.
Accept
SmartData CollectiveSmartData Collective
  • Analytics
    AnalyticsShow More
    Flat editorial illustration: The article is about joining public web signals with internal business metrics so BI teams can inter
    Alternative Data in BI: Bringing Public Web Data Into Your Dashboards
    23 Min Read
    Flat editorial illustration: The article is about automating the collection, preparation, and delivery of marketing data across p
    From Data to Dashboards: 5 Marketing Reporting Automation Software Picks
    12 Min Read
    Flat editorial illustration: The article contrasts passive follower totals with a behavioral path from attention and trust to cli
    Creator Analytics Beyond Follower Count: Seven Metrics That Measure Audience Behavior
    11 Min Read
    Flat editorial illustration: The article connects AI and marketing analytics with managerial judgment grounded in consumer behavi
    How an Online MBA in Marketing Connects AI and Data Analytics
    11 Min Read
    What Kind of Problem-Solving Distinguishes Data Analysts From Software Engineers -- AI-generated illustration
    What Kind of Problem-Solving Distinguishes Data Analysts From Software Engineers
    7 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
Coalesce Missing Data to Highlight the Unknown
Illustration generated with FLUX.2 [klein 4B] via Cloudflare Workers AI.
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 Values
  • Identify Missing Data when Loading a Dimensional Model

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.  

More Read

How to Use Big Data to Improve Your Website’s SEO
How to Use Big Data to Improve Your Website’s SEO
SAS for Data Mining
How To Keep Your Data Security Knowledge Up To Date?
Business People Are Dumb On Average(s)
Big Analytics: Closing the ‘Clue Gap’ with Big Data

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?

TAGGED:missing data
Share This Article
Facebook Pinterest LinkedIn
Share

Follow us on Facebook

Latest News

Flat editorial illustration: The article is about joining public web signals with internal business metrics so BI teams can inter
Alternative Data in BI: Bringing Public Web Data Into Your Dashboards
Big Data Business Intelligence Exclusive Web Analytics
Flat editorial illustration: A SaaS-editorial scene showing product usage, support patterns, and renewal feedback informing a fle
Using Product Usage Analytics and Support Data to Build a B2B Product Roadmap
Business Intelligence Infographic
From Hiring To Payroll: Managing HR Data Across Business Systems -- AI-generated illustration
From Hiring To Payroll: Managing HR Data Across Business Systems
Big Data Business Intelligence Infographic
Flat editorial illustration: The article is about automating the collection, preparation, and delivery of marketing data across p
From Data to Dashboards: 5 Marketing Reporting Automation Software Picks
Analytics Business Intelligence Exclusive Marketing

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.

5 Great Tips for Using Data Analytics for Website UX
5 Great Tips for Using Data Analytics for Website UX
Big Data
Artificial Intelligence for eCommerce: A Closer Look
Artificial Intelligence for eCommerce: A Closer Look
Artificial Intelligence

Quick Link

  • About
  • Contact
  • Privacy
Follow US
© 2008-26 SmartData Collective. All Rights Reserved.
Welcome Back!

Sign in to your account

Username or Email Address
Password

Lost your password?