Dealing with Missing Dates in SAS Visual Analytics
It’s nearing the new year and you may have already started preparing reports that have the organization’s goals and the progress toward those goals. However your chart may not be displaying correctly. If your data is not setup properly then you’ll notice that you only have one or two months when you want to see the entire year even if there is no data yet. Here’s some solutions to keep your reports looking crisp and professional.
Here’s the Issue
Here’s an example of how the report displays along with what you want to happen. The chart on the left shows the data for the first two months of the year, while the other shows all of the months even though there are no values. Many report builders prefer the image on the right because it allows the trend to build each month.
This issue occurs because your data set is coming up short. In this example, the data only has values for the January and February as you can see in this SAS Studio example. This is a simple example, but it illustrates the point – there is no data for March or any month after that. Thus SAS Visual Analytics doesn’t have a categorical value available to plot.
There are two solutions to this issue – you can use a different object or you can change your data.
Solution 1: Use the SAS Visual Analytics Time Series Object
It may have escaped your notice, but there is a time series data object especially for dates and trend charts. The time series object doesn’t mind if you only have data for two months. You can use the Properties menu to change the fixed minimum and maximum dates. Of course, any report using this method will have to be updated again next year but that seems like a small inconvenience. [Are your line charts KISSable?]
Solution 2: Update Your Data
The time series object is neat solution when you are building line charts. However, what if you are creating bar charts – there’s not an special object to deal with the date issue.
Create a Helper Data Set
In this case, you have to update your data set to include the dates or a helper dataset. The helper dataset consists of nothing more than 12 months that was generated by a simple loop. The date was created with the MDY function. This function uses 3 numeric values to create a date. [More about MDY function from SAS Documentation.]
/* ========= Generate the months of the year */ data generateMonths (drop=mth); format RptMonth date9.; do mth= 1 to 12; RptMonth=mdy(mth,1,2016); output; end; run;
If you need to generate a date for every day of the year, this Generating Dates Automatically paper from Armando V. Fabia written many years ago has good examples that uses the INTNX and INTCK functions.
Stack the Data Sets
Once you have your helped data set (generateMonths) created, you can stack the two data sets – the only requirement is that the date variable have the same name. In this case, my date value was RptMonth.
/*Stack the dataset and upload resulting dataset to SAS VA */ data mylib.addmthsexample; set work.dashboarddata work.generateMonths; run;
Here’s the resulting data set with the updated RptMonth variable. SAS Visual Analytics can use the new variable even if the other variables are empty. Plus the empty rows are not counted in the summary of Sales or COR.
Never miss a BI Notes post!
Click here for free subscription. Once you subscribe you'll be asked to confirm your subscription through your email account. You email address is kept private and you can unsubscribe anytime.
Latest posts by Tricia Aanderud (see all)
- Use these 7 Data Storytelling Examples to Make Your Insights more Meaningful Today - 2018-06-09
- Use Network Analysis to Understand Your Customers with SAS Visual Analytics 8.2 - 2018-01-21
- My 7 Favorite Features in SAS Visual Analytics 8.2 on Viya - 2018-01-14
- Designing Dashboards: Finding the Fantastic Five Colors - 2017-06-19
- Creating a Web Analytics Report in SAS Visual Analytics 8.1 - 2017-06-19