How to customize data export in SAS Visual Analytics?

2 Likes

Using SAS Visual Analytics, you can export data from the different visualizations. While it is an acceptable option for most cases, you may sometimes want to customize the export. Whether you are willing to export raw data in CSV or Tab delimited format or create a PDF, you often want the exported information to consider the data selection from the report you are viewing.

In this article, I will explain how you can retrieve the data selection from your report and generate a customized output based on the selection. The resulting implementation will be a report which contains a Data-Driven Content object which uses a SAS Viya Job to generate the output and retrieve the generated output using SAS REST APIs.

What is a Data-Driven Content object?

From SAS documentation:

The data-driven content object enables you to display your data in a custom third-party visualization within your SAS Visual Analytics report. The third-party visualization can be authored in any JavaScript charting framework, such as D3.js, Google Charts, or CanvasJS. The visualization in a data-driven content object receives its data from SAS Visual Analytics and interacts with filters, ranks, and actions

in the same way as other objects in your report.

In addition to third-party visualizations, you can display any web content that is capable of being displayed in an IFrame.

The biggest benefit of the Data-Driven Content object is the data that is passed from the report to your custom web page. Accessing the same data as the report allows the web developers to implement functionalities which would otherwise not be available in SAS Visual Analytics.

Challenge

While working with Data-Driven Content object makes it easy to work with the same data as any other object in the report, working with parameters and filters is a bit more challenging because they are not passed directly to the web page defined for the Data-Driven Content object. As a result, you need to find techniques to:

Pass the parameters
Pass the filter information

Solution

Passing parameters to the Data-Driven Content object can be easily achieved by defining filters on the Data-Driven Content object using the parameters. Sometimes, you should be creative in the filter definition especially if you don't want the data to be filtered. You may in that case create a dummy filter like: 'Sex'n not in ["State"p].

As you can imagine, the filter will have no impact on the data available in the Data-Driven Content object, but the side effect will be to pass the State parameter to the Data-Driven Content object. This will allow you as a developer to use that parameter for further processing. This trick is particularly handy when you want to pass parameters to a job without filtering the data.

Passing filters is a bit trickier in the sense that even if you can define a filter based on a parameter, only the parameter is passed to the Data-Driven Content object. It means that you have no option to get the information about the operator used to filter the data. There is no real solution to this except using a naming convention.

In this article, I've decided to use the following naming convention for the parameter names: VariableName Parameter __ ComparisonOperator.

The VariableName is the variable label for the variable on which the parameter is based.
Parameter is a place holder to indicate it is a parameter.
__ (double underscore) is a separator.
The ComparisonOperator is the operator used to compare the parameter value with the variable value.

For example, the parameter "Age Parameter __ >=" will be interpreted later as: 'Age'n >= 'Age Parameter'p. This conversion can be done in some JavaScript code or in SAS code as we will see later in this article.

It is far from ideal, but it serves our purpose of passing the filter information to the Data-Driven Content object. Using naming convention is more probably not a robust option and needs to be documented and agreed upon by the different developers.

Demo

(view in My Videos)

Implementation

Now that you have a better understanding of the challenges and the solutions, I can go ahead and explain how to proceed.

Building the report

The report will be two pages.

Page 1 will display the filters and parameters to apply to the data and a link to a second page
Page 2 will be a Pop-up page with a link to download the filtered data

The data source is the SASHELP.CARS table which has been loaded into CAS upfront. The report looks like this:

Select any image to see a larger version.
Mobile users: To view the images, select the "Full" version at the bottom of the page.

The Pop-up page is named Export Data (see picture above). It contains a Data-Driven Content object to which the variables to be exported from the CARS table have been assigned. The web page that is used in the Data-Driven Content object, relies on the list of columns to decide which variables should be exported. You could, in a different design, prompt the user to select the variables, but this example does not cover that option.

As you can see in the screenshot, the report uses different prompt types. Some are report level prompts while others are specific to the page. They all have one thing in common. They have been assigned a parameter which follows the naming convention described earlier in this article. Here are two examples:

As you can see in the screenshot above, only the Invoice Parameter has an operator defined in the parameter name. By convention, it is only required to specify the operator when it is different from = or in operators. You should now have a better understanding of the main page of the report, the Export Data page only contains the Data-Driven Content object. The page will be opened when the user clicks on the Export Data link which was created using the Text object:

For more information about Page Link.

Let's look at the Data-Driven Content object properties. Besides the URL which points to the web page that will be covered in the next section, one filter for each parameter must be defined. Without this filter, the Data-Driven Content object will not receive the information about the defined parameters. Without that information, the content which will be exported will not be synchronized with the selection in the report.

What you may notice is that the last filter uses a Source Table parameter. This parameter has not been covered earlier as it was not linked to a prompt. This parameter is used to store the information about the source table for the export. It is defined like this:

Building the web page

Now let's see how the web page is created.

If you take out the JavaScript part of the file, you only have this simple structure:

The body tag contains a single div which will display the status of the request and the when the job ends the link to the file or an error.

The script tag is where the logic of the application is. It contains different functions:

The executeJob function will execute the SAS Viya Job and pass the needed information to generate the export file.

The generateDownloadLink function manipulates the information received from the job to build a link which will download the file.

The onMessage function is the orchestrator who will:

read the data from the VA report,
transform the received data into a usable format for the SAS Viya Job,
execute the SAS Viya Job
trigger the download link generation
inform the user about the status throughout the distinct stages

The last piece of code defines that the onMessage function should be executed when the page receives data from the VA report.

The complete source code can be found in this GitHub repository.

Creating the SAS Viya Job

At this point, the logic in the user interface has been defined. The missing piece is the SAS code which will be executed to generate the export file. The web page sends the data in JSON format which SAS reads using the JSON libname engine. Here is the description of the different steps:

Read the JSON data
Retrieve information about the Source Table and store it in macro variables: caslib and castab
Generate the filter statement which will be used in a where clause and store it in a macro variable: filterValues
Define a session to the CAS server and a library using the CAS libname engine
Generate a macro variable to store the list of variables to be retrieved
Extract data from CAS
Write the export file in the content server
Send information about the file location using proc json

For this example, the export file is a tab delimited file. If you prefer to generate a CSV file, or even a PDF, it is possible. For the CSV, you should replace the dbms to csv on line 41. For the PDF, you should replace the proc export with the procedures you want to use to display the data surrounded by an ODS PDF statement.

Note: to improve the user experience, I recommend configuring the compute server used to execute the job to be reusable. For more information, see Improving Concurrency Performance in SAS Viya

Conclusion

When it comes to exporting data, SAS Visual Analytics built-in functionality is suitable for most usages. Nevertheless, if you want to generate a specific PDF file or do some extra manipulation to your data before exporting, you can use SAS Viya Jobs. Using the technique described in this article, you have the basic process and code to export any type of data into any format.

You should nevertheless be careful when exporting data. CAS tables can be big in terms of rows and columns. It means you as a developer should take the table size into consideration when providing customized export functionality to your end-users. I would for example encourage you to set inobs and outobs options in the proc sql to generate the export table.

Now that you have the basic concepts, you can design any export type that suits your needs.

The code for this article can be found here.

Find more articles from SAS Global Enablement and Learning here.