Tips for working with multiple data sources in SAS Visual Analytics
Long gone are the days where a SAS report is restricted to a single data source. Now you can have an unlimited number of data sources but don’t treat this as an all you can eat buffet. Just as your stomach has limitations so does your SAS environment. What’s the recommendation? Well it will depend on several factors such as:
- size of your environment
- size of your data
- number of users
- report design
but the ultimate goal is that you want responsive meaningful reports. I want to focus on the aspect of working with multiple data sources in a SAS Visual Analytics (VA) report. But for overall report design consideration SAS put together a guide, Beautiful Reports, and it contains great ideas for designing effective reports.
If you are new to SAS Visual Analytics then it can be a little overwhelming to figure out the exact click pattern to maximize your efficiency and eliminate frustration. Keep reading and soon you’ll master the order of operations in SAS VA just as you did for math using PEMDAS: Please Excuse My Dear Aunt Sally. Though in finding the Wikipedia link for reference, I learned other parts of the world use different mnemonics!
Add a Data Source to a VA Report
From the Data pane use the Data menu and select Add data.... Then use the Choose Data window to select the data source to add to the report.
Select any image to see a larger version.
Mobile users: To view the images, select the "Full" version at the bottom of the page.
Or you can use the drop-down arrow and use the + Add data… option. From the drop-down arrow you can see all of the data sources that have been added to this VA report.
Object Tips
SAS Visual Analytics Objects can only have one data source. While the SAS VA report can have multiple data sources each individual object can only resolve data items from one data source. The easiest way to toggle between data sources and assign data roles, is to:
- Select the target object – critical first step!
- Select the desired data source from the Data pane
- Use the Assign Data button on the object or use the Roles pane
Prompting with Multiple Data Sources
When you have multiple data sources added to a VA report, you may want a single prompt, called Control Objects to filter more than the source data source. To do this you will need to Map data sources.
Check out this article here for additional examples: SAS Visual Analytics 8.1: Configuring prompts with different source data: Article | YouTube.
There is a report wide Map data... setting available from the Data menu where you can define the column mappings between each of the VA report data sources.
Combining Data Sources
There may be a time when you really want to include more than one data source in a single object, say a List table or Crosstab. What are your options? Answer: create a new data source by joining the tables. Not familiar with joining tables in SAS Visual Analytics, then check out this article here: VA 8.3 Join Tables in VA Reports.
I won’t rewrite the entirety of that article here but give a few additional pointers. Remember that joining tables will result in the combination of additional columns and will not append rows based on join criteria. In my current report example, let’s say I want to join the two regions North and East. I’ve filtered all the data for the year 2020 so that it’s easier to see, here is what the source tables look like and then the resultant join.
There are missings in our join result because we are joining two disparate values for Facility Region. Even though Facility Region was listed in the join criteria, there is never a row in the North data source that would equal East and that is why you are seeing the sparse result.
Creating a few basic Calculated items we can get the desired result and have our two separate data sources appear as one in a single object.
To come full circle with the example, if you had one Facility Region, West where there were two separate data sources for Profit and Expense then this join result would look like this.
Ultimately, always take the time to double check your join result numbers using a List table object against the incoming data sources.
Adding Footer Information about each Data Source
If you’re not familiar with the Text object check out this article: Using Dynamic Text in a VA 7.4 or 8.3 Report. Two of the neat features of the Text object I want to mention here are the Table Modified Time and Interactive Filters dynamic auto populated information. These pieces of information are helpful to communicate either the status of the data or what filters are being applied to the data to report users.
The key point you need to keep in mind is that you cannot put multiple data source information in a single Text object. You will need one Text object per data source if you wish to add this information to your report. In my example below, I placed three Text objects along the bottom of the report to create a footer.I added the border style in black so that you could see the three different objects easily, if you did not add this it would look like a nicely spaced single footer.
In this screenshot, I also used different color font to highlight the point that these are different data sources with different table modified times, i.e. when the tables were last updated in CAS.
Summary
You should now have a better understanding of how to work with multiple data sources. Bear in mind that your computing resources are finite and while you can have an infinite number of data sources, try to stick to a hand full and link out to other reports once you hit your maximum acceptable response time.
You should have the order of operations memorized for assigning Data Roles :
- Select the target object – critical first step!
- Select the desired data source from the Data pane
- Use the Assign Data button on the object or use the Roles pane
And link out to these resources for additional examples and step-by-step instructions:
- SAS Beautiful Reports
- SAS Visual Analytics 8.1: Configuring prompts with different source data: Article | YouTube
- VA 8.3 Join Tables in VA Reports
- Using Dynamic Text in a VA 7.4 or 8.3 Report
Here’s a look at our example report with each of these techniques applied.
Demo
I have recorded an accompanying video tutorial for these examples so that you may follow along. Though the video uses SAS Visual Analytics on Viya 4, similar steps can be used for earlier releases including SAS Visual Analytics 7.4 and later, but the Data Join feature is available for releases 8.3 and later.
Find more articles from SAS Global Enablement and Learning here.
