Data Entry in SAS Visual Analytics 8.3: Part 2, Write a Viya Job with a form

6 Likes

One of the most popular question we hear is, is it possible to achieve data entry using SAS Visual Analytics 8.3. In the SAS 9 world, there was a simple answer: include a stored process in your report.

Do you think the answer will be the same in the SAS Viya world?

In Part 1 of this series, I explained the basics about SAS Viya Jobs. In this article, I will walk you through the steps to write a simple quiz application using SAS Viya Jobs.

The application will use a CAS table named quiz_programming as input and create a CAS table named quiz_answers as output. Those two tables will be stored into a Global CASLib named Visual in this example. As the objective of the series is to explain data entry, the application will generate a user interface (UI) to interact with end-user.

In order to understand the code, you need to be familiar with CAS programming, macro language and have a bit of understanding about html. I will go through the code and explain the purpose of nearly every line. This means you don't have to write code yourself but if you want to go more deeply in data entry using SAS Viya Jobs, you should understand the following terms:

SAS Job Execution
Streaming output: _webout
CAS language: table actionset
SAS macro : %symexist, %eval
SAS programming: first., last.

Let's start with something easy. As indicated earlier, the application will use a CAS table named quiz_programming as input. We will load that table into a Global CASLib named visual. I assume that you are familiar with loading data into CAS. Just download the file containing the questions and upload it to a Global CAS library. If you need help loading the file into CAS, please refer to the documentation.

The logic

In this example, we will use different tables located in different CASLibs. Those tables will serve different purpose.

quiz_programming table located in Visual CASLib: this table contains the questions for our quiz. The table is loaded from Excel and has a Global scope and is stored in a Global scope CASLib.
quiz_answers table located in Visual CASLib: this table contains the answers for each user with a timestamp. This is a Global scope table and it can then be used for reporting purpose in SAS Visual Analytics.
questions table located in Casuser CASLib: this table is a copy of the quiz_programming table and will be used during the quiz to store the questions and user's answers. The Casuser CASLib is used because each user has it's own CASLib with a Global scope. This is an easy way to keep data between CAS sessions. It also reduces the number of updates to quiz_answers table as this table is accessible by multiple users and is used for reporting. As mentioned in Uttam Kumar's article, Concurrent data append and update to a global CAS table, updates and append requests to CAS tables are queued. So, it is better to reduce the number of calls to reduce processing queue.

The SAS Viya Job is executed for each question in the quiz_programming table. As a result some parameters should be passed between job executions. To achieve this objective, URL parameters will be passed to the Job for each execution. This is an easy technique to pass the state of the requests and have a different behavior depending if this is the first question, any other question or the last question of the quiz. Let's have a look at the logic.

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

First execution will initialize the needed macro variables and copy the quiz_programming table to questions table. It will generate the html form that the user will see and use to answer the first question. When submitting the form, the job will be triggered a second time.
Subsequent executions will save the answers from the previous question to the questions table. Using the URL parameters it will decide which question should be displayed to the user and generate the html form as in the first execution.
Last execution: based on the URL parameters, the job will detect that the user answered the last question of the quiz. So this time, the job will save the answers to the previous question as usual but it will also send the data to quiz_answers table and display a summary page to the end-user. That summary page displays the questions, the selected answers, the valid answers and an explanation.

The tool

We will now write the SAS Viya Job code. We need to connect to SAS Viya 3.4 environment using an URL similar to http://myViya.hostname.com/SASJobExecution/

After authentication, you should see this screen:

Navigate to a location all users can access and hit the New File button. I this example the location is: /gelcontent/Jobs/

Give a name to your job.

Select Job definition.

Click OK.

You should now see your job. Right click on the job and select Edit file.

The code for the job can be downloaded from GitHub.

Open the code in your favorite text editor and paste the code in the browser. You should have something similar to:

The code is specific to my environment and use the Visual CASLib. As you are using this on another environment, you should change the references to "visual". It means that all the occurrences of "visual" should be changed to reflect the CASLib available on your environment. Using the search and replace function is a good solution as you can imagine.

If you stored the code in a location different from /gelcontent/Jobs/ and you gave a different name, you should also update the following line to reflect your location and name:

put '<input type="hidden" name="_program" 
value="/gelcontent/Jobs/Quiz">';

To display the html form and output, you should also set a parameter for your job. Right click on the job name and select Properties. Within Properties, add a new parameter with the following values:

When the code is tailored to your environment, you can press the Submit button on top of the editor.

(view in My Videos)

The details

/* Define macro variables for the parameters defined in the URL */
%global id maxid answer01 answer02 answer03 answer04 answer05;

In order to pass URL parameters to the Job, the macro variables should exist in the macro global scope. This means we need to define the macro variables using the %global statement.

id: the question ID
maxid: the number of questions
answer01 to answer05: the possible answers to the questions

/* Define CAS server connection, session and CAS libraries */
options cashost="intcas01.race.sas.com" casport=5570;
cas mySession sessopts=(caslib="casuser");
libname visual cas caslib="visual";
libname casuser cas caslib="casuser";

To work with CAS data, we need to establish a connection with the CAS server and define the libraries that will be used in data step. In this case, we will use two libraries pointing to two CASLibs. Visual is the Global CASLib that is used to store the input and output tables. Casuser is the Global CASLib that will be used to store data while answering the questions.

/* Macro invoked at the beginning of the Job execution */
%macro init;
%if %symexist(id) and %eval(&id > 0) %then
%do;
/* Call record macro to register the answers */
%record;
/* Increment question id */
%let id = %eval(&id + 1 );
%end;
%else
%do;
/* Code executed when loading the first time */
%let id = 1;
data casuser.questions; 
set visual.quiz_programming; 
run;
proc cas; 
table.promote /caslib="casuser" name="questions";
quit;
%end;
/* Define the number of questions */
proc sql noprint;
create table maxid as 
select max(id) as maxid 
from casuser.questions;
select maxid into :maxid from maxid;
quit;
%mend;

The init macro is executed at each execution of the code. The macro will check if the id macro variable exists. It is used to define which question is currently being processed.

If the value is smaller than 0, the id macro variable is set to one. It indicates that it is the first time the Job is executed. As a result, the quiz_programming table is copied to the questions table in the CASUser CASLib. To be persisted between sessions, the questions table is promoted using proc cas. The proc sql code is used to calculate the number for questions in the quiz and defines a macro variable maxid that will be displayed in the form but will also be used to identify if it is the last execution. Using proc sql implies the data will be moved to the SAS Compute Server to be processed. In this case, it doesn't have a big impact on performance as table is quite small. If you are processing larger tables, you should find use proc fedsql to query the CAS table as CAS doesn't support macro processing and then use proc sql on the result to assign the maxid macro variable.

If the value is higher than 0, it means that this is not the first execution and that a macro named record should be executed. The record macro will save the answers (see below).

/* Macro invoked when last question has been answered */
%macro finish;
%if %eval(&id > &maxid) %then
%do;
/* Generate the data set to be loaded into */
/* the Global CASLib used for reporting */
data casuser.toLoad;
set casuser.questions (drop= questions choices hint justification);
userid = "&sysuserid";
datetime = datetime();
format datetime datetime16.;
run;
/* Load data into the CASLib used for reporting */
/* and perform some cleanup activities */
proc cas;
table.tableExists result=res/caslib='visual' name='quiz_answers';

if res.exists > 0 then
do;
datastep.runcode result=dsResult /code="data visual.quiz_answers 
(append=yes); set casuser.toLoad; run;";
end;
else
do;
table.promote / caslib='casuser' name='toLoad' 
target='quiz_answers' targetlib='visual';
table.promote / caslib='visual' name='quiz_answers';
end;
table.save / caslib='visual' name='quiz_answers' 
table={caslib='visual' name='quiz_answers'} replace=true;
table.dropTable / caslib='casuser' name='questions';
table.deleteSource / caslib='casuser' source='questions';
table.dropTable / caslib='casuser' name='toLoad';
table.deleteSource / caslib='casuser' source='toLoad';
quit;
/* Terminate the CAS session */
cas mySession terminate;
%end;
%mend;

The finish macro is executed at the end of each job execution. It checks if the current question is the last question of the quiz. If it is not, the code is not executed. If it is indeed the last question, it creates a table named toLoad. That table will be loaded into quiz_answers table. The proc cas code will perform the following actions:

Check if the quiz_answers table already exists in Visual CASLib
- If table exists, proc cas will execute datastep.runcode action. This action will append toLoad table to the quiz_answers.
- If table doesn't exist, proc cas will execute table.promote action to copy the toLoad table from CASUser CASLib to Visual CASLib and give it the quiz_answers name. When this is done, the quiz_answers table is promoted to Global scope in order to make it available for reporting purpose.
When the data is stored in memory as quiz_answers, it is a good practice to do some maintenance:
1. Save the quiz_answers to disk
2. Delete the questions table from the CAS temporary location
3. Delete the questions table from the CAS permanent location
4. Delete the toLoad table from the CAS temporary location
5. Delete the toLoad table from the CAS permanent location

When all this is done, the code ends the CAS session.

/* Macro invoked to record the answers */
%macro record;
proc cas;
questionsTbl.name="questions";
do i=1 to 5 by 1;
name_i=put(i, z2.);
varName='Answer'||name_i;
if symget(varName) > "" then
do;
questionsTbl.where="&id = id and name ='"|| varName ||"'";
table.update / table=questionsTbl set={{var='selected', value="1"}};
end;
end;
quit;
%mend;

The record macro is executed to process end-user's answers that are retrieved from the form submission through URL parameters. In this code, I used proc cas to demonstrate how to update records using CAS actions and the CAS language.

The code creates a questionsTbl variable which is used in the table.update action. It contains the name of the CAS table and the where clause to be applied. The where value is generated based on the macro variables answer01 to answer05. One update statement is executed for each answer.

/*Macro generating the html pages */
%macro html;
%if %eval(&id > &maxid) %then
%do;
/* Call the quiz summary page */
%html_result;
%end;
%else
%do;

data _null_;
set casuser.questions (where=(ID=&id)) nobs=rows;
file _webout;
by id;
/* Generate the HTML head */
if _n_ = 1 then do;
put '<html lang="en">';
put '<head>';
put '<title>Quiz Programming</title>';
put '<meta charset="utf-8">';
put '<meta name="viewport" content="width=device-width, 
initial-scale=1.0">';
put '<meta author="BIZOUX, Xavier">';
put '<style>';
put 'article {padding-left:20px;}';
put '</style>';
put '<script>';
put 'function hint() {
var x = document.getElementById("hint");
if (x.style.display === "none") {
x.style.display = "block";
} else {
x.style.display = "none";
}
}';
put 'document.addEventListener("DOMContentLoaded", 
function(event) { hint();});';
put '</script>';
put '</head>';
put '<body>';
end;

if first.id then
do;
/* Create the form calling the SASJobExecution */
/* and the specific job */
put '<form action="/SASJobExecution/">';
put '<input type="hidden" name="_program" 
value="/gelcontent/Jobs/Quiz">';
put '<input type="hidden" name="id" value="' id +(-1)'">';
put '<section>';
put '<header>';
put '<h3>Q ' id "/&maxid : " questions '</h3>';
put '</header>';
put '<article>';
end;

put '<br>';
put '<label> <input type="checkbox" name="' Name +(-1) '" value="' 
Correct +(-1) '">' choices +(-1) '</label>';

if last.id then
do;
put '<br><br>';
put '<button type="button" onclick="hint();">Hint</button>';
put '<input type="submit">';
put '<div id="hint">' hint'</div>';
put '</article>';
put '</section>';
put '</form>';
end;
if _n_=rows then do;
put '</body>';
put '</html>';
end;
run;

%end;
%mend;

The html macro identifies if this the last execution of the code. If it the last execution, the html_result macro is called. Any other execution will create a section with the question, the checkboxes for the possible answers, a hint button and a submit button.

The data step is reading data from the questions table and writes to the _webout destination. As you most probably know, data step reads and processes the rows sequentially. Knowing this, here are the rules that are applied:

If it is the first row of the table, the hml header is created.
If it is the first row of the "by" variable, the form statements are generated.
If it is the last row of the "by" variable, the buttons and the needed closing statements are generated.
Any other row being processed will generate an input statement for the checkboxes.

In the header, you can see two specific html tags: style and script.

The style tag is used to specify some CSS layout.
The script tag is used to add a JavaScript function to handle the hint button.

In this example, the style and script sections are pretty small. This means we can enter those directly in the data step. If larger stylesheet or JavaScript are needed, those should be defined in external files for readability and performance of the SAS code. Those files should be loaded in the Apache web server used by the SAS environment.

/*Macro generating the summary page */
%macro html_result;
data _null_;
length cssClass $200;
set casuser.questions nobs=rows;
file _webout;
by id;
/* Generate the HTML head */
if _n_=1 then
do;
put '<html lang="en">';
put '<head>';
put '<title>Quiz Programming</title>';
put '<meta charset="utf-8">';
put '<meta name="viewport" content="width=device-width, 
initial-scale=1.0">';
put '<meta author="BIZOUX, Xavier">';
put '<style>';
put 'article {padding-left:20px;}';
put '.valid {display:block; 
background-color: lightgreen; width:400px;}';
put '.invalid {display:block; 
background-color: red; width:400px;}';
put '</style>';
put '</head>';
put '<body>';
end;
/* Generate the card for each question */
if first.id then
do;
put '<section>';
put '<header>';
put '<h3>Q ' id "/&maxid : " questions '</h3>';
put '</header>';
put '<article>';
end;

if correct=1 then
cssClass=' class="valid" ';
else
cssClass=' class="invalid" ';
put '<label ' cssClass '>';
put;

if selected=1 then
do;
put '<input type="checkbox" checked disabled>';
end;
else
do;
put '<input type="checkbox" disabled>';
end;
put choices +(-1) '</label>';

if last.id then
do;
put '</article>';
put '<br>';
put '<article>';
put '<header> <h4>Answer</h4> </header>';
put '<p>' justification '</p>';
put '</article>';
put '<br>';
put '</section>';
end;

if _n_=rows then do;
put '</body>';
put '</html>';
end;
run;

%mend;

The html_result macro generates the summary page which appears after the last question is answered. The macro is called within the html macro and displays all the questions with the answers selected by the end-user. The answers are colored using the red and green color logic to indicate if they are correct or not. An explanation is also added after each question to give a better understanding of the correct answers.

The rules that are applied in the data step are as follow:

If it is the first row of the table, the html header is created.
If it is the first row of the "by" variable, the article statement is generated.
If it is the last row of the "by" variable, the explanation and the needed closing statements are generated.
Any other row being processed will generate an input statement for the checkboxes. Compared to the html macro code, the input statements have a bit more logic. They have all been disabled as no user interaction was expected. In addition, each input statement should be identified as:
- being a correct answer or not and colored accordingly.
- being selected by the end-user and check marked accordingly.

Calling the macros

/* Call the different macros */
%init;
%html;
%finish;

The last part of the code calls the macros that were defined earlier in the code. Those macros are called without parameters as the main purpose to use macros was to segregate the different actions used to build the quiz application and the logic behind it.

Some considerations

As we have seen so far, the SAS Job Execution is a replacement for SAS Stored processes in the Viya world. With a bit of SAS and HTML coding, you can create a simple UI for the end-users and you can store the data easily into CAS.

If we compare what is possible with CAS with what was possible with LASR and SAS 9, having multiple users connected to the same application doesn't require SAS Share or a SAS Data Connectors. CAS is capable of queuing the request for read but also for inserts/updates.

Nevertheless, I would recommend to use SAS Data Connectors when processing data entry at larger scale. There is good reason for that. Even though CAS is capable of handling concurrent inserts/updates, CAS doesn't have all the capabilities a transactional database have (rollback being one of them).

So, if you plan to have many users inserting or updating records in your application, I would recommend to make those in a RDBMS using SAS Data Connectors. The RDBMS can then be used as source of CASLib and the data will be available for reporting. This adds extra steps to the process but your application will be more robust and stress resistant.

Next steps

We have now a quiz application that can be executed from the SAS Job Execution interface. The next step will be to integrate this SAS Job into a SAS Visual Analytics report. The integration will be covered in a future article.

Here is a summary of the series:

The basics
Write a Viya Job with a form
Integrate Viya Jobs in SAS Visual Analytics