Using SAS® With Microsoft 365: A Programming Approach - Q&A, Slides, and On-Demand Recording

2 Likes

Join SAS expert Chris Hemedinger as he shows you how to use SAS programs to reach into your Microsoft OneDrive and SharePoint cloud to read and update your files using the REST APIs in Microsoft Office 365 and the HTTP procedure in SAS.

Updated with slides and Q&A from Aug 1, 2024 edition!

Watch the webinar

You will learn:

How to register a new application in your Microsoft Office 365 account.
Authentication and access using OAuth2.
How to use SAS to explore your document folders in OneDrive and SharePoint and import into SAS data sets.
How to use SAS to create new documents in OneDrive and SharePoint.
How to send rich messages to your teammates in Microsoft Teams.

Additional resources

Helpful links from Microsoft

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)

Complete demonstration video

(view in My Videos)

Q & A

How do you get the access token?

After registering your app, you need to visit a special URL via your browser to authenticated and obtain an auth code. Our SAS program will help you build that URL to use. Once you have that, you can run another step in our supplied code to get an access token/refresh token.

Does each user need to register the client app? Or is that done once for an organization?

You could do it once and have everybody agree on what that app needs to do in terms of its permissions. The app could be registered once and multiple people can use the same app in the Azure portal, but everybody needs to get their own access token because these are delegated permissions. For example, if Thiago and I both want to use this from our own environment, I have to get my access token so that it's acting as Chris and Thiago has to get his access token, so it's acting as Thiago -- to get the files he can get access to. We can converge on the same app definition, app ID, tenant ID. That should be fine.

Normally tokens expire every 12 hours. How can we automate the program?

The token data includes an access token and a refresh token. The code we provide will process the refresh token to obtain a new access token as needed. This step can run in SAS Studio, SAS Enterprise Guide, Base SAS in batch...on Windows or Linux, wherever your SAS is (on premise or in cloud).

How often does the token need to be refreshed?

You don't need to refresh the token on a regular basis -- the code we provide will use the refresh token data to obtain a new token each time you run the process. As long as the original authorization is still in place, the refresh token is all you need to get started.

Do you need to be working in Azure or can this be done with AWS?

Microsoft 365, that's Azure. It's a Microsoft product. -- Azure is Microsoft in the cloud. All of that is hosted in Azure. All the steps that I talked about in terms of setting up your client application, getting your authorization access token, all that is going to be just as I described it. If your SAS is running in AWS, it's fine. You can have SAS running in AWS and it can call these APIs which happen to be running in Azure. It's two competing clouds, sure, but yes, they can work together using the APIs. As long as your AWS instance of SAS can talk to the Internet, can get out to and call APIs, then you'll be able to reach and use this method as well.

Do these steps that you described replace the former SAS Add-in for Microsoft Office?

No, not exactly. The SAS Add-In for Microsoft Office sits inside your Office app (ex: Excel) and pulls/publishes content to your SAS environment. This process is the inverse: using SAS to push/pull content to and from Microsoft 365. If using SAS Viya and Microsoft 365, see the latest information about SAS for Microsoft 365 and its growing list of capabilities.

No clue how to get IT help within my organization to get permission to register an application. I searched for a form and could not find one. Is there someone you know of that can help me with this?

This may require partnership with your IT organization. Registering an app and securing the permissions to use an API to access your Microsoft 365 tenant is a privilege that isn't always granted by default. However, it is a routine task and ideally an IT org is prepared to consider and process these requests with a business justification.

Can this be accomplished using SAS EG 8.2 (or SAS Studio 3.8 or SAS Viya or batch SAS)?

Yes -- the code tasks rely only on Base SAS procedures and functions. The code supplied is verified for SAS 9.4 Maint 5 and later (as well as SAS Viya 3.5 and later).

When you create a file, can you edit the file metadata? Is it possible to pull the read access URL to that file (for sending to a person through email)?

Any task that is allowed through the Microsoft Graph API is possible here. In this case you might be looking at the createLink API (for OneDrive in this case) that allows you to create a link to share with others.

Have you explored updating a file owned by another OneDrive Account? In other words, can you update an Excel spreadsheet on someone else's OneDrive account?

Yes, as long as your account (the one used to get authorization/access token) has the permissions to read/write the file you're trying to update.

Are there special considerations for using application IDs in this scenario (for running scheduled jobs)?

Yes. If using a service account, you need that account to have a license for Microsoft 365. If the account needs broad permissions to access resources owned by multiple groups, you may need to get Application permissions for your app and additional admin consent is needed. Most likely you'll need to partner with your IT organization for this effort.

Do you find that once you have this all set up it works pretty reliably? Do you have to worry about software updates from Microsoft breaking things?

The Microsoft Graph APIs are very reliable and should not change. New APIs may be added or versioned, but you can rely on production APIs to remain stable. The only hiccup that I've seen interrupt a production process is when an authorization token is revoked. When this happens you need to reauthorize and obtain new tokens. Events such as changing a password can trigger this. For more reliable/stable authorization, you may need to use a different authorization mechanism such as a system account or certificates. See this Microsoft topic for using the APIs without a user.

Would this work on SAS running on a Linux server?

Yes, works in Windows or Linux, on-premise or in cloud -- exactly the same.

Can you share your SAS programs, which were discussed during this webinar?

Yes, you can find the instructions and code in this blog. Direct link to the code on GitHub.

Is there a similar method that can be used to integrate SAS with other cloud services? My organization has settled on Box for its remote file storage and file sharing.

Yes, Box.com also offers similar APIs for their file sharing services. The OAuth2 mechanism will be similar, and you may find some examples here in the community. If not, just post a question the SAS Programming discussion board.

I map my drives to access SharePoint or OneDrive directly. How does using PROC HTTP compare to that option?

That can work when you're using SAS on your Windows machine (where you can map the drives and synchronize content). If SAS is running in a different environment or you want to scale the process beyond just your machine, you'll need to adopt the API and PROC HTTP approach.

Permissions are different for each SharePoint "site" within our overall scheme… can I do one setup for all, or do I need to do one for each individual "site"? And again when passwords change?

As long as the user that you've authorized (and obtained a token for) can access the files, it should work. A password-change can trigger the need to reauthorize (and get a new token).

Can SAS receive the response from Microsoft Teams Adaptive card?

With Teams you can build a message that includes buttons to take an action, like this:

It won't got to your SAS session that's running the code to send the message. You can use SAS APIs to build other endpoint services to do fancy SAS work like score data or take another action -- but that's a different topic.

There are two of us in the office who use SAS - do both of us have to have separate credentials? (authorization code, tokens, etc.)

When you obtain your access token as a user, all API actions will be as you, so it's not a good idea to share. If you need non-user-specific access, consider setting up "without a user" instead.

Any comments/tips on going from on-prem SAS to cloud Office 365?

The techniques in this webinar/article work the same in on-prem SAS, cloud SAS, or batch SAS. The biggest challenge/change many customers face now is the fact that their MS Office files and SharePoint are no longer on-prem, and so cannot be referenced with traditional network paths. The API approach helps you to connect SAS to the cloud-based Microsoft 365.

Can APIs make connections in both ways, from SAS to Microsoft 365 (such as Excel, Teams) or from Microsoft 365 to SAS?

Yes, of course. This topic covers calling Microsoft 365 from SAS. You can also build REST API applications in SAS to be used in other environments. Learn more at developer.sas.com.

Do we use PROC HTTP to access data in Box as well?

You could do that. Very similar mechanism for doing box. I haven't looked recently. I have looked in the past with box.com. So box.com is sort of an enterprise version of Dropbox, which many people are familiar with, you can just Dropbox in the same way too. All these things, you just look at the at the Box APIs documentation. See where it talks about authentication. It's probably OAuth. None of the macros I made will help you at all because they are all specific to Microsoft 365 and the Graph API. But yes, you can use PROC HTTP to accomplish all of this with Box.

Can we use a DOS command within a RSUBMIT to upload to a SharePoint?

There's a couple of moving parts here. RSUBMIT is usually what we would use when we're connecting to a SAS session from another SAS session, using SAS/Connect as a mechanism. DOS command is if we're running that whole thing as a batch. I think that could work. As long as this SAS connect session that you're connecting to can run the PROC HTTP and you've set up all the information in terms of what's needed for connecting and the access token and all that. It can work.

Can you use an API to connect to a REDCap database?

Yes, you can use PROC HTTP for this. There are several topics on the community for this, like this one.

I have set up a basic program using your paper. I can get my access token and refresh it via the program. We have SAS on a Unix server. Is there a way I can have my code launch every morning at a certain time to pull down some data that I can use to build out reports? I've scheduled things in CRON, but I'm not sure if that will work for this. Is there a paper that will walk me through the steps for full "hands-free" scheduled automation?

Using cron is a great way to do this, and I do this routinely for lots of different API processes. As long as your access token for your app remains valid, you should be able to run the SAS code (unattended) to get a refresh token and complete the operations (download a file, for example).

Could you please clarify- others can still run programs reading from SharePoint site even though credential files are hidden on your personal drive where they do not have direct read access?

No, other people cannot use your credentials (or they should not!), since then those actions would be attributed to your account, not theirs. They need to get their own access token -- even if using the same app definition.

If I have a file to send to SharePoint from a batch SAS program, I need to store it 4 folders down from the "General" folder. Will the macros you provide loop thru folder structure until it finds the proper folder to store the file in?

Use the code/macros I provided to find/list the folders at each level until you find the one you need, then note/save that folder ID for use in the API call you might need to use every time you need to add/update a file there.

Does JMP have similar tools/access features for Teams/SharePoint through Microsoft 365?

JMP has the ability to use APIs with HTTP (in JSL and maybe elsewhere), but I'm not aware of special integration with Teams/SharePoint.

When creating a new account in Azure, does one need to pay or is it free?

Accounts are free, but services are not. Using these APIs with your Azure tenant is free, but the services of storing data, hosting Teams and SharePoint -- general Microsoft 365 -- that can have a cost.

How can we extend the expiry timeframe of the token?

I don't think you can extend the expiry timeframe for the token. The way it's meant to work is when you get the access access token, you can use it immediately for the AP is and you also get what's called a refresh token which does not expire and you can use at a later time. The way this works, for example, if I today run this, run a process that reads, it will read that refresh token. I use the API to get a new token that I can use immediately to call the AP is it will also return me a refresh token. The refresh token is what then I would use say tomorrow or the next time to again get the token. So there's not a way for you as an end user to adjust the expiry time. I'm pretty sure of that. I'm not even sure that something that your administrator can control it. I don't know if that's a tenant control thing or not. It's just the way that Microsoft 365 works. You don't want a token that just sits out there forever that because the longer the longer it's alive, the more risk there is that somebody could use it for purposes they shouldn't. So it's intentionally short-term and for a process such as this, most operations that you would be doing in a SAS session, if you're running them all and sequentially, it should happen very quickly and you shouldn't need the token to be alive for very long all.

The client "application" and authorization code - how do users find out if our organization is "registered"? Can this be blocked by organizations?

You need this application within your Azure portal to be registered as a an endpoint, a thing that you're connecting to that you might not be able to register yourself. So it could be that your organization doesn't allow you to do that and you'll have to ask for someone to do that for you or ask for permission to do that. Or it could be that you're allowed to register it, but you're not allowed to actually activate it to do anything, in which case you need to get admin consent again provided by your IT organization.

If your question is how do I find out whether I've already got one? Well, if you don't know of one with your colleagues who you're working with, who are might also be using SAS for this, if you have access to the Azure portal, you can search the applications. If you know what they might be called by name, you can search the whole list of applications and maybe find it that way. But it's a good idea to be intentional, deliberate about, OK, what's the thing I'm connecting to? What permissions does it have? And again, those delegated permissions are key to be able to get the thing to behave to basically have permission to do the things that you have permission to do.

If you think your application needs more permission to that, like I need to be able to do things that I don't normally have permission to do, that’s more of an application or admin only thing. Like I needed to be able to send e-mail on my boss's behalf. That’s going to get more scrutiny from the people who are providing this for you. I've worked with a lot of customers who have tried to get or have successfully gotten this working in their organization. And if you stay within the boundaries of I just need this to have the delegated permissions to do the thing I needed to do, there's a clear business need to have this capability. It's usually not been a problem.

Is this also available on the Microsoft GCC_High cloud?

There are different Microsoft clouds for different kinds of customers. For example, government customers may be using a different tenant, a different cloud hosting mechanism, or different countries may have different regional things that are restricted, which would adjust some of the URLs that you're using. And in my macro library that I'm sharing out, those things are all replaceable. So if your URLs are different than what you saw me use here, then it's pretty easy for you to swap those out, so the answer is generally yes. Everything I showed here should be available in whatever cloud you're using. The Microsoft 365 API's are used by a lot of the Microsoft applications themselves too. So they should be generally accessible, just some of the URLs may be a little bit different.

When setting up an APP, is there a CLIENT SECRET associate with the APP? If there is, does it need to be refreshed every once?

When you're setting up an app and you have a client ID, no, you don't have a client secret, at least not in the mechanism that I show. Instead, you have this Oauth 2 mechanism that I showed where you are signing in as you getting that access code. That's basically the secret for you that you then use to then turn into to get access tokens that you can use from then on. So it's not an app ID and client secret or client ID and client secret mechanism.

With SAS 9.4, are any modules needed to access the Microsoft 365 OneDrive or SharePoint?

No additional products or modules needed other than just Base SAS. Everything I showed you here is just using base proc HTTP as part of Base and I didn't veer from any of that, so no.

We currently use SAS Studio. Does this work in Studio or Viya only?

It works in SAS Studio, it works in Viya, it works in SAS 9. It works in any SAS environment where you're running code. I've tested it even in SAS On Demand for Academics; it can work there too, although you have to be a little bit careful with your credentials in, in any kind of public environment, obviously so, but yes, it can. It is platform agnostic. So wherever you're running a modern SAS environment that is SAS 9.4 or any version of SAS Viya, it should work fine.

Could you please show a demo of a different output type (maybe pdf) of the report you created and exported to the xlxs workbook you previewed?

I'm not going to show that now I don't really have time, but it works exactly the same way if I created the PDF instead of in my sample here, if I created used ODS PDF instead of ODS Excel that generated the PDF file and then I could upload that into SharePoint in the exact same way. I'm just uploading a file, any file that I want to upload to SharePoint would work. So, the mechanism is exactly the same. Now, if I wanted to download a PDF file and do something with that in SAS, I'd have the, you know, complication of how do I read a PDF file in SAS, which is its own sort of problem set. But for data type things like an Excel file which is very easy to read in SAS, that's a very common use case.

We frequently use ODS Excel. Is there a method for updating a existing worksheet?

No, you can't. You can't update an existing worksheet with ODS Excel. PROC EXPORT is a mechanism that allows you to update an existing workbook, but PROC EXPORT only supports basically exporting SAS data as a new sheet or a replacement sheet. So for example, you could have an ODS Excel that you create and then on one worksheet within your workbook and then you could proc export data onto a different worksheet within that same workbook so you can update it that way. But ODS Excel itself always creates a new Excel file. It cannot update an existing Excel file.

If we needed assistance explaining to IT and answering their security questions, would we be able to get some help from SAS Tech Support?

Well, this is time for you to bake a tray of muffins for your IT staff and get on their good side. I think from what I have seen in organizations, it's pretty clear to demonstrate a business need for this doing this kind of thing because it's a capability that we've traditionally had. Now it's more complicated because everything's in the cloud, but we still have that business need to be able to read and write content in our Microsoft Office ecosystem. SAS tech support does answer a lot of questions about this mechanism. They do often get questions on this and they have a lot of information for helping in terms of like how do you talk to your IT like, well, how do you have that conversation? I do have some guidance in the blog post that I've shared.

What component should we need to access Microsoft 365 from SAS 9.4 M8 (Azure)?

Nothing additional or special. It's just base SAS, no additional components needed. If your SAS session is running in Azure, like in a hosted machine in Azure, I mean, that's fine. That doesn't necessarily get you any benefit. Even though Microsoft 365 is in Azure and if your SAS is in Azure, it's still 2 cloud applications that have to talk to each other with APIs.

The API name is Microsoft Graph API, can we use it to create Microsoft graph? For example, to create Excel graphs using data from SAS.

It's called the "graph API" because it's the style of API -- helps you to navigate a network graph of interconnected information. The API does have some special features for working with Excel content directly -- check out that part of the API here.

Can I put back an alternative authentication mechanism you could include in your macro library? We have specific needs for authentication that may be helpful to others.

Yes! You can send me your ideas/code, or make your own modifications in a forked version of my GitHub project, and send a pull request!

If there's a chunky file to be uploaded, could it be possible to pass the refresh token time (1.5 hours in your demo)?

If your file takes that long to upload...that is one huge file! Your tenant admin might have something to say about the size/types of files you're storing. In my experience even very large files can upload in a few minutes.

Can you update an existing Excel file rather than replace?

The API that I showed does have some special features for working with Excel content directly -- check out that part of the API here. This might allow you to modify/amend/enhance your existing Workbooks.

Can we also connect Outlook to SAS using the approach demonstrated in this webinar?

The Microsoft Graph API has methods for email as well. You would need to assign additional permissions to the app that you register.

How can I get SAS codes Chris shows here?

Here it is! https://github.com/sascommunities/sas-microsoft-graph-api

Can you share your Excel file in SharePoint using SAS code?

You can initiate permissions and sharing links using the Graph API.

Is there a reference or tutorial I can look up for exporting SharePoint lists to Excel files from SAS?

Lists are objects that are accessible via the Graph API. I don't see an "export to Excel" operation, but you could get the ListItem objects from a list to pull into a data set.

Is there anywhere (like a seminar or slides) that explains how we would be able to connect Excel to SAS Viya, for example connecting a pivot table to a SAS table produced on SAS Viya?

I think you want to look at SAS For Microsoft 365, which is part of SAS Viya.

You mentioned a SAS Notebook released recently. Can you describe that?

SAS Notebook is a file format that works in the SAS extension for Visual Studio Code. Learn more from this article. Here's a screenshot of what it looks like from my demo:

Is this different than how to connect to data in Dynamics 365 (Microsoft's CRM application)? I believe it is, just want to confirm.

Yes, it's different. Dynamics has its own API. Also, there may be special data connectors to support the import/export of information from the CRM.

You mentioned it briefly at the beginning, would you please be able to tell us more about getting SAS data into Excel - for example SAS datasets connecting to pivot tables in excel directly from the SAS Viya cloud platform, is this possible?

Check out SAS For Microsoft 365, an offering that is included with SAS Visual Analytics in SAS Viya.

What permissions should a SAS user have on a private SharePoint site, to access that site's document library?

As long as you, the authenticated user, have access to the SharePoint content, then these APIs will work to access that content. The APIs are working with delegated permissions -- they share your same access rights.

Has there been any developments with retrieving client secrets (needed for proc http) from Hashicorp?

Not yet. On my list to develop some examples of retrieving credentials from "secrets server" type platforms.

Want more tips? Be sure to subscribe to the Ask the Expert board to receive follow up Q&A, slides and recordings from other SAS Ask the Expert webinars.