Finding Non-NULL NULL Dates

As we saw in ”Why do 5% of My Services Occur on January 1, 1960?”, NULL dates are frequently passed to claims extracts as 0 dates causing issues with trending and length of stay. To identify this issue, summarize your data by year and month as shown in the DPS Non-NULL NULL Dates dataset.

The results are not always as dramatic as displayed below, but the example illustrates how to identify possible problems. Significant event counts of dollar amounts prior to the time period of the analysis generally indicate that you have found the issue. It is easy enough to reset 0 values to nulls for key date fields. Keep in mind that there are a variety of dates that 0 can be translated into and not all are so far back in time as 1900 or 1960.

In general, it is good practice to look at all of your date fields with counts (or another unit of measurement like dollars or member months) by year and month. Look for outliers prior to the general analytic time period. Dates of birth can be especially tricky since most actually do precede the analytic time period. However, a chart showing counts will help identify outliers from the general trend.

Year and month analysis will also help identify other outlier dates like ’12-31-9999’ or other “non-termination” dates added during warehousing and extract production. These frequently show up in end/termination dates for enrollment spans or discharge dates from facilities.

Dr. Olmsted has a Ph.D. in Economics and has been working with healthcare data for over twenty years for companies including IHCIS (now part of Optum), RTI International, and Health Dialog.