Create a SAS Viya Job with a prompt using Task Prompts

[#1]

In the Data Entry article series, I've been building SAS Viya jobs using HTML forms to pass parameters. SAS Viya 3.5 introduces the functionality to create prompts for SAS Viya jobs using the Common Task Model (CTM). The CTM (Common Task Model) is an XML based model that is used to build custom tasks in SAS Studio. The SAS Viya jobs are using a similar syntax to build the prompts.   

If you are new to SAS Viya and SAS Viya jobs, a quick definition is that SAS Viya jobs are the way to submit custom code in batch similar to the role of Stored Processes in SAS 9. At the end of this article, you should be able to create a prompt for a SAS Viya Job using XML syntax.

Let me first show you the prompt that will be generated.

Select any image to see a larger version.
Mobile users: If you do not see this image, scroll to the bottom of the page and select the "Full" version of this post.

The job that creates an HTML table based on SASHELP.CARS dataset. The end-user selects values for specific variables and the SAS code filters the data that are displayed in the report. The variables used to filter the data are:

• the Origin
• the Type
• the DriveTrain
• the Invoice

The prompts have dependencies between them. Selecting the origin of the car filters the prompt for the Type which impacts the list of available options in the DriveTrain drop-down list which is referred to as a combobox.

In this example, the Financial aspect section is only displayed when Asia is selected for the Origin variable. You will see the selection dependency in action from the demo I included at the end of the article.

In this example, the Financial aspect section is only displayed when Asia is selected for the Origin variable. You will see this in the little demo at the end of the article.

Building the SAS Viya Job

SAS Viya Jobs can be created from two applications: SAS Job Execution web application or SAS Studio. I will use SAS Studio as it is considered as the programming interface of choice for SAS code and SAS Viya Jobs.

Within SAS Studio 5, from the New menu, select Job > Definition.

When the job opens up in SAS Studio, choose the type of Task prompt.

You are now ready to start building the job. The job has two components:

• Code: SAS code that is executed.
• Task prompt: XML code that defines the layout of the prompt

The first step will be to create the prompt. If you want to follow along the SAS code and the Task Prompt XML are available for download: code.sas and taskPrompt.xml

Building the prompt

As mentioned, the Task prompt is defined using XML code.

In this basic example, the XML will be composed by different sections:

• Registration: information about the prompt itself
• Metadata: information about the data source and options
• UI: information about the prompt structure
• OptionDependencies: information about the dependencies between options

This is the basic structure of the file.

The next step will be to fill information for each section of the XML file.

 

Registration section

The Registration section contains the information about the prompt.

The XML tags are self-descriptive. For the Version tag, it is a good practice to specify the version that was used to create the prompt. It will be easier to maintain when future releases will add functionalities and the developer wants to know if the functionality was available or not in the release it was created in.

Metadata section

The Metadata section contains information about the data source and also the options. Let’s first see how the DataSources section is written. 

This section references the different data sources that will be used in the prompt. In this example, only one data source is used but you can define multiple sources. The prompt queries the data source to populate some of the fields. The data source needs to be accessible from the SAS Compute Server and the library should be pre-assigned when the Compute Server starts up.

The DataSources section also defines the filters that will be available for the data source. This information is used when a combobox filters the data of another element of the user interface. As you can see, the filter can reference:

• a column

• a column filtered based on expression using a value selected from the other prompts

In the typeFilter filter, the where section filters the type variable using the value of var_origin to filter out the origin variable. As you see, the Value tag uses option.

It is now time to explain what options are.

Options section

The Options are the building blocks of the prompt. They are defining the properties of the UI elements. Each option has a name that should be unique that name is used when building expressions as we have seen in the Filter but also to define elements in the UI or within the OptionsDependencies section.

In our example, the options are defined in the following code example. Click on the image to enlarge.

Every element that will be displayed in the prompt needs to be defined as an option. An option has a name and an inputType. The list of inputTypes is quite large as you can see in the documentation

Looking at the var_origin option, you see that the originFilter defined in the data source is assigned to the filter property. This creates the link between the data source and the UI element. As a result, the combobox named var_origin will be populated by the origin variable from the data source. The same will happen for var_type and var_driveTrain.

Each option type has a specific properties that are unique. If you want to know more about each type and properties, the documentation is the best source of information.

Now that building blocks of the interface are defined, it is time to build the layout of the prompt.

UI section

The UI defines different types of elements and each element points to an option to retrieve the properties to be applied.

• Container element displays a tab
• Group element displays a group of elements that can be collapsed or expanded
• OptionChoice displays elements like selects and comboboxes
• OptionItem displays a single element like an input field or a slider
• DataItem is a specific element that displays the name of the data source

For more information about the different options, refer to the documentation

Here is the UI section for our example:

OptionsDependencies section

As a last section, we have the OptionsDependencies. This section will define if elements of the UI should be displayed or not based on the value of another element. In our scenario, we want only to display the financialGroup if the var_origin is set to Asia.

Here is the code to achieve this:

Now that the prompt is defined, it is time to write the SAS code that will be executed based on the values from the prompt.

Writing the SAS code

The values selected from the prompts are passed to the SAS code as macro variables. If you want to know which macro variables are available, you can save the following code in the code tab of the job: %put _ALL_;

The code in this example is basic as the purpose is to demonstrate how to create the prompts and use the values from the prompts in the job execution.

The code first defines some macro variables that will store the different parts of the where clause used in the proc print.

The generate macro is in charge of checking the existence of the macro variable and to generate the elements of the where clause.

The last bit is a simple proc print that creates an HTML output.

Run the SAS Viya Job

Now that you have both the prompts defined and the SAS code complete, you can run the SAS Viya job. If you want to look at the SAS log after execution, you can activate the Show Log. To activate that option, you should save the changes after clicking on the button. When your job is ready for production, don't forget to inactivate that option otherwise the end-user will see the SAS Log.

Little demo

Conclusion

SAS Viya Jobs are a great way to submit custom code to solve your reporting and analytic needs. You can create prompts using different techniques HTML forms or Task Prompts. Using the Task Prompt, you don't need to know about the HTML syntax and writing XML is enough. During the building process, you should always keep an eye on the documentation as it contains a lot of information that will help you to pass the hurdles that you may encounter while building the Task Prompt. At this stage, the SAS Viya Jobs with Task Prompts can be used within SAS Job Execution web application and SAS Studio. It is not currently possible to use them within SAS Visual Analytics reports as Web Content or Data-Driven Content objects. The limitation is due to the usage of iFrames in SAS Visual Analytics and for security reasons the iFrames are not allowing pop-ups. The objective will be to give the administrators the opportunity to allow or not the pop-ups. As soon as that option will be available, the SAS Viya Jobs with Task Prompts will be the best option for the developers who prefer to write XML code to building an HTML form.

Share this content:

SAS Users channel on YouTube

YouTube 

[klroesner]

Hi Xavier, in which release will it be possible to use them in SAS Visual Analytics reports? It would be a very cool feature! 🙂 

[XavierBizoux]

Hi, 

The option has been added as mentioned in the following note: https://support.sas.com/kb/65/978.html

Here is the link to the documentation: 

https://go.documentation.sas.com/?cdcId=calcdc&cdcVersion=3.5&docsetId=calconfig&docsetTarget=n08025...

Regards, 

Xavier

[klroesner]

Thanks Xavier, I found the option ... 🙂

[smespb]

Hi Xavier,

Thank you for the detailed example on task prompts.

I have built a SAS Studio task prompt for one of our client requirements and integrated it in the SAS Visual Investigator solution. The prompt displays correctly and the code executes perfectly on submission of the prompt by clicking on the Submit button.

However, the task prompt does not close on clicking the submit button, instead it opens up one more new blank tab and executes the code in background. Now, the user will have to close both these windows manually. Is this the default behavior or can it be changed to close the task prompt window and the new tab being opened on submission of the task prompt?

[XavierBizoux]

Hi, 

I suppose that you are passing the URL of the job somehow to display it. 

At the end of the job URL, you should add the following parameter: &_target=_self

It will force the prompt and the results to be displayed in the same web page. 

Your URL might look like this: 

https:/servername/SASJobExecution/?_program=%2FUsers%2Fgatedemoxxx%2FMy%20Folder%2Fgatedemoxxx_job_prompt&_target=_self

Let me know if that doesn't do the trick. 

Regards, 

Xavier

[smespb]

Hi Xavier,

Yes, I am passing the job URL to a HTML form.

I had tried the target=_self option on the URL. It displays the prompt on the same web page. But on clicking submit, it opens a new browser tab, leaving the previous task prompt open in same state while clicking submit. I am fine with the task prompt opening in new tab, but I want it to close or display a message depending on status of code run post submission.

Could you help me on that direction?

Regards,

Sumit.

[jimbobob]

I'm struggling trying to understand how use these task prompts. I want a datepicker as my option and then pass that value to my sql statements to filter some data from different tables, bring it together and print it out. I didn't put any <datasources> logic in the xml since my code is hitting multiple tables. I didn't put and <filters> logic either since I was under the impression this is for dynamic prompts. I get the date picker to show up when I run job, but after picking date, and submitting, nothing happens.

<?xml version="1.0" encoding="UTF-8"?>
<Task schemaVersion="7.2">
    <Registration>
        <Name>Date Picker</Name>
        <Description>Testing Date Prompt</Description>
        <Version>5.2</Version>
    </Registration>
    <Metadata>
        <Options>
			<Option name="GROUPDATE" inputType="string">DATE PICKER</Option>
			<Option name="startdate" inputType="datepicker" format="date9.">Choose a date:</Option>
        </Options>
   </Metadata>
<UI>
   <Group option="GROUPDATE">
      <OptionItem option="startdate"/>
   </Group>
</UI>
</Task>

How do I pass the startdate to my sql statement

Libname FINTECH cas caslib='FintechData';
%global startdate;
PROC SQL;
	CREATE TABLE WORK.part1 AS SELECT 
		t1.BUSINESS_DAY, 
		t1.SUBJECT_ACCOUNT_NUMBER, 
		(SUM(t1.AMOUNT)) AS TOTAL_BY_ACCOUNT, 
		(COUNT(t1.TRANSACTION_ID)) AS TRAN_COUNT
	FROM FINTECH.ACTIMIZE_TRANSACTION t1
	WHERE t1.BUSINESS_DAY = "&startdate"d 
	GROUP BY t1.BUSINESS_DAY, t1.SUBJECT_ACCOUNT_NUMBER
	ORDER BY t1.SUBJECT_ACCOUNT_NUMBER, t1.BUSINESS_DAY;
QUIT;
proc print data=work.part1;
run;

[XavierBizoux]

Hi @smespb , 

Sorry for the late answer, the parameter is not target but _target. Could you please try with that parameter and let me know if that makes a difference? 

Regards, 

Xavier

[XavierBizoux]

Hi @jimbobob ,

I see that you are executing the proc sql against a CAS library. It is fine but you need to start a CAS session in order to access the CAS table. You can therefore add the following statement at the top of your SAS code: 

cas; 

It will start a CAS session named CASAUTO. If you prefer to give it a specific name and pass specific options, it is also possible. You should use a cas statement similar to this one:

cas mySession sessopts=( ... )

You can pass different options which are documented here. 

Please let me know if adding the cas statement solves your issue. 

Regards, 

Xavier

PS: you should be able to execute the job with the Show Log option activated in order to display the log when executing. This can be useful when developing the code. When running in Production mode, you should disable the option.

[jimbobob]

Xavier thanks for responding, I added the cas; statement to top of my code, submitting job still does nothing, doesn't run. no log since it doesn't run, I have the show log button selected, also if it help my environment is purely sas viya, working in sas studio. I also have the table i'm hitting already assigned and loaded, I can run queries against it like the sql from above, but now I'm trying to pass the "startdate" to the code. Do I have to enable something, or am i missing some logic in my xml, or referring to the "startdate" incorrectly in the sql?

[smespb]

Hi @jimbobob ,

I had also noticed this behavior with datepicker controls on the prompt. I am not sure whether it is a bug or we are missing out on some settings. But what i noticed is, if you replace the datepicker with some other control eg. a textbox, the prompt submits successfully. Also, if you copy the link for this Job and hit it on browser and submit the prompt with datepicker control, it submits successfully. It only has some issues when we run the job from SASStudio.

Could you please try either of the 2 things mentioned above -

1. Replacing the datepicker control and submitting the prompt in SAS Studio

2. Submitting the prompt with datepicker control by hitting the JOB URL in seperate browser tab.

Thanks,
Sumit.

[XavierBizoux]

Hi @jimbobob 

I looked in out tracking system and it seems that in some situations, the datepicker will not run in SAS Studio when datepickers are used. 

In the track, they mention that the parameter should not be defined in the job parameters in order to execute the job. 

They also mention that the same job (with the parameters defined) should execute properly if executed from the SAS Job Execution Web application. 

In my test (on Viya 2021.1), I did not define the parameters in the job definition. 

Could you please remove the parameters from the job definition and let me know if that makes a difference. If not, I would suggest to open a track with Technical Support as this is most probably the best way to move this forward. 

Regards, 

Xavier

[jimbobob]

Hi xavier, 

Getting back to this, my production system needed a complete rebuild, so today went back to to test this and now works with the same logic/xml I used above. So must have been something wrong with original install. I appreciate your help.

[XavierBizoux]

Hi @jimbobob 

Happy to read that the problem is now solved.

Let me know if you need more help.

Regards,

Xavier

[jimbobob]

Actually @XavierBizoux , I'm loving this xml task prompt feature in viya, so much potential. I'm stuck trying to figure out when I create a select type prompt, and want to allow multiple values and how to feed that into a IN clause in sql, from my log I can see I chose the 2 of the three choices from my select, in the log it shows: 

 RULE7=ADJUSTMENT
 RULE7_COUNT=2
 RULE70=2
 RULE71=ADJUSTMENT
 RULE72=CASH

 feeding that into my sql statement:

10   %global startdate enddate rule1 rule2 rule3 rule4 rule5 rule6 rule7;
11   PROC SQL;
12   	CREATE TABLE WORK.part1 AS SELECT
13   		t1.BUSINESS_DAY,
14   		t1.SUBJECT_ACCOUNT_NUMBER,
15   		(SUM(t1.AMOUNT)) AS TOTAL_BY_ACCOUNT,
16   		(COUNT(t1.TRANSACTION_ID)) AS TRAN_COUNT
17   	FROM FINTECH.ACTIMIZE_TRANSACTION t1
18   	WHERE t1.BUSINESS_DAY Between "&startdate"d and "&enddate"d
19   		and t1.TYPE IN ("&rule7")
20   	GROUP BY t1.BUSINESS_DAY, t1.SUBJECT_ACCOUNT_NUMBER
21   	ORDER BY t1.SUBJECT_ACCOUNT_NUMBER, t1.BUSINESS_DAY;
NOTE: Table WORK.PART1 created, with 34448 rows and 4 columns.

it appears to only pick up on the first choice of ADJUSTMENT, how do I get it to put the choices into a In list format to feed the sql where clause, should I be using a different inputtype? From the log looks like it breaks my macro variable into RULE71 and RULE72, and looks like i need to figure out how to combine those into a comma separated list. Any help would be awesome. thanks.

[jimbobob]

Nevermind @XavierBizoux , found that parameter macro, and works like a charm. 

[acordes]

Thanks @XavierBizoux 

I've found the solution, it's the compute context.

https://go.documentation.sas.com/doc/en/pgmsascdc/v_017/jobexecug/n1cl67g4y8t1ofn14i0e8iqdhk2k.htm 

[acordes]

Now it works perfectly fine in SAS Studio and JobExecution but it does not execute in VA when I press the Submit button. It stays on the html form. 

Any Idea?

[XavierBizoux]

Hi Arne, 

To use SAS Viya Jobs in VA, you need to configure the environment properly. 

You might refer to this blog https://communities.sas.com/t5/SAS-Communities-Library/SAS-Viya-Jobs-in-a-SAS-Visual-Analytics-repor...

It covers the integration of jobs in VA reports. 

You should make sure that the prompts are enabled. 

Let me know if you are facing issues. 

Regards,

x@b

[acordes]

Hi @XavierBizoux , at the end of your blog entry you state:

"It is not currently possible to use them within SAS Visual Analytics reports as Web Content or Data-Driven Content objects. The limitation is due to the usage of iFrames in SAS Visual Analytics and for security reasons the iFrames are not allowing pop-ups."

Therefore I stopped following on this issue and take for granted that the user must use sas studio as entry point for the job calling the prompt. 

[jins]

HI,

Kind of new to SAS Jobs in SAS studio. Couple of questions:

1. Is there a way to hide this in the UI. Looks like there are no options for this. I'm using a filter on this data sets and there are couple of them

   <DataItem data="dataset1"/>

 2. Also, once I submit, there seems to be nothing happening and unable to see the log. What might be happening and how should i troubleshoot this?

[Stephane]

Hi jins

please use 

<DataItem data="dataset1" hide="true"/>

you can also use it in the Datasource definition.

you will show the log by clicking the Show LOG button. Did you see it ?

[jins]

HI Stephane,

It works in the Datasource definition not in DataItem. 

Once I press the submit button in the preview, nothing seems to activate. I currently have a simple "proc print data=sashelp.cars;run;" in the Code section. The Show log button does not show anything or does not seem active. For a different job this works.

Thanks!

[jins]

HI Stephane,

when I run that via the url "proc print data=sashelp.cars;run;" and submit it, it runs fine. But not within SAS studio. Just wanted to let you know. Not sure if this information helps.

Thanks again!

[Stephane]

Like that no. 
dynamic Cars works on Studio ?

[jins]

The dynamic car works fine on Studio(via preview) as well as via url. The one which I'm working on does not seem to activate the submit button in the Studio(preview) but works fine via the url. 

[XavierBizoux]

Hi @jins 

If the job works fine with the prompt using the URL and it doesn't work when using the SAS Studio, I would suggest to open a ticket with Technical Support. There should be no reason for the job to behave differently in SAS Studio or using the URL. 

If you still want to investigate on your own, you might want to test in SAS Studio after starting the browser in Incognito mode. Usually browser extensions are not enabled in that mode and some extensions might interfere with SAS Studio. 

It would also be good to check in SAS Studio from another browser. 

Let me know if you need help. 

Regards, 

Xavier

[jimbobob]

@XavierBizoux is it possible to use a CAS memory table instead of a SAS dataset in the the datasources section of the xml? I tried pointing my task to one, but doesn't seem to work. I figured that would be faster to use for my combobox as i have a large list of values but the column is grayed out and will not let me select anything versus when I point it to a sas dataset. Is there a better way of doing this especially with really large list of customer id's to choose from (millions), I want them to have to choose an id for data integrity purposes. Any help would be appreciated. thanks.

[XavierBizoux]

Hi,

The CAS libraries are not accessible by default You need to assign the libraries using the CAS libname engine in the autoexec of the Compute Server. It also means that you need to start a CAS session before assigning the libraries.
Let me know if you need more information.

Regards,

Xavier

[obendav]

I created a SAS report using SAS Viya with prompts, but I am having an error that the dataset doesn't exist even though the dataset is visible in the work library and the filters appears in the prompts. Please help me.

[obendav]

Xavier,

I created a SAS report using SAS Viya with prompts, but I am having an error that the dataset doesn't exist even though the dataset is visible in the work library and the filters appears in the prompts. Please help me!

Thanks,

David

[XavierBizoux]

Hi David, 

You should be careful when building SAS Viya jobs with prompts. The jobs executed in SAS Studio are not running in the same Compute session as the one used to behind the scenes in SAS Studio to execute code. What you see in the SAS Studio interface is the content of the SAS Studio Compute session. This means that the job when executing doesn't have access to the work library which is specific to the session. If you want to share data between the SAS Job and the Compute server session used by SAS Studio, you should store the data in a persistent library. Basically, a library which will exist between Compute server sessions. To achieve this, you should define the libraries on the server. 

Let me know if you need more information.

Xavier

[obendav]

Xavier, 

Please do you have a sample code or guide on how to define libraries on the server? From search online i have not been able to find something that works. 

Thanks,

David 

[XavierBizoux]

Hi David,

You can most probably find useful information in this blog: https://communities.sas.com/t5/SAS-Communities-Library/Managing-Connections-to-Data-Sources-and-SAS-...

Let me know if you have questions.

Regards,

‘Xavier

[illmatic]

Unable to load the task due to one or more errors: 1: The version of this file is out of date. You can only run tasks that were created in SAS Studio 3.1 and later.

Any guidance on how to work around this error on a new task? I'm using Studio 5.2

[XavierBizoux]

Hi,

I just tested the job with the task prompt code from the GitHub repository on Viya latest LTS release and the code is still valid. Could you please confirm that you used the provided task prompt and code? Did you create a job or a task?

Regards,

Xavier

[illmatic]

Hi Xavier,

I found the issue. I changed the 5.2 version to 1.0, but I have since changed it back and all is well.

[Babloo]

@XavierBizoux  This article is really helpful. However I'd like to give the little background where I need the help. We already have the prompts in SAS Viya Job execution. In order to improve the performance and for better UI, I was asked to create the standalone webpage using HTML and CSS.

I wrote the HTML code for the prompts where the drop down values are not dependent on the SAS datasets. For few prompts we have numerous values and it is fetching values from SAS datasets. Now I need some help to tackle this scenario. Any insights?

[laagri]

Hey! 

This has been SUPER useful to me. It's really intuitive to use as someone without a computer science background. Question, though: when using a Task Prompt inside of SAS Visual Analytics, is there any way to make the Job Path not display at the top of the object? 

[XavierBizoux]

@laagri 

Currently the best option to hide the path is to change the title of the job using the _title url parameter and set it to blank. 

This can be done using a url like this one: 

https://server.demo.sas.com/SASJobExecution/?_program=%2FUsers%2FmyUser%2FMy%20Folder%2FNoTitleJob&_title=%20

It means you should just add &_title=%20 at the end of the job url.

Let me know if you have question. 

Regards, 

Xavier