Three Steps to Building an Interactive Tally Sheet
I love to make tally sheets. Whether I'm trying to remember how many times it snowed over the past few winters or how many glasses of water I've drank each day of the week, there always seems to be a piece of paper in my pocket with a bunch of tally marks on it. I recently began to wonder why I like making tally sheets so much. It occurred to me that they are actually a great way to compare data by groups.
I then began to ask myself if I could build an interactive tally sheet in SAS Visual Analytics? Well, as it turns out, the answer is yes! Using the SAS® Graph Builder, I was able to build the report below which contains an interactive tally sheet comparing baseball player's total home runs:
Pretty Neat! For this report's data source, I used the "baseball" dataset available in the "SASHELP" library. SAS also provides this dataset on the SAS Viya Example Data Sets website. In this post, I'll outline three steps to recreate this custom graph and report. Let's get started!
Step 1: Build the graph
Start by creating a new custom graph using the SAS® Graph Builder. Drag the following objects onto the canvas in the following order:
- Vector Plot
- Scatter Plot
- Scatter Plot
Next select the options menu on the left and from the drop down select "Vector Plot 1" and clear the "Show arrowheads" checkbox. Also set the line width to be "6":
Still within the options menu, select "Scatter Plot 1" and set the Marker style size to be 5:
Still within the options menu, select "Scatter Plot 2" and make the same edits as you did to "Scatter Plot 1".
We will also need to make some axis edits. Select "X Axis" from the options menu drop down and clear all the check boxes. Also set the "Grid lines" option to be "Off":
Still within the options menu, select "Left Y Axis" and make the same edits of clearing all the check boxes and setting the "Grid lines" option to be "Off".
Continuing within the options menu, select "Legends" and set the "Visibility" option to "Hide All":
Now from the "Discrete Legend" section of the options menu, uncheck the "Scatter Plot 2", "Scatter Plot 1" and "Vector Plot 1" checkboxes:
The final edit we will make within the options menu is in the "A1" section. Go to this section and for the "Overlaid Graph Colors" option, choose "Start each graph with its first default color":
Now that our graph options are set, we need to make some edits in the "Roles" menu. Select this menu and start by adding a new "Data Driven Lattice Row":
At the next window, keep all default values and press "OK".
Under the "Shared Roles" section we will need to rename the shared role which was automatically created when we added our graph elements. Click the three dots next to "Shared Role 1" and click "Edit Role":
Rename the role "X". In the "Used by:" section click the "Unshare" button next to "Scatter Plot 2".
When you complete these edits, your screen should look like this:
Press "OK".
Now scroll down to the "Graphs" section of the Roles menu and find the "Vector Plot 1" roles. Click the three dots next to the "Y" role and select "Create Shared Role With Another Role" -> "Scatter Plot 1 Y":
At the next screen, give this new shared role a name of "Y" and press "OK".
Still within the "Vector Plot 1" roles, click the three dots next to the "X Origin" role and select "Create Shared Role With Another Role" -> "Scatter Plot 2 X":
At the next screen, give this new shared role a name of "XOrig" and press "OK".
Still within the "Vector Plot 1" roles, click the three dots next to the "X Origin" role and select "Create Shared Role With Another Role" -> "Scatter Plot 2 Y":
At the next screen, give this new shared role a name of "YOrig" and press "OK".
And you're done! You've successfully built the custom graph. Save your graph and give it a name.
Step 2: Prepare your data
First we begin by creating a new dataset from the sashelp.baseball dataset. For this example, we will only include the right fielders, so we'll begin with a where clause that returns only these players. For our tally graph to work correctly, we will need each run a player scored to have its own observation. Hence, we'll run a simple do loop from 1 to the player's total run count (contained in the variable "nHome"). Also, we will need to have a categorical column to use in the Lattice Row of our graph. While we could simply use the player's name here, I have chosen to create a new column which contains the players total runs concatenated with the player name. This will come in handy for sorting when we build our report. The code to do this is below:
data baseballData;
set sashelp.baseball;
where Position = "RF";
runsPlayer = compress(put(nHome,z2.)) || " - " || name;
label runsPlayer="Total Runs - Player Name";
do count=1 to nHome;
output;
end;
keep Name nHome count runsPlayer;
run;
A few observations from the output dataset are printed below:
The next step is to create coordinate data points for the x and y positions for each of the tally marks. This is very similar to how an annotate data set is used when creating ODS graphs. In order to generate these positions, we will join our data set 'baseballData' to the "tallyTemplate" data set which is in this post's supporting GitHub Repository. The join will be based on the "count" variable. In order to generate a nicely formatted dataset, we will order the output dataset by name and count. The code to do this is below:
proc sql;
create table baseballTallyChart as select
t1.*, t2.x, t2.xOrig, t2.y, t2.yOrig
from baseballData as t1
left join tallyTemplate as t2 on (t1.count = t2.count)
order by name, count;
quit;A few observations from the output dataset are printed below:
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 'baseballTallyChart' to your report. We will first need to adjust the aggregation for the column "Home Runs in 1986" (aka "nHome"). Expand this column and set the aggregation to "Average":
Before we add our new custom graph, lets add the list control to the report. From the "Objects" menu, find the "Controls" section and drag the "List" object onto the reporting canvas. Once it is there, select the "Roles" menu on the right and for the Category role select "Player's Name" and for the Measure role select "Home Runs in 1986":
It would be nice if the list control shows the player who scored the most home runs at the top! So lets sort the control in this way. Right click on the list control you just added and select "Sort" -> "Home Runs in 1986: Descending".
We only need to make one final edit to our list control. From the "Options" menu on the right, scroll down until you see the "Required" checkbox and select it:
Great! Now our list control is ready and it's time to add our custom graph. To do this, import the custom graph to the Visual Analytics report we're working on. Drag the custom graph into the report's canvas and place it to the right of the list control we just created:
We will need to set up the report so when a user clicks one or more players in the list control, it filters the custom graph. To do this, select the list control and go to the "Actions" menu on the right. Select the checkbox next to your custom graph:
Great! Now all we have to do is apply the proper roles to our custom graph. Go to the "Roles" menu on the right pane and select your custom graph from the top dropdown. Apply the roles as shown below:
Now our roles have been assigned and we can see our graph! All that's left is for us to add some window dressing. I modified the width of the list control as well as the custom graph until the report looked the way I wanted. I also added a title to the report's tab. Also, for the initial view of the report, I decided to choose 5 players from the list control (however you can choose any number you like) and to color the tally marks red. To do this, with the graph selected go to the options menu and find the "Line/Marker" color palette. Change the first color to red:
The final step is to sort our custom graph so the player who hit the most home runs is at the top! To do this, right click on the custom graph and choose "Sort" -> "Total Runs - Player Name: Descending":
And that's it! You have successfully built the Tally Sheet Custom Graph and used it to visualize the sashelp.baseball data in SAS Visual Analytics!
How to make this example work for you
This example was created in SAS Visual Analytics 8.5. For this report's data source, I used the "baseball" dataset available in the "SASHELP" library. SAS also provides this dataset on the SAS Viya Example Data Sets website.
On Github, you will find the following support files for this article:
- A JSON file containing the completed custom graph - tallyChartGC.json
- A JSON file containing the completed report - tallySheet.json
- The template dataset for creating the tally marks - tallytemplate.sas7bdat
- The code which creates the final baseballTallyChart data set - tallySheetETL.sas
Import the data on your SAS Visual Analytics instance. Import the report via the "Import via GUI" section of these instructions.
