One of the things I like best about creating applications in the
Job Execution Web Application Service is the ability to leverage all of the goodies the Viya platform has to offer. Developers can use the JES environment to make API calls to the SAS Viya services, utilize the powerful
CAS actions sets and even run
Viya procedures! In this two-part article, I'll walk you though developing a JES application which uses all three of these feature sets. This article will focus on creating a JES job which first prompts users to select a Visual Analytics Report from a drop down list. After a selection is made, the JES job will use API calls to retrieve a listing of the report's data sources and determine if they are currently loaded into memory. After this, the job will run a CAS action on all data sources to determine its total columns, rows and even if the data was loaded into CAS using the compress option.
When completed the JES job will function like this:
Pretty neat! Let's take a closer look is creating the job's output. The information retrieved using the Viya APIs is highlighted in ORANGE and the information retrieved using CAS Actions is highlighted in BLUE:
The JES environment offers us the ability to retrieve this vital information and place it in a pretty user interface. Most importantly, since we are using APIs and CAS actions to retrieve the needed information, the results are returned lighting quick! This article will step you though how to build this JES example. All code discussed is available in this
GitHub Repository.
Let's get started!
Setup Steps: Create the JES Job and HTML Input Page
Before can begin to write our job's source code we will need to complete a few setup steps. First we will create the job object and generate an HTML front end for it. To do this, start within the Job Execution WebApp and create a new job. In the screenshot below, I'm creating a job in the folder "/Public/JES_Example":
Give your job a name. In my example, the job is named "JES_APIAction".
All other options can remain as their default values. Press "OK":
With our job in place our next step will be to right click on your job and choose: "Edit" -> "New HTML form" and build the user interface that contains a drop-down list of Visual Analytics Reports from which a user can make a selection. This will be done by employing a script which dynamically creates the HTML drop down prompt by making a API request to the Viya Reports API and gets a collection of reports. Secondly, we will loop though each item in the API's response json file and iteratively convert it into the drop down list options. When the user selects a report from the drop down, the report's specific uri is sent to the JES job in the form of the SAS macro variable "&JESParameter". The code to do this is in this article's GitHub repository and is also covered in detail in my previous article Creating JES Prompts and Output Using the Viya Reports API.
If you like, you can take a look at what your HTML code looks like. To do this, right click on your job and choose: "Display form" -> "HTML form":
With our HTML code in place we can now begin to create the source code for our job to execute. You can add this by right-clicking the job and selecting: "Edit" -> "Source code":
From here you can add SAS Code to the job and save it. So now our JES application development can begin! The code examples and output in the next two sections are all the result of the user having selecting Visual Analytics report "Stock Market Analysis". After the user selects this report from the drop down the report's specific uri will be passed to the Job in the form of the SAS macro variable "&JESParameter".
Step 1: Use the Viya API to retrieve report information and data source state
With the "Stock Market Analysis" report selected and the uri having been passed to the JES job, the first step is to use PROC HTTP to make an API request of the
Viya Relationships API to get a list of the report's data sources. If you would like more information on how the Viya Relationships API works, here is a great
communities article that covers it in detail. The response from the API is held in the filename rep_ds and the json libname is used to convert the response into SAS Datasets. The SAS Code to do this is below:
* Base URI for the service call;
%let BASE_URI=%sysfunc(getoption(servicesbaseurl));
filename rep_ds temp;
proc http url="&BASE_URI/relationships/relationships/?resourceUri=/reports/reports/&JESParameter"
method='get'
oauth_bearer=sas_services
out=rep_ds;
run;
quit;
libname rep_ds json;
After running this code, the library rep_ds contains a data set named "items". This data set has a lot of useful information in it! However we will be mainly interested in only one column: relatedResourceUri. For the report "Stock Market Analysis" the relatedResourceUri contains the following two values:
Looking closer, these two values have a lot of great information! First, we can see that they both begin with the text "/casManagement/servers". We can see from the
CAS Management API documentation that these uris are referring to data sources. Secondly, we can see that both the values contain the text "/caslibs/". The documentation also
tells us that the next text in the string is the value of the data sources caslib. So in our example, the data source "STOCKS" is located in the "Public" caslib and the data source "SNACKS" is located in the caslib "CASUSER(sasdemo)". Finally, we can also infer that the tail of the value contains the actual table within the caslib that is being referred to. The next step is to use these pieces of information to construct our next set of API input parameters. This can be done with a simple data step:
/* extract information about the data sources */
data getReportDataSources;
set rep_ds.items;
where index(relatedResourceUri,"/casManagement/servers")>0 and scan(relatedResourceUri,-2,'/')='tables';
reportCasLib = (scan(relatedResourceUri,-3,'/'));
reportCasTable = (scan(relatedResourceUri,-1,'/'));
caslibEndpoint = tranwrd(relatedResourceUri,trim('/tables/' || reportCasTable),'');
keep reportCasLib reportCasTable caslibEndpoint;
run;
In the code above, we can see that we first subset the rep_ds. items data set to include only values where the relatedResouceUri contains "/casManagement/servers" and has the word "tables" before the listed table. This is due to the fact that the
Viya Relationships API can return objects that are not data sources (such as other VA reports). This where clause ensures that the output table will include only values from the
CAS Management API. We also will extract the table and caslib that each cell contains into two separate variables named reportCasTable and reportCasLib. The last thing we will need for our next API call is the endpoint for the caslib. We also extract this from the relatedResouceURI variable and place it in the variable caslibEndpoint. The final output of the getReportDataSources table is below:
Great! We now know the VA report's tables and their respective caslibs!
The next step is to decipher whether these tables are currently loaded into CAS. To do this, we will use an extremely useful feature of the
CAS Management API to request a list of tables for a specific caslib. The response from this call contains a wealth of information about the tables within a caslib. Details about each table including their scope, state (loaded or unloaded) and row/column counts are included. For example, the SAS code below will make an API call to get information about the tables in your personal caslib and print out some of the variables that are in the response:
%let BASE_URI=%sysfunc(getoption(servicesbaseurl));
filename clibinfo temp;
proc http url="&BASE_URI/casManagement/servers/cas-shared-default/caslibs/casuser/tables?limit=10000"
method='get'
oauth_bearer=sas_services
out=clibinfo;
run;
libname clibinfo json;
proc print data=clibinfo.items noobs;
var caslibname name state;
run;
At the time I wrote this article, I had a total of 4 tables contained in the response from this API call.
The results are below:
You can see how four of the tables in my personal caslib loaded and one is unloaded.
We can incorporate a similar call into our JES job to determine whether the VA report's source tables are currently loaded. To do this we will use the data set getReportDataSources to make the request which retrieves a list of tables for each caslib. Doing this requires we write a very simple macro which makes a request to a caslib and lists all the data sources that currently have a state of 'loaded'. We will take the output from that call and merge any observations that have the same caslib and table back into the original getReportDataSources data set.
To call the macro we will use a distinct list of caslibs from the getReportDataSources data set. We can use a PROC SQL to easily get this and take the resulting distinct list of caslibs and feed it to our macro by using the
CALL EXECUTE routine.
The code to do this is below:
proc sort data=getReportDataSources;
by reportCasLib reportCasTable;
run;
%macro getLoadedTables(caslibURI);
filename clibinfo temp;
proc http url="&BASE_URI/&caslibURI/tables?limit=10000"
method='get'
oauth_bearer=sas_services
out=clibinfo;
run;
libname clibinfo json;
data formatTable;
set clibinfo.items;
rename name = reportCasTable
caslibName = reportCasLib;
run;
proc sort data=formatTable;
by reportCasLib reportCasTable;
run;
Data getReportDataSources;
Merge getReportDataSources(in=T1) formatTable(in=T2);
If T1;
by reportCasLib reportCasTable;
keep reportCasLib reportCasTable state;
if state='' then state='unloaded';
else state=state;
run;
%mend getLoadedTables;
proc sql;
create table reportCaslibs as select distinct caslibEndpoint from getReportDataSources;
quit;
data _NULL_;
set reportCaslibs;
call execute('%getLoadedTables(' || trim(caslibEndpoint) || ')');
run;
The results from this code is below:
While these results look nice, it would be better if we could color code the "state" column in our output. To do this, we'll dust off our
PROC REPORT skills to add the needed color coding to our output.
The code to do this is below:
title "The Report(s) Data Sources are Listed Below /reports/reports/&JESParameter";
proc report data=getReportDataSources nowd missing;
columns reportCasLib reportCasTable state flag;
define flag/computed noprint;
define ID / group 'Report ID';
define reportCasLib / group 'CAS Library';
define reportCasTable /Group 'CAS Table';
define state /Group 'CAS Table State';
compute flag;
if state='loaded' then do;
call define('_c3_', 'style','style={background=green foreground=white}');
end;
if state='unloaded' then do;
call define('_c3_', 'style','style={background=red}');
end;
endcomp;
run;
And here is our color coded output!
Great! We are well on our way to creating our JES job! So far, we have done the following:
- The job is fed a Visual Analytics report uri
- The uri is used to make a call to the Viya Relationships API to retrieve the report's data sources
- The list of data sources is used to make an API call to the CAS Management API to determine if the tables are loaded or not.
The next step of our JES job will leverage a CAS Action to get more information about the report's data sources!
Step 2: Using a CAS Action to retrieve data source details
CAS actions are one of my favorite parts of the Viya ecosystem. If you are looking to preform data operations on data tables in Viya, you can use a CAS action to get the results you need very quickly. Since we are creating a job within the JES framework, you can easily access and run one or more of the CAS Action Sets that are loaded to your environment. For our current example we will be looking at the tableinfo action which provides a list of tables that are in memory on the server.
Since this action provides information on tables that are currently in memory, we must first ensure that at least one of the report's data sources is currently loaded. Since we were able to determine this for each data source in Step 1, we can use that information to our advantage. A simple data step with a where clause will return only observations with state of loaded from the getReportDataSources data set. Then we can use conditional logic to conditionally execute different sets of code. If there are no records in the getReportDataSources with a state of 'loaded' we will simply execute our proc report code from above. Otherwise, we will run the tableinfo CAS action. The code to do this is below:
data getReportDataSourcesCheck;
set getReportDataSources;
where state="loaded";
run;
data _NULL_;
if 0 then set getReportDataSourcesCheck nobs=n;
call symputx('nrows',n);
stop;
run;
%if &nrows=0 %then %do;
title "The Report(s) Data Sources are Listed Below";
proc report data=getReportDataSources nowd missing;
columns reportCasLib reportCasTable state flag;
define flag/computed noprint;
define reportCasLib / group 'CAS Library';
define reportCasTable /Group 'CAS Table';
define state /Group 'CAS Table State';
compute flag;
if state='loaded' then do;
call define('_c3_', 'style','style={background=green foreground=white}');
end;
if state='unloaded' then do;
call define('_c3_', 'style','style={background=red}');
end;
endcomp;
run;
%end;
%else %do;
/* run some cas actions! */
%end;
Nice! Now that we have determined at least one of the reports data sources is loaded, we can run the tableinfo actions against it. The table info action needs to be fed a caslib and table as part of its input parameters. One of the most useful features of CAS actions is the ability to use the saveresult statement to capture the output from the action to a SAS dataset. We will make use of this so we can apply the information we get from the tableinfo action to the main reporting data set our JES job is creating.
For a simple example, we can run the tableinfo action on the data set PUBLIC.STOCKS. While the tableinfo action provides a lot of details about the table, we will only print out the table's name, row/column count and its compressed flag (whether the table was loaded using the compress option)
proc cas;
table.tableInfo result=S
caslib="Public"
table="Stocks";
val = findtable(s);
saveresult val dataout=work.casTableInfo;
run;
proc print data=casTableInfo noobs;
var Name Rows Columns Compressed;
run;
The output is as follows:
Very interesting! The CAS action has told us both the table's size (row and columns) and also if it is compressed in memory. This could be very useful information when looking at a Visual Analytics Report's performance. We can translate our simple example into a macro and add it to the JES job we are working on. As before, we will use the getReportDataSources data set to dynamically call the macro by using the CALL EXECUTE routine.
The code to do this is below:
%macro getTableInfo(reportCasLib,reportCasTable);
ods exclude all;
proc cas;
table.tableInfo result=S
caslib="&reportCasLib"
name="&reportCasTable";
val = findtable(s);
saveresult val dataout=work.casTableInfo;
run;
ods exclude none;
data casTableInfo;
set casTableInfo;
reportCasLib = "&reportCasLib";
rename name = reportCasTable;
run;
proc sort data=casTableInfo;
by reportCasLib reportCasTable;
run;
Data getReportDataSources;
Merge getReportDataSources(in=T1) casTableInfo(in=T2);
If T1;
by reportCasLib reportCasTable;
keep reportCasLib reportCasTable state Rows Columns SourceCaslib SourceName ;
run;
%mend getTableInfo;
data getReportDataSourcesExe;
set getReportDataSources;
length code $ 1000;
code = '%getTableInfo(' || trim(reportCasLib) || ',' || trim(reportCasTable) ||')';
where state="loaded";
run;
data _NULL_;
set getReportDataSourcesExe;
call execute(code);
run;
With the above macro complete we are very close to being ready to print our getReportDataSources data set. However we will need to make one final upgrade to the data set by converting the "compressed" variable from numeric (0 or 1) to character (No or Yes). Now we are ready to use PROC REPORT to print the results with both color coding and proper numeric formatting. The code to do this is below:
data getReportDataSources;
set getReportDataSources;
if compressed=1 then compressDesc = "Yes";
else if compressed=0 then compressDesc = "No";
run;
title "The Report's Data Source Details are Listed Below";
proc report data=getReportDataSources nowd missing;
columns reportCasLib reportCasTable state Columns Rows compressDesc flag;
define flag/computed noprint;
define reportCasLib / group 'CAS Library';
define reportCasTable /Group 'CAS Table';
define state /Group 'CAS Table State';
define Columns/Group 'Columns' format=comma8.;
define Rows /Group 'Rows' format=comma8.;
define compressDesc / Display 'Data Compressed?';
compute flag;
if state='loaded' then do;
call define('_c3_', 'style','style={background=green foreground=white}');
end;
if state='unloaded' then do;
call define('_c3_', 'style','style={background=red}');
end;
if state='unloaded' then do;
call define('_c3_', 'style','style={background=red}');
call define('_c4_', 'style','style={ foreground=white}');
call define('_c5_', 'style','style={ foreground=white}');
call define('_c6_', 'style','style={ foreground=white}');
end;
endcomp;
run;
And that's it! We have successfully written all JES code to replicate the example! In my next article I will be building on today's example by employing the powerful Viya Statistical Procedures to gain even more insight into the Visual Analytics report's data sources!
How to make this example work for you
On GitHub, you will find all the code discussed in this article. All code intended to be saved in a SAS® Job Execution Web Application 2.2 job definition within a Viya 3.5 environment.
On Github, you will find the following support files for this article:
- A JSON file containing the completed JES Job - JES_APIAction.json
- *A SAS Administrator can import this to the Viya content folder "/Public" within a SAS Viya 3.5 (or later) environment using these instructions
- The JES job's source code that runs make the API requests, runs the CAS actions and creates the ODS output - JES_APIAction.sas
- The JES job's HTML code to create prompts and execute the job - JES_APIAction.html
