Jobs: Stored processes in Viya

4

Stored processes were a very popular feature in SAS 9.4. They were used in reporting, analytics and web application development. In Viya, the equivalent feature is jobs. Using jobs the same way as stored processes was enhanced in Viya 3.5. In addition, there is preliminary support for promoting 9.4 stored processes to Viya. In this post, I will examine what is sure to be a popular feature for SAS 9.4 users starting out with Viya.

What is a job? In Viya, that is a complicated question as there are a number of different types of jobs. In the context of the log, we are talking about jobs that can be created in SAS Studio or in the Job Execution application. A SAS Viya job consists of a program and its definition (metadata related to the program). The job definition includes information such as the job name, the author and when it was created. After you have created a job definition, you have an execution URL that you can share with others at your site. The execution URL can be entered into a web browser and ran without opening SAS Studio, or you can run a job directly from SAS Studio. When running a SAS job, SAS Studio uses the SAS Job Execution Web Application.

In addition, you can create task prompt(XML) or an HTML form to provide a user interface to the job. When the user selects an option in the prompt and submits the job, the data specified in the form or task prompt is passed to a SAS session as global macro variables. The SAS program runs and the results are returned to the web browser. Sounds a lot like a stored process!

For more information on authoring jobs in SAS Studio, see SAS® Studio 5.2 Developer’s Guide: Working with Jobs.

With the release of Viya 3.5, there is preliminary support for the promotion of 9.4 Stored Processes to Viya Jobs. For more information on this functionality, see SAS® Viya® 3.5 Administration: Promotion (Import and Export).

How does it work? Stored processes are exported from SAS 9.4 using the export wizard or on the command-line and the resultant package can be imported to Viya using SAS Environment Manager or the sas-admin transfer CLI. The import process converts the stored processes to job definitions which can be run in SAS Studio or directly via a URL.

The new job definition includes the job metadata, SAS code and task prompts.

1. Job Metadata

The data is not included in the promotion process. However, it must be made available to the Viya compute server so that the job code and any dynamic prompts can access it. There are two Viya server contexts that need to have access to the data:

  • Job Execution Context: used when running jobs via a URL
  • SAS Studio Context: used when running jobs from SAS Studio

To make the data available, the compute server has to access it via a libname and the table must exist in the library.

To add a libname to the SAS Job Execution Compute context in SAS Environment Manager, select Contexts > Compute contexts and SAS Job Execution Compute context. Then select Edit > Advanced.

In the box labelled, “Enter each autoexec statement on a new line:” add a line for the libname. If you keep the same 8 character libname as in 9.4, you will have less to change in your code and prompts.

NOTE: the libname could be added to the /opt/sas/viya/config/etc/compsrv/default/autoexec_usermods.sas file on the compute server. While this is perhaps easier than adding to each context that requires it, this would make it available to every Viya compute process.

2. SAS Code

The SAS code that makes up the stored process is copied without modification to the Viya job definition. In most cases, the code will need to be edited so that it will run in the Viya environment. The SAS code in the definition can be modified using SAS Studio or the SAS Job Execution Web Application. Edit the code so that:

  • Any libnames in the new job code to point to the location of the data in Viya
  • Any other SAS 9 metadata related code is removed from the stored process

As you test your new jobs in Viya, additional changes may be needed to get them running.

3. Task Prompts

Prompts that were defined in metadata in the SAS 9 stored process are converted to the task prompts and stored within the job definition. The xml for the prompt definition can be accessed and edited from SAS Studio or the SAS Job Execution Web App. For more information on working with task prompts in Viya, see the SAS Studio Developers guide.

If you have shared prompts you want to include, it is recommended that you select, “Include dependent objects” when exporting from SAS 9.4. If you do not select this option, any shared prompts will be omitted from the package. If the shared prompts, libnames and tables are included in the package with the stored processes, the SAS 9 metadata based library and table definitions referenced in dynamic prompts will convert to use a library.table reference in Viya. When this happens, the XML for the prompt will include the libname.tablename of the dataset used to populate the prompt values. For example:

<datasource active=”true” name=”DataSource2″
defaultValue=”FINDATA.FINANCIAL__SUMMARY” name=”DataSource1″”>

If the libnames and tables are not included in the package, the prompt will show a url mapping to the tables location in 9.4 metadata. For example:

<DataSource active=”true” name=”DataSource2″ url=”/gelcorp/financecontent/Data/Source Data/FINANCIAL__SUMMARY(Table)”>

For the prompt to work in the latter case, you need to edit it and provide the libname.table reference in the same way as it is shown in the first example.

Including libraries and tables in a package imported to Viya results in folders that contain libraries and tables in 9.4 created in Viya. This may result in Viya folders that are not needed, because data does not reside in folders in Viya. As an administrator, you can choose:

  • Include the dependent data and tables in the package and clean up any extra folders after promotion
  • Exclude the dependent data and tables in the package and edit the data source in prompt xml to reference the libname.table (this is not a great option if you have shared prompts)

Issues encountered converting SAS 9 prompts to Viya SAS Prompt Interface will be cause warning messages to be included at the beginning of the xml defining the prompt interface.

As I mentioned earlier, you can run the job from SAS Studio or from the Job Execution Web Application. You can also run the job from a URL just like you could with stored processes in SAS 9.4. To get the URL for the job, select the job properties of in SAS Studio.

Earlier releases of Viya provided a different way to support stored processes. This consists of enabling access to the 9.4 stored process server and its stored processes in Viya. This approach is still supported in Viya 3.5.

For more information, please check out:

Share

About Author

Gerry Nelson

Principal Technical Consultant

Gerry Nelson is a Principal Technical Architect in the Global Enablement and Learning (GEL) Team within SAS R&D's Global Technical Enablement Division. His primary focus is on administration of SAS environments, particularly migration, promotion and upgrading.

4 Comments

  1. Hi Gerry Nelson,

    The blog is very informative. As we need to import the spk and edit the Job to remove 9.4 references , we would like to know if we can access stored process from backend and do bulk edits using shell script. May I know the location where we can find the Viya Jobs on the sas server.

    Regards
    Sandeep

    • Gerry Nelson
      Gerry Nelson on

      Hi Sandeep,

      Glad you found the post useful. What you are asking would be possible, although it will take some work. The jobs are stored in the infrastructure data server. You can access them using the job cli. You would need some scripting around it but this would be the process for one job definition:

      List the job definitions (came from stored processes) that start with sales to get the job definition id.

      ./sas-admin --output text job definitions list --filter 'startsWith(name,"Sales")'

      View the code for one of the job defintions.

      ./sas-admin --output json job definitions show --id 7c970c69-0ad0-4307-b541-007165de7392

      Output the job definition with the code to a JSON file.

      ./sas-admin --output json job definitions show --id 7c970c69-0ad0-4307-b541-007165de7392 > /tmp/mystp.json

      Custom process to update the code in the JSON file.

      Update the job with the new code.

      ./sas-admin --output json job definitions update --id 7c970c69-0ad0-4307-b541-007165de7392 --file-in "/tmp/UPDATED_mystp.json"

      I hope that helps.

      Gerry

  2. Useful post. However, some organizations may wait for future versions of Viya that can handle migration of SAS 9.4 stored processes a bit more seamlessly. Stored processing is a major tenant of SAS information processing that sets it apart from other products.

Leave A Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Back to Top