How to use SAS to access Microsoft 365

[#1]

Many of us are now using cloud-based productivity tools like Microsoft Office 365, hosted in Microsoft Azure.  And some of us are using SAS software in the cloud, too. 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 video, I show you how to use SAS programs to reach into your Microsoft OneDrive, Microsoft Teams and SharePoint cloud to read and update your files.  The approach relies on the REST APIs in Microsoft Office 365 and on the HTTP procedure in SAS.

 

Once you have the basic connection working, you can use the Microsoft Graph API to work with other objects too, such as SharePoint lists.

Helpful links from the video

• Microsoft Azure Portal (to register your client app)
• Microsoft Graph Explorer application (to test your REST API calls)
• Microsoft Graph API documentation
• Microsoft Graph API Permissions reference (breakdown of Delegated and Admin Consent)

Learn more

• Using SAS with Microsoft OneDrive and SharePoint Online (blog post)
• SAS and Microsoft Office 365: A Programming Approach to Integration (PDF, SASGF paper)
• GitHub project with ready-to-use macros for listing SharePoint/OneDrive content, uploading files, do...
• GitHub folder with example Microsoft 365/OneDrive macros (code)

[MattClarke]

Chris, just following up after our email exchange to also let the community know how valuable these techniques have been for us! Using your tutorials and your code, along with input from Joseph Henry, we are successfully integrating our SAS 9.4 Grid with our Sharepoint Online O365 environment. Thanks so much for sharing with all of us! 

[Anant1]

Thanks Chris for this valuable post !! This made my job easy.

[gkokanour]

Chris,

   I am having trouble getting our logic to work for Sharepoint Lists, we have it working to get a file and save to sharepoint a file (some issues with 200 record limit, and large files).  But also we have a requirement to read and write to a sharepoint list, do you have any sas code examples for this 

Greg Kokanour

SCE

[ChrisHemedinger]

@gkokanour I don't have code samples for these scenarios. The 200 record limit (typical for APIs like this) usually requires you to process the JSON response and read the remaining "count" or get a URL endpoint that will fetch the next batch in the list...until the list is exhausted.

[gkokanour]

When you say process the JSON response, this the code that I am running that hits the 200 record limit, so do I just do it a 2nd time both ?
filename resp TEMP;
proc http url="https://graph.microsoft.com/v1.0/me/drives/&driveId./items/root/children"
oauth_bearer="&access_token"
out = resp;
run;
libname jresp json fileref=resp;
/* Create a data set with the top-level paths/files in the drive */
data paths;
set jresp.value;
run;

[gkokanour]

@ChrisHemedinger   I am having trouble trying to get the folder id for the record past the initial 200 limit, how would I go about processing it again (sas code logic) to get the next 200 records?

When you say process the JSON response, this the code that I am running that hits the 200 record limit, so do I just do it a 2nd time both ?
filename resp TEMP;
proc http url="https://graph.microsoft.com/v1.0/me/drives/&driveId./items/root/children"
oauth_bearer="&access_token"
out = resp;
run;
libname jresp json fileref=resp;
/* Create a data set with the top-level paths/files in the drive */
data paths;
set jresp.value;
run;