Introduction to Integration of SAS Visual Analytics with SAS Jobs via Data-Driven Content-Part 1
Since when Data-Driven Content objects where introduced to SAS Visual Analytics, I’ve been using them to solve different problems, but recently I’ve noticed a shift or a trend on the challenges that I’ve been exposed to, where the solution comes from a combination of Data-Driven Content and SAS jobs. The goal of this series of articles is to share with you some of the things I’ve learned and some of the interesting use cases. All the examples are available in GitHub - see References at the end of this article.
All articles of this series have a special focus on passing tables to SAS jobs and how those tables can be consumed by those jobs, but if you only need to pass key/value pairs from SAS Visual Analytics to SAS jobs, you can still benefit from the series. Part 1, this article, explores passing key/value pairs by appending them to the SASJobExecution Web App URL (GET HTTP call). Part 2 and beyond shows how to pass them in the HTTP request body (POST HTTP call) through a series of formData.append commands. If your goal is to use SAS jobs to produce visible content to be displayed in Visual Analytics, parts 1 and 2 are what you need. If your goal is to use SAS jobs to create CAS tables on the fly that Visual Analytics can optionally consume to create the visuals with its native objects, then you should also read part 3 and beyond.
High Level Topology / Overview
Explaining SAS jobs is beyond the scope of this article, but simply stated, SAS job in this context is a SAS code made available to be executed via SAS Job Execution Web application. In other words, you can make HTTP calls to the SAS Job Execution Web application to execute SAS code and pass parameters to it. This is somehow like using SAS Stored Process Web Application to execute SAS Stored Processes in SAS 9 - I can picture those of you that are familiar with Stored Processes already having some sparkling ideas.
The fact that you can execute SAS code via HTTP calls, and you can make those HTTP calls from JavaScript within Data-Driven Content (DDC), suddenly becomes a powerful option to solve interesting problems in SAS Visual Analytics. This is because other SAS Visual Analytics report objects can seamlessly pass information to DDC objects, which in turn can pass information to SAS jobs and execute SAS code.
You don’t really need a DDC object to execute a SAS job. A Web Content object could also be used for this task, but if you want to be able to have the job to respond to clicks and actions in other report objects, then you will need a DDC.
You can use the SAS job to transform the data, perform complex calculations, generate charts, read and write information from/to tables, and anything else you can do with a SAS code. Here are some ideas about how the output of the SAS job could be leveraged by SAS Visual Analytics:
- the output of the SAS code can be displayed back in the report by the DDC, in case a visible output is generated, such as SAS ODS output, or it can be downloaded as a file (CSV, PDF, etc.)
- the code can return data back to the DDC in JSON format for example, and the DDC can use third party visualization packages to display the results, using visualizations that are not available in SAS Visual Analytics - this is the more traditional use of DDC, but the SAS job adds value by simplifying data access and data transformation needs
- the SAS code can store the results as tables (relational databases, in-memory, etc.) for consumption by other applications or the same SAS Visual Analytics report
- etc.
I hope by now you are realizing how powerful the combination of DDC with SAS job can be.
Note: if you want to know more about Data-Driven Content, I suggest reading my other article called Data-Driven Content: leveraging third-party visualizations in SAS Visual Analytics, among many others available in SAS Communities.
A Bit More on SAS Job Execution Web Application and SAS Jobs
SAS Job Execution is the Web application used not only to execute SAS jobs, but also to create and manage them. The SAS Job Execution Web application can be launched through the URL:
http://<your.host.name>/SASJobExecution/
You can use the SAS Job Execution Web application to interactively submit jobs, access their output, and investigate their logs, but what we are proposing here is to leverage the SAS Job Execution Web application to execute jobs as services. In that case, a job can be called with parameters by providing additional arguments to the previous URL. For example:
http://<your.host.name>/SASJobExecution/?_program=SASJobName¶meter1=value1¶meter2=value2
In the documentation you will find information about many pre-defined parameters that can be used, but here is a short list to start with:
- _program
- _output_type
- _debug
- _action
_program is used to inform the SAS job to be executed, including its path, in encoded format. For example, if the job is located at /Public/Jobs/MyJob, the value for _program should be %2FPublic%2FJobs%2FMyJob. JavaScript offers the encodeURIComponent() function that can be used in those cases where encoding is needed.
The _output_type parameter specifies the expected job output, such as ODS_HTML5, JSON, NONE, etc. The complete list of output types can be found in the documentation.
The _debug parameter comes handy when developing the job. If set to LOG, it appends the SAS job log to the job output. When debug is turned on, you must remember to set the _output_type parameter appropriately to receive text information back from the job.
The _action parameter is particularly interesting because setting _action to the value FORM (not case sensitive) causes the SAS Job Web Execution Web application to transfer execution to a form, instead of executing the SAS code stored as a job. You can store the DDC HTML content as a form in the SAS Job Web Execution Web application and leverage the _action argument to transfer execution to the form (=DDC HTML), which in turn can call the job (this time with _action=EXECUTE) as you would normally do with a DDC HTML file stored somewhere under the HTTP server document root location.
Besides being able to manage both job and form through the same Web application, the advantage of storing the DDC HTML content as a Form in the SAS Job Web Execution Web application is that you don’t need access to an HTTP Server document root to deploy your DDC HTML files. The disadvantage is that you cannot pass parameters via URL (from the DDC Web content URL option) to the form – you still can pass parameters as you will see, just not in the URL.
Jobs and forms can be created with the New file option in the SAS Job Execution Web application and selecting the appropriate File type. You can also define job prompts, but you will not be using prompts this time. The interface may vary according to the version you have. The screenshots used in this article were based on Viya 3.5.
The previous screenshot shows how jobs and forms can be created as independent contents, but starting on Viya 3.5, you can also create a form that is “attached” to a job:
When the HTML form is attached to the job, only the job is listed in the SAS Job Execution application GUI.
Besides using the job URL, parameters can also be passed to the job by assigning them in the job’s properties. If you are storing the DDC HTML code as a job form, you will have to remember to always assign the _action parameter with the value FORM, so it makes sense to set it in the job’s properties. A parameter set in the job URL has precedence over the parameter set in the job’s properties:
Depending on where the DDC HTML code is stored, the DDC URL that you can use to set the DDC object in the SAS Visual Analytics report can be any of these two options:
- DDC HTML stored in Web Server document folder:
http://your.host.name/path/your_ddc.html
- DDC HTML stored as job form:
http://your.host.name/SASJobExecution/?_program=/path/your_job
Passing Information to SAS Jobs
SAS jobs can be executed via GET or POST HTTP requests. For GET requests, information is transferred to SAS jobs as key/value pairs that are appended to the HTTP request URL. For POST requests, the key/value pairs are carried in the HTTP request body via form submission and don’t need to be encoded.
In the job perspective, there is no difference on how the information is passed. In both cases, all the key/value pairs become macro variables in the SAS code. For example:
- Multiple key/value pairs for single value parameters:
key1=value1, key2=value2
would become macro variables
KEY1=value1, KEY2=value2
- Multiple key/value pairs for a multi value parameter:
key=value1, key=value2
would become macro variables
KEY_COUNT=KEY0=2, KEY=KEY1=value1, KEY2=value2
Note: there is a %PARAM_LIST macro function that can be used to parse multi value parameters. More information in the documentation.
You can also combine multiple key/value pairs in one single JSON structure, and only send one key where the value is the stringified JSON. This is particularly interesting when sending tables (rows and columns) to the job. For example:
table=[{"Name":"Alfred","Sex":"M","Age":14,"Height":69,"Weight":112.5},{"Name":"Alice","Sex":"F","Age":13,"Height":56.5,"Weight":84},{"Name":"Barbara","Sex":"F","Age":13,"Height":65.3,"Weight":98},{"Name":"Carol","Sex":"F","Age":14,"Height":62.8,"Weight":102.5},{"Name":"Henry","Sex":"M","Age":14,"Height":63.5,"Weight":102.5},{"Name":"James","Sex":"M","Age":12,"Height":57.3,"Weight":83},{"Name":"Jane","Sex":"F","Age":12,"Height":59.8,"Weight":84.5},{"Name":"Janet","Sex":"F","Age":15,"Height":62.5,"Weight":112.5},{"Name":"Jeffrey","Sex":"M","Age":13,"Height":62.5,"Weight":84}]
This is perfectly fine if the table is small, because there are limitations on the length of an URL, which is especially important if you are calling the job using the GET request. This limit depends on the server and client used, as well as the proxy they might be using and configuration options. It could vary from 2K to 8K bytes, but the most conservative ones may say that 255 bytes is the safest length.
POST requests also have limitations on size, mostly imposed by the server, but it’s much higher, normally up to 2G. The best solution to transfer large amounts of data is to use the POST request with blobs and have your table data uploaded to the server as a JSON file. When you make a POST request, you must specify “$CSRF$” exactly as shown. This ensures that the request is considered non-malicious by sending a Cross-Site Request Forgery token to the server. Don’t worry, you will see an example of that.
There are JavaScript objects and methods that come handy to deal with JSON and help formatting the data and uploading it to the server, and independently of how the JSON data is transferred, it’s very simple to retrieve that data with SAS code by utilizing pre-defined macro variables and the JSON libname engine.
HelloSmallWorld Example
In this example, you select a car origin on the button bar across the top of the SAS Visual Analytics report and a simple ODS HTML table containing the filtered data is created by the job and presented back in the report.
The source table used by the SAS Visual Analytics report is the CARS table that has been loaded into memory in the Public caslib. The table is originally available in the SASHELP library.
The button bar is the source of a filter action, where both the list table and the DDC objects are the target.
The List Table was added to this report just as a reference, so you can see what was passed to the DDC and what to expect as a result from the job. The same roles assigned to the List Table were assigned to the DDC.
As seen in the List Table, the data passed to the DDC and subsequently to the job is not only filtered by Origin, but also aggregated by the category data items added in the Roles pane of those objects, Origin and Type in this example. It’s a subset of the CARS table in JSON format, which is small enough to be transferred as a parameter in the URL via GET request.
SAS Job Code
The SAS code (job) is the one below:
vaJSON is the input parameter containing the stringified JSON message that VA passed to the DDC. It basically carries the filtered and aggregated table and its columns metadata in the data and columns structures respectively. You need to copy that string to a temporary file so you can read it as a table with the JSON libname engine.
As you can see, vaJSON represents information in nested structures that can be many layers deep. Each one of those structures will become a table in the jsonLib libname. For example: jsonLib.data, jsonLib.columns, jsonLib.columns_format, etc. You can find more information about the JSON libname engine in the documentation.
The PROC PRINT at the end is what generates the output you see displayed in the report. You’ve got an ODS HTML output because _output_type parameter was set to ODS_HTML5 at the time the job was called, as you’re going to see next.
Data-Driven Content Code
Now let’s look at the DDC HTML code, which could be a file somewhere under the Web Server document root folder or a form in the SAS Job Execution Web application:
The JavaScript code embedded in the HTML leverages some functions from the messagingUtil.js module defined in the GitHub project called sas-visualanalytics-thirdpartyvisualizations. The subfolder util from GitHub is a pre-requisite for this and other examples you will be exploring.
The code first defines the callback function onDataReceived(), which is called whenever there is a new message sent from VA. The callback is set at the end of the JavaScript code:
va.messagingUtil.setOnDataReceivedCallback(onDataReceived);
One of the first things that the onDataReceived() callback function does is checking to see if you have a valid message, such as checking if at least one data item has been added to the DDC object Roles pane. If things are ok, it calls the job via function callJob() and either displays the ODS output returned from the job or error messages, depending on the job result.
The callJob() function first stringifies the JSON message received from VA, then creates the job URL parameters by concatenating some key/value pairs. Next it makes a jQuery ajax() call to the SASJobExecution web application with the GET request. jQuery ajax isn’t the only way of making HTTP calls, but using it makes the code concise and easy to understand. There are many examples in the documentation that uses XMLHttpRequest() instead. Observe that the _output_type parameter is set to ODS_HTML5 (not case sensitive), as indicated earlier, and the JSON data is in the vajson parameter, matching the macro variable name in the SAS job, which is not case sensitive either. The header tells the called service (the job) that only text/html values are accepted back.
Deploying This Example
All files used in this example are available for downloading from the GitHub project sas-visualanalytics-thirdpartyvisualizations, under folder called samples/IntegrationWithSASJobs.
It requires the CARS table to be loaded into memory in the Public caslib. The table is originally available in the SASHELP library.
Deployment steps:
- Download the GitHub project
- Unzip it into your Web Server document folder. These instructions assume you have unzipped the GitHub project in a folder called github in the Web Server document root folder. You will only need the content of folders github/utils and github/samples/IntegrationWithSASJobs.
- Logged into SAS Job Execution Web application, do the following:
a. Create a new job in a folder of your preference (e.g. /Public/Jobs/SAS Communities) and name it HelloSmallWorld
b. Open the job for edition, copy & paste the content of file github/samples/IntegrationWithSASJobs/1.HelloSmallWorld/HelloSmallWorld.sas
c. Save the job
d. Add job parameter _action=FORM as discussed in the beginning of this article
e. Create a new job form either as a separate content or attached to the job (SAS Visual Analytics 8.5 or above only), as explained in the beginning of this article. If it’s a separate content, give it the same name as the job
f. Open the job form for edition, copy & paste the content of file github/samples/IntegrationWithSASJobs/1.HelloSmallWorld/HelloSmallWorld.html (1)
g. Make changes to the host name (search for your.host.name and replace it accordingly) and path of src on line 20 (1):
h. Make changes to the job path on line 66 if necessary (tip: right click on the code and turn on “Show line numbers”), so it matches your environment (same as #3a) (1):
i. Save the job form
- Logged into SAS Visual Analytics, do the following:
- Create a new empty report
- With the report opened, hit Ctrl+Alt+B to bring the SAS Visual Analytics Diagnostics window.
- With the BIRD tab selected across the top (that’s the default), replace the BIRD XML content with the content of the file github/samples/IntegrationWithSASJobs/1.HelloSmallWorld/VA-DDC-Job Hello Small World.xml
- Hit Load (this will close the diagnostics window)
- With the DDC object at the bottom of the report selected, go to the Options pane on the right and fix the URL entry: replace your.host.name accordingly and make sure the path to the job is correct (same value entered in #3a)
- Save the report
Note: (1) Starting with release 2023.06, the examples that used to inherit jQuery from their parent (the SAS Visual Analytics web application) no longer work, so we have provided replacement codes. These replacement files have the same names as their original, but they end with .v4. Because of that, some references to line numbers may not exactly match on those .v4 files.
We could have exported all the content as a package, but this would require special privileges in order to import it. Sharing the example as standalone files will give you the opportunity to better explore the SAS Job Execution Web application, familiarize yourself with the content, and understand how they are connected.
Next Steps
You might have noticed that the HTML table produced by the job needs some work. For example, there were a few columns that didn’t exist in the original table, such as ordinal_root and ordinal_data, the columns names were changed to element<N>, and the values were not formatted. In the next article you will see how to fix those things and learn how to send data to the job by uploading it to the server.
Stay tuned!
References
- SAS Job Execution Web Application 2.2: User’s Guide documentation
- JSON Libname Engine documentation
- Reading data with the SAS JSON libname engine documentation
- Data-Driven Content: leveraging third-party visualizations in SAS Visual Analytics (part 1 of 2) by SAS’s Renato Luppi
- Data-Driven Content: leveraging third-party visualizations in SAS Visual Analytics (part 2 of 2) by SAS’s Renato Luppi
- Using parameters with Data-Driven Content in SAS Visual Analytics by SAS’s Renato Luppi
- sas-visualanalytics-thirdpartyvisualizations GitHub project and examples
Learn More…
- Programming Considerations for Data-Driven Visualizations documentation
- Customize data visualizations in SAS Visual Analytics with #D3Thursday by SAS’ Ryan West
- Leverage a Google 3D-PieChart within VA using the Data-Driven Content Object by SAS’ Meera Venkataramani
- Custom Visualizations in SAS Visual Analytics - A Strategy for Using the Data-Driven Content Object by SAS’ Robby Powell
- Use Custom Visualizations to Extend SAS® Visual Analytics by SAS’ Robby Powell
- Reading data with the SAS JSON libname engine by SAS’s Chris Hemedinger
Just an FYI to @Renato_sas and anyone having problem implementing this article.
Thanks to this article I've been using the approach a lot for all kinds of VA report enhancement. I prefer to do it the way you do in the demo, "hosting" the html in the Viya Job however since patching/Hot Fixing our Viya 3.5 environment in April 2022 all these integrations are broken due to changes in the Content Security Policy. All you get when trying to apply the "html file in the Viya Job" method is the console errors:
Refused to apply inline style because it violates the following Content Security Policy directive: "default-src 'self'"....
Refused to apply inline script because it violates the following Content Security Policy directive: "default-src 'self'"....
Hoisting the HTML file on the server fixes the issue but might result in some issue where the job has to be triggered twice (at least in our case), the first time credentials are validated and the second time the job is actually executed.
In other words, the first approach in the image below works, the second does not atm.
@Renato_sas While this "DDC to Viya Jobs" method still works when hosting the HTML on the server I would like to be able to keep the HTML file as a Job form once more... I'm guessing that blocking in line style and scripts are a security thing but would it be possible, and if so how, to open up the Content Security Policy for inline script once more?
@Renato_sas My data-driven content doesn't show any results, i changed the url to my host name and in the html code, I'm getting no errors, any thoughts?
@malima Thanks malima, i have those files from a previous example i walked thru https://communities.sas.com/t5/SAS-Communities-Library/Data-Entry-in-SAS-Visual-Analytics-8-3-Part-3... from @XavierBizoux where it said to store them in /var/www/html :
so i did and updated the logic in this example to point to them but maybe i missed something path i put in code similar to Xaviers <script type="text/javascript" src="/util/messagingUtil.js"></script>
. I never got the example I mentioned to fully work which was the part that showed what others answered. So I'm wondering is my browser is not configured to utilize the js files or something, because that is the only part of this example not working either. I can clearly see the js file when I put their direct path in the browser:
Could it be an issue with our certificates not being applied yet on our servers? Or setting I need to make in Edge to make it work, I already have allow javascript:
Any help would be great, thanks.
@Renato_sas So I have that path in the code and also have show log, but I do not even see the log or errors, do i need to do something in the browser to see more information?
@Renato_sas Yeah got it to work! looked up some of the errors in the console log, turns out one of the extensions was causing an issue removed it and now I see the results!
