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

This article helps you to connect your SAS program code to Microsoft 365. If what you really want to do is connect your Microsoft 365 applications (Excel, PowerPoint, etc.) to SAS with "point-and-click", then check out SAS for Microsoft 365, which you can use to work with SAS Viya content in your favorite Office apps.

A SAS macro library to help with the basics

UPDATE 02Feb2026: I updated this article to address recent changes in the Microsoft authentication flow, and to introduce instructions/support for a Device Code flow option. You'll find reference implementations in PowerShell and SAS code in my GitHub repository

For most SAS users, the main goal is to be able to list files and folders in Microsoft 365, download any file to SAS, and upload files from SAS to SharePoint or OneDrive. If that's your situation, then I've got you covered with some SAS macros that hide much of the complexity.

I've created this project on GitHub with SAS macros to automate the most common tasks. You will still need to complete Step 1 and Step 2 as described in this article, but after that the macros help with the most common tasks. I've recorded a special video that shows how to get started with these macros. (The video features SAS Viya Workbench, but you do not need SAS Viya Workbench or any other specialized SAS products to use this technique.)

These macros include logic to manage some of the trickier aspects of the Microsoft Graph API:

• They provide a way to manage your access token and credentials safely, whether you store them in a secure file (SAS 9 or SAS Viya) or in SAS Content folders (SAS Viya only).
• The routines to list folders and files will include all items in the collections. The Microsoft Graph APIs return a maximum of 200 items with each call, but these macros detect this situation and will make multiple calls to retrieve the full set.
• When publishing content from SAS to a OneDrive or SharePoint folder, the method for uploading files is complex. For large files, the API requires that you create an "upload session" and split the file into chunks. Each chunk is uploaded via an API call and then reassembled on the other side. These SAS macros manage the file splitting, upload session, and iterative upload requests until the file publish is complete. (Learn more about the file splitting technique in this article.)

You can study the code in these macros to implement in your own way, or you can simply use them "as-is" for these common operations. This article uses this macro library in its examples.

Learning more about how to connect SAS to Microsoft 365

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

You can also watch this Ask the Expert webinar to learn everything that I discuss here, and see demonstrations of the techniques in action.

Using SAS with Microsoft 365: an overview

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

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

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

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

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

Step 1: Register your application

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

Remember: Multiple people can use the same registered app. Each user will need to sign in and get their own personal access tokens (covered in a later step). If your team (or everyone in your company) uses the same app, then you need to do this step only once! If you do all camp on a single app, it's a good idea to tell your IT support staff that's what you intend to do. Then they will know to expect to see activity from multiple users.

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.

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

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

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

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

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 SharePoint and OneDrive, and also add new docs and update existing docs. The permissions I need are:

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

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

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

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

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

While here, look at what's needed for Device Code Flow: While defining your app, consider enabling "Allow Public Client Flows" (under Authentication settings). This is necessary if you want to use the Device Code authentication flow (described later). Since your app does not need or store client credentials -- and relies only on delegated privileges -- this is considered a safe setting.

Probably required: Obtaining admin consent

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

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

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

Download and include ms-graph-macros.sas code

The GitHub repository contains a SAS program (named ms-graph-macros.sas) with all of the macro routines you need for the remaining tasks. Download this file to a local folder and use %INCLUDE to submit in SAS.

%let src=<my-local-project>\sas-microsoft-graph-api;
%include "&src./ms-graph-macros.sas";

You can also include directly from GitHub:

/* Run just once in your session */
options dlcreatedir;
%let repopath=%sysfunc(getoption(WORK))/sas-microsoft-graph-api;
libname repo "&repopath.";
data _null_;
    rc = git_clone( 
      "https://github.com/sascommunities/sas-microsoft-graph-api", 
      "&repoPath." 
    			); 
    put 'Git repo cloned ' rc=; 
run;
%include "&repopath./ms-graph-macros.sas";

Creating a configuration file and set its folder location

There are a few app-specific values that we'll need to reference throughout the SAS programs we're writing. The macros I've provided use a configuration file for these settings rather than hard-code them into SAS statements.

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

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

Designate a secure location for this file and for your token.json file (to be created in a later step). The information within these files is sensitive and specific to you and should be protected. See How to protect your REST API credentials in SAS programs for guidance.

If you are using SAS Viya, you can optionally create this folder and file in your SAS Content area that is private to you. For example, create a ".creds" folder within "/Users/your.account/My Folder". By default, only your account will be able to read content you place there.

The macro routines need to know where your config.json and token.json file are located. The initConfig macro initializes this.

/* This path must contain your config.json, and will also */
/* be the location of your token.json */
%initConfig(configPath=/u/yourId/Projects/ms365);

If you are using SAS Viya and you would like to store your config and token files in the SAS Content folders (instead of the file system), this is supported with a boolean flag on initConfig. For example, if you store config.json in a folder named .creds within your SAS Content user home, this tells the macro to look in that folder:

%initConfig(configPath=/Users/your.account/My Folder/.creds, sascontent=1);

Note: This sascontent flag is needed to tell the macro to use the FILENAME FILESVC method to access the SAS Content area. It requires a different file access method than traditional file systems.

Step 2: Obtain an authorization code (Auth Code Flow or Device Code Flow)

Before continuing, you need to decide which authentication flow you want to use. The GitHub project includes PowerShell and SAS implementations for both Auth Code Flow and Device Code Flow, and you can pick whichever method is easiest or most reliable in your environment.

Option A: Authorization Code (Auth Code) Flow – More interactive, more steps

The Auth Code flow opens a browser window and walks you through an interactive Microsoft sign‑in. This step needs to be completed from a web browser while I am signed into my Microsoft 365 account. The web address is very long...but we can use a SAS program to generate it for us.

This helper macro will generate the URL you can use to generate an auth code.

%generateAuthUrl();

This produces these output lines in the SAS log:

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

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

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

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

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

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

If you just generated your auth code for the first time or needed to get a new one because the old one was revoked or expired, then you need to use the auth code to get an initial access token.

/* Note: this code can be quite long -- 700+ characters. */
%let auth_code=PASTE-YOUR-AUTH-CODE-HERE;
 
/*
  Now that we have an authorization code we can get the access token
  This step will write the token.json file that we can use in our
  production programs.
*/
%get_access_token(&auth_code.);

When successful, token.json will be created/updated in the config directory you specified.

Note: This method is the traditional OAuth 2.0 approach, and it still works but there is an important recent change. Microsoft now displays an anti‑phishing warning during the redirect, and the browser automatically navigates away from the code result after a short delay, making it harder to manually capture or view the returned code.

Because of this new security behavior, the Auth Code flow is now slightly more cumbersome and timing‑sensitive, especially if you rely on manually copying the returned code value. The GitHub project includes a PowerShell helper that automates the code return and makes it easier to grab and parse the code from the URL, so if you still prefer Auth Code flow, use the included PowerShell approach rather than relying on the raw browser response.

Option B: Device Code Flow – Simpler UX but requires Public Client support

Device Code flow displays a brief short-lived "device code" in your terminal or SAS log and instructs you to visit https://microsoft.com/devicelogin on any browser. After you authenticate there, the program polls until the token is ready. Device Code flow is often the easiest option because:

• No redirect URIs are involved
• No embedded browser is required
• You can complete authentication on any device, including your phone.

To use Device Code authentication in SAS with this macro library:

1. Call %generateDeviceCode(); to create a temporary device code
2. Open the device login URL displayed in the SAS log
3. Enter the device code when prompted
4. Complete authentication in your browser
5. Call %confirmDeviceCodeToken(); to retrieve and store your access token. By default, this macro will continuous poll the login service until you either complete the authentication or the code expires (usually 15 minutes).

The token will be saved to token.json (in your config folder) for future use. Here's an example sequence:

   %initConfig(configPath=/path-to-config.json);
   %generateDeviceCode();
   /* Use the URL and code to log in, then run the following */
   %confirmDeviceCodeToken();

This prints the device login and unique, temporary code to the SAS log:

Complete the device login using your local browser:
-- INSTRUCTIONS -------
To sign in, use a web browser to open the page https://microsoft.com/devicelogin and enter the code [UNIQUECODE] to authenticate.
---END ---------

Use your web browser to complete the authentication. You'll be prompted to log into your Microsoft 365 environment (including all multifactor login steps). You will then be asked to confirm the access for your application. The following screen captures illustrate the steps.

If you've ever used your phone to connect a streaming service to your smart TV, the Device Code flow will feel familiar.

However, there are two important restrictions:

• Your app registration must have "Allow public client flows" enabled, because Device Code is a public‑client‑only authentication method. This is easy to set in the app properties within your Azure or Entra ID portal.
  "Allow Public Client Flow" is a safe setting when your app does not require/store your personal or client credentials -- all true for the process documented here.
• And yet, your organization may restrict Device Code flow -- some admins now allow it only for Intune‑managed devices -- or block it entirely due to recent guidance around phishing‑resistant auth policies.

If Device Code flow is permitted in your tenant, it is typically the most reliable option -- especially for automation or CLI‑based workflows.

Summary: Pick the flow that fits your environment

Use Device Code flow if your org allows public client flows and you want the simplest sign‑in experience.

Use Auth Code flow if your org blocks Device Code or if you prefer the standard OAuth pattern. PowerShell and SAS examples are included in the GitHub project.

Regardless of which method you choose, the remaining steps in the workflow are the same: once you obtain the authorization result (Auth Code or Device Code completion), you exchange it for your access/refresh tokens and proceed with the rest of the process.

Step 3: Obtain an access token

You should now have both config.json and token.json in your designated config folder. This screenshot shows an example of these files in a hidden folder named "~/.creds".

The content of the token.json will look something like this:

IMPORTANT: It's very important that you keep this file secure and private to you (do not share!). With the information in this file (your refresh token) and your config.json file (with your client ID and tenant ID), anyone can use these code techniques to impersonate you and access your Microsoft 365 data.

Using Microsoft 365 APIs to access SharePoint and OneDrive content from SAS

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

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

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

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 SharePoint and OneDrive interactions. Use the %initSessionMS365 macro routine to exchange the refresh-token stored in token.json for an active non-expired access token.

 %initSessionMS365;

When this is successful, you will see notes similar to these in the SAS log:

M365: Reading token info from token.json
M365: Token expires on 26JUL2024:10:04:22

The Microsoft Graph API session token is stored in the macro variable &access_token, which is referenced implicitly in the other macro routines in this package. 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?)

With the authenticated session established, you can use PROC HTTP to execute any API endpoint that your app permissions allow. For example, with User.Read (most apps have this), you can list your own account profile details:

filename resp temp;
proc http url="https://graph.microsoft.com/v1.0/me" 
  oauth_bearer="&access_token" 
  out=resp;
run;
data _null_;
    rc=jsonpp('resp','log');
run;
filename resp clear;

This is a useful test API call to make sure everything is set up and working. If you're following along and you don't get a good result from this, it's time to back up and retrace your steps before trying the remaining examples in this article. See the "Troubleshooting" section on the GitHub project for guidance and tools, including a PowerShell script that you can use outside of your SAS environment to make sure your Microsoft 365 connection is working as expected.

Example workflow: Using SAS code to explore SharePoint Online/Teams folders

Let's look at content in SharePoint Online. Remember that files that you store in Microsoft Teams channels are also using SharePoint behind the scenes, so the process is the same for exploring content in Teams.

To simulate the experience of "drilling down" into folders and subfolders to find the file we want, we use an iterative approach to list content items. Each provided SAS macro produces an output data set with the list of folders and files in the current location. We can then use PROC SQL with SELECT INTO to store the unique ID of our next target into a macro variable, then use that macro variable as input into the next step.

Let's start with the %listSiteLibraries macro routine to get the root level content for our site:

/* Let's look at SharePoint / Teams folders */
%let siteHost = sasoffice365.sharepoint.com;
%let sitePath = /sites/SASandMicrosoft365APIdemo;
 
/* stores the libraries metadata in work.libs */
%listSiteLibraries( siteHost=&siteHost.,sitepath=&sitePath.,out=libs);
/* store the ID value for the library in a macro variable, where "Documents" is at root */
proc sql noprint;
 select id into: libraryId from libs where name="Documents";
quit;
 
/* LIST TOP LEVEL FOLDERS/FILES */
 
/* special macro to pull ALL items from root folder */
/* stores the folder items metadata in work.paths */
%listFolderItems(driveId=&libraryId., folderId=root, out=work.paths);
proc print data=paths (obs=10);
run;

"Drill" into that top folder to see the subfolders the next level down:

proc sql noprint;
 select id into: folderId from work.paths where name="Team Content";
quit;
 
%listFolderItems(driveId=&libraryId., folderId=&folderId., out=work.paths);
proc print data=paths (obs=10);
run;

Now drill into that folder to see its subfolders and content.

proc sql noprint;
 select id into: folderId from work.paths where name="Reports";
quit;
 
%listFolderItems(driveId=&libraryId., folderId=&folderId., out=work.paths);
proc print data=paths (obs=10);
run;

Download a file from SharePoint and import to SAS

Now that we found the folder that contains the file we want (SciFi-AI.xlsx), we can use the %downloadFile macro to bring it into our SAS session. In this example, I'm storing the downloaded file in the WORK folder (so it will be deleted from here when the SAS session ends).

%let localFolder = %sysfunc(getoption(WORK));
 
%downloadFile(driveId=&libraryId., 
  folderId=&folderId., 
  sourceFilename=SciFi-AI.xlsx, 
  destinationPath=&localFolder.);

Note: We now have the IDs for the SharePoint library (drive) and the folder for this content, we can store them and use in future code, since they will not change. Then next time we want to do this download, we can skip the "query and discover" steps and go directly to the download step.

%put &=libraryId.;
%put &=folderId.;
 
%let reportLibrary=&libraryId;
%let reportFolder=&folderId;

With that file downloaded and in a local folder, we can now PROC IMPORT as an Excel file.

/* Downloaded an Excel file into SAS? Now we can PROC IMPORT if we want */
proc import file="&localFolder./SciFi-AI.xlsx" 
 out=scifi
 dbms=xlsx replace;
run;
 
/* Preview the contents of this imported file */
proc print data=scifi (obs=10);
run;

Upload a file from SAS into SharePoint

Now let's use the %uploadFile macro to send a SAS-generated report file to this Teams/SharePoint folder.

First, let's generate a report in Excel format to share. We'll use the data that we downloaded and imported as the source material.

proc sql;
   create table withProfit as 
    select t1.*, (t1.BoxOfficeReceipts - t1.CostToMake) * 1000000 as Profit_Loss format=dollar12.
   from scifi t1
   order by Profit_Loss desc;
quit;
 
filename report "&localFolder./SciFi-Summary.xlsx";
 
ods excel(id=xl) file=report
 options(sheet_interval='none' sheet_name='MOVIES');
ods graphics on / imagefmt=png;
 
Title "Top 10 Profitable Sci-Fi Movies";
proc print data=withProfit(obs=10);
 var YearProduced MovieTitle Profit_Loss;
run;
 
title height=3 "Profit amounts by Year";
proc sgpie data=withProfit;
  styleattrs datacolors=(cXfbb4ae cXb3cde3 cXccebc5 cXdecbe4);
  pie YearProduced / response=Profit_Loss 
  datalabelattrs=(Size = 22pt) 
  datalabeldisplay=(category percent);
run;
 
ods excel(id=xl) close;

This code produces report content that looks like this, and stores it in an Excel file named SciFi-Summary.xslx:

Using the IDs for the SharePoint library and folder that we stored from earlier steps, we can use the %uploadFile macro to send it to SharePoint:

%uploadFile(driveId=&reportLibrary., folderId=&reportFolder.,
 sourcePath=&localFolder.,
sourcefilename=SciFi-Summary.xlsx);

The %uploadFile macro is doing a lot of work behind the scenes. It splits the source file into multiple "chunks" for uploading, and then uses a special "upload session" API to transfer those pieces to Microsoft 365. It works sort of like a Star Trek transporter: it disassembles the file on the local side, and it's reassembled in place in the SharePoint destination folder. (Beam up your file to your Teams site, Scotty!)

If you repeatedly upload a file to the same destination (for example, a new version of the file each day or week), SharePoint keeps track of those versions for you in its Version history:

Example workflow: Using SAS code to explore OneDrive

Most of the actions that we can perform with SharePoint folders, we can also do with OneDrive. The main difference is that the API to list OneDrive root content is different, so we have a special routine called %listMyDrives.

/* Exploration: get the list of top-level drives in OneDrive */
%listMyDrives(out=work.drives);
proc print data=drives;
run;

From the available "drives", we can use PROC SQL to single out the ID for the main "Documents" folder and then list its contents.

/*
 If you have multiple drives you can filter 
 the set with a where clause on the name value.
 Note that the system may track additional drives behind-the-scenes, so
 don't assume you have just one!
 
 In my case, the main drive is labeled "Documents".
*/
/* store the ID value for the drive in a macro variable */
proc sql noprint;
 select id into: driveId from drives where driveDisplayName="Documents";
quit;
 
/* LIST TOP LEVEL FOLDERS/FILES */
 
/* special macro to pull ALL items from root folder               */
/* Note that the APIs return only 200 items at a time             */
/* This macro will iterate through multiple times to get the full */
/* collection.                                                    */
%listFolderItems(driveId=&driveId., folderId=root, out=work.paths);
/* preview a few of these */
proc print data=paths (obs=10);
run;

From this point on, you can follow the same methods used for SharePoint, already covered earlier. Listing folder items, downloading a file, and uploading a file all work using the same routines as for SharePoint.

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:

• Using Google Analytics APIs from SAS
• Using GitHub APIs from SAS
• How to publish to a Microsoft Teams channel using SAS
• Sending messages to a Slack channel with SAS
• How to test PROC HTTP and the JSON library engine
• Securing your REST API credentials in your SAS programs

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

Update 03Jul2025: I've updated the graph and code in this post for the most recent versions of SAS Enterprise Guide and SAS. I also gave the chart a branding facelift, reflecting the most recent SAS colors and fonts that we use.

You might notice that my chart is a bit busier than Rick's version. As a desktop client application that works across multiple SAS versions, we tend to issue major releases of SAS Enterprise Guide more often than we update the core engine of SAS software.

On my chart, the labels on the data points indicate the "event" that motivated the release. In some cases, we issued a new release to go with a new version of SAS (such as v1.2 with SAS 8.2). In other cases, we "remastered" a version to support more languages or another operating system (such as Microsoft Vista support with v4.1 in 2007, or the L10N release of v4.3 in 2010). Most recently, we released a version of SAS Enterprise Guide that can connect to SAS Viya 4 and then SAS Viya Workbench.

You might also notice that some of these releases happened very close together. Often, SAS R&D works on updates to multiple releases in parallel. Developers make changes to multiple versions to provide maintenance and hotfix changes (based on customer needs), while still contributing to the Next Big Version that will contain the compelling new features (based on customer wishes).

Here's the code that produced the chart:

data Releases;
  format Date DATE7.;
  input Category $8. Release $7. Date DATE9. Details $20.;
  datalines;
Ancient 1.0    01Nov1999 SAS 8.0
Ancient 1.1    01Jul2000 SAS 8.1
Ancient 1.2    01Apr2001 SAS 8.2
Ancient 1.3    01Sep2001 
Ancient 2.0    01Sep2002 
Ancient 2.05   01May2003 SAS 9.0
Ancient 2.1    01Apr2004 SAS 9.1
Ancient 3.0    01May2004 .NET rewrite
Ancient 3.02   01Feb2005 remaster/L10N
Ancient 4.1    01Mar2006 
Ancient 4.1v   01Apr2007 Vista
Ancient 4.2    01Mar2009 SAS 9.2
Ancient 4.22   01Sep2009 SAS 9.2m2
Old     4.3    01Aug2010
Old     4.305l 01Sep2010 L10N
Old     4.305r 01Jul2011 SAS 9.3
Old     5.1    01Feb2012
Old     6.1    01Jul2013 SAS 9.4
Old     7.1    01Oct2014 
Old     7.11   01May2015
Old     7.12   01Feb2016
Old     7.13   01Nov2016 SAS 9.4m4
Recent  7.15   01Sep2017 SAS 9.4m5
Recent  8.1    01Jun2019 Redesign
Recent  8.2    01Nov2019
Recent  8.3    18Aug2020 SAS 9.4m7
Recent  8.4    19Mar2024 SAS Viya
Recent  8.5    04Dec2024 
Recent  8.6    19Jun2025 SAS Viya Workbench
;
 
/* running in SAS EG - modify active ODS to add title to graph */
ods html5(id=eghtml) style=htmlencore gtitle;
 
ods graphics / height=850 width=1300 ;
title font="Anova" height=2.2 color=cx0766D1 
  "SAS Enterprise Guide Releases (1999-2025)";
proc sgplot data=Releases noautolegend;
  styleattrs datacolors=(cxC4DEFD cxFFCC33 cx4398F9);
  block x=date block=category / transparency = 0.75 
     valueattrs=(weight=bold size=14pt color=navy 
      family='Anova');
  scatter x=date y=release / datalabel=Details 
    datalabelpos=topleft dataskin=matte
    datalabelattrs=(size=10pt weight=bold Family="Anova")
    markerattrs=(symbol=CircleFilled color=cxFF66B2 size=14);
  xaxis grid type=time offsetmax=0.1 offsetmin=0 valueattrs=(Family="Anova") 
        ranges=('01Jan1997'd-'01Jul2025'd) display=(nolabel) ;
  yaxis type=discrete grid offsetmax=0.1 valueattrs=(Family="Anova")
    label="Release/Event" labelattrs=(Family="Anova");
  inset "Updated &SYSDATE9." / 
     textattrs=(weight=bold) position=bottomright;
run;

The post Through the years: SAS Enterprise Guide versions appeared first on The SAS Dummy.

For years my wife and I have enjoyed using the Merlin app (from Cornell labs) on our smartphones to identify the birds we hear around our home. The app is like Shazam for birds, but instead of telling you it's Taylor Swift, it tells you it's a Chimney Swift (or maybe a Carolina Wren). As fun as that is, the Merlin app has a limitation: it can only listen when you listen in the moment.

Enter BirdNET-PI

BirdNET-PI is a Raspberry Pi-based system that uses machine learning to detect and identify birdsongs in real time.

I set up a BirdNET-PI device in my backyard in Raleigh, NC. It listens 24/7, logs detections, and stores them in a local database. Within days, I had a treasure trove of bird call data—timestamps, species names, confidence scores, and audio clips. It's been running for over 6 months now, and it has detected over 140,000 bird songs!

I first learned about BirdNET-PI during a session at All Things Open, a huge annual conference about open source technologies and community. (SAS has been a sponsor of the event.) You can learn more about my experience with BirdPI-NET by reading this post I shared on LinkedIn.

From Chirps to Charts with SAS

Naturally, I wanted to do more than just listen. I wanted to see the patterns. So I built a SAS program to ingest the BirdNET-PI data and generate reports:

• Species frequency over time: Which birds are most common in the morning vs. evening?
• Seasonal trends: Are the warblers just passing through, or do they stick around?
• Confidence heatmaps: How sure is BirdNET-PI about what it hears?

Using combination of SAS code and SAS Visual Analytics, I created dashboards that let me explore the data interactively. Here are a few example insights I've captured.

First, a heatmap of all of the bird species and the number of detections over time. It's a long chart because there are over 100 detected species. From this chart you can see that some birds are seasonal (obviously!) while others we hear all year long!

Our listening device is always on and working hard. From this Daily Detections plot, you can see that it's picking up anywhere from dozens to thousands of detections per day!

I've configured our BirdNET-PI device to record detections only if the machine learning algorithm scores the certainty at 0.7 (70%) or higher. In this SAS Visual Analytics table, I've enabled the "heat map" visualization for the Confidence value for each detection. The algorithm isn't just matching the bird song with 5-second snippets -- it's also considering our geography and time of year when it assigns the probability (in case a song sounds a bit like a European magpie, but we know that just cannot be in my backyard).

Some species visit only in certain seasons, and hummingbirds are back! We've put out our hummingbird feeders and it's bringing them in. Our BirdNET-PI device is detecting their chirps, as you can see from this SAS Visual Analytics chart.

We're always delighted when we hear owls. They are with us year round, but you can see that they are more vocal in the springtime. I'm guessing they are looking for love, or at least a hootin' good time!

Try it yourself: analyze our birdsong data!

Interested in seeing what you can do with this data? I periodically update my GitHub site with the latest detection data from our device. You can always grab the latest with this SAS code:

filename birdcsv temp;
proc http
 method="GET"
 url="https://raw.githubusercontent.com/cjdinger/birdnet-data/refs/heads/main/alldetect.csv"
 out=birdcsv;
run;
 
proc import file=birdcsv
 out=birds
 dbms=csv
 replace;
run;

On my GitHub project, you can also grab a more complete program that imports the data, summarizes it a bit, and generates the heatmap chart.

Building a happy nest for birds and data

This project has been the perfect mashup of my love for nature and my obsession with data. Even better? It's one of the few tech experiments my wife hasn't just tolerated—-she's genuinely into it. She checks the bird detection logs like she's reading the morning paper, and she even let me mount a microphone on the side of the house and set up a Raspberry Pi in the family room. Some might say she's humoring me in my "mature years," but I prefer to think I’ve finally proven that marrying a data nerd has its perks. At the very least, our living room now has the best bird surveillance system on the block.

Want to build your own?

If you're inspired to put together your own BirdNET-PI device, here's a link to the instructions that I used. The Rasberry Pi device and accessories cost only about $40 US, and I already had a suitable microphone in my box of disused equipment.

The post How I use BirdNET-PI and SAS to track my feathered neighbors appeared first on The SAS Dummy.

To summarize what was announced on stage (and note, I have updated this list in May 2025 to reflect latest progress and announcements):

• SAS 9 will be available to our customers as they transition to SAS Viya.
• SAS is investing in SAS 9 with the creation of the SAS 9 division. The SAS 9 division comprises developers, testers and product managers, all devoted to keeping SAS 9 relevant and its customers productive with their platform.
• SAS released SAS 9.4 Maintenance 9 (SAS 9.4M9) in June 2025. (Recall that SAS released SAS 9.4 M8 in early 2023.) The team releases SAS 9.4 quarterly security updates and fixes as needed.
• There will be future releases of SAS 9. The SAS software support policy promises standard support for a release for 5 years from its general-availability date. With more SAS 9 releases on the horizon, support for SAS 9 will move forward beyond that for many years to come. (As confirmed in a recent webinar, M10 is already the books for a future release.)
• Beginning with SAS Enterprise Guide 8.4, you can connect to SAS Viya. Soon you can also connect to SAS Viya Workbench (SAS Enterprise Guide 8.6, scheduled for June 2025).

Building a bridge to SAS Viya

Even with this focus on SAS 9, SAS is not letting up on our attention to SAS Viya. The SAS Viya platform has monthly releases and is where most new innovation takes place. However, the move to SAS Viya from SAS 9 can be a big project, and SAS continues to build new tools and processes to help customers assess and migrate their analytics work to the more modern platform.

As part of this effort, Bryan and Susan announced that SAS Enterprise Guide will soon be able to connect to a SAS Viya 4 environment. (And, as of March 2024, this is now available as SAS Enterprise Guide 8.4!) This is a change from previous direction, and is a direct result of customer feedback. Up until now, SAS Enterprise Guide was tethered to SAS 9 only, because SAS Studio (with its many enhancements) has been the deluxe coding interface for SAS Viya. SAS Studio is still the best environment to leverage SAS Viya features, but can use SAS Enterprise Guide as a bridge to leverage your existing projects and code workflows as you adopt SAS Viya.

SAS programmers are spoiled for choice

In addition to these SAS 9 announcements, SAS Explore featured the premier of SAS Viya Workbench, a light-weight cloud-native coding environment for developers using SAS, Python, and R. These are all native code experiences that leverage the SAS Viya platform with your favorite programming language. SAS Viya Workbench allows you to use VS Code, Jupyter Notebooks, and even SAS Enterprise Guide for your coding experience. Read more about these technology announcements in this news release.

And if you love VS Code (I do), you can use the SAS extension for VS Code right now to manage and view your SAS programs, while connecting to SAS Viya or SAS 9 environments to run programs and view results.

The post What's next for SAS 9 and SAS Enterprise Guide? appeared first on The SAS Dummy.

SAS has seen some changes since those early Dummies days. Until now, the most recent edition of the book was from 2009 and covered SAS 9.2 and its era of applications. So I was excited when Wiley, the publisher of all Dummies books, approached me in late 2024 to write a third edition. "We still sell copies of this book even though it's 15 years old. We think an updated version could be successful." (I was also a bit apprehensive, because I know how much work it is to write a book on a Dummies schedule. It's basically an intense 14-week sprint of writing, followed by a few weeks of quick-turnaround edits. But no way was I going to let somebody else write it!)

The new SAS For Dummies

I'm pleased to say that SAS For Dummies, 3rd Edition is now available, and I am very proud of the results! This new edition addresses all of the ways that people use SAS today, covering SAS 9.4 and SAS Viya. It has chapters about SAS programming, data management, reporting, machine learning, and data visualization. The book shows the pantheon of modern SAS tools like SAS Visual Analytics, SAS Model Studio and Model Manager, SAS for Microsoft 365, SAS Viya Workbench, and of course the ever-enduring SAS Enterprise Guide.

You can find the book anywhere that Dummies titles are sold, including Barnes & Noble, Amazon, and maybe even your local bookstore (support them!). You can also find it among all SAS books on RedShelf.

We'll have some copies of the book at the upcoming SAS Innovate event! (I can't wait, because that might be the first time I get my hands on it too!) If you're lucky enough to snag a copy, come find me and I'll sign it for you (but only if you really want that).

Book credits

As the writer I curated all of the content, examples, and graphics -- but as with most tech books like this, there is a huge team of people that make it a great product. I'm especially grateful to Jennifer Tamburro, my longtime friend and colleague (and SAS documentation writer) who served as Technical Editor. And of course the multiple other editors and production crew at Wiley, who accepted my pile of chapters with edits and screen shots, and turned it into an amazing publication.

The post SAS For Dummies: New edition for the modern SAS user appeared first on The SAS Dummy.

That's why SAS Viya Workbench is my new favorite tool for SAS programming work. It's easy to spin up and load a project from a Git repository. All of my SAS code works great within it, and when I need something a little extra, like a Python task, I can accomplish that in the same environment.

I've recorded three short demos of work that I've done using SAS Viya Workbench.

Wordle-like game in SAS Viya Workbench for Learners

Video: Run my Wordle-like game (built in SAS) in SAS Viya Workbench for Learners.

In this demo, I used a Git command to clone my wordle-sas program into a fresh environment, then was able to immediately "play" and embarrass myself with my guesses. If you are a student or faculty member at a university, you can use SAS Viya Workbench for Learners too!

SAS code to access SharePoint and OneDrive

Video: Connect to Microsoft 365 files like SharePoint Online or OneDrive using SAS code.

I wanted to demonstrate how easy it is to connect to Microsoft 365 and work with files in SharePoint and OneDrive, leveraging a library of helper macros I built in SAS.

Python for Google Analytics data, SAS to analyze it

Video: Connect to Google Analytics using a supported Python client library, then analyze the data with SAS.

Finally, I show off a real process that I use for my own reporting here at SAS -- monitoring the activity on our web sites using GA data in SAS tools.

All of these demos -- and several more -- are available on the SAS Viya Workbench: Getting Started board, hosted on SAS Communities. Check them out and let me know what you think!

The post 3 SAS Viya Workbench use cases for SAS Users appeared first on The SAS Dummy.

However, the 2024 IEEE Spectrum "Top Programming Languages" list recently placed SAS at the #5 position when the ranking is weighted for "job opportunities". This surprise validation has excited a lot of us SAS fans, as this confirms something that we've known all along. Organizations in all industries rely on SAS technology for mission-critical tasks, and they recruit SAS-skilled talent to maintain these and get the most from their SAS investment.

The methodology for tracking programming language popularity usually does not favor SAS. That's because these rankings track public activity such as "prevalence on GitHub or public repositories", "amount of search traffic" in public search engines, or "discussion activity on forums like StackOverflow or Reddit." As a vendor-supported technology, most internet activity for SAS is concentrated on SAS sites and communities. And since SAS customers often work in regulated industries building proprietary methods and models – they cannot and would not share this work on GitHub, at least not at the scale you might see for the top-ranked languages like Java or C. (By the way, SAS users do freely share their knowledge on our own community and in-person at SAS user events, which I am very grateful for.)

I've often said that if we could rank languages by "amount of code that is running in production systems at organizations around the world," SAS would rise to the top.

But does SAS even belong on a "programming languages" list?

I'm going to get pedantic here: SAS is not a programming language. SAS has a programming language. (Actually, SAS has several programming languages.) The SAS programming language you're thinking of (DATA step, PROCs and SAS macro) provides access to all the capabilities of SAS: data management, analytics, machine learning and AI, and more. While most SAS users leverage code to get to these capabilities, there are many other interfaces and methods that provide access as well.

Most of the programming languages that appear on these "top language" lists do not provide inherent capabilities for any specific discipline. For example, Java is a popular object-oriented language that is used to teach computer science concepts in schools. Its standard libraries provide basic access to operating system services and computation. But in industry, Java is most often used with specific frameworks to build things like mid-tier applications or embedded systems. Developers add these frameworks to the stew to provide the capabilities, and the Java syntax is their tool to unlock them. The same is true for C and C++. (By the way, C is the programming language that is used to build the core capabilities of SAS – so we love the C language here.)

Many programming languages are agnostic of discipline/industry. If you tell me that you are a Java or C programmer, then I know what syntax you use for coding but I don't know anything about your real job. Some languages provide cues about your specialty. If you tell me that you are a TypeScript programmer, then I might guess that you build web applications using the React framework…because I know that is a common use for TypeScript. (Many of our SAS application front-end experiences are built with React.)

But if you tell me that you "program with SAS", then I know immediately that you work with data and statistics. With SAS, you do not have to add additional frameworks or libraries to get work done – it's all built-in with hundreds of SAS procedures and analytics actions.

Yes (please) Code

We live in a world with much hype about "low-code" and "no-code" workflows. Companies say they don't want to require programming skills to unlock value from the systems they have invested in. While we have that covered in the SAS Viya platform, we've also doubled down on the developer who just wants to, you know, code.

Are you a SAS fan or a SAS snob?

Here's how I make the subtle distinction:

• SAS fan: "I rarely find anything in my job I cannot accomplish by using SAS."
• SAS snob: "If I can't accomplish a task with SAS, it's not worth doing!"

We celebrate both personas here, but even I admit that I'm just a SAS fan, not a SAS snob. As a developer I appreciate a specific tool for what it can do for me, but not to the exclusion of everything else.

SAS Viya Workbench will make you a fan

If you're a "get out of the way and let me code" kind of person, then you will love SAS Viya Workbench.

SAS Viya Workbench is a developer-first IDE that provides access to SAS' analytic capabilities using the SAS programming language, or Python…and soon R. SAS Viya Workbench brings the developer experience to the forefront in a way that I appreciate, using the familiar VS Code environment that I'm already so familiar with. SAS Viya Workbench launches quickly in my browser, relieving me of the task of installing SAS (something I have done hundreds of times without complaint!), and it spins down when I'm not using it. I love to use it for work and it's fun to demo for SAS users.

The post Is SAS a programming language? appeared first on The SAS Dummy.

Let's start with some definitions related to columns.

Length: The column length, in SAS terms, is the amount of storage allocated in the data set to hold the column values. The length is specified in bytes. For numeric columns, the valid lengths are usually 3 through 8. The longer the length, the greater the precision allowed within the column values. For character columns, the length can be 1 through 32767. For single-byte data values, that equates to the number of characters the column can hold. For multibyte data values (DBCS, Unicode, or UTF-8), where a character can occupy more than one byte, the number of characters that fit might be less than the length value of the column.

Format: The column format, in SAS terms, is a basically an instruction for how to transform a raw value into an appearance that is suitable for a given purpose. A basic attribute of a format is the format length, which controls how much of the value is displayed. For example, a character column might have a storage length of 10 bytes, but a format length of 5 characters ($5. format), so when you see the formatted values you will see at most 5 characters for each record.

Another attribute of the format is the displayed precision. For example, the DOLLAR8. format will show you up to 8 characters of a value (including a currency symbol and thousands separator) in dollars, but no cents. DOLLAR8.2 will show you the decimal point and the value to the nearest cent (2 decimal spaces). In each case, the value displayed will not exceed 8 characters.

What happens if the formatted value runs into trouble squeezing into 8 characters? SAS drops the thousands separator and currency symbol to free up 2 slots first. If that's not enough, SAS begins to drop precision by lopping off the pennies, then the dimes. If that's still not enough, SAS rounds the value and uses scientific notation and other tricks to save space -- all the while keeping the integrity of your original value for use in any calculations.

For example, run this program:

data dollars;
 length val1 8 val2 8 val3 8 val4 8;
 format val1 DOLLAR8. val2 DOLLAR8.2 val3 DOLLAR4.2 val4 DOLLAR10.2;
 val1=12345.67;
 val2=12345.67;
 val3=12345.67;
 val4=12345.67;
 
run;
proc print;
run;

You see these values:

val1    $12,346
val2    12345.67
val3    12E3
val4    $12,345.67

data long;
  length name $ 8;
  format name $8.;
  infile datalines dsd;
  input name;
datalines4;
aaaaaaaa
bbbbbbbb
cccccccc
dddddddd
;;;;
run; 
 
data short;
  length name $ 4;
  format name $4.;
  infile datalines dsd;
  input name;
datalines4;
eeee
ffff
gggg
hhhh
;;;;
run;
 
data longfirst;
  set long short;
run;
proc contents; run;
proc print; run;
 
data shortfirst;
  set short long;
run;
proc contents; run;
proc print; run;

If you SET the table with the short length and format first, the resulting table assumes the shorter format -- and thus you "lose" precision. But your SAS log warns you:

WARNING: Multiple lengths were specified for the variable name by input data set(s). 
This may cause truncation of data. 

If you SET the table with the longer length and format first, then the result has the longer format. No problems or warnings with that, because you can always fit the smaller values into a larger space, just not vice versa.

Sometimes you don't know all of the attributes of your input columns ahead of time. In this case, experienced SAS programmers will often create an empty "template" data set that has the columns and attributes that they want to see in the final output. By adding this template data as the first in their SET statement, this establishes the correct attributes in the result.

data template;
 length name $ 8;
 format name $8.;
/* trick to establish the variable attributes in the header, 
   STOP with 0 observations */
 stop; 
run;
 
/* Code for SHORT and LONG DATA steps omitted */
 
/* FINAL will have the column attributes from TEMPLATE */
data final;
 set template /* our empty data template */
     short long;
run;

SAS formats are extremely powerful. They allow you to reuse data values for all sorts of reporting and categorization purposes without having to actually change the underlying values. My simple explanations here only scrape the surface of the flexibility and nuances of SAS formats. For more information, you might check out The Power of PROC FORMAT or The Little SAS Book.

(Note: I create this article originally in 2007 while writing SAS For Dummies. I've made just a few updates in 2024 to reshare it.)

The post Lengths and formats in SAS: the long and short of it appeared first on The SAS Dummy.

Even with the limited character set, pictorial communication was possible with ASCII through the fun medium of "ASCII art." ASCII art is basically the stone-age version of emojis. For example, consider the shrug emoji: 🤷

Its text-art ancestor (not strictly ASCII as a sharp reader pointed out) is this: ¯\_(ツ)_/¯ While ASCII and text art currently enjoys a retro renaissance, the emoji has become indispensable in our daily communications.

I'm publishing this article to match the timing of World Emoji Day, which happens on July 17. If you ever forget the date of this important event, just look at the traditional calendar emoji for a reminder!

(Update: I first published this article in 2021. I've updated it to include code that references the most recent Unicode emoji standards.)

Emojis before Unicode

Given the ubiquity of emojis in every communication channel, it's sometimes difficult to remember that just a few years ago emoji characters were devised and implemented in vendor-specific offerings. As the sole Android phone user in my house, I remember a time when my iPhone-happy family could express themselves in emojis that I couldn't read in the family group chat. Apple would release new emojis for their users, and then Android (Google) would leap frog with another set of their own fun symbols. But if you weren't trading messages with users of the same technology, then chunks of your text would be lost in translation.

Enter Unicode. A standard system for encoding characters that allows for multiple bytes of storage, Unicode has seemingly endless runway for adding new characters. More importantly, there is a standards body that sets revisions for Unicode characters periodically so everyone can use the same huge alphabet. In 2015, emoji characters were added into Unicode and have been revised steadily with universal agreement.

This standardization has helped to propel emojis as a main component of communication in every channel. Text messages, Twitter threads, Venmo payments, Facebook messages, Slack messages, GitHub comments -- everything accepts emojis. (Emojis are so ingrained and expected that if you send a Venmo payment without using an emoji and just use plain text, it could be interpreted as a slight or at the least as a miscue.)

For more background about emojis, read How Emjois Work (source: How Stuff Works).

Unicode is essential for emojis. In SAS, the use of Unicode is possible by way of UTF-8 encoding. If you work in a modern SAS environment with a diverse set of data, you should already be using ENCODING=UTF8 as your SAS session encoding. If you use SAS OnDemand for Academics (the free environment for any learner), this is already set for you. And SAS Viya offers only UTF-8 -- which makes sense, because it's the best for most data and it's how most apps work these days.

Emojis as data and processing in SAS

Emojis are everywhere, and their presence can enrich (and complicate) the way that we analyze text data. For example, emojis are often useful cues for sentiment (smiley face! laughing-with-tears face! grimace face! poop!). It's not unusual for a text message to be ALL emojis with no "traditional" words.

The website Unicode.org maintains the complete compendium of emojis as defined in the latest standards. They also provide the emoji definitions as data files, which we can easily read into SAS. This program reads all of the data as published and adds features for just the "basic" emojis:

/* MUST be running with ENCODING=UTF8 */
filename raw temp;
proc http
  url="https://unicode.org/Public/emoji/latest/emoji-sequences.txt"
 out=raw;
run;
 
ods escapechar='~';
data emojis (drop=line);
length line $ 1000 codepoint_range $ 45 val_start 8 val_end 8 
       type $ 30 comments $ 65 saschar $ 20 htmlchar $ 25;
infile raw ;
input;
line = _infile_;
 
/* skip comments and blank lines */
/* data fields are separated by semicolons */
if substr(line,1,1)^='#' and line ^= ' ' then do;
 
 /* read the raw codepoint value - could be single, a range, or a combo of several */
 codepoint_range = scan(line,1,';');
 /* read the type field */
 type = compress(scan(line,2,';'));
 /* text description of this emoji */
 comments = scan(line,3,'#;');
 
 /* for those emojis that have a range of values */
 val_start = input(scan(codepoint_range,1,'. '), hex.);
 if find(codepoint_range,'..') > 0 then do;
  val_end = input(scan(codepoint_range,2,'.'), hex.);
 end;
 else val_end=val_start;
 
 if type = "Basic_Emoji" then do;
  saschar = cat('~{Unicode ',scan(codepoint_range,1,' .'),'}');
  htmlchar = cats('<span>&#x',scan(codepoint_range,1,' .'),';</span>');
 end;
 output;
end;
run;
 
/* Assuming HTML or HTML5 output destination */
/* print the first 50 emoji records */
proc print data=emojis (obs=50); run;

(As usual, all of the SAS code in this article is available on GitHub.)

The "features" I added include the Unicode representation for an emoji character in SAS, which could then be used in any SAS report in ODS or any graphics produced in the SG procedures. I also added the HTML-encoded representation of the emoji, which uses the form &#xNNNN; where NNNN is the Unicode value for the character. Here's the raw data view:

When you PROC PRINT to an HTML destination, here's the view in the results browser:

In search of structured emoji data

The Unicode.org site can serve up the emoji definitions and codes, but this data isn't exactly ready for use within applications. One could work through the list of emojis (thousands of them!) and tag these with descriptive words and meanings. That could take a long time and to be honest, I'm not sure I could accurately interpret many of the emojis myself. So I began the hunt for data files that had this work already completed.

I found the GitHub/gemoji project, a Ruby-language code repository that contains a structured JSON file that describes a recent collection of emojis. From all of the files in the project, I need only one JSON file. Here's a SAS program that downloads the file with PROC HTTP and reads the data with the JSON libname engine:

filename rawj temp;
 proc http
  url="https://raw.githubusercontent.com/github/gemoji/master/db/emoji.json"
  out=rawj;
run;
 
libname emoji json fileref=rawj;

Upon reading these data, I quickly realized the JSON text contains the actual Unicode character for the emoji, and not the decimal or hex value that we might need for using it later in SAS.

I wanted to convert the emoji character to its numeric code. That's when I discovered the UNICODEC function, which can "decode" the Unicode sequence into its numeric values. (Note that some characters use more than one value in a sequence).

Here's my complete program, which includes some reworking of the tags and aliases attributes so I can have one record per emoji:

filename rawj temp;
 proc http
  url="https://raw.githubusercontent.com/github/gemoji/master/db/emoji.json"
  out=rawj;
run;
 
libname emoji json fileref=rawj;
 
/* reformat the tags and aliases data for inclusion in a single data set */
data tags;
 length ordinal_root 8 tags $ 60;
 set emoji.tags;
 tags = catx(', ',of tags:);
 keep ordinal_root tags;
run;
 
data aliases;
 length ordinal_root 8 aliases $ 60;
 set emoji.aliases;
 aliases = catx(', ',of aliases:);
 keep ordinal_root aliases;
run;
 
/* Join together in one record per emoji */
proc sql;
 create table full_emoji as 
 select  t1.emoji as emoji_char, 
    unicodec(t1.emoji,'esc') as emoji_code, 
    t1.description, t1.category, t1.unicode_version, 
    case 
     when t1.skin_tones = 1 then  t1.skin_tones
	 else 0
	end as has_skin_tones,
    t2.tags, t3.aliases
  from emoji.root t1
  left join tags t2 on (t1.ordinal_root = t2.ordinal_root)
  left join aliases t3 on (t1.ordinal_root = t3.ordinal_root)
 ;
quit;
 
proc print data=full_emoji; run;

Here's a snippet of the report that includes some of the more interesting sequences:

The diversity and inclusion aspect of emoji glyphs is ever-expanding. For example, consider the emoji for "family":

• The basic family emoji code is \u0001F46A (👪)
• But since families come in all shapes and sizes, you can find a family that better represents you. For example, how about "family: man, man, girl, girl"? The code is \u0001F468\u200D\u0001F468\u200D\u0001F467\u200D\u0001F467, which includes the codes for each component "member" all smooshed together with a "zero-width joiner" (ZWJ) code in between (👨‍👨‍👧‍👧)
• All of the above, but with a dark-skin-tone modifier (\u0001F3FF) for 2 of the family members: \u0001F468\u0001F3FF\u200D\u0001F468\u200D\u0001F467\u200D\u0001F467\u0001F3FF (👨🏿‍👨‍👧‍👧🏿)

Note: I noticed that not all browsers have caught up on rendering that last example. In my browser it looks like this:

Conclusion: Emojis reflect society, and society adapts to emojis

As you might have noticed from that last sequence I shared, a single concept can call for many different emojis. As our society becomes more inclusive around gender, skin color, and differently capable people, emojis are keeping up. Everyone can express the concept in the way that is most meaningful for them. This is just one way that the language of emojis enriches our communication, and in turn our experience feeds back into the process and grows the emoji collection even more.

As emoji-rich data is used for reporting and for training of AI models, it's important for our understanding of emoji context and meaning to keep up with the times. Already we know that emoji use differs among different age generations and across other demographic groups. The use and application of emojis -- separate from the definition of emoji codes -- is yet another dimension to the data.

Our task as data scientists is to bring all of this intelligence and context into the process when we parse, interpret and build training data sets. The mechanics of parsing and producing emoji-rich data is just the start.

If you're encountering emojis in your data and considering them in your reporting and analytics, please let me know how! I'd love to hear from you in the comments.

The post How to work with emojis in SAS appeared first on The SAS Dummy.

I originally wrote this article when I was inspired by a news story on NPR's Morning Edition about the merits and risks of the $100 bill. There are a lot of them in circulation, but no one knows exactly where they are. According to the report, they are seldom used for legitimate business transactions. When a transaction reaches into the hundreds of dollars, the business parties tend to resort to a digital transaction (or maybe a good-old-fashioned personal check). Large cash transactions are often used to avoid taxation or, more nefariously, to hide criminal activity.

It was easy to find the data on the US Federal Reserve: Currency in Circulation web site. I could have copied and pasted the table for use in SAS, but the Fed already makes an ASCII version of the table available for nerds like me. With a bit of SAS code I was able to download the data (using PROC HTTP), transpose it for analysis, calculate the currency values for the counts of the various notes, and create a graph that verifies what I heard on the news. Indeed, much of our nation's cash wealth is floating around on a bunch of Benjamins.

The percentage of wealth represented in the $100 bill has grown over the past 20 years, perhaps at the expense of the far-more-ATM-friendly $20 bill. In 1996, the ratio of hundreds-to-twenties (in cash value) was 61/20. In 2016, it was 79/12. The following table shows the two years side-by-side. $100 bills increased by nearly 18 percentage points, while all other notes decreased. $20 notes were the biggest loser at -8.3 points, and $50 notes were next at -5.8 percentage points.

If we look at the percent breakdown by bill counts (instead of value), you can see the shifts a bit differently.

I created those two plots by calculating the percentages with PROC FREQ, and then using PROC SGPLOT to graph Year, using the percentages as the response value and stacking the data by Denomination. I used GROUPORDER=Data to keep the data colors and legend consistent across the different graphs. The raw values are interesting to examine as well, as it more clearly shows the trends for the past 20 years. Here are the two representations (cash value and bill counts) with their actual values and not just the percentages.

An interesting aside: the $2 bill remains a novelty. I had the opportunity to spend a few of these recently, and the person receiving them had to be convinced that they were real money. It made me want to secure more $2 bills (a.k.a. "some Jeffersons") for future transactions -- that's the sort of trouble-making consumer that I like to be.

In case you're interested, here's the SAS program that I used -- try it yourself! This should work fine in any recent SAS version, including in SAS OnDemand for Academics.

The post Visualized: US Currency in circulation, past and present appeared first on The SAS Dummy.