Getting Details About VA Report’s Data Sources Using Viya APIs, CAS Actions and Viya PROCs – Part 2
In my last article I demonstrated how to use the Job Execution Web Application Service to create an application that leverages both the Viya APIs and CAS actions to retrieve information about a Visual Analytics report's data source(s). But did you know that JES developers can also make use of the Viya procedures? In this article, we will be using a Viya procedure to add additional functionality to the application I created in my previous article. Specifically, we will add an additional prompt to the user interface asking the user if they would like to retrieve cardinality information for the report's datasets that are currently loaded into memory. If the user chooses to do this, the program will then run PROC CARDINALITY and return the results at the bottom of the output! The completed application will look like this:
Pretty sweet! Let's take a closer look at what is creating the job's output. The information retrieved using the Viya APIs is highlighted in ORANGE, the information retrieved using CAS Actions is highlighted in BLUE and finally information retrieved using a Viya Procedure is highlighted in PURPLE:
The example above demonstrates yet another way developers can harness the power of the Viya platform (Viya APIs, CAS Actions and Viya Procedures) to create applications using the Job Execution Web Application Service!
Step 1: Adding an additional prompt to the user interface
We will first need to prompt our users asking if they would like to run PROC CARDINALITY on any datasets that are currently loaded into memory. To do this we will need to edit the HTML for our JOB. This can be achieved by right-clicking on the job and selecting "Edit"->"HTML form":
From here we can see the HTML code that we will be editing. Within the HTML form in our code there are two </nbsp> tags. In between them, place the following code:
<br><input type="checkbox" id="getCard" class="jobexec_sample_input_checkbox">
Run PROC CARDINALITY on loaded data set(s)
if (document.querySelector('#getCard').checked == true) {
formData.append("cardRequest","true")
} else {
formData.append("cardRequest","false")
};
The first few lines of the submitForm function should now look like this:
Step 2: Adding PROC CARDINALITY to the Job's Source Code
In order to edit the source code of our Job Execution WebApp Job, right click on the job and select "Edit" -> "Source Code":
At the very bottom of the SAS code, add the following lines:
%if &cardRequest = true %then %do;
%macro retrieveCardinality(caslib,casdata);
%let maxLev=254;
proc cardinality data=&caslib..&casdata outcard=casuser.card maxlevels=&maxLev;
run;
PROC SQL noprint;
select max(_cardinality_) into :maxCard
from casuser.card;
quit;
footnote;
%if &maxCard = &maxLev %then %do;
footnote ITALIC HEIGHT=2 "*PROC Cardinality's MAXLEVEL set to: &maxCard";
%end;
title "Cardinality For &caslib..&casdata";
proc print data=casuser.card label noobs;
var _varname_ _type_ _cardinality_;
run;
title;
%mend retrieveCardinality;
cas myses;
caslib _ALL_ assign;
data getReportDataSourcesCard;
set getReportDataSources;
where state="loaded";
if upcase(compress(scan(reportCasLib,1,"(")) = "CASUSER") then reportCaslib="CASUSER";
else reportCaslib=reportCaslib;
call execute('%nrstr(%retrieveCardinality(' || compress(reportCaslib) || ',' ||compress(reportCasTable) || "));");
run;
cas myses terminate;
%end;
The code above does a few tasks. Let's go through it step by step.
First there is a "%if" check to see if the macro variable "getCard" value is equal to "true", and if so there is subsequently a "%then %do" block of code to be executed. Within that block of code, we first create a macro named "retrieveCardinality" to execute PROC CARDINALITY dynamically. Input parameters for the macro are a caslib and a cas dataset (named "caslib" and "casdata" respectively). The macro first executes the proc, feeding it the caslib and cas dataset that are in the input parameters. The data table to contain the cardinality information for each variable is saved to a cas table in the user's personal caslib named "card". We have also set the maxlevels value to be 254 (which the documentation tells us is the max value we can request). Since users of our application might not know that the maxlevels option is set to 254, it would be good to place a footnote at the bottom of the table in the event that some of the variables' cardinality is greater than 254. To do this, we run PROC SQL on the table "casuser.card" and retrieve the max value of the variable "_cardinality_". We record this value in the macro variable "maxCard". Finally, we check to see if this value is greater than 254; and if so, we conditionally print a footnote informing the user that the maxCard limit has been reached. After PROC CARDINALITY has completed, we simply print out the results of the table casuser.card using PROC PRINT.
Since the macro will interact with CAS we first establish a cas session and assign our libraries. With our macro created and our CAS session established, we now will need to execute the retrieveCardinality macro dynamically. As we saw in the previous example, the source code of this Job Execution WebApp Service Job leverages one main dataset named "getReportDataSources" which contains all the datasets that the VA report uses. We subset this dataset to contain only the loaded cas tables and then use this dataset in combination with a CALL EXECUTE Routine to run our macro dynamically on the resulting tables. When all is completed, we terminate our CAS session.
And that's it! We have successfully added the needed components to first prompt the user to see if they want to run PROC CARDINALITY, and if so run the procedure and return the results. By leveraging this additional functionality along with the results from the API and CAS Actions from the previous example, we have built a powerful little application that returns a lot of useful information about a Visual Analytics Report's Datasets:
*The code in this article is intended to be run against VA reports which have been developed using the Visual Analytics interface using loaded CAS data sets as the data source(s). Results might vary when selecting preinstalled VA reports or VA reports created via other methods.
