Three Steps to Building a Bar Chart Heatmap
In my previous custom graph articles, I almost always focus on creating graphs that have multiple graph objects overlaid on top of each other. For example, in this article the final graph was created by overlaying line plots with scatter plots. However did you know that you can use the graph builder to create a single graph that has multiple elements side by side? This uncommonly used feature allows developers to create interactive custom graphs that combine the power of multiple graph elements, all rolled into one!
For example, I like heatmaps and bar charts for different reasons. Heat maps are great at showing values for two data items by using a table with colored cells. Bar charts can display a data item's total values via the height of its bars. These two graph types each have unique ways of displaying data. And if I could combine their powers into one interactive graph, now that would really be something!
One way to achieve this is to use the Graph Builder to combine a heat map and two bar charts into a single graph! Using the Graph Builder and the sashelp library's snacks data set, I was able to create the following report which shows the total sales of snacks by month and year:
The bar charts on the top and left show us the totals by weekday and month and the heatmap in the center shows us the distribution of sales for weekday and month using the colored cells. More so, since we are using visual analytics, our graph can be interactive! In the report above, the user can choose which snack product they would like to see data for!
Pretty neat! In this post, I'll outline the three steps to add these types of labels to recreate the report above. Let's get started!
Step 1: Build the graph
Start by creating a new custom graph using the SAS® Graph Builder. Drag the a schedule chart object onto the canvas:
Next, select a bar chart and drag it to the TOP of the schedule chart.
The Graph Builder will display the message "Add to new row" when you do this:
Now do the same thing again, but this time drag your bar chart to the RIGHT of the schedule chart.
This time the Graph Builder will display the message "Add to new column":
With these three graphs in their respective cells, you have the ability to change the size of each cell by clicking and moving the three dots that are on the cell borders. This is great because we want our heatmap to be the centerpiece of our custom graph. In my example below, I have rearranged the cells so the top bar chart takes up about 15 percent of the vertical space and the right bar chart takes up about 15 percent of the horizontal space:
Now select the options menu on the right and from the drop down select "Bar Chart 1" and under the "Spacing" section, change the value to be 10%:
Now, scroll down to "Fill color" section and set this to be "Data color 1". Also select the "Data labels" checkbox:
Now, from the main options drop down select the "Bar Chart 2" option and make the same edits of setting the spacing to be 10%, the fill color to be "Data color 1" and select the "Data labels" checkbox. Also, set the "Direction" option to be "Horizontal":
Now, from the main options drop down, select the "X Axis" option located directly under the "Bar Chart 1" option:
In this section, set the "Grid lines" option to "Off". Also deselect the "Axis label" and "Axis line" check boxes:
Now, from the main options drop down, select the "Left Y Axis" option located directly under the "Bar Chart 1" option:
In this section, set the "Grid lines" option to "Off". Also deselect the "Bold" option:
Now, from the main options drop down, select the "X Axis" option located directly under the "Bar Chart 2" option:
In this section, set the "Grid lines" option to "Off". Also deselect the "Bold" option:
Now, from the main options drop down, select the "Left Y Axis" option located directly under the "Bar Chart 2" option:
In this section, set the "Grid lines" option to "Off". Also deselect the "Axis label", "Axis Line", "Tick values" and "Tick marks" check boxes:
Now select the options menu on the right and from the drop down select "Schedule Chart 1" and under the "Spacing" section, change the value to be 0%:
Now, from the main options drop down, select the "X Axis" option located directly under the "Schedule Chart 1" option:
In this section, set the "Grid lines" option to "Off". Also deselect the "Axis label", "Axis Line", "Tick values" and "Tick marks" check boxes:
Now, from the main options drop down, select the "Left Y Axis" option located directly under the "Schedule Chart 1" option:
In this section, deselect the "Axis label" option:
Great! Now that our graph options are set, we need to make some edits in the "Roles" menu.
Start by selecting the main "Roles" menu and under the "Bar Chart 1" section click "Add Role":
At the next screen select "Color" for the role type and click "OK":
Next, under the "Schedule Chart 1" section click the three dots next to the "Task" role and choose "Create Shared Role with Another Role" -> "Bar Chart 2 Category":
At the next screen, input "Bar Chart 2 Category" as the role name and click "OK":
Now, still under the "Schedule Chart 1" section section click "Add Role":
At the next screen select "Group" for role type and click "Save":
Now, still under the "Schedule Chart 1" section section click "Add Role":
At the next screen select "Data Tip" for role type and click "Save":
Now, under the "Bar Chart 2" section of the click "Add Role":
At the next screen select "Color" for the role type and click "OK":
Great! Our roles are now complete. The final step to completing this custom graph is to remove the legends. To do this, select the "Options" menu on the right hand side of the page and from the main drop down select the "Discrete Legend" option.
Within the "Display in legend" section, uncheck all the check boxes:
Now, from the main options drop down, select the "Bar Chart 1 Continuous Legend" option and deselect the "Show legend" checkbox:
And finally, select the "Bar Chart 2 Continuous Legend" from the main options drop down and deselect the "Show legend" checkbox:
And you're done! You've successfully built the custom graph. Save your graph and give it a name.
Step 2: Prepare your data
As I mentioned before, the source data for the report shown at the top of this post is the 'snacks' data set which is available in the sashelp library. We will need to add a few items to the data so it can support our custom graph. Let's start by using the weekday values from the data to generate 'start' and 'finish' columns for our schedule chart. Also, since our graph will be summarizing the data by both weekday and month values we will create character versions of these.
*For this article, I decided to subset my data to just a few snack products. However this subsetting of the data is certainly optional:
data addColumns;
set sashelp.snacks;
start = Weekday(Date);
finish=start+1;
Weekday = left(put(Date,downame3.));
Month = left(put(Date,monname.));
WHERE Product in ('Carmelized popcorn','Cheddar cheese popcorn','Multigrain chips',
'Saltine crackers','WOW cheese puffs');
run;
Now that we have our needed columns in place, we will need to summarize the data for the heatmap and both bar charts. This can easily be done using PROC SQL:
proc sql;
create table totalSold as select
"Heatmap" as Category length=40, Product, Month, Weekday, start, finish,
sum(QtySold) as TotalSales format=dollar10.0
from addColumns
group by Category, Product, Month, Weekday, start, finish ;
quit;
proc sql;
create table totalSoldMonthly as select
"Month" as Category length=40, Product, Month, Weekday, start, finish,
TotalSales, sum(TotalSales) as TotalSalesMonthly label="Total Sales by Month" format=dollar10.0
from totalSold
group by Category, Product, Month ;
quit;
proc sql;
create table totalSoldWeekday as select
"Weekday" as Category length=40, Product, Month, Weekday, start, finish,
TotalSales, sum(TotalSales) as TotalSalesWeekday label="Total Sales by Weekday" format=dollar10.0
from totalSold
group by Category, Product, Weekday ;
quit;
The next step is to categorize our totals into groups so we can place color values on them in our report. We can use PROC RANK for this:
proc rank data=totalSold out=HeatmapRanks ties=low groups=5;
by product;
var TotalSales;
ranks TotalSalesRankHeatmap;
run;
proc rank data=totalSoldMonthly out=MonthlyRanks ties=low groups=12;
by product;
var TotalSalesMonthly;
ranks TotalSalesRankMonthly;
run;
proc rank data=totalSoldWeekday out=WeekdayRanks ties=low groups=7;
by product;
var TotalSalesWeekday;
ranks TotalSalesRankWeekday;
run;
In my example code above, I decided to create five groups for the heatmap, seven groups for the weekday bar chart and twelve groups for the month bar chart. However you might choose to have a different number of groups in your graph.
The last step is to combine our three summary data sets together. In this step we will also create a categorical version of our heatmap ranking variable, place missing values in the 'start' column where the total sales is zero, add some labels, and finally drop any columns that we will not need in our output data set:
data barChartHeatmap;
set HeatmapRanks MonthlyRanks WeekdayRanks;
if Category = "Heatmap" then heatMapGroup = trim(Category) || "-" || compress(TotalSalesRankHeatmap);
label TotalSalesRankMonthly = "Month Sales Rank"
TotalSalesRankWeekday = "Weekday Sales Rank";
if TotalSales = 0 then start=.;
else start=start;
drop Category TotalSalesRankHeatmap;
run;
Great! Our data is now ready for SAS Visual Analytics!
Step 3: Build the report!
Begin by creating a new Visual Analytics report and adding the data set 'barChartHeatmap' to your report. We will first need to set a few data options to our columns.
Let's begin by adding a custom sort to the "Month" category. To do this, right click on "Month" and select "Custom sort...":
Sort the values as shown below and click "OK":
Now create a custom sort for the "Weekday" column. Sort the values as shown below and click "OK":
Now set the aggregation for the "Total Sales by Month" and "Total Sales by Weekday" metrics to be "Average":
Great! Those are all the needed options for our data. We can now move on to building the report!
For the example to work correctly, a required drop down prompt is needed so users could filter the dashboard by different snacks. To do this, drag a drop-down list control on top of the report's tab:
With the drop-down list control selected apply "Product name" to it's "Category" role:
Also, in the drop-down list control's "Options" menu, check the "Required" option:
Great! Our report is now ready for our custom graph! All that's left for us is to import the custom graph to the Visual Analytics report we're working on. Drag the custom graph into the report's canvas and apply the data roles as shown:
The last thing we need to do is add a display rule so the heatmap's cells with higher values will be a darker color. We will use the "heatMapGroup" variable we added to our final data set for this. To do this, go to the "Rules" menu and create a new display rule based on the variable "heatMapGroup". Since this data is about snack sales, I chose to make a rule with various shades of green:
Below are the hex color values for the display rule I created in my example report:
- Heatmap-4 - 17785F
- Heatmap-3 - 16976D
- Heatmap-2 - 15B57B
- Heatmap-1 - 43D09D
- Heatmap-0 - 71EBBF
Great! We can now see our report. There's only one last thing to do. By default Visual Analytics is sorting the Months in ascending order. We want these sorted in descending order. To do this right-click the heat map and select "Sort" -> "Month: Descending":
All that's left for us to do is add some window dressing. For this, I applied a fixed report size of 650x550 to ensure that the graph always renders in the dimensions best suited for display. I also renamed the tab. The final report that contains these edits are included as JSON files in this GitHub Repository. After these edits, our final report looks like this:
How to make this example work for you
This example was created in SAS Visual Analytics 8.5. For this article's data, I used the 'snacks' data set which is in the sashelp library and did some basic data manipulation to it. The SAS code that performs this data manipulation is located in this article's GitHub folder.
On Github, you will find the following support files for this article:
- The code to which creates the final barChartHeatmap data set - barChartHeatmap_ETL.sas
- A JSON file containing the completed custom graph - barChartHeatmap_CG.json
- A JSON file containing the completed report - barChartHeatmap.json
