BI Tools

Tips and tricks for building information maps, OLAP cubes, reports, and dashboards

BI Admin

Learn your way around a SAS BI installation.

Visual Analytics

Learn your way around the SAS Visual Analytics tool

Coding & Data

Extract, transform, and load your data into the SAS BI toolset

Stored Processes

Create and design stored processes like a rock star

Home » Coding & Data, Visual Analytics

SAS VA: Dealing with Missing Dates

Submitted by on 2015-12-20 – 1:27 PM One Comment

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.

va_helper_dsn

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.

va_helper_dsn_04

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?]

va_helper_dsn_03

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.

va_helper_dsn_02

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.

va_helper_dsn_05

 

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.
The following two tabs change content below.

Tricia Aanderud

Director of Data Visualization at Zencos Consulting
Tricia Aanderud is a SAS Business Intelligence and Visual Analytics consultant based in Raleigh, NC who works for Zencos Consulting. She has written several books about SAS, presented papers at many SAS conferences, and has been using SAS since 2001. Contact her for assistance with your next project.

Tags: , , ,

One Comment »