SAS Visual Analytics


Several months ago, I posted a blog about calculating moving averages for a measure in the Visual Analytics Designer. Soon after that, I was asked about calculating not only the average, but also the standard deviation over a period of months, when the data might consist of one or more repeated values of a measure for each month of a series of N months.  For the example of N=20 months, we might want to view the average and standard deviation over the last n months, where n is any number between 3 and 20.

The example report shown below allows the user to type in a number, n, between 3 and 20, to display a report consisting of the amount values for past n months, the amount values for Current Month Amt-Previous, the Avg over the last n months, the Standard Deviation over the last n months, and the absolute value of the (Current Month Amt – Previous Month Amt), divided by the Standard Deviation over the last n months. A Display rule is applied to the final Abs column, showing Green for a value less than 1 and red for a value greater than or equal to 1.

The data used in this example had multiple Amount values for each month, so we first used the Visual Data Builder to create a SUM aggregation for Amount for each unique Date value.  This step gives more flexibility in using the amount value for aggregations in the designer.

When the modified data source is initially added to the report, it contains only the Category data item Month, with a format of MMYYYY, and the measure Amount Sum for Month.

The data will be displayed in a list table. The first columns added to the table will be Month, displayed with a MMYYYY format, and Amount Sum for Month.

Specify the properties for the list table as below:

Since we want to display the last n months, we create a new calculated data item, Numeric Date, calculated as below, using the TREATAS operator on the Month data item:

Then we create the Current Month Amt-Previous aggregated measure using the RelativePeriod date operator:

Next, create the Avg over all displayed months aggregated measure as below:

Then, create the Std.Dev. over all displayed months aggregated measure as shown below:

Create the Abs (Current-Previous/StdDev) as shown below:

Create a numeric parameter, Number of Months, as shown, with minimum value of 3 (smallest value that a standard deviation will make sense) and maximum value of 20 (the number of months in our data). You can let the default (Current value) value be any value that you choose:

For the List Table, create a Rank, as shown below. Note that we are creating the rank on the Numeric Date (not the Month data item), and rather than a specific value for count, we are going to use the value of the parameter, Number of Months.

Create a text input object that enables the user to type in a ‘number of months’ between 3 and 20.

Associate the Parameter with the Text input object:

If you wish, you can add display rules to sound an alarm whenever there is an alarming month-to-month difference in comparison to the standard deviation for the months.

So the final result of all of the above is this report, which points out month-to-month differences, which might deserve further concern or investigation. Note that the Numeric Date value is included below just to enable you to see what those values look like—you likely would not want to include that calculated data item in your report.

Calculating standard deviation of a measure in Visual Analytics Designer was published on SAS Users.


As a practitioner of visual analytics, I read the featured blog of ‘Visualizations: Comparing Tableau, SPSS, R, Excel, Matlab, JS, Python, SAS’ last year with great interest. In the post, the blogger Tim Matteson asked the readers to guess which software was used to create his 18 graphs. My buddy, Emily Gao, suggested that I should see how SAS VA does recreating these visualizations. I agreed.

SAS Visual Analytics (VA) is better known for its interactive visual analysis, and it’s also able to create nice visualizations. Users can easily create professional charts and visualizations without SAS coding. So what I am trying to do in this post, is to load the corresponding data to SAS VA environment, and use VA Explorer and Designer to mimic Matteson’s visualizations.

I want to specially thank Robert Allison for his valuable advices during the process of writing this post. Robert Allison is a SAS graph expert, and I learned a lot from his posts. I read his blog on creating 18 amazing graphs using purely SAS code, and I copied most data from his blog when doing these visualization, which saved me a lot time preparing data.

So, here’s my attempt at recreating Matteson’s 18 visualization using SAS Visual Analytics.

Chart 1

This visualization is created by using two customized bar charts in VA, and putting them together using precision layout so it looks like one chart. The customization of bar charts can be done by using the ‘Custom Graph Builder’ in SAS VA, which includes: set the reverse order for X axis, set the axes direction to horizontal, and don’t show axis label for X axis and Y axis, uncheck the ‘show tick marks’, etc. Comparing with Matteson’s visualization, my version has the tick values on X axis displayed as non-negative numbers, as people generally would expect positive value for the frequency.

Another thing is, I used the custom sort for the category to define the order of the items in the bar chart. This can be done by right click on the category and select ‘Edit Custom Sort…’ to get the desired order. You may also have noticed that the legend is a bit strange for the Neutral response, since it is split into Neutral_1stHalf and Neutral_2ndHalf, which I need to gracefully show the data symmetrically in the visualization in VA.

Chart 2

VA can create a grouped bar chart with desired sort order for the countries and the questions easily. However, we can only put the questions texts horizontally atop of each group bar in VA. VA uses vertical section bar instead, with its tooltip to show the whole question text when the mouse is hovered onto it. And we can see the value of each section in bar interactively in VA when hovering the mouse over.

Chart 3

Matteson’s chart looks a bit scattered to me, while Robert’s chart is great at label text and markers for the scatterplot matrix. Here I use VA Explorer to create the scatterplot matrix for the data, which omitted the diagonal cells and its diagonal symmetrical part for easier data analysis purpose. It can then be exported to report, and change the color of data points.

Chart 4

I used the ‘Numeric Series Plot’ to draw this chart of job losses in recession. It was straightforward. I just adjust some setting like checking the ‘Show markers’ in the Properties tab, unchecking the ‘Show label’ in X Axis and unchecking the ‘Use filled markers’, etc. To make refinement of X axis label of different fonts, I need to use the ‘Precision’ layout instead of the default ‘Tile’ layout. Then drag the ‘Text’ object to contain the wanted X axis label.

Chart 5

VA can easily draw the grouped bar charts automatically. Disable the X axis label, and set the grey color for the ‘Header background.’ What we need to do here, is to add some display rules for the mapping of color-value. For the formatted text at the bottom, use the ‘Text’ object. (Note: VA puts the Age_range values at the bottom of the chart.)

Chart 6

SAS VA does not support drawn 3D charts, so I could not make similar chart as Robert did with SAS codes. What I do for this visualization, is to create a network diagram using the Karate club dataset. The grouped detected communities (0, 1, 2, 3) are showing with different colors. The diagram can be exported as image in VAE.

***I use the following codes to generate the necessary data for the visualization: 

/* Dataset of Zachary’s Karate Club data is from:  
This dataset describes social network friendships in karate club at a U.S. university.
data LinkSetIn;
   input from to weight @@;
 0  9  1  0 10  1  0 14  1  0 15  1  0 16  1  0 19  1  0 20  1  0 21  1
 0 23  1  0 24  1  0 27  1  0 28  1  0 29  1  0 30  1  0 31  1  0 32  1
 0 33  1  2  1  1  3  1  1  3  2  1  4  1  1  4  2  1  4  3  1  5  1  1
 6  1  1  7  1  1  7  5  1  7  6  1  8  1  1  8  2  1  8  3  1  8  4  1
 9  1  1  9  3  1 10  3  1 11  1  1 11  5  1 11  6  1 12  1  1 13  1  1
13  4  1 14  1  1 14  2  1 14  3  1 14  4  1 17  6  1 17  7  1 18  1  1
18  2  1 20  1  1 20  2  1 22  1  1 22  2  1 26 24  1 26 25  1 28  3  1
28 24  1 28 25  1 29  3  1 30 24  1 30 27  1 31  2  1 31  9  1 32  1  1
32 25  1 32 26  1 32 29  1 33  3  1 33  9  1 33 15  1 33 16  1 33 19  1
33 21  1 33 23  1 33 24  1 33 30  1 33 31  1 33 32  1
/* Perform the community detection using resolution levels (1, 0.5) on the Karate Club data. */
proc optgraph
   data_links            = LinkSetIn
   out_nodes             = NodeSetOut
   graph_internal_format = thin;
      resolution_list    = 1.0 0.5
      out_level          = CommLevelOut
      out_community      = CommOut
      out_overlap        = CommOverlapOut
      out_comm_links     = CommLinksOut;
/* Create the dataset of detected community (0, 1, 2, 3) for resolution level equals 1.0 */ 
proc sql;
	create table mylib.newlink as 
	select a.from,, b.community_1, c.nodes from LinkSetIn a, NodeSetOut b, CommOut c
 	where a.from=b.node and and c.resolution=1 ;

Chart 7

I created this map using the ‘Geo Coordinate Map’ in VA. I need to create a geography variable by right clicking on the ‘World-cities’ and selecting Geography->Custom…->, and set the Latitude to the ‘Unprojected degrees latitude,’ and Longitude to the ‘Unprojected degrees longitude.’ To get the black continents in the map, go to VA preferences, check the ‘Invert application colors’ under the Theme. Remember to set the ‘Marker size’ to 1, and change the first color of markers to black so that it will show in white when application color is inverted.

Chart 8

This is a very simple scatter chart in VA. I only set transparency in order to show the overlapping value. The blue text in left-upper corner is using a text object.

Chart 9

To get this black background graph, set the ‘Wall background’ color to black. Then change the ‘Line/Marker’ color in data colors section accordingly. I’ve also checked the ‘Show markers’ option and changed the marker size to bigger 6.

Chart 10

There is nothing special for creating this scatter plot in VA. I simply create several reference lines, and uncheck the ‘Use filled markers’ with smaller marker size. The transparency of the markers is set to 30%.

Chart 11

In VA’s current release, if we use a category variable for color, the marker will automatically change to different markers for different colors. So I create a customized scatterplot using VA Custom Graph Builder, to define the marker as always round. Nothing else, just set the transparency to clearly show the overlapping values. As always, we can add an image object in VA with precision layout.

Chart 12

I used the GEO Bubble Map to create this visualization. I needed to create a custom Geography variable from the trap variable using ‘lat_deg’ and ‘lon_deg’ as latitude and longitude respectively. Then rename the NumMosquitos measure to ‘Total Mosquitos’ and use it for bubble size. To show the presence of west nile virus, I use the display rule in VA. I also create an image to show the meaning of the colored icons for display rule. The precision layout is enabled in order to have text and images added for this visualization.

Chart 13

This visualization is also created with GEO bubble map in VA. First I did some data manipulation to make the magnitude squared just for the sake of the bubble size resolution, so it shows contrast in size. Then I create some display rules to show the significance of the earth quakes with different colors, and set the transparency of the bubble to 30% for clarity. I also created an image to show the meaning of the colored icons.

Be aware that some data manipulation is needed for original longitude data. Since the geographic coordinates will use the meridian as reference, if we want to show the data of American in the right part, we need to add 360 to the longitude, whose value is negative.

Chart 14

My understanding that one of the key points of this visualization Matteson made, is to show the control/interaction feature. Great thing is, VA has various control objects for interactive analysis. For the upper part in this visualization, I simply put a list table object. The trick here is how to use display rule to mimic the style. Before assigning any data to the list table in VA, I create a display rule with Expression, and at this moment we can specify the column with any measure value in an expression. (Otherwise, you need to define the display rule for each column with some expressions.) Just define ‘Any measure value’ is missing or greater than a value with proper filled color for cell. (VA doesn’t support filling the cell with certain pattern like Robert did for missing value. Therefore, I use grey for missing value to differentiate from 0 with a light color.)

For the lower part, I create a new dataset for interventions to hold the intervention items, and put it in the list control and a list table. The right horizontal bar chart is a target bar chart with the expected duration as the targeted value. The label on each bar shows the actual duration.

Chart 15

VA does not have solid-modeling animation like Matteson made in his original chart, yet VA has animation support for bubble plots in an interactive mode. So I made this visualization using Robert’s animation dataset, trying to make an imitation of the famous animation by the late Hans Rosling as a memorial. I set the dates for animation by creating the dates variable with the first day in each year (just for simplicity). One customization here is: I use the custom graph builder to add a new role so that it can display the data label in the bubble plot, and set the country name as the bubble label in VA Designer. Certainly, we can always filter the interested countries in VA for further analysis.

VA can’t show only a part of the bubble labels as Robert did using SAS codes. So in order to clearly show the labels of those interested countries, I made a rank of top 20 countries of average populations, and set a filter to show data between year 1950 to 2011. I use a capture screen tool to have the animation saved as a .gif file. Be sure to click the chart to see the animation.

Chart 16

I think Matteson’s original chart is to show the overview axis in the line chart, since I don’t see specialty of the line chart otherwise. So I draw this time series plot with the overview axis enabled in VA using the SASHELP.STOCK dataset. It shows the date on X axis with tick marks splitting to months, which can be zoomed in to day level in VA interactively. The overview axis can do the zooming in and out, as well as movement of the focused period.

Chart 17

For this visualization, I use a customized bubble plot (in Custom Graph Builder, add a Data Label Role for Bubble Plot.) so it will have bubble labels displayed. I use one reference line with label of Gross Avg., and 2 reference lines for X and Y axis accordingly, thus it visually creats four quadrants. As usual, add 4 text objects to hold the labels at each corner in the precision layout.

Chart 18

I think Matteson made an impressive 3D chart, and Robert recreated a very beautiful 3D chart with pure SAS codes. But VA does not have any 3D charts. So for this visualization, I simply load the data in VA, and drag them to have a visualization in VAE. Then choose the best fit from the fit line list, and export the visualization to report. Then, add display rules according to the value of Yield. Since VA shows the display rules at information panel, I create an image for colored markers to show them as legend in the visualization and put it in the precision layout.

There you have it. Matteson’s 18 visualizations recreated in VA.

How did I do?

18 Visualizations created by SAS Visual Analytics was published on SAS Users.


In this post I wanted to shed some light on a visualization you may not be using enough: the Word Cloud. Word association exercises can often be a fun way to pass the time with friends, or it can trigger immediate action – just think of your email inbox and seeing an email from a particular person: your boss, wife, husband or child. The same can be true for information for your organization. A single word can quickly, efficiently and effectively communicate the performance of a company’s metric, hence the value of using a word cloud visualization in your report.

Let’s look at some examples. Here I am using the Insight Toy data and looking at the performance of Products based on customer orders.

As the word cloud in SAS Visual Analytics 7.3 Designer has a maximum row return of 100, I have used the Rank feature to look at the top 25 Products and the bottom 25 Products. I also created a filtered interaction between the word clouds and their respective list tables below to show a bit more detail around the next level in the hierarchy after Product Make.

Notice how impactful these Product names are compared to when using their corresponding SKUs. Be sure to pick a meaningful category to represent your data in the word cloud.

This type of visualization could lead to a great comparison report, comparing what the top and bottom Products were for the same month in the previous year.

What if your data doesn’t have the appropriate column to display on a word cloud? No problem. In this next example, I took the value of Sales Rep Rating and created a new Calculated Data Item to represent values less than or equal to 25% to be Poor, inclusively between 26% and 50% to be Average and everything else to be Above Average.

Using a word cloud for this new category data item allows you to quickly move through the different states and compare the Sales Rep Performance frequency. You could also use this new category to compare each performance group’s Order Totals.

Here is California’s Sales Rep Performance:

And here is Maryland’s Sales Rep Performance:

These are two ideas for you to think about how you might include the word cloud visualization into your reports to help quickly and effectively represent the status of a company’s metric beyond the standard text analytics usage.

tags: SAS Professional Services, SAS Visual Analytics

Visualization Spotlight: Visual Analytics Designer 7.3 Word Cloud was published on SAS Users.


SAS® Viya™ 3.1 represents the third generation of high performance computing from SAS. Our journey started a long time ago and, along the way, we have introduced a number of high performance technologies into the SAS software platform:

Introducing Cloud Analytic Services (CAS)

SAS Viya introduces Cloud Analytic Services (CAS) and continues this story of high performance computing.  CAS is the runtime engine and microservices environment for data management and analytics in SAS Viya and introduces some new and interesting innovations for customers. CAS is an in-memory technology and is designed for scale and speed. Whilst it can be set up on a single machine, it is more commonly deployed across a number of nodes in a cluster of computers for massively parallel processing (MPP). The parallelism is further increased when we consider using all the cores within each node of the cluster for multi-threaded, analytic workload execution. In a MPP environment, just because there are a number of nodes, it doesn’t mean that using all of them is always the most efficient for analytic processing. CAS maintains node-to-node communication in the cluster and uses an internal algorithm to determine the optimal distribution and number of nodes to run a given process.

However, processing in-memory can be expensive, so what happens if your data doesn’t fit into memory? Well CAS, has that covered. CAS will automatically spill data to disk in such a way that only the data that are required for processing are loaded into the memory of the system. The rest of the data are memory-mapped to the filesystem in an efficient way for loading into memory when required. This way of working means that CAS can handle data that are larger than the available memory that has been assigned.

The CAS in-memory engine is made up of a number of components - namely the CAS controller and, in an MPP distributed environment, CAS worker nodes. Depending on your deployment architecture and data sources, data can be read into CAS either in serial or parallel.

What about resilience to data loss if a node in an MPP cluster becomes unavailable? Well CAS has that covered too. CAS maintains a replicate of the data within the environment. The number of replicates can be configured but the default is to maintain one extra copy of the data within the environment. This is done efficiently by having the replicate data blocks cached to disk as opposed to consuming resident memory.

One of the most interesting developments with the introduction of CAS is the way that an end user can interact with SAS Viya. CAS actions are a new programming construct and with CAS, if you are a Python, Java, SAS or Lua developer you can communicate with CAS using an interactive computing environment such as a Jupyter Notebook. One of the benefits of this is that a Python developer, for example, can utilize SAS analytics on a high performance, in-memory distributed architecture, all from their Python programming interface. In addition, we have introduced open REST APIs which means you can call native CAS actions and submit code to the CAS server directly from a Web application or other programs written in any language that supports REST.

Whilst CAS represents the most recent step in our high performance journey, SAS Viya does not replace SAS 9. These two platforms can co-exist, even on the same hardware, and indeed can communicate with one another to leverage the full range of technology and innovations from SAS. To find out more about CAS, take a look at the early preview trial. Or, if you would like to explore the capabilities of SAS Viya with respect to your current environment and business objectives speak to your local SAS representative about arranging a ‘Path to SAS Viya workshop’ with SAS.

Many thanks to Fiona McNeill, Mark Schneider and Larry LaRusso for their input and review of this article.


tags: global te, Global Technology Practice, high-performance analytics, SAS Grid Manager, SAS Visual Analytics, SAS Visual Statistics, SAS Viya

A journey of SAS high performance was published on SAS Users.

十一 152016

If your SAS Visual Analytics report requirements include linking out to separate reports without the need to pass values, you may want to consider using images to enhance the appearance of your base report. Here are three style examples using images that you can use depending on your report design requirements and report user preference:

1.     Visually appealing
2.     Generic
3.     Screenshot of actual report.

There is no better substitute for looking at examples so here are some screenshots for you:

1.     Visually appealing

Use images in SAS Visual Analytics

2.     Generic


3.     Screenshot of actual report


Image selection

Using an image in your report has never been easier. You can navigate your local machine for the image and, if you want, you can also save the image in metadata. This allows other users with access to that metadata location the ability to use the same image. This is great when you want to impose consistency throughout your reports.



Setting link using image

To define a report link from your image, click on your image then open the Interactions tab. Then use the New drop-down menu and select the type of link you wish to define. For a Report Link or Section Link, use the Browse button to navigate the metadata and select your target. If you are linking to an External URL then enter the fully qualified URL or you can define a link to a stored process.


Here is a breakdown of the report objects used in the main dashboard report. I also included the screenshots of my Daily, Weekly, and Monthly report examples.

Dashboard example breakdown


Daily report example


Weekly report example


Monthly report example




tags: SAS Professional Services, SAS Visual Analytics

Use images in SAS Visual Analytics to enhance your report link was published on SAS Users.


Requirements that are the most easily described can often be the most difficult to implement. I’m referring to requests like:

  • Display a gauge with the most recently collected metric.
  • Plot a 18 month rolling window of profit.
  • Display last month’s products percent of total metrics for visual comparison.

Okay, so these are pretty specific requests, which I built a report to answer, but none the less, requirements like these do exist.

Use Rank in SAS Visual Analytics

So, how do you implement these requests? Use rank! You might be wondering how this is possible since the rank feature requires a numeric value and these requirements are based on dates. Solution: use the TreatAs function. Let’s break it down step by step.

But first, here is a breakdown of the report objects used in this report. Notice that this report contains a section prompt via a button bar which prompts the user to select a Product Line. This section prompt filters all of the other objects by that Product Line value.


Step 1: Use TreatAs to create a metric from your date category

I am assuming that your data source has a date category. This will work with a date or date by month or date by year formatted data item. So long as the data item is recognized as a date then this technique will work.

This example will use the Date by Month data item. We will use the TreatAs function to create a metric, or in other words, a numeric representation of the date. That’s the great thing about dates in SAS, they simply represent the number of days before or after January 1, 1960. So the most recent the date, the larger the number, which we can then use rank to order.

From the Data tab, use the drop-down menu and select New Calculated Item….


Give your new calculated data item a name.

The result type will be numeric.

Under Operators, use the search window to find the TreatAs function; then drag that onto the visual pane. For the drop-down option, select _Number_.
Finally, drag the date data item onto the visual pane. In this example, we are using Date by Month


Step 2: Change the aggregation on your new measure to be non-additive

Next, we need to make sure this new metric that represents the Date by Month date is non-additive. We will not get the proper result if this new metric takes the sum or average when displayed on a visualization. To do this, navigate to the Data tab and click on the name of the new metric you created. In my example, I created a new metric named DateByMonthNum.

Toward the bottom of the Data tab are the data properties. Under the Aggregation property use the drop-down menu and select one of the non-additive metrics such as: Minimum, Median, or Maximum.


Step 3: Verify that your new measure returns the correct results

Now we can verify that when we rank our new measure, we get the expected results. To do this, I used a list table and added both the date data item Date by Month and the new metric data item DateByMonthNum. Here we can see that when I sort the metric data item by descending I get the expected results where each Date by Month value gives me a different DateByMonthNum value. I can also see that the more recent Date by Month value pairs to a larger DateByMonthNum value.


To be sure that you properly assigned a non-additive aggregation type, you can use the Show detail data property from the Properties tab. At the detail level you should see the same value pairs for the date and metric data items. Once you de-select Show detail data you should see the exact same value pairs. If you do, then you have correctly assigned your non-additive aggregation type.


Step 4: Use Rank to meet report requirements

Now that we have our metric properly created, we can use the Rank feature to display the last month’s metrics or a rolling window.

Last Month’s Metrics
In this visualization I used the Gauge Object.


On the Roles tab, I assigned Profit to the Measure role and Product to the Group role. I then created a five interval Display Rule between 0% and 50% at 10% intervals where anything over 50% is grouped together under the darkest green rule.


Now we must filter this visualization to display only the last month’s profit metrics; we do this by using the Rank feature. From the Ranks tab, you must first select the category data item you wish to subset by the rank. In our example, we want to display the last month’s metrics, so we will want to add a rank for the Date by Month data item. Once selected, click the button Add Rank.


Next we will need to select the metric we want to rank by. Next to the By drop-down; select our newly created metric DateByMonthNum. Then we will want to select the type of rank and how many to return. In this example, we will return the Top Count, i.e. the greatest value. And for the Count we want to return 1.


To help with the titling of the report, I added the exact same rank to a List Table object to display the data’s last month and to help report users know which month they are looking at.



Rolling 18 Month Window
The next visualization I created was a Line Chart Object plotting a rolling window of 18 month profit.


On the Roles tab, I selected Date by Month as the Category and Profit as the Measure.
On the Ranks tab, I selected the same values as I did for the list table and gauge objects, except I selected a Count of 18 to return the top 18 values of Date by Month ranked on our newly created metric DateByMonthNum. The rank will return the top 18 highest values for DatebyMonthNum which pair to the most recent 18 values for Date by Month giving us a rolling 18 month window.


Other Applications

In this example I used Rank at the month level but you could use this technique at the day level, quarter level, essentially for any supported date interval.

Assuming you have the proper data collected, you could also use Rank for the standard use of ranking the top X performing products, sales representatives or investment funds. You could also use rank to identify your bottom performing manufacturing equipment, car mileage, or school ratings.

Other Report Screenshots



tags: SAS Professional Services, SAS Programmers, SAS Visual Analytics

Use Rank in SAS Visual Analytics to display the last date, month or rolling window was published on SAS Users.


Calculate a moving average using periodic operators in SAS Visual AnalyticsThumbAnalysts often use a simple moving average to get an idea of the trends in data. This is simply an average of a subset of time periods, and the size of the subset can differ depending on the application. The technique can be used with data based on time periods, such as sales data, expense data, telecom data, or stock market data. The average is called ‘moving’ because it is continually recomputed as more data becomes available. This type of average is also called a ‘rolling average’ or ‘running average’. In this post, I’ll share a little bit about how to use the periodic operators in SAS Visual Analytics Designer to calculate a simple moving average.

The report below, created in the designer, shows the summary of the Amount column by month. The Three-Month Moving Average column displays the average of a month and the previous two months’ Amount sums. The 3-month sum is simply divided by 3.

Calculate a moving average using periodic operators in SAS Visual Analytics

This Three-Month Moving Average column is an aggregation, calculated using the RelativePeriod Periodic operator.  Both the visual and the text forms of the aggregation are shown below:

Calculate a moving average using periodic operators in SAS Visual Analytics2

Calculate a moving average using periodic operators in SAS Visual Analytics3

The RelativePeriod operator returns aggregated values - sum of Amount, in this case - relative to the current period – in this case, the previous month. The data item for the period calculation is Month, which is a date value with an associated YYYYMM format. The interval is _ByMonth_, and the 0, -1, and -2 offset values represent the current month, the previous month, and the month before the previous, respectively. The division by three creates the 3-month moving average, but the number of RelativePeriod expressions and the divisor could be adjusted to calculate an average based on any number of months.

The report below displays a 3-Year Moving Average column.

Calculate a moving average using periodic operators in SAS Visual Analytics4

In this report, the RelativePeriod operator is used to calculate the three-year moving average. The data item for the period calculation is Year, which is a date value with an associated Year format.

Calculate a moving average using periodic operators in SAS Visual Analytics5

The interval is _ByYear_, with the 0, -1, and -2 offset values representing the current year, the previous year, and the year before the previous, respectively. Again, the number of RelativePeriod expressions and the divisor could be adjusted to calculate an average based on any number of years.

Calculate a moving average using periodic operators in SAS Visual Analytics6

Calculate a moving average using periodic operators in SAS Visual Analytics7

The ParallelPeriod operator is used in the report below to display a 3-month moving average based on amounts corresponding to the same month in each of the three years. This report, of course, has missing values for all months up until the third year of data.

Calculate a moving average using periodic operators in SAS Visual Analytics8

Calculate a moving average using periodic operators in SAS Visual Analytics9

The first moving average value to be calculated is based on data from January 2010, 2011, and 2012. The measure is Amount and the periodic item for the aggregation Month. The inner interval for the aggregation is _ByMonth_ and the outer interval is _ByYear_. The 0, -1, and -2 offset values represent months of the current year, the previous year, and the year before the previous, respectively. These ParallelPeriod expressions and the divisor could also be adjusted to calculate an average based a different divisor.

Calculate a moving average using periodic operators in SAS Visual Analytics10

Calculate a moving average using periodic operators in SAS Visual Analytics11

Do keep in mind that for all of these periodic aggregations the ‘aggregation by’ column representing month or year must be included in the report.

I hope these examples using the periodic operators will be helpful to you in creating your Visual Analytics reports.

tags: business intelligence, SAS Professional Services, SAS Visual Analytics

Calculate a moving average using periodic operators in SAS Visual Analytics Designer was published on SAS Users.


For many people, building something from scratch, no matter how simple or complex, is fascinating. That’s why programs similar to How’s It Made are so appealing and, for me, addicting. And thus, the inspiration for this blog; I will walk you through building a set of graphs and how to improve each visualization through my own personal iterative process. Like all forms of art, a visualization is never complete, as constant improvement, tweaking and alterations are required to accommodate the constant influx of data and the ever changing needs of our audience.

These graphs use telecom data about cell phone network service including call duration and data usage.

Example 1: Calls versus Drops

In this first example, I noticed that the data contained the number of calls and the number of dropped calls. Like most analytics, audiences are interested in the outliers. In this case, we look at the poor performing occurrence of a call being dropped. This data would prove useful if a company wanted to research poor performing cell technology either of the handset itself or of the cell towers. It could also be used to find any dead zones, where additional towers may need to be added. In this example, I decided to plot the data against the 24-hour day to determine if volume of calls impacted the number of calls dropped.

Example 1: Iteration 1
Naturally, I started with a bar chart visualization. I plotted the hours of the day (24-hour scale) on the x-axis and the number of calls and the number of dropped calls on the y-axis. At first glance, it looks like there is some variation in the number of dropped calls and the hour of the day.

Visualize cell phone data in SAS Visual Analytics

Example 1: Iteration 2
Since the number of calls and the number of drops are of the same scale, we can easily take the ratio of the two to plot the call drop rate. The equation is to take the number of dropped calls divided by the number of calls. I used an aggregated measure to create this ratio, which will be evaluated on the fly, depending on the Group By variable. In this example the “ByGroup” is the hour of the day, and I used a Percent format.
Visualize cell phone data in SAS Visual Analytics2

This now gives us one bar to evaluate against the hours of the day. We can see that the Call Drop Rate does not fluctuate as much as the previous graph could lead one to believe.

I also added a reference line at 5% to make it easier to see which hour of the day fell below or above the 5% rate.

Visualize cell phone data in SAS Visual Analytics3

Example 1: Iteration 3
Finally, I noticed that the data contained a Cell Technology category. I thought it would be interesting to see if a certain technology was more unreliable than another. To do this, I added Cell Technology to the graph. I liked the visualization the best when I changed the bar chart to a horizontal orientation, used a row lattice for the Cell Technology and kept the 5% reference line. This now gives me an enhanced “quick glance” comparison ability to see that the 4G Cell Technology seems to have the most consistently high Call Drop Rate (over 5%) for all hours of the day.
Visualize cell phone data in SAS Visual Analytics4

Example 2: Call Duration

Example 2: Call Duration
In this second example, I used the Voice_Seconds data item to study the duration of calls over the course of the 24-hour day. This visualization could help determine what the peak hours of the day are for voice calls and potentially the best time to schedule any required maintenance to impact the least amount of customers.

Example 2: Iteration 1
Again, I stared with a bar chart visualization where I plotted hours of the day on the x-axis and the Voice_Seconds on the y-axis. The first thing I noticed was that at hour 20 there was a peak _SUM_ of over 3 million Voice_Seconds. This immediately prompted me to want to find out how long 3 million seconds was and that I need to look at the average of Voice_Seconds.

Visualize cell phone data in SAS Visual Analytics5

Example 2: Iteration 2
The first thing I did was create another aggregated measure to produce the Average Call Duration. To do this I took the sum of Voice_Seconds divided by the sum of the number of calls for a By Group.

Visualize cell phone data in SAS Visual Analytics6

The next thing I wanted to do was provide a reference point for how long 1,000 seconds is in minutes. Granted, I could convert Voice_Seconds into a new metric but instead I decided to use a reference line where 900 seconds equates to 15 minutes.

Visualize cell phone data in SAS Visual Analytics7

Example 2: Iteration 3
Lastly, I wanted to see if the type of Cell Technology had any impact on the distribution or length of call, mostly just because I was curious.  I was surprised that this data shows an average call of 15 minutes. That’s a long personal call when I consider most of my calls consist of “are you on your way?” and “we forgot x at the store – please pick it up on your way home”.

If this were call center data you would be able to determine how quickly issues were getting resolved. If this were sales call data, and representatives were following a script, this visualization would show, on average, how long those calls took and maybe the longer calls would result in a sale. So you could see which hours of the day sold more product.

Visualize cell phone data in SAS Visual Analytics8

Example 3: Data Usage

In this third example, I explored the data usage. I had two data items available for use: mbytes_up and mbytes_down. This visualization could help determine peak hours for which to perform system upgrades or maintenance. It could also help identify those peak hours and then add tower locations to help determine if additional hardware could help network speed performance.

Example 3: Iteration 1
I started with the bar chart visualization and plotted the hours of the day on the x-axis and the mbytes_up and mbytes_down on the y-axis. Again, the first thing I noticed was that I was looking at the _SUM_ for the metrics and the large difference in the numbers for up versus down usage.
Visualize cell phone data in SAS Visual Analytics9

Example 3: Iteration 2
The first thing I did was convert the megabytes to gigabytes by creating new calculated data items and then I created the Average Upload and Download by creating aggregated measures.

Here are the two metrics I created for Upload:
Visualize cell phone data in SAS Visual Analytics10

And here are the two metrics I created for Download:

Visualize cell phone data in SAS Visual Analytics11

This makes the visualization a bit easier to consume, now that we can compare the average upload or download size per session. I also added two reference lines at 5 GB and 15 GB. I still felt like the data needed to be visualized a bit better to understand the usage since I know most typical cell phone plans allow for 5 GB of data per month and the average session using more than 15 GB, it just seems like a lot.

Visualize cell phone data in SAS Visual Analytics12

Example 3: Iteration 3
To further classify the data I added Cell Technology to the visualization and broke it into two visualizations: one for upload and one for download. Once I did that, the visualization really started to show different data usage patterns.

Both visualizations show the 4G technology doesn’t even reach 5 GB, which makes me think that the customers with new phones and new service plans are sticking to their data allowance. But the customers with the older technology of 3G may be “grandfathered” in with their unlimited data plan and making the most of it.

Average Upload (GB)
Visualize cell phone data in SAS Visual Analytics13

Average Download (GB)

Visualize cell phone data in SAS Visual Analytics14

This blog has taken you through three examples of how I iteratively develop visualizations using SAS Visual Analytics. Ultimately, what this process shows you is that the more specific business question you have, the better a visualization you can create.

tags: SAS Professional Services, SAS Visual Analytics

Steps to visualize cell phone data in SAS Visual Analytics: Can you hear me know? was published on SAS Users.


After posting a couple of blogs on the subject of dates and date formats in Visual Analytics Designer, I got a question from a user who wondered how to compare data for a selected date to data from the same day of the previous year. Here’s one way to do this.

The example report enables a user to type in a date value in a variety of formats and displays the sale amount for the specified date, along with the sale amount for the same day of the previous year.

Working with dates in SAS Visual Analytics

The data source includes information on thousands of orders. Irrelevant data items have been hidden, with the items of interest shown below. Transaction Date has an associated MMDDYYYY format and Transaction Weekday is simply a duplicate of the date with an associated Day of Week format.

Working with dates in SAS Visual Analytics02

A parameter, Param date, is associated with the Parameter role of the Text input field and will store the value typed in the field.

Working with dates in SAS Visual Analytics03

Working with dates in SAS Visual Analytics04

Several calculated data items are created for ‘behind the scenes’ filtering and Ref Date is a data item that will store the date converted from the entered text version of the date.

Working with dates in SAS Visual Analytics05

Note that the ANYDTDTE informat is a great one to use when you are uncertain as to exactly how users will be typing in a date value.
Ref Date (Yr-1) is the ‘same day a year ago’ date.

Working with dates in SAS Visual Analytics06

A filter on the list table completes the report:
( Ref Date = Transaction Date ) OR ( Ref Date (Yr-1) = Transaction Date )
The data items below are now used to populate the report showing the data for the specified date for comparison with the data for the same day of the previous year.

Working with dates in SAS Visual Analytics07

Working with dates in SAS Visual Analytics08

With the addition of a few more calculated data items and filters, some additional report objects can offer alternate ways of displaying the data.
Prod Sale (ref date) and Prod Sale (Yr-1) are calculated as below:

Working with dates in SAS Visual Analytics09

The addition of the two new data items allow the information to be presented in the list table below, with this filter applied:  (Prod Sale (ref date) NotMissing OR Prod Sale (Yr-1) NotMissing)

Working with dates in SAS Visual Analytics10

The same filter (Prod Sale (ref date) NotMissing OR Prod Sale (Yr-1) NotMissing) can be applied to a crosstab object to produce the result below:

Working with dates in SAS Visual Analytics11

The addition of one additional calculated data item, Date, and a new crosstab object with a filter on Date, enables still a different display.

Working with dates in SAS Visual Analytics12

I found this to be an interesting example–both the problem and the solutions. I hope this blog will give you more ideas about using your dates to the best advantage in report.

tags: SAS Professional Services, SAS Visual Analytics

Working with dates in SAS Visual Analytics: Comparing to the previous year was published on SAS Users.


Reference lines on a visualization are used to help identify goals or targets, acceptable or unacceptable ranges, etc; basically any metric that puts a frame of reference around the values on the visualization.

The Percent of Total of a metric is used to help identify a part-to-whole relationship. It answers the question, how much of the whole does this piece represent?

In this blog, let’s take a look at how you can use both the Percent of Total metric and Reference Lines  to enhance your data visualizations using SAS Visual Analytics.

Example 1

In this section, we are reporting on the Percent of Total for Revenue. First, look at the single select List control object. You’ll see I have displayed the available Product Lines and their corresponding frequency percent. This allows the report viewer to quickly understand the number of rows associated with that Product Line when compared to the whole of the data.

Next, under the List control object, we have a Stacked Bar Chart graphing the Revenue (Percent of Total) which allows the report viewer to understand the part-to-whole relationship of the Products that make up that Product Line. While we can clearly see that the Board Product, colored in blue, is outperforming the other two Products, it may be difficult to tell which remaining Product is pulling in the higher Revenue.

The Grouped By Bar Chart in the middle of the report can be used to quickly compare the performance of each Product. I added a reference line so that the report viewer can quickly identify which Products are pulling in more than 25% of the Revenue for that Product Line.

Percent of Total and Reference Lines

Here are some additional views from this report:

In this view, we have selected the Action Figure Product Line and it makes up 57.32% of the Frequency Percent. We can see that not one individual Product in the Action Figure Product Line reaches 25% of the Revenue’s Percent of Total and that they are all hovering near 15%. By using the Revenue (Percent of Total) metric all of the data is normalized and the static reference line allows for quick and easy comparison over all of the Product Lines.
Percent of Total and Reference Lines

And in this view, for the Promotional Product Line which makes up 7.05% of the Frequency Percent of the data, we can see that there are a few Products that are outperforming the others. As these are promotional products this can be expected as trends and styles fluctuate.

Percent of Total and Reference Lines

After examining this report about Revenue (Percent of Total), you can easily think of other reports that would be useful to an organization. For the high and low Revenue (Percent of Total) values, are the number of employees assigned to the Products and/or Product Lines appropriate? What about the Expenses both Operational and Material for your most and least revenue generating Products and/or Product Lines, is the spending reasonable? Are the Product Material Costs justified?

Using a part-to-whole comparison visualization can help identify other areas of business that may need further investigation.

Example 2

Here is another report example using the Revenue (Percent of Total) metric with reference lines. In this example, we plotted Revenue (Percent of Total) against the months of the year. Here we can see how the Revenue (Percent of Total) is dispersed across the months. I’ve also added a Percent of Total and Reference Lines

As the Promotional Product Line lends itself to the most fluctuation, we can see the breakdown of the Revenue (Percent of Total) and how it maps to the different months. Like the other report, this can then lead to additional reports to answer questions of if there is any seasonality to the spikes, or pair these findings with other market events. The Revenue (Percent of Total) for iPhone Covers is higher in July 2011, was there a new iPhone release that month? It may also be good to learn what was happening in April 2011 as the Revenue (Percent of Total) for Backpacks increased.

Percent of Total and Reference Lines

Combining the results of these reports with other groups in the organization can help determine which business decisions are having the desired impact on the bottom line results. Are the marketing strategies effective? Are the planned expense reductions are being met? Are we making better use of our product material waste?

Reference lines can help by making it easy to quickly identify whether or not targets are being met. And by using the Revenue (Percent of Total), a single reference line can be used across several categories since the scale has been adjusted to 100%.

tags: business intelligence, SAS Administrators, SAS Professional Services, SAS Programmers, SAS Visual Analytics

Use Percent of Total and Reference Lines to ask better business questions was published on SAS Users.