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.

In this second video, I demonstrate using just these helper macros to get started quickly with the most common tasks: list folders and files, download files from SharePoint into SAS for processing, and upload files from SAS to SharePoint. (In this demo I use SAS Viya Workbench, but the approach works from any SAS 9.4 or SAS Viya environment.)

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;

[mprikolota]

Hi @ChrisHemedinger , many thanks for the video and the macros - very usefull!

I was able to successfully download a file using the macro %downloadFile, but I'm getting some strange warnings about not resolved references which appear to be coming from PROC HTTP. Do you have any idea what might be wrong? Below you can see part of the log.

Many thanks,

Maksym

NOTE: Deleting WORK.__TMPLST (memtype=DATA).
NOTE: PROCEDURE DELETE used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

WARNING: Apparent symbolic reference TRANSLATE not resolved.
WARNING: Apparent symbolic reference TRANSLATE not resolved.
WARNING: Apparent symbolic reference TEMPAUTH not resolved.
WARNING: Apparent symbolic reference APIVERSION not resolved.
WARNING: Apparent symbolic reference TRANSLATE not resolved.
WARNING: Apparent symbolic reference TRANSLATE not resolved.
WARNING: Apparent symbolic reference TEMPAUTH not resolved.
WARNING: Apparent symbolic reference APIVERSION not resolved.
NOTE: 200 OK
NOTE: PROCEDURE HTTP used (Total process time):
real time 0.28 seconds
cpu time 0.05 seconds

[ChrisHemedinger]

Hi @mprikolota - thanks for the feedback. The way the Download macro works is that it grabs "download URL" from the Microsoft 365 data properties about the file, and then simply GETs that URL using PROC HTTP. The URL (provided by the system) has a number of URL parameters that are set off with & characters, and the ampersand character has a special meaning in SAS for macro variables.

So the warning is innocuous in this case -- it's just SAS trying to resolve these "macro variables". I did play with trying to escape or use %nrstr() to prevent SAS from processing these, but didn't get it working the way it should. It does not affect how it works but it does clutter the log.