Calling REST API’s from SAS : Using proc http to extract report content

7 Likes

In most of my blogs, I use Python to call REST API's. In this blog, I will use the SAS language to extract report content. As you will see, this is straightforward and requires intermediate SAS programming skills. If you attended the SAS Programming 1 and SAS Macro Language 1 trainings, you should be ready to understand the code I will describe and then write your own.

Some companies request SAS to integrate reports in their DevOps processes. They want to handle the report versioning in Git repositories like they would do for code. To build such a process, they can use sas-admin CLI to extract information from their Viya environment and then store it in a Git repository for the versioning. The same tasks can also be achieved using REST API's from any programming language that can handle http requests. The developer.sas.com web site provides examples for languages like Python, JavaScript, Go or using the curl command line. You may be surprised to learn you can even do this in SAS code and I wanted to provide an example. The most important part of this blog is not the SAS code but more the logic behind it and to explain how you can reuse the examples provided for other languages on the developer.sas.com.

Program Logic

What makes the usage of SAS Viya REST API's a bit challenging is the granularity of the API's. SAS Viya offers REST API endpoints for most of the actions that can be achieved in a user interface. What does that mean practically? Well this means that when you want to extract the structure of the reports in a folder you will need to perform a few tasks/steps. Using a user interface, you would:

Navigate to the report location
Select the report you want to extract
Extract the content/structure of the specific report
Save the report content to a file
Perform steps 2 to 4 for each report

Our code will follow the exact same steps but in a more computational way. Let me describe the steps to transform the previous logic into SAS code:

Retrieve the ID of the folder where the report resides
Get a list of reports in that specific folder
Loop though the list of reports and extract report content (structure)
Write the report information and content to a json file

Let's start with a basic call to a SAS Viya REST API.

First Call

The basic procedure to call REST API's from SAS is the proc http. As its name indicates, it makes HTTP requests. A simple request to a SAS Viya REST API has the following form.

This example is close to step one in our logic: it retrieves a list of folders. In the Full Code section, we will retrieve a specific folder using a query option in the proc http.

filename folders temp;
proc http
    url = "https://intviya01.race.sas.com/folders/folders"
    out= folders
    oauth_bearer = sas_services;
    headers
        'Accept'= 'application/vnd.sas.collection+json';
run;
libname folders clear;
libname folders json;

The simplest request calls url https://intviya01.race.sas.com/folders/folders and write output to a temporary file called folders. The last part of the url is called the endpoint and in this case is set to /folders/folders. As the SAS Viya REST API's require authentication, an oauth_bearer option is set to sas_services. This is a trick to reuse the authentication token that is already in use for SAS Studio (where we write the code). In addition to these parameters, we need to specify the headers statement. This statement adds extra information to the HTTP header request. In order to access the output of the HTTP request, we assign a library using the json engine. You may ask yourself why there is no reference to a file in the json libname. By default, SAS will use a fileref with the same name as the library name if there is no file reference in the libname. This is an easy way to write less code.

You might have a question now: How do I know which values should be set for the url, endpoint and headers?

Collecting Information

The developer.sas.com website is THE source of information when you are building requests to SAS Viya REST API's. On the web site, you can explore the different endpoints and get information about the different operations they support.

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

For each operation, you get a description and a list of possible parameters:

You also get sample code for different languages and the type of response you will get.

At the first look to this documentation, it may feel obscure. As soon as you become acquainted to it, it will become your best friend.

First Response

The biggest step will be the first one. And that step has already been done using the example in the First Call section of this blog. You can copy the code from that section and execute it within SAS Studio. Check the log and validate that there is no error.

Well done! You can now look at the results and output of your call to a SAS Viya REST API using SAS code. Open the FOLDERS library in SAS Studio and look at the content:

The different tables in the FOLDERS library are generated by the json libname engine for you. Each table contains different kinds of information.

ALLDATA contains the raw extract from the json returned by the REST API call
ITEMS contains the list of folders extracted by the REST API call
ITEMS_LINKS contains link information for each row in the ITEMS table
ITEMS_PROPERTIES contains properties for each row in the ITEMS table
LINKS contains the endpoint information that can be used to replay the REST API call
ROOT contains the current endpoint information

ITEMS and ITEMS_LINKS are interesting as they provide the list of folders returned by the REST API call and ITEMS_LINKS provide information about the endpoints that can be called for each folder. This information is useful to build new calls. The ITEMS and ITEMS_LINKS can be merged by ordinal_items.

The information in the table depends on the response that the REST API sent. So, this information is endpoint specific. This means that you should explore the outcome of your code while developing with a specific objective in mind.

Full Code

By now, you should have a better understanding about the SAS Viya REST API's, how to call them and how to visualize their response. It's now time to see how you achieve the task described in the second part of this blog's title: Using proc http to extract report content.

Our code achieves the following steps:

Retrieve the ID of the folder where the report resides
Get a list of reports in that specific folder
Loop though the list of reports and extract report content (structure)
Write the report information and content to a json file

%let hostname = https://intviya01.race.sas.com;
%let endpoint = /folders/folders;
%let path = "/Users/sbxxab/My Folder" ;
%let outfolder= /tmp; 

/**********************************************************/
/* Retrieve the ID of the folder where the report resides */
/**********************************************************/
filename folders clear;
filename folders temp; 
proc http
    url = "&hostname.&endpoint/@item"
    query = ("path"=&path)
    out= folders
    oauth_bearer = sas_services;
    headers
         'Accept'= 'application/vnd.sas.content.folder+json';
run; 

libname folders clear;
libname folders json; 

/*************************************************/
/* Get a list of objects in that specific folder */
/*************************************************/
/* Identify the endpoint to be used */
proc sql noprint;
    select href, type into :endpoint, :type
    from folders.links
    where method="GET" and rel = "members";
quit; 

/* Retrieve the list of objects */
filename memList clear;
filename memList temp; 

proc http
    url = "&hostname.%trim(&endpoint)"
    out= memList
    oauth_bearer = sas_services;
    headers
        "Accept"= "%trim(&type)+json";
run; 

libname memList clear;
libname memList json; 

/******************************************************/
/* Extract report content (structure) for each report */
/******************************************************/
/* Macro to write the output to a json file */
%sysmacdelete writeJson;
%macro writeJson(name, path, output, content);
    filename out "&output/&path._&name..json";     
    proc json out=out nosastags pretty noscan;
        export info;
    run;
%mend; 

/* Macro to read the report content and generate the output file */
%sysmacdelete readReportContent;
%macro readReportContent (url, name, path, endpoint, output);
    filename content clear;
    filename content temp;     
    proc http
        url="&url.%trim(&endpoint)/content"
        out=content
        oauth_bearer=sas_services;
        headers
            "Accept"="application/vnd.sas.report.content+json";
    quit;     
    data info;
        length report $128
            path $1024
            content $32767;
        infile content;
        input;
        report="&name";
        path="&path";
        content=_infile_;
    run;     
    data _null_;
       set info;
       where path ne '';
       location=tranwrd(path, "/", "_");
       mcall=cat('%writeJson(', trim(report), ',', trim(location), ',', trim("&outFolder"), ')');
       call execute(mcall);
    run;     
    proc sql;
        drop table info;
    quit;
%mend; 

/* Generate a view containing information to call readReportContent */
proc sql;
    create view merged as
    select "&hostname" as url,
        a.ordinal_items,
        a. name,
        b.href,
        b.type ,
        b.method
    from memlist.items as a
    left join memlist.items_links as b
    on a.ordinal_items=b.ordinal_items
    having a.contentType="report" and b.rel="getResource";
quit; 

/* Call readReportContent macro for each report */
data _null_;
    length mcall $1024;
    set merged;
    out=&output;
    path=&path;
    mcall=cat('%readReportContent(', trim(url), ",", trim(name), ",", trim(path),",", trim(href), ",", trim(out), ')');
    call execute(mcall);
run;

The code generates a json file for each report. The file contains the report name, original location in the SAS Content Server and the report content. Here is the outcome for one report:

If you want to execute the code on your environment, only the macro variables at the beginning of the code need to be adapted.

%let hostname = https://intviya01.race.sas.com;
%let endpoint = /folders/folders;
%let path = "/Users/sbxxab/My Folder" ;
%let outfolder = /tmp;

Conclusion

Using proc http, you can easily call SAS Viya REST API's. In order to know how to call the REST API's, the developer.sas.com website is your best friend.

In this example, I've been extracting data from SAS Viya. The REST API's are also designed to update resources. Even though it is possible, I've to admit that I prefer to use python or any other language with enhanced JSON support when it comes to updating data into SAS Viya. Python, JavaScript and Go are by nature better than the combination of proc json and json libname when it comes to handling json objects. If you plan to update the information extracted from SAS Viya and to upload the updated information, you might feel the process becomes really complex due to the current json functionalities offered by SAS.

The code above is yours, tweak it in the way you want!

Find more articles from SAS Global Enablement and Learning here.