A common aspect of data analysis is being able to see the underlying data and examine it when an investigation is needed. When it comes to using visualization tools like SAS Visual Analytics (VA), users sometimes want to have that underlying data available to them on the screen.
Often they will opt to put list tables at the bottom of their reports so that as they are filtering and interacting with the report, the list table gets updated with the source rows of their selections. While this is convenient, it is not exactly a good practice when using visualization tools since you are taking up valuable space with just rows of data.
What you can do instead is create a link that downloads the source data when you are ready for a deeper investigation. Not only does this link provide you with the detailed source data, but through the use of parameters in SAS VA, the data can be filtered as well so you are only seeing the subset that you want.
In the following step by step example, we have a report based on the sashelp.cars dataset and build out an external link in SAS VA that calls a stored process to output the source data that is filtered based on what the user has selected.
Create a Stored Process
The first thing that you will want to do is create a stored process and set your ODS destination to CSV. Also within the stored process, you will want to create a macro that will query the source dataset.
In the macro, you will want to declare global macro variables for the fields that you want to filter on in your report. For the CARS dataset, we are using the following variables:
Now in order to correctly build the query that filters out the data you don’t want, you will need to create a dynamic where clause with these global variables. Steps to create that are here.
Create Parameters
Now we need a way to pass these values around as selections are made in SAS VA, parameters are one way to do that. You can go into your report and create parameters for each one of the global variables that were created in the previous step. In this case, we are using all categories so all of the parameters are set to the character.
Create Calculated Items for Parameters
When linking to a stored process only calculated items can be passed to the global variables. Therefore, we need to create calculated items for each of the parameters that were just initialized. Creating the calculation is easy since you can just use the parameter as the value.
Example of the DriveTrain variable is shown below.
Designate Parameters to Control Objects
Now that we have the parameters and a way to pass them to the stored process, we need to set the parameters so that they get populated as users work within the report. Parameters can be set through control objects and in this example we are using all category variables so drop-down lists will be used. In the roles tab for each object is where you can set the respective parameter.
Create a List Table
The final piece of this process is linking to the stored process. This needs to be done through a list table in order to pass all of the values. First, a new calculated item is to be created for the text that you want to be shown for the link. In this example, we created a calculated item named “Option” with the value of “Click to Download Data”.
Then all of the other calculated items that were created for this process are put into the list table as well. You can see in the screenshot below that as the selections are made in the drop-down lists, the calculated items in the list table get populated with the values. This how they are passed through the parameters.
Now the interaction has to be set up. When clicked on the list table, you will go to the interactions tab and click on New>External Link. This is where you will click on the “Link to Stored Process” button and find your stored process.
For the final step, all that is needed is to add parameters for each of the global variables that we have been creating throughout this process. Don’t confuse this with the VA parameters we created earlier, these parameters are passing things outside of SAS VA. You will want to use the calculated items that we put into the list table as the source and the target is the global macro variables that were created in the beginning.
Since the stored process is now linked, the list table now set as a link and can be cleaned up a bit. The reason why we created the Option calculated item earlier is so that we can display that text for the link and that’s all. In this example, all of the dropdowns were put into a horizontal container at the top with the list table then getting put on the end. You can see that it barely looks like a table at all and it’s an easy link to get to.
Now that everything is linked up, you can go ahead and try it out. Based on the selections that we made when the link is clicked we get this output below:
Notice that it is only data relevant to the selections made and contains all of the fields. This could be expanded even more if you had a large table that you trimmed down to put into VA. Then when the data is downloaded, you can use the original large table as the source so that you can have all of the fields available to analyze.
Conclusion
There was a lot of passing going on throughout the steps below is a diagram showing how you start with the user selections in the control objects and how that gets passed all the way to the stored process.
While not the most conventional route, this is a great way for a user to be able to get the filtered source data quickly without it taking up space in the report.
If you are interested in learning more about what parameters can do in SAS VA, Craig has a great post that outlines how you can use them to control objects within your report.