REST API

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.

7月 252019
 

Recommendations on SAS Support Communities

If you visit the SAS Support Communities and sign in with your SAS Profile, you'll experience a little bit of SAS AI with every topic that you view.

While it appears to be a simple web site widget, the "Recommended by SAS" sidebar is made possible by an application of the full Analytics Life Cycle. This includes data collection and prep, model building and test, API wrappers with a gateway for monitoring, model deployment in containers with orchestration in Kubernetes, and model assessment using feedback from click actions on the recommendations. We built this by using a combination of SAS analytics and open source tools -- see the SAS Global Forum paper by my colleague, Jared Dean, for the full list of ingredients.

Jared and I have been working for over a year to bring this recommendation engine to life. We discussed it at SAS Global Forum 2018, and finally near the end of 2018 it went into production on communities.sas.com. The engine scores user visits for new recommendations thousands of times per day. The engine is updated each day with new data and a new scoring model.

Now that the recommendation engine is available, Jared and I met again in front of the camera. This time we discussed how the engine is working and the efforts required to get into production. Like many analytics projects, the hardest part of the journey was that "last mile," but we (and the entire company, actually) were very motivated to bring you a live example of SAS analytics in action. You can watch the full video at (where else?) communities.sas.com. The video is 17 minutes long -- longer than most "explainer"-type videos. But there was a lot to unpack here, and I think you'll agree there is much to learn from the experience. Not ready to binge on our video? I'll use the rest of this article to cover some highlights.

Good recommendations begin with clean data

The approach of our recommendation engine is based upon your viewing behavior, especially as compared to the behavior of others in the community. With this approach, we don't need to capture much information about you personally, nor do we need information about the content you're reading. Rather, we just need the unique IDs (numbers) for each topic that is viewed, and the ID (again, a number) for the logged-in user who viewed it. One benefit of this approach is that we don't have to worry about surfacing any personal information in the recommendation API that we'll ultimately build. That makes the conversation with our IT and Legal colleagues much easier.

Our communities platform captures details about every action -- including page views -- that happens on the site. We use SAS and the community platform APIs to fetch this data every day so that we can build reports about community activity and health. We now save off a special subset of this data to feed our recommendation engine. Here's an example of the transactions we're using. It's millions of records, covering nearly 100,000 topics and nearly 150,000 active users.

Sample data records for the model

Building user item recommendations with PROC FACTMAC

Starting with these records, Jared uses SAS DATA step to prep the data for further analysis and a pass through the algorithm he selected: factorization machines. As Jared explains in the video, this algorithm shines when the data are represented in sparse matrices. That's what we have here. We have thousands of topics and thousands of community members, and we have a record for each "view" action of a topic by a member. Most members have not viewed most of the topics, and most of the topics have not been viewed by most members. With today's data, that results in a 13 billion cell matrix, but with only 3.3 million view events. Traditional linear algebra methods don't scale to this type of application.

Jared uses PROC FACTMAC (part of SAS Visual Data Mining and Machine Learning) to create an analytics store (ASTORE) for fast scoring. Using the autotuning feature, the FACTMAC selects the best combination of values for factors and iterations. And Jared caps the run time to 3600 seconds (1 hour) -- because we do need this to run in a predictable time window for updating each day.

proc factmac data=mycas.weighted_factmac  outmodel=mycas.factors_out;
   autotune maxtime=3600 objective=MSE 
       TUNINGPARAMETERS=(nfactors(init=20) maxiter(init=200) learnstep(init=0.001) ) ;
   input user_uid conversation_uid /level=nominal;
   target rating /level=interval;
   savestate rstore=mycas.sascomm_rstore;
run;

Using containers to build and containers to score

To update the model with new data each day and then deploy the scoring model as an ASTORE, Jared uses multiple SAS Viya environments. These SAS Viya environments need to "live" only for a short time -- for building the model and then for scoring data. We use Docker containers to spin these up as needed within the cloud environment hosted by SAS IT.

Jared makes the distinction between the "building container," which hosts the full stack of SAS Viya and everything that's needed to prep data and run FACTMAC, and the "scoring container", which contains just the ASTORE and enough code infrastructure (include the SAS Micro Analytics Service, or MAS) to score recommendations. This scoring container is lightweight and is actually run on multiple nodes so that our engine scales to lots of requests. And the fact that it does just the one thing -- score topics for user recommendations -- makes it an easier case for SAS IT to host as a service.

DevOps flow for the recommendation engine

Monitoring API performance and alerting

To access the scoring service, Jared built a simple API using a Python Flask app. The API accepts just one input: the user ID (a number). It returns a list of recommendations and scores. Here's my Postman snippet for testing the engine.

To provision this API as a hosted service that can be called from our community web site, we use an API gateway tool called Apigee. Apigee allows us to control access with API keys, and also monitors the performance of the API. Here's a sample performance report for the past 7 days.

In addition to this dashboard for reporting, we have integrated proactive alerts into Microsoft Teams, the tool we use for collaboration on this project. I scheduled a SAS program that tests the recommendations API daily, and the program then posts to a Teams channel (using the Teams API) with the results. I want to share the specific steps for this Microsoft Teams integration -- that's a topic for another article. But I'll tell you this: the process is very similar to the technique I shared about publishing to a Slack channel with SAS.

Are visitors selecting recommended content?

To make it easier to track recommendation clicks, we added special parameters to the recommended topics URLs to capture the clicks as Google Analytics "events." Here's what that data looks like within the Google Analytics web reporting tool:

You might know that I use SAS with the Google Analytics API to collect web metrics. I've added a new use case for that trick, so now I collect data about the "SAS Recommended Click" events. Each click event contains the unique ID of the recommendation score that the engine generated. Here's what that raw data looks like when I collect it with SAS:

With the data in SAS, we can use that to monitor the health/success of the model in SAS Model Manager, and eventually to improve the algorithm.

Challenges and rewards

This project has been exciting from Day 1. When Jared and I saw the potential for using our own SAS Viya products to improve visitor experience on our communities, we committed ourselves to see it through. Like many analytics applications, this project required buy-in and cooperation from other stakeholders, especially SAS IT. Our friends in IT helped with the API gateway and it's their cloud infrastructure that hosts and orchestrates the containers for the production models. Putting models into production is often referred to as "the last mile" of an analytics project, and it can represent a difficult stretch. It helps when you have the proper tools to manage the scale and the risks.

We've all learned a lot in the process. We learned how to ask for services from IT and to present our case, with both benefits and risks. And we learned to mitigate those risks by applying security measures to our API, and by limiting the execution scope and data of the API container (which lives outside of our firewall).

Thanks to extensive preparation and planning, the engine has been running almost flawlessly for 8 months. You can experience it yourself by visiting SAS Support Communities and logging in with your SAS Profile. The recommendations that you see will be personal to you (whether they are good recommendations...that's another question). We have plans to expand the engine's use to anonymous visitors as well, which will significantly increase the traffic to our little API. Stay tuned!

The post Building a recommendation engine with SAS appeared first on The SAS Dummy.

4月 052019
 

When working with files like SAS programs, images, documents, logs, etc., we are used to accessing them in operating system directories. In Viya, many of these files are not stored on the file-system. In this blog, we will look at where and how files are stored in Viya, and how to manage them.

In Viya, external files are stored in the Infrastructure Data server and accessed using the file micro-service. The file service manages a wide variety of file types including, images, html files, text files, csv files, SAS programs, media files, pdfs, office documents and logs. The file service is not a complete file system, but rather a method of accessing individual files stored in the infrastructure data server identified via their URI (a unique identifier).

Some files may also be accessible in Viya folders. The file service stores resources like images, SAS programs, etc. in the Infrastructure Data server, but they are accessible from Viya folders in the Content Area of Environment Manager or in SAS Drive.

The screenshot below shows the properties of an image file that was uploaded to a Viya folder using SAS Drive. You can see the filename, the URI (including the ID) and the location in the folder structure.

For files that are surfaced in folders, we can manage the files (view, copy, move, delete etc.) using SAS Environment Manager Content area, SAS Drive or the Folders command line interface. To learn more, watch this video from the SAS Technical Insights and Expertise Series.

However, many system generated files are managed by the file service and stored in the infrastructure data server, but are not accessible from the folders interfaces. A good example of system generated files that an administrator may need to manage are logs.

Many times when processing in Viya, logs are created and stored in the infrastructure data server. For example, when CAS table state management jobs, data plans, file imports, model or scoring processes are executed, a log is generated and stored by the file service.
How do we manage these files? While the files generated are mostly small in size, a large active Viya system with a long history will need management of the log and other files stored in the infrastructure data server.

One way we can access these files is using the REST API of the file service.

You can use your favorite tool to access the REST API. In this blog, I will use the GEL pyviyatools, initially the basic callrestapi tool. For more details on the pyviyatools, read this blog or go directly to the SAS GitHub site.

To get a list of all files are stored in the infrastructure data server send a GET request to the /files/files endpoint of the files service.

/./callrestapi.py -m get -e /files/files -o simple/

Partial output shows the image file referenced above and what could be a system generated log file.
=====Item 0 =======
contentType = image/jpeg
createdBy = geladm
creationTimeStamp = 2019-01-24T14:40:15.085Z
description = None
encoding = UTF-8
id = 6d2995b3-0fa6-4090-a338-1fbfe51fb26b
modifiedBy = geladm
modifiedTimeStamp = 2019-01-25T17:59:28.500Z
name = company_logo.JPG
properties = {}
size = 327848
version = 2

=====Item 164 =======
contentType = text/plain
createdBy = sasadm
creationTimeStamp = 2019-01-31T12:35:00.902Z
description = None
encoding = UTF-8
id = e4d4c83d-8677-433a-a8d3-b03bf00a5768
modifiedBy = sasadm
modifiedTimeStamp = 2019-01-31T12:35:09.342Z
name = 2019-01-31T12:35.823Z-results.log
parentUri = /jobExecution/jobs/380d3a0c-1c31-4ada-bf8d-4db66e786669
properties = {}
size = 2377
version = 2

To see the content of a file, use a GET request, the id of the file and the content endpoint. The content of the log file is displayed in the call below.

/./callrestapi.py -m get -e /files/files/01eb020f-468a-49df-a05a-34c6f834bfb6/content/

This will display the file contents. The output shows the log from a CAS table state management job.

———-JOB INFORMATION———-

Job Created: 2019-01-31T12:35.823Z
Job ID: 380d3a0c-1c31-4ada-bf8d-4db66e786669
Heartbeat interval: PT0.3S
Job expires after: PT168H
Running as: sasadm
Log file: /files/files/e4d4c83d-8677-433a-a8d3-b03bf00a5768 [ 2019-01-31T12:35.823Z-results.log ]
Arguments:

Options:
{
“enabled” : true,
“type” : “LOAD”,
“settings” : {
“refresh” : false,
“refreshMode” : “newer”,
“refreshAccessThreshold” : 0,
“varChars” : false,
“getNames” : true,
“allowTruncation” : true,
“charMultiplier” : 2,
“stripBlanks” : false,
“guessRows” : 200,
“scope” : “global”,
“encoding” : “utf-8”,
“delimiter” : “,”,
“successJobId” : “”
},
“selectors” : [ {
“serverName” : “cas-shared-default”,
“inputCaslib” : “hrdl”,
“outputCaslib” : “hrdl”,
“filter” : “or(endsWith(tableReference.sourceTableName,’.sashdat’), endsWith(tableReference.sourceTableName,’.SASHDAT’),\nendsWith(tableReference.sourceTableName,’.sas7bdat’),\nendsWith(tableReference.sourceTableName,’.csv’)\n)”,
“settings” : { }
} ]
}

————————————————–

Created session cas-shared-default: d4d1235a-6219-4649-940c-e67526be31ed (CAS Manager:Thu Jan 31 07:35:01 2019)
Using session d4d1235a-6219-4649-940c-e67526be31ed
Server: cas-shared-default
Input caslib: hrdl
Output caslib: hrdl
Effective Settings:

———-LOAD STARTING———-

— loaded –> unloaded – HR_SUMMARY unloaded – HR_SUMMARY_NEW unloaded – HRDATA unloaded – PERFORMANCE_LOOKUP <– performance_lookup.sas7bdat
Access denied.
———-LOAD COMPLETE———-

———-CLEANUP STARTING———-

Session deleted: cas-shared-default: d4d1235a-6219-4649-940c-e67526be31ed
———-CLEANUP COMPLETE———-

Final Job State: completed
Log file:/files/files/e4d4c83d-8677-433a-a8d3-b03bf00a5768

I started my journey into the file service to discover how an administrator could manage the log files. In reviewing the files and their contents using the REST API, I discovered that there was no easy way to uniquely identify a log file. The table below shows the attributes of some of log files from the GEL Shared Viya environment.

As this post has illustrated, file service REST API can be used to list and view the files. It can also be used for other file management activities. To help administrators manage files that are not visible via a user interface, a couple of new tools have been added to the pyiyatools.

listfiles.py provides an easy interface to query what files are currently stored in the infrastructure data server. You can sort files by size or modified date, and query based on date modified, user who last modified the file, parentUri or filename. The output provides the size of each file so that you can check the space being used to store files. Use this tool to view files managed by the file service and stored in the infrastructure data server. You can use lisfiles.py -h to see the parameters.

For example, if I want to see all potential log files older than 6 days old created by the /jobexecution service, I would use:

/./listfiles.py -n log -p /jobExecution -d 6 -o csv/

The output is a list of files in csv format:

id ,name ,contentType ,documentType ,createdBy ,modifiedTimeStamp ,size ,parentUri

“f9b11468-4417-4944-8619-e9ea9cd3fab8″,”2019-01-25T13:35.904Z-results.log”,”text/plain”,”None”,”sasadm”,”2019-01-25T13:35:09.490Z”,”2459″,”/jobExecution/jobs/37536453-fe2f-41c2-ba63-ce72737e482c”

“dffdcc97-3fb0-47c1-a47d-e6c8f24f8077″,”2019-01-25T12:35.869Z-results.log”,”text/plain”,”None”,”sasadm”,”2019-01-25T12:35:08.739Z”,”2459″,”/jobExecution/jobs/99708443-2449-40b7-acc3-c313c5dbca23″

“5fa889b6-93fb-4496-98ba-e0c055ca5999″,”2019-01-25T11:35.675Z-results.log”,”text/plain”,”None”,”sasadm”,”2019-01-25T11:35:09.118Z”,”2459″,”/jobExecution/jobs/eb182f88-4853-41f4-be24-225176991e8a”

“87988659-3c2d-4602-b61a-8042b34022ac”,”2019-01-25T10:35.657Z-results.log”,”text/plain”,”None”,”sasadm”,”2019-01-25T10:35:09.881Z”,”2459″,”/jobExecution/jobs/73fffe47-a7ef-4c1d-b7bf-83ef0b86319e”

archivefiles.py allows you to read files from the file service and save them to a directory on the file system. Optionally, the tool will also delete files from the file service to free up space. For example, if I want to archive all the files I listed above, I would use:

/./archivefiles.py -n log -d 6 -p /job -fp /tmp/

This tool will create a timestamp directory under /tmp and save a copy of each file in the directory.

If you want to archive and delete, add the -x option.

IMPORTANT: Use the archive tool carefully. We recommend that you run a Viya Backup prior to running the tool to delete files.

Now you know where your files are and you have some help with managing them. For the full details of how you can manage files using the file service REST API you can view the file service REST API documentation on developer.sas.com. If you would like to suggest any changes to the existing tools, please enter a suggestion on GitHub.

Where are my Viya files? was published on SAS Users.

12月 222018
 

This post rounds out the year and my series of articles on SAS REST APIs. The first two articles in the series: Using SAS Viya REST APIs to access images from SAS Visual Analytics and Using SAS Cloud Analytics Service REST APIs to run CAS Actions, examined how to use SAS Viya REST and SAS CAS REST APIs to access SAS data from external resources. Access the links to for a quick detour to get some background. This article takes things a step further and outlines how to use a simple application to interact with SAS Viya using REST APIs.

What do chocolate and toffee have to do with optimization? Read on and find out.

The application

When deciding on an example to use in this article, I wanted to focus on the interaction between the application and SAS, not app complexity. I decided to use an application created by my colleague, Deva Kumar. His OptModel1 is an application built on the restAF framework and demonstrates how SAS REST APIs can be used to build applications that exploit various SAS Viya functionalities. This application optimizes the quantities of chocolate and toffee to purchase based on a budget entered by the user.

Think of the application as comparable to the guns and butter economic model. The idea in the model is the more you spend on the military (guns), the less you spend on domestic programs and the civilian goods (butter). As President Johnson stated in 1968, "That bitch of a war, killed the lady I really loved -- the Great Society." In this article, I'll stick to chocolate and toffee, a much less debatable (and tastier) subject matter.

The OptModel1 application uses the runOptmodel CAS action to solve the optimization problem. The application launches and authenticates the user, the app requests a budget. Based on the amount entered, a purchase recommendation returns for chocolate and toffee. The user may also request a report based on returned values. In the application, OptModel1 and SAS interact through REST API calls. Refer to the diagram below for application code workflow.

Create the application

To create the application yourself, access the source code and install instructions on SAS' github page. I recommend cloning, or in the least, accessing the repository. I refer to code snippets from multiple files throughout the article.

Application Workflow

Represented below is the OptModel1 work flow. Highlighted in yellow is each API call.

OptModel1 Work Flow

OptModel1 Work Flow

Outlined in the following sections is each step in the work flow, with corresponding numbers from the diagram.

Launch the application

Enter url http://localhost:5006/optmodel in a browser, to access the login screen.

OptModel1 app login page

1. Login

Enter proper credentials and click the 'Sign In' button. The OptModel1 application initiates authentication in the logon.html file with this code:

        <script>
            function logonButton() {
                let store = restaf.initStore();
                store.logon(LOGONPAYLOAD)
                    .then(msg => console.log(msg))
                    .catch(err => alert(err));
            }
        </script>

Application landing page

After successfully logging in, the application's main page appears.

Application landing page

Notice how the host and access token are part of the resulting url. For now, this is as far as I'll go on authentication. I will cover this topic in depth in a future article.

As I stated earlier, this is the simplest of applications. I want to keep the focus on what is going on under the covers and not on a flashy application.

2a. Application initialization

Once the app confirms authentication, the application initialization steps ensue. The app needs to be available to multiple users at once, so each session gets their own copy of the template Visual Analytics (VA) report. This avoids users stepping on each other’s changes. This is accomplished through a series of API calls as explained below. The code for these calls is in vaSetup.js and reportViewer.js.

2b. Copy data

The app copies data from the Public caslib to a temporary worklib – a worklib is a standard caslib like casuser. The casl code below is submitted to CAS server for execution. The code to make the API call to CAS is in vaSetup.js. The relevant snippet of javascript code is:

  // create casl statements
    let casl = `
        /* Drop the table in memory */
        action table.dropTable/
        caslib='${appEnv.work.caslib}' name='${appEnv.work.table}' quiet=TRUE;
 
        /* Delete the table from the source */
        action table.deletesource / 
        caslib='${appEnv.work.caslib}' source='${appEnv.work.table}.sashdat' quiet=TRUE;
 
        /* Run data step to copy the template table to worklib */
        action datastep.runCode /
            code='
            data ${appEnv.work.caslib}.${appEnv.work.table}; 
            set ${appEnv.template.caslib}.${appEnv.template.table};
            run;';
 
        /* Save the new work table */
        action table.save /
            caslib  = '${appEnv.work.caslib}'
            name    = '${appEnv.work.table}'
            replace = TRUE
            table= {
                caslib = '${appEnv.work.caslib}'
                name   = '${appEnv.work.table}'
            };
 
        /* Drop the table to force report to reload the new table */
        action table.dropTable/
            caslib='${appEnv.work.caslib}' name='${appEnv.work.table}' quiet=TRUE;
 
 
    `;
 
    // run casl statements on the server via REST API
    let payload = {
        action: 'sccasl.runCasl',
        data: {code: casl}
    }
    await store.runAction(session, payload);

2c. Does report exist?

This step checks to see if the personal copy of the VA report already exists.

2d. Delete temporary report

If the personal report exists it is deleted so that a new one can be created using the latest VA report template.

// If temporary report exists delete it - allows for potential new template report
    let reportsList = await getReport( store, reports, `${APPENV.work.report}`);
    if ( reportsList !== null ) {
        await store.apiCall(reportsList.itemsCmd(reportsList.itemsList(0), 'delete'));
      };

2e. Create new report

A new personal report is created. This new report is associated with the table that was created in step 2b.

// make the service call to create the temporary report
    let changeData = reportTransforms.links('createDataMappedReport');
    let newReport = await store.apiCall(changeData, p);

2f. Save report info

A new personal report is created. This new report is associated with the table that was created in step 2b.

// create src parameter for the iframe
    let options = "&appSwitcherDisabled=true&reportViewOnly=true&printEnabled=true&sharedEnabled=true&informationEnabled=true&commentEnabled=true&reportViewOnly=true";
    let href = `${appEnv.host}/SASReportViewer/?reportUri=${reportUri}${options}`;
 
    // save href in appEnv to use for displaying VA report in an iframe
    appEnv.href = href;

3. Enter budget

Enter budget in the space provided (I use $10,000 in this example) and click the Optimize button. This action instructs the application calculate the amount of chocolate and toffee to purchase based on the model.

Enter budget and optimize

4. & 5. Generate and execute CASL code

The code to load the CAS action set, run the CAS action, and store the results in a table, is in the genCode.js file:

  /* Assumption: All necessary input tables are in memory */
	pgm = "${pgm}";
	/*Load action set and run optimization*/
	loadactionset 'optimization';
		action optimization.runOptmodel / 
		code=pgm printlevel=0; 
		run; 
 
	/* save result of optimization for VA to use */
	action table.save /
		caslib  = '${appEnv.work.caslib}'
		name    = '${appEnv.work.table}'
		replace = TRUE
		table= {
			caslib = '${appEnv.work.caslib}'
			name   = '${appEnv.work.table}'
		};
 
	/* fetch results to return for the UI to display */
	action table.fetch r=result /
		table= {caslib = '${appEnv.work.caslib}' name = '${appEnv.work.table}'};
	run;
 
	/* drop the table to force report to reload the new table */
	action table.dropTable/
		caslib='${appEnv.work.caslib}' name='${appEnv.work.table}' quiet=TRUE;

Note: The drop table step at the end of the preceding code is important to force VA to reload the data for the report.

6. Get the results - table form

The results return to the application in table form. We now know to buy quantities of 370 chocolate and 111 toffee with our $10,000 budget. Please refer to the casTableViewer for code details of this step.

Data view in table format

6. Get the results - report form

Select the View Graph button. This action instructs OptModel1 to display the interactive report with the new data (the report we created in step 2f). Please refer to the onReport function in index.html for code details of this step.

Data view in report format

Now that we know how much chocolate and toffee to buy, we can make enough treats for all of the holiday parties just around the corner. More importantly, we see how to integrate SAS REST APIs into our application. This completes the series on using SAS REST APIs. The conversation is not over however. I will continue to search out and report on other topics related to SAS, open source languages, and agile technologies. Happy Holidays!

SAS REST APIs: a sample application was published on SAS Users.

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 Office 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 cloud to read and update your files. Note: All of this assumes that you already have a Microsoft Office 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.

Using SAS with Microsoft OneDrive: an overview

Microsoft Office 365 uses an OAuth2-style authentication flow to grant access and permissions to third-party apps. When we're writing SAS programs to access Microsoft OneDrive, we're also 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 Application Registration Portal. (You will need to sign in with your Office 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 Office 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 Office 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.

Step 1: Register your application

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

Microsoft Application Registration portal

Note: At the time of this writing, Microsoft was testing a new web experience for application developers. I opted for the "preview experience" with the hope that my screenshots would have a longer shelf life for future readers.

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

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 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.

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

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 conf.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 Office 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. It's going to be a very long string -- over 700 characters. We'll need that value for the next step.

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 Office 365 data. There are techniques for storing these files such that only you can see them.

Using Microsoft Office 365 APIs to access OneDrive from SAS

Whew! It took near 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).

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

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.

Retrieve a list of top-level folders/files

With the drive identifier in hand, 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

/*
  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 4 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.

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 to access and update files on Microsoft OneDrive appeared first on The SAS Dummy.

11月 172018
 

Disclaimer: this article does not cover or promote any political views. It’s all about data and REST APIs.

I am relieved, thankful, elated, glad, thrilled, joyful (I could go on with more synonyms from my thesaurus.com search for 'happy') November 6, 2018 has come and gone. Election day is over. This means no more political ads on TV, and those signs lining the streets will be coming down! It is a joy to now watch commercials about things that matter. Things like injury lawyers who are on your side or discovering a copper colored pan is going to cook my food better than a black one.

The data in this article pertains to advertising expenditures in the 2018 elections. This is the second of three articles in a series outlining the use of REST APIs and SAS. The first article, Using SAS Viya REST APIs to access images from SAS Visual Analytics, I used SAS Viya REST APIs to download an image from a flight data SAS report. In this article I use Cloud Analytics Service (CAS) REST APIs to run statistical methods on political ad spending data. The third article will bring both APIs together in an application.

The data

In the closing days of the election season, while being inundated with political advertising, I thought about how much money is spent during each cycle. The exact numbers vary depending on the resource, but the range for this year’s mid-term elections is between four and five billion dollars.

A little research reveals that outside the candidates themselves, the biggest spenders on political ads are political action committees, aka PACs. The Center for Responsive Politics compiled the data set used in this article, and derives from a larger data set released by the Federal Election Commission. The data set lists a breakdown of PAC contributions to campaign finances.

CAS REST APIs

As I explained in the previous article, SAS publishes two sets of APIs. Which APIs to use depends on the service, the data organization, or the intended use of the data. Please refer to the SAS Viya REST API article for more information on each set of APIs.

CAS REST APIs use CAS actions to perform statistical methods across a variety of SAS products. You can also use the CAS REST APIs to configure and maintain the SAS Viya environment. Here, I focus on the CAS actions. Calling the CAS actions via the REST API allow users to access SAS data and procedures and integrate them into their applications.

The process

How to construct the API call

I start with the API documentation for information on how to construct and use the CAS REST APIs. The REST API can submit actions and return the results. Parameters and result data are in JSON format. To specify your parameters, encapsulate the attributes in a JSON object, then submit a POST method on the action. The URL for your action will include the UUID of your session in the format: /cas/sessions/{uuid}/actions/{action}. Replace {uuid} and action with the appropriate values.

Create a session

The first requirement is to create a session. I use the following cURL command to create the session.

curl -X POST http://sasserver.demo.sas.com:8777/cas/sessions \
    -H 'Authorization: Bearer <access-token-goes-here>'

The response is a JSON object with a session ID:

{
    "session": "16dd9ee7-3189-1e40-8ba7-934a4a257fd7"
}

I’ll use the UUID for the session to build the URLs for the remainder of the REST calls.

Build the CAS REST API call body

Now we know the general structure of the CAS REST API call. We can browse the CAS actions by name to determine how to build the body text.

Using the simple.summary action definition, I build a JSON body to access the PAC spending from a CASTable, create a new table grouped by political views, and calculate total spending. The resulting code is below:

{
	"table":{"caslib":"CASUSER(sasdemo)","name":"politicalspending2018","groupBy":{"name":"view"}},
	"casout":{"caslib":"CASUSER(sasdemo)","name":"spendingbyaffiliation","promote":true},
	"inputs":"total",
	"subset":["SUM","N"],
}

Each line of code above contributes to running the CAS action:

  1. Define the table to use and how to group the data
  2. The output of the API call will create a new CASTable
  3. Dictate the column to summarize.
  4. The statistical method(s) to include in the result table; in this case I want to sum the Total column and count the number of PACs by group.

Send the CAS REST API

Next, I send the body of the text with the curl call below. Notice the session ID obtained earlier is now part of the URL:

curl -X POST http://sasserver.demo.sas.com:8777/cas/sessions/16dd9ee7-3189-1e40-8ba7-934a4a257fd7/actions/simple.summary \
  -H 'Authorization: Bearer <access-token-goes-here>' \
  -H 'Accept = application/json' \
  -H 'Content-Type = application/json'

The REST call creates a new CASTable, SPENDINGBYAFFILIATION. Refer to the screen shot below.

New table

SAS CASTable created by the simple.summary action

I also have the option of returning the data to create the SPENDINGBYAFFILIATION table in JSON format. To accomplish this, remove the casout{} line from the preceding call. Below is a snippet of the JSON response.

JSON response

JSON response to the simple.summary REST call

After parsing the JSON response code, it is now ready for utilization by a web application, software program, or script.

Moving on

The Thanksgiving Day holiday is fast approaching here in the United States. I plan to eat a lot of turkey and sweet potato pie, welcome the out-of-town family, and watch football. It will be refreshing to not hear the back-and-forth banter and bickering between candidates during commercial breaks. Oh, but wait, Thanksgiving is the start of the holiday season. This means one thing: promotions on Black Friday deals for items I may not need will start airing and last through year's-end. I guess if it is not one thing filling the advertising air waves, it is another. I'll just keep the remote handy and hope I can find another ball game on.

What’s next?

I understand and appreciate political candidates’ needs to communicate their stance on issues and promote their agendas. This takes money. I don't see the spending trend changing direction in the coming years. I can only hope the use of the funds will promote candidates' qualifications, beliefs, and ideas, and not to bash or belittle their opponents.

My next article will demonstrate how to use both the SAS Viya and the CAS REST APIs under the umbrella of one web application. And I promise, no politics.

Using SAS Cloud Analytics Service REST APIs to run CAS Actions was published on SAS Users.

11月 172018
 

Disclaimer: this article does not cover or promote any political views. It’s all about data and REST APIs.

I am relieved, thankful, elated, glad, thrilled, joyful (I could go on with more synonyms from my thesaurus.com search for 'happy') November 6, 2018 has come and gone. Election day is over. This means no more political ads on TV, and those signs lining the streets will be coming down! It is a joy to now watch commercials about things that matter. Things like injury lawyers who are on your side or discovering a copper colored pan is going to cook my food better than a black one.

The data in this article pertains to advertising expenditures in the 2018 elections. This is the second of three articles in a series outlining the use of REST APIs and SAS. The first article, Using SAS Viya REST APIs to access images from SAS Visual Analytics, I used SAS Viya REST APIs to download an image from a flight data SAS report. In this article I use Cloud Analytics Service (CAS) REST APIs to run statistical methods on political ad spending data. The third article will bring both APIs together in an application.

The data

In the closing days of the election season, while being inundated with political advertising, I thought about how much money is spent during each cycle. The exact numbers vary depending on the resource, but the range for this year’s mid-term elections is between four and five billion dollars.

A little research reveals that outside the candidates themselves, the biggest spenders on political ads are political action committees, aka PACs. The Center for Responsive Politics compiled the data set used in this article, and derives from a larger data set released by the Federal Election Commission. The data set lists a breakdown of PAC contributions to campaign finances.

CAS REST APIs

As I explained in the previous article, SAS publishes two sets of APIs. Which APIs to use depends on the service, the data organization, or the intended use of the data. Please refer to the SAS Viya REST API article for more information on each set of APIs.

CAS REST APIs use CAS actions to perform statistical methods across a variety of SAS products. You can also use the CAS REST APIs to configure and maintain the SAS Viya environment. Here, I focus on the CAS actions. Calling the CAS actions via the REST API allow users to access SAS data and procedures and integrate them into their applications.

The process

How to construct the API call

I start with the API documentation for information on how to construct and use the CAS REST APIs. The REST API can submit actions and return the results. Parameters and result data are in JSON format. To specify your parameters, encapsulate the attributes in a JSON object, then submit a POST method on the action. The URL for your action will include the UUID of your session in the format: /cas/sessions/{uuid}/actions/{action}. Replace {uuid} and action with the appropriate values.

Create a session

The first requirement is to create a session. I use the following cURL command to create the session.

curl -X POST http://sasserver.demo.sas.com:8777/cas/sessions \
    -H 'Authorization: Bearer <access-token-goes-here>'

The response is a JSON object with a session ID:

{
    "session": "16dd9ee7-3189-1e40-8ba7-934a4a257fd7"
}

I’ll use the UUID for the session to build the URLs for the remainder of the REST calls.

Build the CAS REST API call body

Now we know the general structure of the CAS REST API call. We can browse the CAS actions by name to determine how to build the body text.

Using the simple.summary action definition, I build a JSON body to access the PAC spending from a CASTable, create a new table grouped by political views, and calculate total spending. The resulting code is below:

{
	"table":{"caslib":"CASUSER(sasdemo)","name":"politicalspending2018","groupBy":{"name":"view"}},
	"casout":{"caslib":"CASUSER(sasdemo)","name":"spendingbyaffiliation","promote":true},
	"inputs":"total",
	"subset":["SUM","N"],
}

Each line of code above contributes to running the CAS action:

  1. Define the table to use and how to group the data
  2. The output of the API call will create a new CASTable
  3. Dictate the column to summarize.
  4. The statistical method(s) to include in the result table; in this case I want to sum the Total column and count the number of PACs by group.

Send the CAS REST API

Next, I send the body of the text with the curl call below. Notice the session ID obtained earlier is now part of the URL:

curl -X POST http://sasserver.demo.sas.com:8777/cas/sessions/16dd9ee7-3189-1e40-8ba7-934a4a257fd7/actions/simple.summary \
  -H 'Authorization: Bearer <access-token-goes-here>' \
  -H 'Accept = application/json' \
  -H 'Content-Type = application/json'

The REST call creates a new CASTable, SPENDINGBYAFFILIATION. Refer to the screen shot below.

New table

SAS CASTable created by the simple.summary action

I also have the option of returning the data to create the SPENDINGBYAFFILIATION table in JSON format. To accomplish this, remove the casout{} line from the preceding call. Below is a snippet of the JSON response.

JSON response

JSON response to the simple.summary REST call

After parsing the JSON response code, it is now ready for utilization by a web application, software program, or script.

Moving on

The Thanksgiving Day holiday is fast approaching here in the United States. I plan to eat a lot of turkey and sweet potato pie, welcome the out-of-town family, and watch football. It will be refreshing to not hear the back-and-forth banter and bickering between candidates during commercial breaks. Oh, but wait, Thanksgiving is the start of the holiday season. This means one thing: promotions on Black Friday deals for items I may not need will start airing and last through year's-end. I guess if it is not one thing filling the advertising air waves, it is another. I'll just keep the remote handy and hope I can find another ball game on.

What’s next?

I understand and appreciate political candidates’ needs to communicate their stance on issues and promote their agendas. This takes money. I don't see the spending trend changing direction in the coming years. I can only hope the use of the funds will promote candidates' qualifications, beliefs, and ideas, and not to bash or belittle their opponents.

My next article will demonstrate how to use both the SAS Viya and the CAS REST APIs under the umbrella of one web application. And I promise, no politics.

Using SAS Cloud Analytics Service REST APIs to run CAS Actions was published on SAS Users.

10月 312018
 

This article is the first in a series of three posts to address REST APIs and their use in, and with, SAS. Today, I'll present a basic example using SAS Viya REST APIs to download an image from a report in SAS Visual Analytics.

The second article will show an example of the Cloud Analytics Services (CAS) REST APIs. My third planned article will outline show a simple application that accesses SAS Viya using both sets of REST APIs.

The inspiration for this example: a visualization of air traffic data

I ran across a great post from Mike Drutar: How to create animated line charts that "grow" in SAS Visual Analytics. I followed the steps in Mike's example, which creates a visualization of airline traffic. The result was an animated line chart. For this article, I removed the animation, as it will serve me better in my use case.

SAS Viya APIs and CAS APIs: Two entry points into SAS Viya

The first thing I'd like to cover is why SAS Viya offers two sets of REST APIs. Let's consider who is using the APIs, and what they are trying to accomplish? SAS Viya APIs target enterprise application developers (who may or may not be versed in analytics), who intend to build on the work of model builders and data scientists. These developers want to deliver apps based on SAS Viya technology -- for example, to call an analytical model to score data. On the other hand, the CAS REST API is used by data scientists and programmers (who are decidedly adept at analytics) and administrators, who need to interact with CAS directly and are knowledgeable about CAS actions. CAS actions are the building blocks of analytical work in SAS Viya.

How to get started with SAS Viya REST APIs

The best place to start working with SAS Viya REST APIs is on the SAS Developer's web site. There, you will find links to the API documentation.

The REST APIs are written to make it easy to integrate the capabilities of SAS Viya to help build applications or create scripts. The APIs are based on URLs, using HTTP Authentication, and HTTP verbs. The API documentation page is split into multiple categories. The following table outlines the breakdown:

API Category Description
Visualization Provide access to reports and report images
Compute Act on SAS compute and analytic servers, including Cloud Analytic Services (CAS)
Text Analytics Provide analysis and categorization of text documents
Data Management Enable data manipulation and data quality operations on data sources
Decision Management Provide access to machine scoring and business rules
Core Services Provide operations for shared resources such as files and folders

 

The REST API documentation page is divided into multiple sections.

SAS Viya REST API doc

  1. The categories are listed in the upper-left side.
  2. Once a you select a category, related services and functions are listed in the lower left pane.
  3. The service appears in the center pane with a description, parameters, responses, and error codes.
  4. The right pane displays how to form a sample request, any optional or required body text, and sample response code.

The REST API call process

The example outlined in this article shows how to access a report image from SAS Visual Analytics. To try this out yourself, you will need: a SAS Viya environment (with SAS Visual Analytics configured), an access token, and a REST client. The REST client can be cURL (command line), Postman (a popular REST API environment), or Atom with the rest-client plugin -- or any other scripting language of your choice. Even if you do not have access to an environment right now, read on! As a SAS developer, you're going to want to be aware of these capabilities.

Get a list of reports from SAS Visual Analytics

Run the following curl command to get a list of reports on the SAS server:

curl -X GET http://sasserver.demo.sas.com/reports/reports\
  -H 'Authorization: Bearer &lt;access-token-goes-here&gt;' \
  -H 'Accept: application/vnd.sas.table.column+json'

Alternatively, use Postman to enter the command and parameters:

GET Report List API call from Postman

From the JSON response, find the report object and grab the id of the desired report:

GET Report List Response

Create a job

The next step is to create an asynchronous job to generate the SVG image from the report. I use the following HTTP POST with the /jobs verb:

curl -X POST <a href="http://sasserver.demo.sas.com/reportImages/jobs/">http://sasserver.demo.sas.com/reportImages/jobs\
  -H 'Authorization: Bearer &lt;access-token-goes-here&gt;' \
  -H 'Accept = application/vnd.sas.report.images.job+json'\
  -H 'Content-Type = application/vnd.sas.report.images.job.request+json'

Using the following sample Body text

{
  "reportUri" : "/reports/reports/b555ea27-f204-4d67-9c74-885311220d45",
  "layoutType" : "entireSection",
  "selectionType" : "report",
  "size" : "400x300",
  "version" : 1
}

Here is the sample response:

POST Job Creation Response

The job creation kicks off an asynchronous action. The response indicates whether the job is completed at response time, or whether it's still pending. As you can see from the above response, our job is still in a 'running' state. The next step is to poll the server for job completion.

Poll for job completion

Using the 'id' value from the job creation POST, the command to poll is:

curl -X GET http://sasserver.demo.sas.com/reportImages/jobs/f7a12533-ac40-4acd-acda-e0c902c6c2c1\
  -H 'Authorization: Bearer ' \ 
  -H ‘Accept = application/vnd.sas.report.images.job+json’

And the response:

GET Poll Job Creation Response

Once the job comes back with a 'completed' state, the response will contain the information we need to fetch the report image.

Get the image

I am now ready to get the image. Using the image file name (href field) from the response above, I run the following command:

curl -X GET http://sasserver.demo.sas.com/reportImages/images/K1870020424B498241567.svg\
  -H 'Authorization: Bearer ' \ 
  -H ‘'Accept: image/svg+xml'

Postman automatically interprets the response as as an image. If you use the curl command, you'll need to redirect the output to a file.

SAS Visual Analytics Graph for Air Traffic

What's Next?

SAS Visual Analytics is usually considered an interactive, point-and-click application. With these REST APIs we can automate parts of SAS Visual Analytics from a web application, a service, or a script. This opens tremendous opportunities for us to extend SAS Visual Analytics report content outside the bounds of the SAS Visual Analytics app.

I'll cover more in my next articles. In the meantime, check out the Visualization APIs documentation to see what's possible. Have questions? Post in the comments and I'll try to address in future posts.

Using SAS Viya REST APIs to access images from SAS Visual Analytics was published on SAS Users.

10月 312018
 

This article is the first in a series of three posts to address REST APIs and their use in, and with, SAS. Today, I'll present a basic example using SAS Viya REST APIs to download an image from a report in SAS Visual Analytics.

The second article will show an example of the Cloud Analytics Services (CAS) REST APIs. My third planned article will outline show a simple application that accesses SAS Viya using both sets of REST APIs.

The inspiration for this example: a visualization of air traffic data

I ran across a great post from Mike Drutar: How to create animated line charts that "grow" in SAS Visual Analytics. I followed the steps in Mike's example, which creates a visualization of airline traffic. The result was an animated line chart. For this article, I removed the animation, as it will serve me better in my use case.

SAS Viya APIs and CAS APIs: Two entry points into SAS Viya

The first thing I'd like to cover is why SAS Viya offers two sets of REST APIs. Let's consider who is using the APIs, and what they are trying to accomplish? SAS Viya APIs target enterprise application developers (who may or may not be versed in analytics), who intend to build on the work of model builders and data scientists. These developers want to deliver apps based on SAS Viya technology -- for example, to call an analytical model to score data. On the other hand, the CAS REST API is used by data scientists and programmers (who are decidedly adept at analytics) and administrators, who need to interact with CAS directly and are knowledgeable about CAS actions. CAS actions are the building blocks of analytical work in SAS Viya.

How to get started with SAS Viya REST APIs

The best place to start working with SAS Viya REST APIs is on the SAS Developer's web site. There, you will find links to the API documentation.

The REST APIs are written to make it easy to integrate the capabilities of SAS Viya to help build applications or create scripts. The APIs are based on URLs, using HTTP Authentication, and HTTP verbs. The API documentation page is split into multiple categories. The following table outlines the breakdown:

API Category Description
Visualization Provide access to reports and report images
Compute Act on SAS compute and analytic servers, including Cloud Analytic Services (CAS)
Text Analytics Provide analysis and categorization of text documents
Data Management Enable data manipulation and data quality operations on data sources
Decision Management Provide access to machine scoring and business rules
Core Services Provide operations for shared resources such as files and folders

 

The REST API documentation page is divided into multiple sections.

SAS Viya REST API doc

  1. The categories are listed in the upper-left side.
  2. Once a you select a category, related services and functions are listed in the lower left pane.
  3. The service appears in the center pane with a description, parameters, responses, and error codes.
  4. The right pane displays how to form a sample request, any optional or required body text, and sample response code.

The REST API call process

The example outlined in this article shows how to access a report image from SAS Visual Analytics. To try this out yourself, you will need: a SAS Viya environment (with SAS Visual Analytics configured), an access token, and a REST client. The REST client can be cURL (command line), Postman (a popular REST API environment), or Atom with the rest-client plugin -- or any other scripting language of your choice. Even if you do not have access to an environment right now, read on! As a SAS developer, you're going to want to be aware of these capabilities.

Get a list of reports from SAS Visual Analytics

Run the following curl command to get a list of reports on the SAS server:

curl -X GET http://sasserver.demo.sas.com/reports/reports\
  -H 'Authorization: Bearer &lt;access-token-goes-here&gt;' \
  -H 'Accept: application/vnd.sas.table.column+json'

Alternatively, use Postman to enter the command and parameters:

GET Report List API call from Postman

From the JSON response, find the report object and grab the id of the desired report:

GET Report List Response

Create a job

The next step is to create an asynchronous job to generate the SVG image from the report. I use the following HTTP POST with the /jobs verb:

curl -X POST <a href="http://sasserver.demo.sas.com/reportImages/jobs/">http://sasserver.demo.sas.com/reportImages/jobs\
  -H 'Authorization: Bearer &lt;access-token-goes-here&gt;' \
  -H 'Accept = application/vnd.sas.report.images.job+json'\
  -H 'Content-Type = application/vnd.sas.report.images.job.request+json'

Using the following sample Body text

{
  "reportUri" : "/reports/reports/b555ea27-f204-4d67-9c74-885311220d45",
  "layoutType" : "entireSection",
  "selectionType" : "report",
  "size" : "400x300",
  "version" : 1
}

Here is the sample response:

POST Job Creation Response

The job creation kicks off an asynchronous action. The response indicates whether the job is completed at response time, or whether it's still pending. As you can see from the above response, our job is still in a 'running' state. The next step is to poll the server for job completion.

Poll for job completion

Using the 'id' value from the job creation POST, the command to poll is:

curl -X GET http://sasserver.demo.sas.com/reportImages/jobs/f7a12533-ac40-4acd-acda-e0c902c6c2c1\
  -H 'Authorization: Bearer ' \ 
  -H ‘Accept = application/vnd.sas.report.images.job+json’

And the response:

GET Poll Job Creation Response

Once the job comes back with a 'completed' state, the response will contain the information we need to fetch the report image.

Get the image

I am now ready to get the image. Using the image file name (href field) from the response above, I run the following command:

curl -X GET http://sasserver.demo.sas.com/reportImages/images/K1870020424B498241567.svg\
  -H 'Authorization: Bearer ' \ 
  -H ‘'Accept: image/svg+xml'

Postman automatically interprets the response as as an image. If you use the curl command, you'll need to redirect the output to a file.

SAS Visual Analytics Graph for Air Traffic

What's Next?

SAS Visual Analytics is usually considered an interactive, point-and-click application. With these REST APIs we can automate parts of SAS Visual Analytics from a web application, a service, or a script. This opens tremendous opportunities for us to extend SAS Visual Analytics report content outside the bounds of the SAS Visual Analytics app.

I'll cover more in my next articles. In the meantime, check out the Visualization APIs documentation to see what's possible. Have questions? Post in the comments and I'll try to address in future posts.

Using SAS Viya REST APIs to access images from SAS Visual Analytics 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.