BookmarkSubscribeRSS Feed

How to use SAS to access Microsoft 365

Started ‎07-08-2020 by
Modified ‎10-02-2022 by
Views 25,107

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

Learn more

 

Comments

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! 

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

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

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

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;

@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;

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

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.

Version history
Last update:
‎10-02-2022 01:54 PM
Updated by:
Contributors

SAS Innovate 2025: Call for Content

Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!

Submit your idea!

Free course: Data Literacy Essentials

Data Literacy is for all, even absolute beginners. Jump on board with this free e-learning  and boost your career prospects.

Get Started

Article Tags