CAS Table State Management for everybody

2 Likes

The CAS Table State Management, is a functionality available to the SAS Administrator. The CAS table state management enables you to manage the import and load of source files as well as the unload of tables from memory in caslibs. It is made available to the SAS administrator using predefined sample jobs.

This article will show how these jobs can be made available to a normal user, so that he can take advantage of the functionality provided by these jobs.

Topics we will look into

Introduction to the sample jobs
Make a copy of a sample job
Create new jobs from a template
Considerations

Introduction to the sample jobs

There are 3 Table State Management jobs available:

Sample: Import cas-shared-default Public data
This job demonstrates settings that import all CSV, SAS7BDAT, and EXCEL files in the Public caslib to SASHDAT files in the same caslib.
Sample: Load cas-shared-default Public data
This job demonstrates how to load all SASHDAT files found in the Public caslib.
Sample: Unload cas-shared-default Public data
This job demonstrates how to unload all loaded CAS tables in the Public caslib that have not been accessed within the past 7 days.

These job are available to the SAS Administrator in a read-only mode. In order to use them, the SAS Administrator will make a copy of one of these jobs and can then edit the options and settings for the respective job.

We will show how any user can take advantage of the functionality provided by these jobs to import, load and unload data to a caslib without writing any code.

Here we see where the SAS Administrator can find them.

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

Below we can see the options and settings (partial list) for the Sample: Load cas-shared-default Public data job.

Since this is the original sample job, there is no way for us to edit this information. To actually work with one of the sample jobs, the SAS Administrator needs to make a copy.

Make a copy of a sample job

To make a copy of the job, the SAS Administrator uses the context menu on one of the sample jobs and choose Copy. We can provide a name and a description for the new job. By default the text "_copy" is added to the end of the name. For our example I changed the name of the caslib in both the name and description and removed the "Sample:" from the name.

The copy of this job is only available to SAS Administrators.

Let's compare the two jobs. They are jobRequest objects, so we can use the sas-viya job requests CLI to have a closer look.

First the SAS Administrator needs to find the id of a jobRequest object. He can do this either by displaying the properties, or he can use the command below. Please be aware that the eq function is case sensitive by default. To make the function ignore the case use this filter definition:

--filter 'eq($primary, name, "sample: load cas-shared-default public data")'

OID=$(sas-viya --output json job requests list --filter 'eq(name, "Sample: Load cas-shared-default Public data")' | jq -r '.items[].id')

Likewise the SAS Administrator does this for the new job

NID=$(sas-viya --output json job requests list --filter 'eq(name, "Load cas-shared-default

Finance data")' | jq -r '.items[].id')

The SAS Administrator has now the id's of the original job (OID) and the new job (NID). With this information he can store the definition of a job in a JSON file and compare the contents.

sas-viya --output json job requests show --id $OID > ojob.json

sas-viya --output json job requests show --id $NID > njob.json

Now let's compare the two files using icdiff (might not be installed by default) or you preferred tool to compare files.

We can see the main differences being the user that created/modified the job, id, timestamp information, name and description. The highlighted text shows whether it is a sample job, or a copy of a job. This is what determines if we can edit the options and settings for this job.

With this knowledge we can make a copy of a job without going through the GUI. Either using an editor and make the changes or using the functionality provided by the jq command, that allows us to manipulate a JSON file. Here is an example:

This command changes the text for "value": "true" to "value": "copy" and provides a generic value for name and description. You can verify the content of the new file using:

jq . load-name-caslib.json

We now have a json file that can be used to create new job. In this new job we will be able to edit the options and settings, like caslib, which type of files to load etc.

Note: All the sas-viya commands have to be run as an SAS Administrator because of permissions.

Create new jobs from a template

The JSON file created before can now be used as a template to create new jobs using this command:

sas-viya job requests create

This command supports two options:

--file-in
Specifies the input file that contains the details about the job-request definition.
--folder-id (optional)
Specifies the ID of the parent folder in which to save the job-request definition.

Providing a folder id, has several advantages like: you can rename a job, all users that have the appropriate permission for the folder content can work with this job.

The information on user created/modified, id, timestamp information still present in the JSON file is ignored when creating a new job. To get the id of a folder and store it in a environment variable we use this command:

Public_ID=$(sas-viya --output json folders show --path /Public | jq -r '.id')

We are now ready to create a new job and store it in a folder.

sas-viya job requests create --file-in load-name-caslib.json --folder-id $Public_ID

Although the job is a member in a folder, in order to edit the settings of the job you go to SAS Environment Manager - Jobs & Flows - Scheduling tab. When displaying the properties of a job we can go to the Arguments tab and see the edit icon, that allows us to change the caslibs, filter (which files to process) and other settings.

See the Job Options for more details on what can be set.

From the Jobs and Flows page we can now schedule a job so that it runs on regular intervals. Please note, only the user that executed a job will see the result of his job. The SAS Administrator will see all the jobs from all the users.

Using this method more jobs can be created for additional caslibs.

Considerations

The user executing one of the Table State Management jobs needs the appropriate permissions on the caslibs used by the job.

Import and Load jobs need the following permissions: ReadInfo, Select, LimitedPromote, Promote, CreateTable, DropTable, DeleteSource.
The Unload job needs the following permissions: ReadInfo, Select, DropTable

If a group of users need to maintain a job and look at the results of the executions see Using a Group-Managed Service Account for more information.

We can use the jobRequest API to create jobs. Here is a basic example using a SAS program.

Summary

By default the Table State Management jobs are only available to the SAS Administrator. We have seen how the SAS Administrator can provide this functionality to normal users. This allows them to take advantage of the functionality provided by these jobs to load, import or unload data without any coding.

Additional reading: SAS Viya jobs object model explained to better understand what a job is.

Find more articles from SAS Global Enablement and Learning here.