Craving More Data? SAS Visual Analytics Designer Can Create it for You

[#1]

When building a report, have you ever wished you had more data to work with? For example, you're using a numeric variable and want to easily categorize the measure's numeric values into groups? In SAS Visual Analytics Designer you can! In this post, I'll show you how.

For today's example, I'll be using data from the USGS Water Service that I mentioned in my previous post: Is summer over already? Time to create a custom vector plot in SAS Visual Analytics! 

The dataset has three variables:

• Date - Date of observation
• AVG_of_Level - Average Water Level on the Date of Observation
• AVG_of_Precip - Average Precipitation on the Date of Observation
• AVG_of_Water_Temp - Average Water Temperature on the Date of Observation
  
  

Since there are 104 days of readings (or 104 observations in the dataset), graphing the data can be a challenge. I could create a bar chart (Date on the X axis and AVG_of_Water_Temp on the Y axis), but since there is so much data to display, this can look a little bland:

Wouldn't it be great if we had just one more categorical variable that grouped all those temperature readings into categorical groups? Using this new variable (let's call it "Temperature Range"), we could add additional information to our graph and make it look a lot better.

Creating this dynamic variable "Temperature Range" is possible within SAS Visual Analytics Designer. It's actually quite easy! All you have to do is create a new calculated item with a formula.

Start by clicking the down-arrow on the data tab on the left hand pane of SAS Visual Analytics Designer. Then, click "New Calculated Item."

On the next window, name your new calculated item "Temperature Range." Also, change the "Result Type" to "Character" and switch to the "Text View":

In the formula field, input the following:

• IF ( floor('AVG_of_Water_Temp'n) < 10 )
  RETURN '0-9'
  ELSE RemoveBlanks(Concatenate(Concatenate(Concatenate(Substring(Format(floor('AVG_of_Water_Temp'n), 'COMMA3.0'), 1, 2), '0'), ' - '), Concatenate(Substring(Format(floor('AVG_of_Water_Temp'n), 'COMMA3.0'), 1, 2), '9')), _All_)

Press OK.

Great!  Now you have successfully built the new character variable "Temperature Range." 

But what does this formula do? Essentially, it will dynamically categorize the numeric values of "AVG_of_Water_Temp" into groups of 10. For example, the numeric value of 25 will be returned as "20-29."

If we look at the formula it starts with the text:

• IF (floor( 'AVG_of_Water_Temp'n) < 10 ) RETURN '0-9'

This Boolean logic states that if (for any observation) the value of "AVG_of_Water_Temp"n is less than 10 (say 8 degrees), it returns the character string "0-9." Simple enough. We use the floor option to ensure that there are no rounding issues with the numeric values.

The formula continues on to state that for all other values, convert the numeric value into a character string. The numeric value of 25 would be returned initially as "25."

• Format(Floor('AVG_of_Water_Temp'n), 'COMMA3.0')

Then take that text string and remove the final character. The numeric value of 25 is now being returned as "2." This is achieved by the substring function.

• Substring(Format(Floor('AVG_of_Water_Temp'n), 'COMMA3.0'), 1, 2)

The next step is to place a "0" after the "2" which was returned. We will use the Concatenate function to achieve this. Notice how we simply concatenate the dynamically returned "2" with a hard coded "0" to create "20."

• (Concatenate(Substring(Format(Floor('AVG_of_Water_Temp'n), 'COMMA3.0'), 1, 2), '0'))

Now for the numeric value of 25, the formula will return as "20."  Similarly, for the numeric value of 37, the formula will return as "30." Visual Analytics Designer is dynamically CREATING data for us. Exciting!

Looking back at the original formula, we using similar concatenate logic to place a space, dash and other space: " - " after the first returned character string:

• (Concatenate(Concatenate(Substring(Format(floor('AVG_of_Water_Temp'n), 'COMMA3.0'), 1, 2), '0'), ' - '))

Now the formula returns (again for the numeric value of 25): "20 - "

Finally, using similar logic as above, we place a final character string at the end of our calculated item. Except this time, instead of placing a "0" at the end, we will place a "9."  

• (Concatenate(Concatenate(Concatenate(Substring(Format(Floor('AVG_of_Water_Temp'n), 'COMMA3.0'), 1, 2), '0'), ' - '), Concatenate(Substring(Format(Floor('AVG_of_Water_Temp'n), 'COMMA3.0'), 1, 2), '9')))

And that's the formula! We have created logic that returns a nice text string for each numeric value. The original goal of building something that for the numeric value of 25 returns the character string "20-29" has been achieved!

To see what our formula looks like in action, click the "Preview" button on the "New Calculated Item" wizard. You will see how the AVG_of_Water_Temp values are correctly grouped with our new variable "Temperature Range":

Optionally, you can remove the "RemoveBlanks" function I have added to the formula.  

What does our new calculated item look like in our report? It can easily be shown by creating the same bar chart that we had before. Except this time, we will add our new column "Temperature Range" as a group variable:

Our new calculated item (that Visual Analytics Designer created for us) is already making our report look better.

We could additionally create a second bar chart, but this time to show the Frequency of temperatures by "Temperature Range":

The best part about the formula in this post is the fact that's DYNAMIC. Meaning not only does it return correct categorical values for AVG_of_Water_Temp, but it can be used to create dynamic character strings for ANY numeric column within the data. For example, we could create a second calculated item named "Precipitation Range."  Here is the formula for it:

IF ( Floor('AVG_of_Precip'n) < 10 )
RETURN '0-9'
ELSE RemoveBlanks(Concatenate(Concatenate(Concatenate(Substring(Format(Floor('AVG_of_Precip'n ), 'COMMA3.0'), 1, 2), '0'), ' - '), Concatenate(Substring(Format(Floor('AVG_of_Precip'n ), 'COMMA3.0'), 1, 2), '9')), _All_)

 And here is the formula preview:

As you can see, having SAS Visual Analytics Designer create dynamic categorical groups from your numeric variables is a very powerful tool. Not only does it give the report creator more data to use, but it can help them create more visually appealing reporting. It should be noted the the formula in this post is built for numeric values that are less than 1,000. However, it would be easy to create a modified version of the formula to accommodate for higher values.

How to make the example work for you:

This example was created in SAS Visual Analytics 7.3. Attached is a SAS package file containing the sample report and data in csv file format. Load the csv file into memory on your Visual Analytics instance and you will be able to import this package if you have access to the SAS Management Console 7.3. Please refer to these instructions.

If you do not have access, contact your SAS Visual Analytics Administrator. Be sure to select the report when importing.

Closing note: The data in this example are static (the dates are from April); however, there is potential to connect this graph to an automated feed from the USGS Water Service. To see how to connect the two, use the FILENAME URL method I demonstrate in my 2012 SAS Global Form Paper, "There’s an App for That”: It’s Called SAS® ODS! Mobile Data Entry and Reporting via SAS ODS.