Using SAS with Microsoft 365 (OneDrive, Teams, and SharePoint)

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

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

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 06Oct2025: I updated this article to emphasize the use of the macro library I created. It's the quickest path to success for the tasks that most people need to accomplish. As a result, there are fewer lines of code to show! But if you want to learn the details of the behind-the-scenes techniques, feel free to study and experiment with the macro source code.

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.

Click to watch the video tutorial.

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:

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!

Using your browser while you are signed into Microsoft 365, navigate to a special web address to obtain an authorization code for your application.

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

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.

Microsoft Application Registration portal

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.

"Register an application" form

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

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

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

Redirect URI Selections

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

Details for my sample application

Specifying your app permissions

Your app will need specific permissions in order to function. In my example, I want my SAS program to read documents from 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.

Adding permissions that the app needs

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

Delegated versus Application Permissions: In our example, we are sticking to Delegated permissions, which allow the application to take actions on behalf of the signed-in user and provides access to the user's data. However, some use cases require use of Application permissions, which allow the application to take actions without a signed-in user and potentially access data across the system and different users.

Admin Consent Required: Some permissions cannot be delegated or granted without the approval of an administrator. This restriction permits the organization to maintain oversight of the important resources that might be accessed by the application and to prevent unauthorized uses. The Microsoft Azure Portal provides an easy way for you to submit a request to an admin, so you can get the permissions that you need. However, I recommend that you follow up (or better yet, precede this) with a formal request to your IT support staff to state what you need and your business case. In my experience, this helps to expedite the process. A good working relationship with IT is important for any SAS user!

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

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

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 Microsoft 365. 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:

App needs permission to access resources

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

Step 3: Obtain an access token

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.

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.

Example from a Graph Explorer session

Initializing and refreshing the access token in a new session

Now that we have the access and refresh tokens, we can get down to business with some actual 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.
WATCH | ACCESSING MICROSOFT 365 FROM SAS (SAS Innovate 2025)
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