PROC HTTP

7月 302020
 
How often have you needed Google Translate, but for SAS code?

SAS Technical Support often gets requests like the following: "I have this API named <insert name of really cool API here> and I want to process data I get back from the API with SAS. How do I do it?"

In this article, I'll show you how to translate the examples from your API documentation (offered in a select few languages) to the equivalent in SAS.

Test your API with cURL

My first recommendation is to know your API. Most APIs come with documentation and will give specific guidance regarding how to POST data (send) to the API and how to GET data (receive) from the API. Often, examples are provided using cURL commands.

With this information, you are welcome to examine the SAS documentation for the HTTP procedure and build your code. Before you call or email SAS Technical Support asking for PROC HTTP code, I encourage you to verify that you can communicate with your API (or URL) from outside of SAS. One way to do so is with cURL. cURL (Client URL) is a command-line tool that is shipped with many UNIX flavors and installed easily on Windows. With a cURL command, you can interact with your URLs from outside of SAS from a command-line prompt like this:

curl -o "c:\temp\file.txt" -request "https://httpbin.org/get"

From SAS, you can use a cURL command with the DATA step, like this:

data _null_;
 %sysexec curl -o "c:\temp\file.txt" -request "https://httpbin.org/get";
run;

File.txt contains the response from the URL:

{
  "args": {}, 
  "headers": {
    "Accept": "*/*", 
    "Host": "httpbin.org", 
    "Range": "bytes=Request", 
    "User-Agent": "curl/7.46.0", 
    "X-Amzn-Trace-Id": "Root=1-5f028cd3-2ec7e1e05da1f616e9106ee8"
  }, 
  "origin": "149.173.8.1", 
  "url": "https://httpbin.org/get"
}

However, if you use SAS® Enterprise Guide® or SAS® Studio, you might not have permissions to run operating system commands from SAS, so you need a way to translate your cURL commands to SAS. The previous cURL command is easily translated to the following PROC HTTP code:

filename out "c:\temp\file.txt";
proc http url="https://httpbin.org/get" out=out;
run;
  1. The -o (OUTPUT) cURL argument translates to the OUT= argument in PROC HTTP.
  2. The -request argument defaults to a GET for cURL (also the default for PROC HTTP, so METHOD=“GET” is the correct syntax but unnecessary for this step).
  3. Note: The URL= argument is always quoted.

The cURL command supports many options and features. Check out the cURL reference page. SAS can't guarantee that all are directly translatable to PROC HTTP, but I do want to cover some of the most popular that SAS customers have asked about.

Sending data to an API

If your cURL command uses the -d (DATA) option, you'll use the IN= argument in your PROC HTTP statement. Here I am posting to the URL httpbin.org/post a file called test.csv, which resides in my c:\temp directory:

curl -d "c:\temp\test.csv" -X post "https://httpbin.org/post";

This command translates to the following PROC HTTP code:

filename test "c:\temp\test.csv";
 
proc http url="https://httpbin.org/post"
 /* If the IN= argument is used then method="post" is the default */ 
 /* and therefore unnecessary in this step */
 method="post"
 in=test;
run;

Working with authentication

None of the URLs above require authentication, but you'll likely find authentication is part of most APIs. Many APIs have moved to OAuth for authentication. This method of authentication requires the use of an access token, which you obtain with a POST command. With the correct credentials, this cURL command posts to the SAS® Viya® SASLogon REST API in order to obtain an access token:

 
curl -X POST "https://server.example.com/SASLogon/oauth/token" \
      -H "Content-Type: application/x-www-form-urlencoded" \
      -d "grant_type=password&username=userid&password=mypassword" \
      -u "app:mysecret" -o "c:\temp\token.txt"

The following PROC HTTP code does the same task:

 filename out temp;
 proc http url="http://servername/SASLogon/oauth/token"
    in="grant_type=password&username=userid&password=mypassword"
    webusername="clientid"
    webpassword="clientsecret"
    method="post"
    out=out;
   headers "Content-Type"="application/x-www-form-urlencoded";
run;
  1. The -u option is for user ID and password.*
  2. The -o command/output captures the response, in this case a JSON file. In this case you mimic -o with a FILENAME statement to write the text in JSON format to the WORK library location.
  3. The -H command is popular and translates to the HEADERS statement in PROC HTTP.

*Read about ways to hide your credentials in Chris Hemedinger's post here: How to secure your REST API credentials in SAS programs.

The output file contains an access token, necessary to make requests on behalf of a client to the REST API. In this example, a cURL command like the following requests a list of folders from the Folders microservice:

curl -X GET "https://server.example.com/folders/folders/@myFolder" \
      -H "Accept: application/json" \
      -H "Authorization: Bearer TOKEN-STRING"

The PROC HTTP code will look like this if you "directly translate":

filename new temp;
 
proc http url=" https://server.example.com/folders/folders/@myFolder"
 method="get" out=new;
 headers "Accept"="application/json" 
         "Authorization"="Bearer TOKEN-STRING";
run;

But starting in SAS® 9.4M5, there's a shortcut with the OAUTH_BEARER option:

filename new temp;
 
proc http OAUTH_BEARER="TOKEN-STRING" 
 url="https://server.example.com/folders/folders/@myFolder" 
 method="get" 
 out=new;
run;

Processing JSON responses with the JSON engine

I can't tell you about PROC HTTP without a mention of the JSON engine. Starting in SAS® 9.4m4, the JSON engine enables us to easily read JSON files. I can use the previous cURL command to pipe my access token to a file with the -o argument, but using my PROC HTTP code I can easily move that value into a macro variable. I'll add a LIBNAME statement that points to the fileref in the previous step:

libname test json fileref=new;

I can then examine the contents of the JSON output with this step:

proc contents data=test._all_;
run;

Here I spy the access token I will need for a later PROC HTTP step:

Here's how I can place it in a macro variable:

data _null_;
 set test.root;
 call symputx("access_token",access_token);
run;
 
%put &access_token;

So everywhere I used TOKEN-STRING in the previous code, I can now use the macro variable instead, like this:

proc http OAUTH_BEARER="&access_token" 
 url="https://server.example.com/folders/folders/@myFolder" 
 method="get" 
 out=new;
run;

Debugging with PROC HTTP

With the cURL command, you can use the -v (verbose) argument to get connection and header information. It's helpful for debugging and diagnosing trouble spots.

In SAS® 9.4M5, the DEBUG statement was added to PROC HTTP. DEBUG supports several options. I'll highlight the LEVEL= option here:

 Level= 0 | 1 | 2 | 3

Selecting 0 provides no debugging information while 3 provides the highest amount of data and messages. Base SAS 9.4 Procedures Guide includes full descriptions of each debug level.

See the HTTP Procedure documentation for additional syntax.

Generating cURL commands with Postman

If you want someone to "write your code for you," I recommend using a product like Postman to test your POST and GET commands with your API from outside of SAS. Postman is an open-source product with a super cool feature: it will produce CURL commands from successful communication with a URL.

Recommended resources

How to translate your cURL command into SAS code was published on SAS Users.

7月 302020
 
How often have you needed Google Translate, but for SAS code?

SAS Technical Support often gets requests like the following: "I have this API named <insert name of really cool API here> and I want to process data I get back from the API with SAS. How do I do it?"

In this article, I'll show you how to translate the examples from your API documentation (offered in a select few languages) to the equivalent in SAS.

Test your API with cURL

My first recommendation is to know your API. Most APIs come with documentation and will give specific guidance regarding how to POST data (send) to the API and how to GET data (receive) from the API. Often, examples are provided using cURL commands.

With this information, you are welcome to examine the SAS documentation for the HTTP procedure and build your code. Before you call or email SAS Technical Support asking for PROC HTTP code, I encourage you to verify that you can communicate with your API (or URL) from outside of SAS. One way to do so is with cURL. cURL (Client URL) is a command-line tool that is shipped with many UNIX flavors and installed easily on Windows. With a cURL command, you can interact with your URLs from outside of SAS from a command-line prompt like this:

curl -o "c:\temp\file.txt" -request "https://httpbin.org/get"

From SAS, you can use a cURL command with the DATA step, like this:

data _null_;
 %sysexec curl -o "c:\temp\file.txt" -request "https://httpbin.org/get";
run;

File.txt contains the response from the URL:

{
  "args": {}, 
  "headers": {
    "Accept": "*/*", 
    "Host": "httpbin.org", 
    "Range": "bytes=Request", 
    "User-Agent": "curl/7.46.0", 
    "X-Amzn-Trace-Id": "Root=1-5f028cd3-2ec7e1e05da1f616e9106ee8"
  }, 
  "origin": "149.173.8.1", 
  "url": "https://httpbin.org/get"
}

However, if you use SAS® Enterprise Guide® or SAS® Studio, you might not have permissions to run operating system commands from SAS, so you need a way to translate your cURL commands to SAS. The previous cURL command is easily translated to the following PROC HTTP code:

filename out "c:\temp\file.txt";
proc http url="https://httpbin.org/get" out=out;
run;
  1. The -o (OUTPUT) cURL argument translates to the OUT= argument in PROC HTTP.
  2. The -request argument defaults to a GET for cURL (also the default for PROC HTTP, so METHOD=“GET” is the correct syntax but unnecessary for this step).
  3. Note: The URL= argument is always quoted.

The cURL command supports many options and features. Check out the cURL reference page. SAS can't guarantee that all are directly translatable to PROC HTTP, but I do want to cover some of the most popular that SAS customers have asked about.

Sending data to an API

If your cURL command uses the -d (DATA) option, you'll use the IN= argument in your PROC HTTP statement. Here I am posting to the URL httpbin.org/post a file called test.csv, which resides in my c:\temp directory:

curl -d "c:\temp\test.csv" -X post "https://httpbin.org/post";

This command translates to the following PROC HTTP code:

filename test "c:\temp\test.csv";
 
proc http url="https://httpbin.org/post"
 /* If the IN= argument is used then method="post" is the default */ 
 /* and therefore unnecessary in this step */
 method="post"
 in=test;
run;

Working with authentication

None of the URLs above require authentication, but you'll likely find authentication is part of most APIs. Many APIs have moved to OAuth for authentication. This method of authentication requires the use of an access token, which you obtain with a POST command. With the correct credentials, this cURL command posts to the SAS® Viya® SASLogon REST API in order to obtain an access token:

 
curl -X POST "https://server.example.com/SASLogon/oauth/token" \
      -H "Content-Type: application/x-www-form-urlencoded" \
      -d "grant_type=password&username=userid&password=mypassword" \
      -u "app:mysecret" -o "c:\temp\token.txt"

The following PROC HTTP code does the same task:

 filename out temp;
 proc http url="http://servername/SASLogon/oauth/token"
    in="grant_type=password&username=userid&password=mypassword"
    webusername="clientid"
    webpassword="clientsecret"
    method="post"
    out=out;
   headers "Content-Type"="application/x-www-form-urlencoded";
run;
  1. The -u option is for user ID and password.*
  2. The -o command/output captures the response, in this case a JSON file. In this case you mimic -o with a FILENAME statement to write the text in JSON format to the WORK library location.
  3. The -H command is popular and translates to the HEADERS statement in PROC HTTP.

*Read about ways to hide your credentials in Chris Hemedinger's post here: How to secure your REST API credentials in SAS programs.

The output file contains an access token, necessary to make requests on behalf of a client to the REST API. In this example, a cURL command like the following requests a list of folders from the Folders microservice:

curl -X GET "https://server.example.com/folders/folders/@myFolder" \
      -H "Accept: application/json" \
      -H "Authorization: Bearer TOKEN-STRING"

The PROC HTTP code will look like this if you "directly translate":

filename new temp;
 
proc http url=" https://server.example.com/folders/folders/@myFolder"
 method="get" out=new;
 headers "Accept"="application/json" 
         "Authorization"="Bearer TOKEN-STRING";
run;

But starting in SAS® 9.4M5, there's a shortcut with the OAUTH_BEARER option:

filename new temp;
 
proc http OAUTH_BEARER="TOKEN-STRING" 
 url="https://server.example.com/folders/folders/@myFolder" 
 method="get" 
 out=new;
run;

Processing JSON responses with the JSON engine

I can't tell you about PROC HTTP without a mention of the JSON engine. Starting in SAS® 9.4m4, the JSON engine enables us to easily read JSON files. I can use the previous cURL command to pipe my access token to a file with the -o argument, but using my PROC HTTP code I can easily move that value into a macro variable. I'll add a LIBNAME statement that points to the fileref in the previous step:

libname test json fileref=new;

I can then examine the contents of the JSON output with this step:

proc contents data=test._all_;
run;

Here I spy the access token I will need for a later PROC HTTP step:

Here's how I can place it in a macro variable:

data _null_;
 set test.root;
 call symputx("access_token",access_token);
run;
 
%put &access_token;

So everywhere I used TOKEN-STRING in the previous code, I can now use the macro variable instead, like this:

proc http OAUTH_BEARER="&access_token" 
 url="https://server.example.com/folders/folders/@myFolder" 
 method="get" 
 out=new;
run;

Debugging with PROC HTTP

With the cURL command, you can use the -v (verbose) argument to get connection and header information. It's helpful for debugging and diagnosing trouble spots.

In SAS® 9.4M5, the DEBUG statement was added to PROC HTTP. DEBUG supports several options. I'll highlight the LEVEL= option here:

 Level= 0 | 1 | 2 | 3

Selecting 0 provides no debugging information while 3 provides the highest amount of data and messages. Base SAS 9.4 Procedures Guide includes full descriptions of each debug level.

See the HTTP Procedure documentation for additional syntax.

Generating cURL commands with Postman

If you want someone to "write your code for you," I recommend using a product like Postman to test your POST and GET commands with your API from outside of SAS. Postman is an open-source product with a super cool feature: it will produce CURL commands from successful communication with a URL.

Recommended resources

How to translate your cURL command into SAS code was published on SAS Users.

7月 302020
 
How often have you needed Google Translate, but for SAS code?

SAS Technical Support often gets requests like the following: "I have this API named <insert name of really cool API here> and I want to process data I get back from the API with SAS. How do I do it?"

In this article, I'll show you how to translate the examples from your API documentation (offered in a select few languages) to the equivalent in SAS.

Test your API with cURL

My first recommendation is to know your API. Most APIs come with documentation and will give specific guidance regarding how to POST data (send) to the API and how to GET data (receive) from the API. Often, examples are provided using cURL commands.

With this information, you are welcome to examine the SAS documentation for the HTTP procedure and build your code. Before you call or email SAS Technical Support asking for PROC HTTP code, I encourage you to verify that you can communicate with your API (or URL) from outside of SAS. One way to do so is with cURL. cURL (Client URL) is a command-line tool that is shipped with many UNIX flavors and installed easily on Windows. With a cURL command, you can interact with your URLs from outside of SAS from a command-line prompt like this:

curl -o "c:\temp\file.txt" -request "https://httpbin.org/get"

From SAS, you can use a cURL command with the DATA step, like this:

data _null_;
 %sysexec curl -o "c:\temp\file.txt" -request "https://httpbin.org/get";
run;

File.txt contains the response from the URL:

{
  "args": {}, 
  "headers": {
    "Accept": "*/*", 
    "Host": "httpbin.org", 
    "Range": "bytes=Request", 
    "User-Agent": "curl/7.46.0", 
    "X-Amzn-Trace-Id": "Root=1-5f028cd3-2ec7e1e05da1f616e9106ee8"
  }, 
  "origin": "149.173.8.1", 
  "url": "https://httpbin.org/get"
}

However, if you use SAS® Enterprise Guide® or SAS® Studio, you might not have permissions to run operating system commands from SAS, so you need a way to translate your cURL commands to SAS. The previous cURL command is easily translated to the following PROC HTTP code:

filename out "c:\temp\file.txt";
proc http url="https://httpbin.org/get" out=out;
run;
  1. The -o (OUTPUT) cURL argument translates to the OUT= argument in PROC HTTP.
  2. The -request argument defaults to a GET for cURL (also the default for PROC HTTP, so METHOD=“GET” is the correct syntax but unnecessary for this step).
  3. Note: The URL= argument is always quoted.

The cURL command supports many options and features. Check out the cURL reference page. SAS can't guarantee that all are directly translatable to PROC HTTP, but I do want to cover some of the most popular that SAS customers have asked about.

Sending data to an API

If your cURL command uses the -d (DATA) option, you'll use the IN= argument in your PROC HTTP statement. Here I am posting to the URL httpbin.org/post a file called test.csv, which resides in my c:\temp directory:

curl -d "c:\temp\test.csv" -X post "https://httpbin.org/post";

This command translates to the following PROC HTTP code:

filename test "c:\temp\test.csv";
 
proc http url="https://httpbin.org/post"
 /* If the IN= argument is used then method="post" is the default */ 
 /* and therefore unnecessary in this step */
 method="post"
 in=test;
run;

Working with authentication

None of the URLs above require authentication, but you'll likely find authentication is part of most APIs. Many APIs have moved to OAuth for authentication. This method of authentication requires the use of an access token, which you obtain with a POST command. With the correct credentials, this cURL command posts to the SAS® Viya® SASLogon REST API in order to obtain an access token:

 
curl -X POST "https://server.example.com/SASLogon/oauth/token" \
      -H "Content-Type: application/x-www-form-urlencoded" \
      -d "grant_type=password&username=userid&password=mypassword" \
      -u "app:mysecret" -o "c:\temp\token.txt"

The following PROC HTTP code does the same task:

 filename out temp;
 proc http url="http://servername/SASLogon/oauth/token"
    in="grant_type=password&username=userid&password=mypassword"
    webusername="clientid"
    webpassword="clientsecret"
    method="post"
    out=out;
   headers "Content-Type"="application/x-www-form-urlencoded";
run;
  1. The -u option is for user ID and password.*
  2. The -o command/output captures the response, in this case a JSON file. In this case you mimic -o with a FILENAME statement to write the text in JSON format to the WORK library location.
  3. The -H command is popular and translates to the HEADERS statement in PROC HTTP.

*Read about ways to hide your credentials in Chris Hemedinger's post here: How to secure your REST API credentials in SAS programs.

The output file contains an access token, necessary to make requests on behalf of a client to the REST API. In this example, a cURL command like the following requests a list of folders from the Folders microservice:

curl -X GET "https://server.example.com/folders/folders/@myFolder" \
      -H "Accept: application/json" \
      -H "Authorization: Bearer TOKEN-STRING"

The PROC HTTP code will look like this if you "directly translate":

filename new temp;
 
proc http url=" https://server.example.com/folders/folders/@myFolder"
 method="get" out=new;
 headers "Accept"="application/json" 
         "Authorization"="Bearer TOKEN-STRING";
run;

But starting in SAS® 9.4M5, there's a shortcut with the OAUTH_BEARER option:

filename new temp;
 
proc http OAUTH_BEARER="TOKEN-STRING" 
 url="https://server.example.com/folders/folders/@myFolder" 
 method="get" 
 out=new;
run;

Processing JSON responses with the JSON engine

I can't tell you about PROC HTTP without a mention of the JSON engine. Starting in SAS® 9.4m4, the JSON engine enables us to easily read JSON files. I can use the previous cURL command to pipe my access token to a file with the -o argument, but using my PROC HTTP code I can easily move that value into a macro variable. I'll add a LIBNAME statement that points to the fileref in the previous step:

libname test json fileref=new;

I can then examine the contents of the JSON output with this step:

proc contents data=test._all_;
run;

Here I spy the access token I will need for a later PROC HTTP step:

Here's how I can place it in a macro variable:

data _null_;
 set test.root;
 call symputx("access_token",access_token);
run;
 
%put &access_token;

So everywhere I used TOKEN-STRING in the previous code, I can now use the macro variable instead, like this:

proc http OAUTH_BEARER="&access_token" 
 url="https://server.example.com/folders/folders/@myFolder" 
 method="get" 
 out=new;
run;

Debugging with PROC HTTP

With the cURL command, you can use the -v (verbose) argument to get connection and header information. It's helpful for debugging and diagnosing trouble spots.

In SAS® 9.4M5, the DEBUG statement was added to PROC HTTP. DEBUG supports several options. I'll highlight the LEVEL= option here:

 Level= 0 | 1 | 2 | 3

Selecting 0 provides no debugging information while 3 provides the highest amount of data and messages. Base SAS 9.4 Procedures Guide includes full descriptions of each debug level.

See the HTTP Procedure documentation for additional syntax.

Generating cURL commands with Postman

If you want someone to "write your code for you," I recommend using a product like Postman to test your POST and GET commands with your API from outside of SAS. Postman is an open-source product with a super cool feature: it will produce CURL commands from successful communication with a URL.

Recommended resources

How to translate your cURL command into SAS code was published on SAS Users.

9月 062019
 

A few years ago I shared a method to publish content from SAS to a Slack channel. Since that time, our teams at SAS have gone "all in" on collaboration with Microsoft Office 365, including Microsoft Teams. Microsoft Teams is the Office suite's answer to Slack, and it's not a coincidence that it works in nearly the same way.

The lazy method: send e-mail to the channel

Before I cover the "deluxe" method for sending content to a Microsoft Teams channel, I want to make sure you know that there is a simple method that involves no coding, and no need for APIs. The message experience isn't as nice, but it does the job. You can simply "send e-mail" to the channel. If you're automating output from SAS, it's a simple, well-documented process to send e-mail from a SAS program. (Here's an example from me, using FILENAME EMAIL.)

When you send e-mail to a Microsoft Teams channel, the message notice includes the message subject line, sender, and the first bit of the message content. To see the entire message, you must click on the "View original e-mail" link in the notice. This "downloads" the message to your device so that you can open it with a local tool (such as your e-mail reader, Microsoft Outlook). My team uses this method to receive certain alerts from our communities.sas.com platform. Here's an example:

To get the unique e-mail address for a channel, right-click on the channel name and select Get email address. Any message that you send to that e-mail address will be distributed to the team.

Getting started with a Microsoft Teams webhook

In order to provide a richer, more integrated experience with Microsoft Teams, you can publish content using a webhook. A webhook is a REST API endpoint that allows you to post messages and notifications with more control over the appearance and interactive options within the messages. In SAS, you can publish to a webhook by using PROC HTTP.

To get started, you need to add and configure a webhook for your Microsoft Teams channel:

  1. Right-click on the channel name and select Connectors.
  2. Microsoft Teams offers built-in connectors for many different applications. To find the connector for Incoming Webhook, use the search field to narrow the list. Then click Add to add the connector to the channel.
  3. You must grant certain permissions to the connector to interact with your channel. In this case, you need to allow the webhook to send messages and notifications. Review the permissions and click Install.
  4. On the Configuration page, assign a name to this connector and optionally customize the image. The image will be the avatar that's used when the connector posts content to the channel. When you've completed these changes, select Create.
  5. The connector generates a unique (and very long) URL that serves as the REST API endpoint. You can copy the URL from this field -- you will need it later in your SAS program. You can always come back to these configuration settings to change the connector avatar or re-copy the URL.

    At this point, it's a good idea to test that you can publish a basic message from SAS. The "payload" for a Teams message is a JSON-formatted structure, and you can find examples in the Microsoft Teams reference doc. Here's a SAS program that publishes the simplest message. Add your webhook URL and run the code to verify the connector is working for your channel.

    filename resp temp;
    options noquotelenmax;
    proc http
      /* Substitute your webhook URL here */
      url="https://outlook.office.com/webhook/your-unique-webhook-address-it-is-very-long"
      method="POST"
      in=
      '{
          "$schema": "http://adaptivecards.io/schemas/adaptive-card.json",
          "type": "AdaptiveCard",
          "version": "1.0",
          "summary": "Test message from SAS",
          "text": "This message was sent by **SAS**!"
      }'
      out=resp;
    run;

    If successful, this step will post a simple message to your Teams channel:

    Design a message card for Microsoft Teams

    Now that we have the basic plumbing working, it's time to add some bells and whistles. Microsoft Teams calls these notifications "message cards", which are messages that can include interactive features such as images, data, action buttons, and more.

    Designing a simple message

    Microsoft Teams supports a large palette of building blocks (expressed in JSON) to create different card experiences. You can experiment with these cards in the MessageCard Playground that Microsoft hosts. The tool provides templates for several card varieties, and you can edit the JSON definitions to tweak and design your own.

    For one of my use cases, I designed a simple card to show the status of our recommendation engine on SAS Support Communities. (Read this article for more information about how we built and monitor the recommendation engine.) The engine runs as a service and is accessed with its own API. I wanted a periodic "health check" to post to our internal team that would alert us to any problems. Here's the JSON that I used in the MessageCard Playground to design it.

    Much of the JSON is boilerplate for the message. I drew the green blocks to indicate the areas that need to be dynamic -- that is, replaced with values from the real-time API call. Here's what the card looks like when rendered in the Microsoft Teams channel.

    Since my API call to the recommendation engine service creates a data set, I can run that data through PROC JSON to create the JSON segment I need:

    /* reading the results from my API call to the engine */
    libname results json fileref=resp;
     
    /* Prep a simple name-value data set with the results */
    data segment (keep=name value);
     set results.root;
     name="Score data updated (UTC)";
     value= astore_creation;
     output;
     name="Topics scored";
     value=left(num_topics);
     output;
     name="Number of users";
     value= left(num_users);
     output;
     name="Process time";
     value= process_time;
     output;
    run;
     
    /* use PROC JSON to create the segment */
    filename segment temp;
    proc json out=segment nosastags pretty;
     export segment;
    run;

    I shared a version of the complete program on GitHub. It should run as is -- but you would need to supply your own webhook endpoint for a channel that you can publish to.

    Design a message with actions

    I also use Microsoft Teams to share updates about the SAS Software GitHub organization. In a previous article I discussed how I use GitHub APIs to gather data from the GitHub service. Each day, my program summarizes the recent activity from github.com/sassoftware and publishes a message card to the team. Here's an example of a daily update:

    This card is fancier than my first example. I added action buttons that can direct the team members to the internal reports for more details and to the GitHub site itself. I used the Microsoft Teams documentation and the MessageCard Playground to design the experience:

    Messaging apps as part of a DevOps strategy

    Like many organizations, we (SAS) invest a considerable amount of time and energy into gathering metrics and building reports about our operations. However, reports are useful only when the intended audience is tuned in and refers to them regularly. With a small additional step, you can use SAS to bring your most interesting data forward to your team -- automatically.

    Whether you use Microsoft Teams or Slack, automated alerting and updates are a great opportunity to keep your teams informed. Each of these tools offers fit-for-purpose connectors that can tie in with information from other popular operational systems (Salesforce, GitHub, Yammer, JIRA, and many more). For cases where a built-in connector is not available, the webhook approach allows you to easily create your own.

The post How to publish to a Microsoft Teams channel using SAS appeared first on The SAS Dummy.

11月 292018
 

If your work environment is like ours here at SAS, you're seeing more of your data and applications move to the cloud. It's not yet a complete replacement for having local files on your desktop machine, but with cloud storage and apps -- like Microsoft OneDrive -- I can now access my work documents from any browser and any device, including my smartphone. I can update now my spreadsheets while waiting in the dentist office. Oh joy.

For those of us who use SAS to read and create Microsoft Excel documents, cloud-based files can add an extra wrinkle when we automate the process. It also adds some exciting possibilities! The Microsoft 365 suite offers APIs to discover, fetch, and update our documents using code. In this article, I'll show you how to use SAS programs to reach into your Microsoft OneDrive (or SharePoint Online) cloud to read and update your files. Note: All of this assumes that you already have a Microsoft 365 account -- perhaps provisioned by your IT support team -- and that you're using it to manage your documents.

Before I go on, I have to give major credit to Joseph Henry, the SAS developer who maintains PROC HTTP. Joseph did the heavy lifting for putting together the code and examples in this article. He also regularly adds new features to PROC HTTP that make it a more natural fit for calling REST APIs that require special authentication flows, such as OAuth2.

Note: I've updated this article several times to include detailed steps and "gotchas." I've added use cases for SharePoint Online and Microsoft Teams. I wrote a comprehensive paper for SAS Global Forum 2020. And I also recorded a 25-minute video (posted on SAS Support Communities) that shows all of the steps that I followed.

Click to watch the video tutorial.

Using SAS with Microsoft 365: an overview

Microsoft 365 uses an OAuth2-style authentication flow to grant access and permissions to third-party apps. If you're accustomed to the simpler style of just user/password authentication (ah, those were the days), OAuth2 can be intimidating. Joseph Henry does a great job of deconstructing OAuth2 -- with code samples -- in this SAS Global Forum paper.

When we're writing SAS programs to access Microsoft OneDrive or SharePoint, we're actually writing a third-party app. This requires several setup steps, a few of which cannot be automated. Fortunately, these need to be done just once, or at least infrequently. Here's an outline of the steps:

  1. Register a new client application at the Microsoft Azure Portal. (You will need to sign in with your Microsoft 365 credentials, which might be your primary organization credentials if you have single-signon with Active Directory.)
  2. Using your browser while you are signed into Microsoft 365, navigate to a special web address to obtain an authorization code for your application.
  3. With your authorization code in hand, plug this into a SAS program (PROC HTTP step) to retrieve an OAuth2 access token (and a refresh token).
  4. With the access token, you can now use PROC HTTP and the Microsoft 365 APIs to retrieve your OneDrive folders and files, download files, upload files, and replace files.

You'll have to complete Step 1 just once for your application or project. Steps 2 and 3 can be done just once, or at least just occasionally. The access token is valid for a limited time (usually 1 hour), but you can always exchange the refresh token for a new valid access token. This refresh token step can be automated in your program, usually run just once per session. Occasionally that refresh token can be revoked (and thus made invalid) when certain events occur (such as you changing your account password). When that happens, you'll need to repeat steps 2 and 3 to get a new set of access/refresh tokens.

Oh, and by the way, even though the examples in this article are specific to OneDrive, the exact same authentication flow and steps can be used for all of the Microsoft 365 APIs. Have fun with Outlook, Teams, Excel, and all of your favorite cloud-based Microsoft apps.

Step 1: Register your application

Visit the Microsoft Application Registration portal to register your new app. You'll sign in with your Microsoft 365 credentials.

Microsoft Application Registration portal

Click New Registration to get started. This presents you with a form where you can complete the details that define your app. Mainly, you're giving it a name and defining its scope. You'll probably want to limit its use to just your organization (your company) unless you're collaborating with colleagues who work elsewhere.

"Register an application" form

As you register your application, you also need to provide a redirect URL for the authorization flow. In our example, our app is considered "Public client/native (mobile/desktop)." The standard URL to indicate this is:

 https://login.microsoftonline.com/common/oauth2/nativeclient 

In the Redirect URI section, select this option and specify this URL value.

Redirect URI Selections

When you create an app, you'll receive a Client ID (unique to your app) and Tenant ID (unique to your organization). You'll need these values to obtain your authorization code and tokens later. The application portal provides a sort of control center for all aspects of your app. (Note: I masked out my client ID and tenant ID in this screenshot.)

Details for my sample application

Specifying your app permissions

Your app will need specific permissions in order to function. In my example, I want my SAS program to read documents from my OneDrive, and also add new docs and update existing docs. The permissions I need are:

  • Files.ReadWrite.All: Allows the app to read, create, update and delete all OneDrive files that you can access.
  • User.Read: Allows you to sign in to the app with your organizational account and let the app read your profile.
  • Sites.ReadWrite.All (if using SharePoint): Allows the app to read, create, update and delete SharePoint Online files for sites that you can access.

To add these to your app, click the API Permissions tab in the control center. To be clear, these are not permissions that your app will automatically have. These are the permissions that will be requested when you "sign into" the app for the first time, and that you'll have to agree to in order for the app to run.

Adding permissions that the app needs

Permission types have their own terminology that is important to understand:

  • Delegated versus Application Permissions: In our example, we are sticking to Delegated permissions, which allow the application to take actions on behalf of the signed-in user and provides access to the user's data. However, some use cases require use of Application permissions, which allow the application to take actions without a signed-in user and potentially access data across the system and different users.
  • Admin Consent Required: Some permissions cannot be delegated or granted without the approval of an administrator. This restriction permits the organization to maintain oversight of the important resources that might be accessed by the application and to prevent unauthorized uses. The Microsoft Azure Portal provides an easy way for you to submit a request to an admin, so you can get the permissions that you need. However, I recommend that you follow up (or better yet, precede this) with a formal request to your IT support staff to state what you need and your business case. In my experience, this helps to expedite the process. A good working relationship with IT is important for any SAS user!

The documentation for the Microsoft Graph API provides a comprehensive list of the permission names, whether they are Delegated or Application level, and whether Admin Consent is required. This documentation also includes a helpful 4-minute video on the topic.

Possibly required: Obtaining admin consent

We're creating an app that hooks into your enterprise productivity suite -- and that's usually the domain of IT professionals. At SAS we are a tech company with many "citizen app developers", so our IT grants us more latitude than you might find at other places. But even at SAS, "normal" employees can't just create apps and empower them with access to our data. We have a process.

Because it's a common request, our IT folks created a form that makes it easy for them to review requests for new apps in our Microsoft 365 environment. The form asks:

  • Your app name (“SAS via PROC HTTP” for mine)
  • Your App (client) ID
  • Grant type – my instructions assume "Authorization code grant type"
  • Whether you need additional Delegated API permissions: Most need 'Files.ReadWrite.All' for OneDrive, 'Sites.ReadWrite.All' for SharePoint (in addition to the default 'User.Read').
  • Whether your app needs Application Permissions. (Note: Answering YES here will trigger more scrutiny.)

Creating a configuration file

There are a few app-specific values that we'll need to reference throughout the SAS programs we're writing. I decided to create a configuration file for these settings rather than hard-code them into my SAS statements. This will make it easier for other people to reuse my code in their own applications.

I created a file named config.json that looks like this (but with different tenant_id and client_id values):

{
  "tenant_id": "206db638-6adb-41b9-b20c-95d8d04abcbe",
  "client_id": "8fb7804a-8dfd-40d8-bf5b-d02c2cbc56f3",
  "redirect_uri": "https://login.microsoftonline.com/common/oauth2/nativeclient",
  "resource" : "https://graph.microsoft.com"
}

By "externalizing" the IDs specific to my account/instance, I can use SAS code to read the values at run time. Note: This code, like all of the code in this article, uses features from SAS 9.4 Maintenance 5.

/*
  Set the variables that will be needed through the code
  We'll need these for authorization and also for runtime 
  use of the service.
 
  Reading these from a config.json file so that the values
  are easy to adapt for different users or projects.
*/
 
%if %symexist(config_root) %then %do;
  filename config "&config_root./config.json";
  libname config json fileref=config;
  data _null_;
   set config.root;
   call symputx('tenant_id',tenant_id,'G');
   call symputx('client_id',client_id,'G');
   call symputx('redirect_uri',redirect_uri,'G');
   call symputx('resource',resource,'G');
  run;
%end;
%else %do;
  %put ERROR: You must define the CONFIG_ROOT macro variable.; 
%end;

Step 2: Obtain an authorization code

Now that I've defined the application, it's time to "sign into it" and grant it the permission to read and manage content in OneDrive. This step needs to be completed from a web browser while I am signed into my Microsoft 365 account. The web address is very long...but we can use a SAS program to generate it for us.

/* location of my config file */
%let config_root=/folders/myfolders/onedrive;
 
%include "&config_root./onedrive_config.sas";
 
/* Run this line to build the authorization URL */
%let authorize_url=https://login.microsoftonline.com/&tenant_id./oauth2/authorize?client_id=&client_id.%nrstr(&response_type)=code%nrstr(&redirect_uri)=&redirect_uri.%nrstr(&resource)=&resource.;
options nosource;
%put Paste this URL into your web browser:;
%put -- START -------;
%put &authorize_url;
%put ---END ---------;
options source;

This produces these output lines in the SAS log:

 Paste this URL into your web browser:
 -- START -------
https://login.microsoftonline.com/206db638-6adb-41b9-b20c-95d8d04abcbe/oauth2/authorize?client_id=8fb7804a-8dfd-40d8-bf5b-d02c2cbc56
f3&response_type=code&redirect_uri=https://login.microsoftonline.com/common/oauth2/nativeclient&resource=https://graph.microsoft.com
 ---END ---------

Copy and paste the URL (all on one line, no spaces) into the address bar of your web browser. When you press Enter, you'll be prompted to grant the required permissions:

Once you click Accept, the browser will redirect to what looks like a blank page, but the URL contains the authorization code that we need:

Copy the value that appears after the code= in the URL, only up to the &session= part. It's going to be a very long string -- over 700 characters. We'll need that value for the next step.

Note: if you don't see the permissions prompt but instead see something like this:

App needs permission to access resources

Then you probably need to work with your IT support to grant consent for your app. See the section "Possibly required: Obtaining admin consent" above.

Step 3: Obtain an access token

My colleague Joseph wrote a few convenient utility macros that can help manage the access token and refresh token within your SAS session. These macros include:

  • %get_token - get the initial access and refresh tokens, given an authorization code. Remember, an access token will expire in about 60 minutes. But the refresh token can be used to get a renewed access token.
  • %refresh - exchange a valid refresh token for a new access token
  • %process_token_file - read/update an external token file so that these values persist beyond your current SAS session.

I'm not going to walk through the macro code in this article, but the SAS programs are straightforward and well-documented. See "How to get this example code" at the end of this article.

With these macros in place, we can paste the (very long) authorization code we retrieved in the previous step into a macro variable. Then we can run the %get_token macro to generate the tokens and store them in a local file.

%let config_root=/folders/myfolders/onedrive;
 
%include "&config_root./onedrive_config.sas";
%include "&config_root./onedrive_macros.sas";
 
filename token "&config_root./token.json";
%let auth_code=AQABAAIAAAC5una0EUFgTIF8ElaxtWjTqwohjyfG; * and much more;
 
/*
  Now that we have an authorization code we can get the access token
  This step will write the tokens.json file that we can use in our
  production programs.
*/
%get_token(&client_id.,&auth_code,&resource.,token,tenant=&tenant_id);

Running this step will create a new file, token.json, in your designated config folder. Here's an screenshot of what my version looks like right now:

It's very important that you keep this file secure. With the information in this file (your refresh token) and your conf.json file (with your client ID and tenant ID), anyone can use these code techniques to impersonate you and access your Microsoft 365 data. There are techniques for storing these files such that only you can see them.

Using Microsoft 365 APIs to access OneDrive from SAS

Whew! I've spent nearly 1500 words to get this far, so thanks for sticking with me. The good news is that these steps take much longer to describe than to actually execute. Plus, creating apps is fun! (Right?)

From the screenshots I've shared, you probably already noticed that these services are working on Microsoft Azure, which is Microsoft's cloud platform for applications. For the remainder of this article, I'll be using methods from the Microsoft Graph API. This REST-based API provides access to almost all of Microsoft's hosted services. For my examples, I'll be using methods within the Files component of the API: Drives and Drive Items (folders and files).

You can explore and try the Microsoft 365 APIs with the Graph Explorer application from Microsoft. If you sign in with your own account, you can use the APIs with your own data. This is a great way to try these APIs and discover the correct methods to use before implementing them in your SAS code.

Example from a Graph Explorer session

Initializing and refreshing the access token in a new session

Now that we have the access and refresh tokens, we can get down to business with some actual OneDrive interactions. Here's how to initialize your SAS session with the tokens.

%let config_root=/folders/myfolders/onedrive;
 
%include "&config_root./onedrive_config.sas";
%include "&config_root./onedrive_macros.sas";
 
/*
  Our json file that contains the oauth token information
*/
filename token "&config_root./token.json";
 
%process_token_file(token);
 
/* If this is first use for the session, we'll likely need to refresh  */
/* the token.  This will also call process_token_file again and update */
/* our token.json file.                                                */
%refresh(&client_id.,&refresh_token.,&resource.,token,tenant=&tenant_id.);
 
/*
  At this point we have a valid access token and we can start using the API.
*/

If all goes well, we'll have our access token, and it will be stored in a macro variable named &access_token. It's going to be another long and illegible (>700 characters) value.

(Ever hear of the "infinite monkey theorem?" That a monkey hitting a typewriter for an infinite amount of time is certain to produce a certain text, such as the complete works of Shakespeare? Well, that monkey is not going to produce this access token. Plus, who has a typewriter anymore?)

Retrieving the top-level drive identifier (OneDrive)

We'll need to explore the OneDrive system from the top-down, using code. First, we need the identifier for the root drive. It's possible for you to have multiple root drives, and if that's the case for you, you'll need to modify this code a bit. This code queries the service for your drives, and stores the identifier for just the first drive in a macro variable. We'll need that identifier later to retrieve a list of top-level items.

/*
First we need the ID of the "drive" we are going to use.
to list the drives the current user has access to you can do this
*/
filename resp TEMP;
/* Note: oauth_bearer option added in 9.4M5 */
proc http url="https://graph.microsoft.com/v1.0/me/drives/"
     oauth_bearer="&access_token"
     out = resp;
	 run;
 
libname jresp json fileref=resp;
 
/*
 I only have access to 1 drive, but if you have multiple you can filter 
 the set with a where clause on the name value.
 
 This creates a data set with the one record for the drive.
*/
data drive;
 set jresp.value;
run;
 
/* store the ID value for the drive in a macro variable */
proc sql noprint;
 select id into: driveId from drive;
quit;

Note that this code uses the new OAUTH_BEARER option in PROC HTTP -- a convenient addition when working with OAuth2-compliant APIs. This is shorthand -- and more intuitive syntax -- for placing "Authorization: Bearer TOKEN-VALUE" in the HTTP headers.

Retrieving the top-level drive identifier (SharePoint Online)

The steps for SharePoint Online are nearly the same as for OneDrive, except that we need to reference the site hostname (yoursite.sharepoint.com, for example) and the /sites resource (instead of the /me/drives resource).

/* Note: oauth_bearer option added in 9.4M5                       */
/* Using the /sites methods in the Microsoft Graph API            */
/* May require the Sites.ReadWrite.All permission for your app    */
/* Set these values per your SharePoint Online site.
   Ex: https://yourcompany.sharepoint.com/sites/YourSite 
    breaks down to:
       yourcompany.sharepoint.com -> hostname
       /sites/YourSite -> sitepath
   This example uses the /drive method to access the files on the
   Sharepoint site -- works just like OneDrive.
   API also supports a /lists method for SharePoint lists.
   Use the Graph Explorer app to find the correct APIs for your purpose.
    https://developer.microsoft.com/en-us/graph/graph-explorer
*/
%let hostname = yourcompany.sharepoint.com;
%let sitepath = /sites/YourSite;
proc http url="https://graph.microsoft.com/v1.0/sites/&hostname.:&sitepath.:/drive"
     oauth_bearer="&access_token"
     out = resp;
	 run;
 
libname jresp json fileref=resp;
 
/*
 This creates a data set with the one record for the drive.
 Need this object to get the Drive ID
*/
data drive;
 set jresp.root;
run;
 
/* store the ID value for the drive in a macro variable */
proc sql noprint;
 select id into: driveId from drive;
quit;

Retrieve a list of top-level folders/files

With the drive identifier in hand (whether OneDrive or SharePoint), I can use the /children verb on the Microsoft Graph API to get a list of all of the top-level objects in that drive. These represent the folders and files that are at the root.

/*
 To list the items in the drive, use the /children verb with the drive ID
*/
filename resp TEMP;
proc http url="https://graph.microsoft.com/v1.0/me/drives/&driveId./items/root/children"
     oauth_bearer="&access_token"
     out = resp;
	 run;
 
libname jresp json fileref=resp;
 
/* Create a data set with the top-level paths/files in the drive */
data paths;
 set jresp.value;
run;

Here's what I'm keeping in my OneDrive right now. It's not too disorganized, is it?

List the files in a particular folder

If I'm interested in exploring a particular folder, I'll need to find the folder identifier as it's known to OneDrive. Using PROC SQL and SELECT INTO, I can find the folder by its name and store its ID in another macro variable. Then, I use the /children verb again, but this time with the folder ID instead of the "root" constant.

/*
 At this point, if you want to act on any of the items, you just replace "root" 
 with the ID of the item. So to list the items in the "SASGF" folder I have:
  - find the ID for that folder
  - list the items within by using the "/children" verb
*/
 
/* Find the ID of the folder I want */
proc sql noprint;
 select id into: folderId from paths
  where name="SASGF";
quit;
 
filename resp TEMP;
proc http url="https://graph.microsoft.com/v1.0/me/drives/&driveId./items/&folderId./children"
     oauth_bearer="&access_token"
     out = resp;
	 run;
 
/* This creates a data set of the items in that folder, 
   which might include other folders.
*/
libname jresp json fileref=resp;
data folderItems;
 set jresp.value;
run;

Here are the items from my SASGF folder. Can you tell that I don't throw anything away?

Download a file from OneDrive and import into SAS

I know that I keep a spreadsheet named "sas_tech_talks_18.xlsx" in this SASGF folder. With the /content verb, I can download the file from OneDrive and store it in the file system that is local to my SAS session. Then, I can use PROC IMPORT to read it into a SAS data set.

/*
 With a list of the items in this folder, we can download
 any item of interest by using the /content verb 
*/
 
/* Find the item with a certain name */
proc sql noprint;
 select id into: fileId from folderItems
  where name="sas_tech_talks_18.xlsx";
quit;
 
filename fileout "&config_root./sas_tech_talks_18.xlsx";
proc http url="https://graph.microsoft.com/v1.0/me/drives/&driveId./items/&fileId./content"
     oauth_bearer="&access_token"
     out = fileout;
	 run;
 
/* Import the first sheet into a SAS data set */
proc import file=fileout 
 out=sasgf
 dbms=xlsx replace;
run;

Boom! I've just downloaded my data from the cloud and brought it into my SAS session.

Add a new file to OneDrive

We can build wonderful documents from SAS too, and it's important to be able to share those. By using the PUT method with the /content verb, we can copy a file from the local SAS session into a target folder on OneDrive. Most often, this will probably be an Excel spreadsheet or maybe a PDF report. (But hey, maybe it's a good opportunity to try out the new ODS WORD destination in SAS 9.4 Maint 6?)

/*
  We can upload a new file to that same folder with the PUT method and /content verb
  Notice the : after the folderId and the target filename
*/
 
/* Create a simple Excel file to upload */
%let targetFile=iris.xlsx;
filename tosave "%sysfunc(getoption(WORK))/&targetFile.";
ods excel(id=upload) file=tosave;
proc print data=sashelp.iris;
run;
ods excel(id=upload) close;
 
filename details temp;
proc http url="https://graph.microsoft.com/v1.0/me/drives/&driveId./items/&folderId.:/&targetFile.:/content"
  method="PUT"
  in=tosave
  out=details
  oauth_bearer="&access_token";
run;
 
/*
  This returns a json response that describes the item uploaded.
  This step pulls out the main file attributes from that response.
*/
libname attrs json fileref=details;
data newfileDetails (keep=filename createdDate modifiedDate filesize);
 length filename $ 100 createdDate 8 modifiedDate 8 filesize 8;
 set attrs.root;
 filename = name;
 modifiedDate = input(lastModifiedDateTime,anydtdtm.);
 createdDate  = input(createdDateTime,anydtdtm.);
 format createdDate datetime20. modifiedDate datetime20.;
 filesize = size;
run;

Replace/update a file in OneDrive

If you want to replace an existing file, then you'll want to perform the additional step of retrieving the unique ID for that file from OneDrive. When you PUT the new version of the file into place, its history and sharing properties should remain intact. Here is my code for navigating the folder/file structure in my OneDrive and finally replacing an existing file.

/*
  If you want to replace a file instead of making a new file 
  then you need to upload it with the existing file ID.  If you
  don't replace it with the existing ID, some sharing properties
  and history could be lost.
*/
/* Create a simple Excel file to upload */
%let targetFile=iris.xlsx;
filename tosave "%sysfunc(getoption(WORK))/&targetFile.";
ods excel(id=upload) file=tosave;
proc print data=sashelp.iris;
run;
ods excel(id=upload) close;
 
/* Navigate the folder and file IDs from my OneDrive */
proc sql noprint;
 select id into: folderId from paths
  where name="SASGF";
quit;
 
proc http url="https://graph.microsoft.com/v1.0/me/drives/&driveId./items/&folderId./children"
     oauth_bearer="&access_token"
     out = resp;
	 run;
 
libname jresp json fileref=resp;
data folderItems;
 set jresp.value;
run;
 
/* Find the ID of the existing file */
proc sql noprint;
 select id into: fileId from folderItems
  where name="iris.xlsx";
quit;
 
libname attrs json fileref=details;
proc http url="https://graph.microsoft.com/v1.0/me/drives/&driveId./items/&fileId./content"
 method="PUT"
 in=tosave
 out=details
 oauth_bearer="&access_token";
run;

As you can see from my OneDrive history for this file, I've tested this program a few times -- resulting in 23 revisions of this file in its history!

How to get this example code

You can find the source files for these examples on GitHub.

I've organized this code into 5 different files in order to make it easy to reuse:

  • onedrive_config.sas - read the fields from the conf.json and set them as global macro variables. This includes your client_id and tenant_id.
  • onedrive_setup.sas - the SAS statements that represent code you will need to run just once to get your authorization code and first access code.
  • onedrive_macros.sas - three utility macros that help you to create, refresh, and manage your access token and refresh token in your token.json file
  • onedrive_example_use.sas - sample SAS steps that I used in this article. They won't quite work for you as-is, since you don't have the same files that I do. (Unless you do have the same files, in which case...creepy.) My hope is that you can read and adapt them for your own content.
  • onedrive_sharepoint_example.sas - sample SAS steps for reading and writing files with SharePoint Online. The basic steps are the same as for OneDrive, except that you use the /sites resource instead of the OneDrive-specific methods.

I also included a template for the conf.json file, with obvious placeholders for the client_id and tenant_id that you'll substitute with your own values. You'll also need to change the statements that define &CONFIG_LOC -- the location of your configuration directory where you're storing these files. I developed these examples in SAS University Edition -- yes, this works there! I also ran the code from my full SAS environment via SAS Enterprise Guide.

More about using REST APIs from SAS

This has been a monster article -- in terms of its length. But I hope it's clear enough to follow and has sufficient detail for you to try this on your own. If you have questions, post in the comments.

I've published a number of other articles about using REST APIs from SAS -- it's one of my favorite things to do. Check out:

The post Using SAS with Microsoft 365 (OneDrive, Teams, and SharePoint) appeared first on The SAS Dummy.

11月 012018
 

This blog post was also written by SAS' Bari Lawhorn.

We have had several requests from customers who want to use SAS® software to automate the download of data from a website when there is no application programming interface (API) to do it. As an example, the Yahoo Finance website recently changed their service to decommission their API, and this generated an interesting challenge for one of our customers. This SAS programmer wanted to download historical stock price data "unattended," without having to click through a web page. While working on this case, we discovered that the Yahoo Finance website requires a cookie-crumb combination to download. To help you automate downloads from websites that do not have an API, this blog post takes you through how we used the DEBUG feature of PROC HTTP to achieve partial automation, and eventually full automation with this case.

Partial automation

To access the historical data for Apple stock (symbol: AAPL) on the Yahoo Finance website, we use this URL: https://finance.yahoo.com/quote/AAPL/history?p=AAPL

We click Historical Data --> Download Data and get a CSV file with historical stock price data for Apple. We could save this CSV file and read it into SAS. But, we want a process that does not require us to click in the browser.

Because we know the HTTP procedure, we right-click Download Data and then select Copy link address as shown from a screen shot using the Google Chrome browser below:

Note: The context menu that contains Copy link address looks different in each browser.

Using this link address, we expect to get a direct download of the data into a CSV file (note that your crumb= will differ from ours):

filename out "c:\temp\aapl.csv";
 
proc http
 url='https://query1.finance.yahoo.com/v7/finance/download/AAPL?period1=1535399845&period2=1538078245&interval=1d&events=history&crumb=hKubrf50i1P'
 method="get" out=out;
run;

However, the above code results in the following log message:

NOTE: PROCEDURE HTTP used (Total process time):
real time           0.25 seconds
cpu time            0.14 seconds
 
NOTE: 401 Unauthorized

When we see this note, we know that the investigation needs to go further.

filename out "c:\temp\aapl.csv";
proc http
 url='https://query1.finance.yahoo.com/v7/finance/download/AAPL?period1=1535399845&period2=1538078245&interval=1d&events=history&crumb=hKubrf50i1P'
 method="get" out=out;
 debug level=3;
run;

When we run the code, here's what we see in the log (snipped for convenience):

> GET
/v7/finance/download/AAPL?period1=1535399845&period2=1538078245&interval=1d&events=history&crumb=h
Kubrf50i1P HTTP/1.1
 
> User-Agent: SAS/9
> Host: query1.finance.yahoo.com
> Accept: */*
> Connection: Keep-Alive
> Cookie: B=fpd0km1dqnqg3&b=3&s=ug
> 
< HTTP/1.1 401 Unauthorized
< WWW-Authenticate: crumb
< Content-Type: application/json;charset=utf-8
 
…more output…
 
< Strict-Transport-Security: max-age=15552000
 
…more output…
 
{    "finance": {        "error": {            "code": "Unauthorized",
"description": "Invalid cookie"        }    }}
NOTE: PROCEDURE HTTP used (Total process time):
      real time           0.27 seconds
      cpu time            0.15 seconds
 
NOTE: 401 Unauthorized

The log snippet reveals that we did not provide the Yahoo Finance website with a valid cookie. It is important to note that the response header for the URL shows crumb for the authentication method (the line that shows WWW-Authenticate: crumb. A little web research helps us determine that the Yahoo site wants a cookie-crumb combination, so we need to also provide the cookie. But, why did we not need this step when we were using the browser? We used a tool called Fiddler to examine the HTTP traffic and discovered that the cookie was cached when we first clicked in the browser on the Yahoo Finance website:

Luckily, starting in SAS® 9.4M3 (TS1M3), PROC HTTP will set cookies and save them across HTTP steps if the response contains a "set-cookie:  <some cookie>" header when it successfully connects to a URL. So, we try this download in two steps. The first step does two things:

  • PROC HTTP sets the cookie for the Yahoo Finance website.
  • Adds the DEBUG statement so that we can obtain the crumb value from the log.
filename out "c:\temp\Output.txt";
 
filename hdrout "c:\temp\Response.txt";
 
proc http
 out=out
 headerout=hdrout
 url="https://finance.yahoo.com/quote/AAPL/history?p=AAPL"
 method="get";
 debug level=3;
run;

Here's our log snippet showing the set-cookie header and the crumb we copy and use in our next PROC HTTP step:

…more output…
< set-cookie: B=2ehn8rhdsf5r2&b=3&s=fe; expires=Wed, 17-Oct-2019 20:11:14 GMT; path=/;
domain=.yahoo.com
 
…more output…
 
Initialized"},"account-switch-uh-0-AccountSwitch":{"status":"initialized"}}},"CrumbStore":{"crumb":
"4fKG9lnt5jw"},"UserStore":{"guid":"","login":"","alias":"","firstName":"","comscoreC14":-1,"isSig

The second step uses the cached cookie from Yahoo Finance (indicated in the "CrumbStore" value), and in combination with the full link that includes the appropriate crumb value, downloads the CSV file into our c:\temp directory.

filename out "c:\temp\aapl.csv";
 
proc http
 url='https://query1.finance.yahoo.com/v7/finance/download/AAPL?period1=1535399845&period2=1538078245&interval=1d&events=history&crumb=4fKG9lnt5jw'
 method="get"
 out=out;
run;

With the cookie value in place, our download attempt succeeds!

Here is our log snippet:

31
32   proc http
33       out=data
34       headerout=hdrout2
35       url='https://query1.finance.yahoo.com/v7/finance/download/AAPL?period1=1534602937&peri
35 ! od2=1537281337&interval=1d&events=history&crumb=4fKG9lnt5jw'
36       method="get";
37   run;
 
NOTE: PROCEDURE HTTP used (Total process time):
      real time           0.37 seconds
      cpu time            0.17 seconds
 
NOTE: 200 OK

Full automation

This partial automation requires us to visit the website and right-click on the download link to get the URL. There’s nothing streamlined about that, and SAS programmers want full automation!

So, how can we fully automate the process? In this section, we'll share a "recipe" for how to get the crumb value -- a value that changes with each transaction. To get the current crumb, we use the first PROC HTTP statement to "screen scrape" the URL and to cache the cookie value that comes back in the response. In this example, we store the first response in the Output.txt file, which contains all the content from the page:

filename out "c:\temp\Output.txt";
filename hdrout "c:\temp\Response.txt";
 
proc http 
    out=out 
    headerout=hdrout
    url="https://finance.yahoo.com/quote/AAPL/history?p=AAPL" 
    method="get";
run;

It is a little overwhelming to examine the web page in its entirety. And the HTML page contains some very long lines, some of them over 200,000 characters long! However, we can still use the SAS DATA step to parse the file and retrieve the text or information that might change on a regular basis, such as the crumb value.

In this DATA step we read chunks of the text data and scan the buffer for the "CrumbStore" keyword. Once found, we're able to apply what we know about the text pattern to extract the crumb value.

data crumb (keep=crumb);
  infile out  recfm=n lrecl=32767;
  /* the @@ directive says DON'T advance pointer to next line */
  input txt: $32767. @@;
  pos = find(txt,"CrumbStore");
  if (pos>0) then
    do;
      crumb = dequote(scan(substr(txt,pos),3,':{}'));
      /* cookie value can have unicode characters, so must URLENCODE */
      call symputx('getCrumb',urlencode(trim(crumb)));
      output;
    end;
run;
 
%put &=getCrumb.;

Example result:

 102        %put &=getCrumb.;
 GETCRUMB=PWDb1Ve5.WD

We feel so good about finding the crumb, we're going to treat ourselves to a whole cookie. Anybody care for a glass of milk?

Complete Code for Full Automation
The following code brings it all together. We also added a PROC IMPORT step and a bonus highlow plot to visualize the results. We've adjusted the file paths so that the code works just as well on SAS for Windows or Unix/Linux systems.

/* use WORK location to store our temp files */
filename out "%sysfunc(getoption(WORK))/output.txt";
filename hdrout "%sysfunc(getoption(WORK))/response1.txt";
 
/* This PROC step caches the cookie for the website finance.yahoo.com */
/* and captures the web page for parsing later                        */
proc http 
  out=out
  headerout=hdrout
  url="https://finance.yahoo.com/quote/AAPL/history?p=AAPL" 
  method="get";
run;
 
/* Read the response and capture the cookie value from     */
/* the CrumbStore field.                                   */
/* The file has very long lines, longer than SAS can       */
/* store in a single variable.  So we read in <32k chunks. */
data crumb (keep=crumb);
  infile out  recfm=n lrecl=32767;
  /* the @@ directive says DON'T advance pointer to next line */
  input txt: $32767. @@;
  pos = find(txt,"CrumbStore");
  if (pos>0) then
    do;
      crumb = dequote(scan(substr(txt,pos),3,':{}'));
      /* cookie value can have unicode characters, so must URLENCODE */
      call symputx('getCrumb',urlencode(trim(crumb)));
      output;
    end;
run;
 
%put &=getCrumb.;
 
filename data "%sysfunc(getoption(WORK))/data.csv";
filename hdrout2 "%sysfunc(getoption(WORK))/response2.txt";
 
proc http 
    out=data 
    headerout=hdrout2
    url="https://query1.finance.yahoo.com/v7/finance/download/AAPL?period1=1535835578%str(&)period2=1538427578%str(&)interval=1d%str(&)events=history%str(&)crumb=&getCrumb."
    method="get";
run;
 
proc import
 file=data
 out=history
 dbms=csv
 replace;
run;
 
proc sgplot data=history;
  highlow x=date high=high low=low / open=open close=close;
  xaxis display=(nolabel) minor;
  yaxis display=(nolabel);
run;


Disclaimer: As we've seen, Yahoo Finance could change their website at any time, so the URLs in this blog post might not be accurate at a later date. Note that, as of the time of this writing, the above code runs error-free with Base SAS 9.4M5. And it also works in SAS University Edition and SAS OnDemand for Academics!

How to automate a data download with PROC HTTP was published on SAS Users.

7月 032018
 

At SAS, we love data. Data is central to our corporate vision: to transform a world of data into a world of intelligence. We're also famous for enjoying M&Ms, but to us they are more than a sweet snack. They're also another source of data.

My colleague Pete Privitera, with a team of like-minded "makers," built a device that they named SnackBot. SnackBot is an internet-connected sensor that measures the flow of M&Ms in a particular SAS break room. There's a lot to love about this project. You can learn more by watching its origin story in this video:

As the number of M&Ms changes, SnackBot takes a reading and records the M&M count in a database. Most readings reflect a decrease in candy pieces, as my colleagues help themselves to a treat. But once per week, the reading shows a drastic increase -- as our facilities staff restocks the canister. SnackBot has its own website. It also has its own API, and you know what that means (right?). It means that we can use SAS to read and analyze the sensor data.

Reading sensor data into SAS with the SnackBot API

The SnackBot system offers a REST API with a JSON data response. Like any REST API, we can use PROC HTTP to fetch the data, and the JSON library engine to parse the response into a SAS data set.

%let start = '20MAY2018:0:0:0'dt;
 
/* format the start/end per the API needs */
%let start_time= %sysfunc(putn(&start.,is8601dt26.));
%let end_time=   %sysfunc(datetime(),is8601dt26.);
 
/* Call the SnackBot API from snackbot.net */
filename resp temp;
proc http
  method="GET"
  url="http://snackbot.net/snackdata?start_time=&start_time.%str(&)end_time=&end_time.%str(&)utc_offset_minutes=-240"
  out=resp;
run;
 
/* JSON libname engine to read the result       */
/* Simple record layout, all in the ROOT member */
libname mms json fileref=resp;
data mmlevels;
  set mms.root;
run;

I've written about how to use SAS with REST APIs in several other blog posts, so I won't dwell on this part of the process here. This short program retrieves the raw data from SnackBot, which represents a series of M&M "levels" (count of remaining pieces) and a timestamp for each measurement. It's a good start. Though there are only two fields to work with here, there's quite a bit we can do with these data.

raw SnackBot data

Add features to the raw sensor data

With a few additional DATA step statements and some built-in SAS formats, we can derive several interesting characteristics of these data for use in further analysis.

First, we need to convert the character-formatted datetime field to a proper SAS datetime value. That's easily achieved with the INPUT function and the ANYDTDTM informat. (Rick Wicklin wrote a helpful article about how how the ANYDT* informats work.)

data mmlevels;
  set mms.root;
  drop ordinal_root timestamp;
  /* Convert the TIMESTAMP field to native value -- it's a character */
  datetime = input(timestamp, anydtdtm.);
  date = datepart(datetime);
  time = timepart(datetime);
  dow = date;
  qhour = round(datetime,'0:15:0'T);
  format  datetime datetime20. 
          qhour datetime20.
          date date9.
          time timeampm10.
          dow downame.;
run;

For convenience, I duplicated the datetime value a few times and applied different formats so we can get different views of the same value: datetime, just the date, just the time-of-day, and the day-of-week. I also used the ROUND function to "round" the raw datetime value to the nearest quarter hour. I'll explain why I've done that in a later step, but the ROUNDing of a time value is one of the documented unusual uses of the ROUND function.

SnackBot data with features

Even with this small amount of data preparation, we can begin to analyze the characteristics of these data. For example, let's look at the descriptive stats for the data classified by day-of-week:

title "SnackBot readings per day-of-week";
proc means data=mmlevels mean stddev max min;
 var pieces;
 class dow;
run;

SnackBot by day of week

The "N Obs" column shows the number of measurements taken over the entire "study period" broken down by day-of-week. If a measurement is a proxy for a "number-of-pieces-changed" event, then we can see that most events happen on Wednesday, Thursday, and Friday. From this, can you guess which day the M&M canister is refilled?

Let's take another slice through these data, but this time looking at time-of-day. For this, I used PROC FREQ to count the measurements by hour. I applied the HOUR2. format, which allows the SAS procedure to group these data into hour-long intervals with no need for additional data prep. ( I've written previously about how to use SAS formats to derive new categories without expensive data rewriting.) Then I used PROC SGPLOT to produce a step plot for the 24-hour cycle.

/* Count of readings per hour of the day */ 
title "SnackBot readings per hour";
proc freq data=mmlevels ;
 table time / out=perhour;
 format time hour2.;
run;
 
ods graphics / height=400 width=800;
 
title "SnackBot readings per hour";
proc sgplot data=perhour des="Readings per hour of day";
 step x=time y=count;
 xaxis min='0:0:0't max='24:0:0't label="Time of day" grid;
 yaxis label="Servings";
run;

SnackBot hour step

From the chart, we can see that most M&M "events" happen around 11am, and then again between 2pm and 4pm. From personal experience, I can confirm that those are the times when I hear the M&Ms calling to me.

Expand the time series to regular intervals

The SnackBot website can tell you how many M&Ms are remaining right now. But what if you want to know how many were remaining last Friday? Or on any typical Monday morning?

The sensor data that we've analyzed so far is sparse -- that is, there are data entries for each "change" event, but not for every discrete time interval in the study period. I don't know how the SnackBot sensor records its readings -- it might sample the M&M levels every minute, or every second. Regardless, the API reports (and probably stores) only the records that represent a change. If SnackBot records that the final 24 pieces were depleted at 25JUN2018:07:45:00 (a Monday morning) bringing the count to 0, how many M&Ms remain at 1pm later that day? The data don't tell us explicitly with a recorded reading. But we can assume at that point that the count was still 0. The next recorded reading occurs at 27JUN2018:10:30:00 (on a Wednesday, bringing the count to 1332 -- oh joy!).

If we want to create a useful time series visualization of the M&M candy counts over time, we need to expand the time series from these sparse recordings to regular intervals. SAS offers a few sophisticated time series procedures to accomplish this: PROC EXPAND, PROC TIMESERIES, and PROC TIMEDATA. Each of these offer powerful econometrics methods for interpolation and forecasting -- and that's more than we need for this situation. For my example, I took a more low-tech approach.

First, I created an empty data set with datetime entries at quarter-hour intervals, covering the study period of the data we're looking at.

/* Empty data set with 15 minute interval slots    */
/* Regular intervals for the entire "study" period */
data timeslots;
  last = datetime();
  length qhour 8;
  format qhour datetime20;
  drop last i;
  do i = &start. to last by '0:15:00't;
    qhour = i;
    output;
  end;
run;

Then I used a DATA step to merge these empty slots with the actual event data that I had rounded to the nearest quarter hour (remember that?):

/* Merge the sample data with the timeslots */
data expand;
  merge mmlevels(keep=pieces qhour) timeslots;
  by qhour;
run;

Finally, I used a variation of a last-observation-carried-forward (LOCF) approach to fill in the remaining empty slots. If a reading at 20MAY2018:11:15:00 reports 132 pieces remaining, then that value should be RETAINed for each 15-minute slot until the next reading at 20MAY2018:17:30:00. (That reading is 82 pieces -- meaning somebody helped themselves to 50 pieces. Recommended serving size for plain M&Ms is 20 pieces, but I'm not passing judgement.) I also recorded a text value for the day-of-week to help with the final visualization.

/* for empty timeslots, carry the sample data   */
/* forward, so we always have a count of pieces */
/* Variation on a LOCF technique                */
data final;
  set expand;
  length day $ 3;
  /* 3-char value for day of week */
  day=put(datepart(qhour),weekdate3.);
  retain hold;
  if not missing(pieces) then
    hold=pieces;
  else pieces=hold;
  drop hold;
  if not missing(pieces);
run;

Now I have data that represents the regular intervals that we need.

SnackBot regular intervals

Putting it all together

For my final visualization, I created a series plot for the study period. It shows the rise and fall of M&Ms levels in one SAS break room over several weeks. For additional "color", I annotated the plot with a block chart to delineate the days of the week.

title 'Plain M&M pieces on S1 tracked by SnackBot';
ods graphics / height=300 width=1600;
 
proc sgplot data=final des='M&M pieces tracked by SnackBot';
 
  /* plot the data as a series */ 
  series x=qhour y=pieces / lineattrs=(color=navy thickness=3px);
 
  /* Yes, these are the "official" M&M colors               */
  /* Will be applied in data-order, so works best when data */
  /* begins on a Sunday                                     */
  styleattrs datacolors=(red orange yellow green blue CX593B18 red);
  /* block areas to indicate days-of-week                   */
  block x=qhour block=day / transparency=0.65
    valueattrs=(weight=bold size=10pt color=navy);
 
  xaxis minor display=(nolabel);
  yaxis display=(nolabel) grid max=1600 minor;
run;

You can see the pattern. M&Ms are typically filled on Wednesday to the canister capacity of about 1400 pieces. We usually enter into the weekend with 0 remaining, but there are exceptions. The week of May 27 was our Memorial Day holiday, which explains the lack of activity on Monday (and even Tuesday) during that week as SAS folks took advantage of a slow week with their vacation plans.

SnackBot visualization

More about SAS and M&Ms data

You can download the complete code for this example from my public Gist on GitHub. The example code should work with SAS University Edition and SAS OnDemand for Academics, as well as with any SAS environment that can reach the internet with PROC HTTP.

For more M&M data fun, check out Rick Wicklin's article about the distribution of colors in plain M&Ms. SnackBot does not (yet) report on how many and which color of M&Ms are taken per serving, but using statistics, we can predict that!

The post The Internet of Snacks: SnackBot data and what it reveals about SAS life appeared first on The SAS Dummy.

1月 242018
 

Using SAS with REST APIs is fun and rewarding, but it's also complicated. When you're dealing with web services, credentials, data parsing and security, there are a lot of things that can go wrong. It's useful to have a simple program that verifies that the "basic plumbing" is working before you try to push a lot of complex coding through it.

I'm gratified that many of my readers are able to adapt my API examples and achieve similar results. When I receive a message from a frustrated user who can't get things to work, the cause is almost always one of the following:

  • Not using a recent enough version of SAS. PROC HTTP was revised and improved in SAS 9.4 Maint 3. The JSON library engine was added in SAS 9.4 Maint 4 (released in 2016). It's time to upgrade!
  • Cannot access the web service through a corporate firewall. Use the SSL certificates with the SSLCALISTLOC= option. If you're using SAS University Edition, you'll need the update from December 2017 or later -- that's where SSL was added. The editions prior to that did not include the SSL support.

The following SAS program is a simple plumbing test. It uses a free HTTP test service (httpbin.org) to verify your Internet connectivity from SAS and your ability to use SSL. The endpoint returns a JSON-formatted collection of timestamps in various formats, which the program parses using the JSON library engine. I have successfully run this program from my local SAS on Windows, from SAS University Edition (Dec 2017 release), and from SAS OnDemand for Academics (using SAS Studio).

If you can run this program successfully from your SAS session, then you're ready to attempt the more complex REST API calls. If you encounter any errors while running this simple test, then you will need to resolve these before moving on to the really useful APIs. (Like maybe checking on who is in space right now...)

/* PROC HTTP and JSON libname test          */
/* Requires SAS 9.4m4 or later to run       */
/* SAS University Edition Dec 2017 or later */
 
/* utility macro to echo the JSON out */
%macro echoFile(fn=);
  data _null_;
   infile &fn end=_eof;
   input;
   put _infile_;
  run;
%mend;
 
filename resp "%sysfunc(getoption(WORK))/now.json";
proc http
 url="https://now.httpbin.org/"
 method="GET"
 out=resp;
run;
 
/* Supported with SAS 9.4 Maint 5 */
/*
 %put HTTP Status code = &SYS_PROCHTTP_STATUS_CODE. : &SYS_PROCHTTP_STATUS_PHRASE.; 
*/
 
%echoFile(fn=resp);
 
/* Tell SAS to parse the JSON response */
libname time JSON fileref=resp;
 
title "JSON library structure";
proc datasets lib=time;
quit;
 
/* interpret the various datetime vals and convert to SAS */
data values (keep=from:);
 length from_epoch 8
        from_iso8601 8
        from_rfc2822 8
        from_rfc3339 8;
 
 /* Apply the DT format to a range of vars */
 format from: datetime20.;
 set time.now;
 
 /* epoch = # seconds since 01Jan1970        */
 /* SAS datetime is based on 01Jan1960, so   */
 /* add the offset here for a SAS value      */
 from_epoch = epoch + '01Jan1970:0:0:0'dt;
 
 /* straight conversion to ISO8061           */
 from_iso8601 = input(iso8601,is8601dt.);
 
 /* trim the leading "day of week"      */
 from_rfc2822 = input(substr(rfc2822,5),anydtdtm21.);
 
 /* allow SAS to figure it out */
 from_rfc3339 = input(rfc3339,anydtdtm.);
 
 run;
 
title "Raw values from the JSON response";
proc print data=time.now (drop=ord:);
run;
 
title "Formatted values as SAS datetime";
proc print data=values;
run;
 
libname time clear;
filename resp clear;

The post How to test PROC HTTP and the JSON library engine appeared first on The SAS Dummy.