Using Jobs to Load Data in SAS Visual Analytics

2 Likes

In a previous article, I discussed how to use incorporate SAS Viya jobs using JSON-based step prompt forms into SAS Visual Analytics reports and share them with end users. In that example, I demonstrated a job that took a user’s selection and returned static output in the form of a PROC PRINT report and a PROC SGPLOT graph. However, there may be times when you want to provide additional functionality to end users.

For example, imagine you want to provide end users an interface to select the particular data that they want to examine, and then provide them a number of interactive graphs and visualizations to explore that data further. In this article, I explain how to use a SAS Viya job with a JSON step prompt form to allow users to update the data used in a SAS Visual Analytics report, allowing end users without programming experience to run a job, take the results, and create interactive visualizations.

Start with a Working Program

Like always, you’ll want to start with a working SAS program that does what you want it to do. I’ll begin with a simple program that allows the user to change the value of macro variable to filter the SASHELP.CARS data set by Make and prints the resulting table.

%let selectMake=Jeep;

data work.cars_subset;
	set sashelp.cars;
	where Make="&selectMake";
run;

proc print data=work.cars_subset;
	var make model;
run;

Create a Job Definition

In order to convert this program to a job, all I need to do is replace the %LET statement with a %GLOBAL statement, as follows:

%global selectMake;
data work.cars_subset;
…

As discussed in my previous article, I’ve also created a step prompt form using the custom step Designer and added the JSON syntax to the Step Prompt (JSON) tab in the job definition.

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

Load the Resulting Table to CAS

I can use the job content object to add the existing job to a SAS Visual Analytics report. However, the job would be independent of the rest of the report. Instead, I want to allow the report viewer to use the job to update the data used by the report, so they can make dynamic selections and have objects in the report update to reflect the results.

To do this, I’ll need to add some additional code to my job definition. I’ll start a new CAS session, and then use PROC CASUTIL to load the WORK.CARS_SUBSET table to memory in the PUBLIC caslib.

cas mySession;
proc casutil;
	droptable casdata="cars_job" incaslib="public" quiet;
	load data=work.cars_subset outcaslib="public"
		casout="cars_job" promote;
quit;

In this example, the CARS_JOB table in the PUBLIC caslib already exists but contains the complete, unfiltered data set. When the job runs, the CARS_JOB table will be dropped from memory if it exists, and then the WORK.CARS_SUBSET table created by the previous DATA set will be loaded to memory in the PUBLIC caslib as CARS_JOB. It will also be promoted so that it can be used in a SAS Visual Analytics report.

Create a SAS Visual Analytics Report

Once the job has been created and updated to load the resulting data to an in-memory CAS table, I’ll create a SAS Visual Analytics report to allow users to interact with the data.

The report is built using the CARS_JOB table in the PUBLIC caslib. Remember, the version of this table that is stored in on disk as a .sashdat file is unfiltered, and contains all of the data in SASHELP.CARS.

On one page in the report, I’ve added several visualizations that provide the user additional insight into the data:

Then, on the first page in the report, I’ve added a job content object. I’ll select the job that I just created, that allows the user to pick a Make of car and then loads the resulting table to CAS.

Using the Job to Update the Report

To use the job to update the report, all I need to do is make my parameter selections and run the job. The PROC PRINT results will be displayed in the job content object, but behind the scenes, the CARS_JOB table in the PUBLIC caslib will also have been updated.

To reflect those changes to the data set in the report, I can refresh the report data by click on the Data pane, clicking the Data menu button, and then selecting Refresh CARS_JOB.

Once the CARS_JOB table has been refreshed, the objects in the report will update to reflect the modified data:

It’s also possible to automatically refresh the report. You can do this by using the Periodically reload data option in the Options pane at the report level. This option allows you to specify a time interval to refresh the report data with the updated job results. Depending on your environment, setting this option to a very frequent interval may negatively impact performance.

Conclusion

By adding a few lines of code to your job definition, you can create an interface allowing users to interactively filter and modify the data used in a SAS Visual Analytics reports. The possibilities here are endless: you create jobs that allow users to select data sets to join, analysis options to perform, and more.

Tips

The CAS table used by the report (in this case, CARS_JOBS) must already be created and be saved to disk. It’s also a good idea if it contains the unprocessed data so that if a user opens the report without making any selections in the job or refreshing the data, they won’t be confused by what’s displayed.
If the report will be shared with others, make sure the data is stored in a caslib accessible to everyone.
It is also possible to use the data-driven content object to interact with a job and pass data back and forth to SAS Visual Analytics. View this article for an example. However, this solution will often require additional knowledge of HTML and JavaScript.

Find more articles from SAS Global Enablement and Learning here.