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
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.
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.
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 OneDrive, the exact same authentication flow and steps can be used for all of the Microsoft 365 APIs. Have fun with 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.
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.
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.
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.)
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 my 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.
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.
Possibly 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.)
Creating a configuration file
There are a few app-specific values that we'll need to reference throughout the SAS programs we're writing. I decided to create a configuration file for these settings rather than hard-code them into my SAS statements. This will make it easier for other people to reuse my code in their own applications.
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"
}
By "externalizing" the IDs specific to my account/instance, I can use SAS code to read the values at run time. Note: This code, like all of the code in this article, uses features from SAS 9.4 Maintenance 5.
/* Set the variables that will be needed through the code We'll need these for authorization and also for runtime use of the service. Reading these from a config.json file so that the values are easy to adapt for different users or projects. */ %if %symexist(config_root) %then %do; filename config "&config_root./config.json"; libname config json fileref=config; data _null_; set config.root; call symputx('tenant_id',tenant_id,'G'); call symputx('client_id',client_id,'G'); call symputx('redirect_uri',redirect_uri,'G'); call symputx('resource',resource,'G'); run; %end; %else %do; %put ERROR: You must define the CONFIG_ROOT macro variable.; %end; |
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 OneDrive. 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.
/* location of my config file */ %let config_root=/folders/myfolders/onedrive; %include "&config_root./onedrive_config.sas"; /* Run this line to build the authorization URL */ %let authorize_url=https://login.microsoftonline.com/&tenant_id./oauth2/authorize?client_id=&client_id.%nrstr(&response_type)=code%nrstr(&redirect_uri)=&redirect_uri.%nrstr(&resource)=&resource.; options nosource; %put Paste this URL into your web browser:; %put -- START -------; %put &authorize_url; %put ---END ---------; options source; |
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:
Then you probably need to work with your IT support to grant consent for your app. See the section "Possibly required: Obtaining admin consent" above.
Step 3: Obtain an access token
My colleague Joseph wrote a few convenient utility macros that can help manage the access token and refresh token within your SAS session. These macros include:
- %get_token - get the initial access and refresh tokens, given an authorization code. Remember, an access token will expire in about 60 minutes. But the refresh token can be used to get a renewed access token.
- %refresh - exchange a valid refresh token for a new access token
- %process_token_file - read/update an external token file so that these values persist beyond your current SAS session.
I'm not going to walk through the macro code in this article, but the SAS programs are straightforward and well-documented. See "How to get this example code" at the end of this article.
With these macros in place, we can paste the (very long) authorization code we retrieved in the previous step into a macro variable. Then we can run the %get_token macro to generate the tokens and store them in a local file.
%let config_root=/folders/myfolders/onedrive; %include "&config_root./onedrive_config.sas"; %include "&config_root./onedrive_macros.sas"; filename token "&config_root./token.json"; %let auth_code=AQABAAIAAAC5una0EUFgTIF8ElaxtWjTqwohjyfG; * and much more; /* Now that we have an authorization code we can get the access token This step will write the tokens.json file that we can use in our production programs. */ %get_token(&client_id.,&auth_code,&resource.,token,tenant=&tenant_id); |
Running this step will create a new file, token.json, in your designated config folder. Here's an screenshot of what my version looks like right now:
It's very important that you keep this file secure. With the information in this file (your refresh token) and your conf.json file (with your client ID and tenant ID), anyone can use these code techniques to impersonate you and access your Microsoft 365 data. There are techniques for storing these files such that only you can see them.
Using Microsoft 365 APIs to access OneDrive 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.
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 OneDrive interactions. Here's how to initialize your SAS session with the tokens.
%let config_root=/folders/myfolders/onedrive; %include "&config_root./onedrive_config.sas"; %include "&config_root./onedrive_macros.sas"; /* Our json file that contains the oauth token information */ filename token "&config_root./token.json"; %process_token_file(token); /* If this is first use for the session, we'll likely need to refresh */ /* the token. This will also call process_token_file again and update */ /* our token.json file. */ %refresh(&client_id.,&refresh_token.,&resource.,token,tenant=&tenant_id.); /* At this point we have a valid access token and we can start using the API. */ |
If all goes well, we'll have our access token, and it will be stored in a macro variable named &access_token. 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?)
Retrieving the top-level drive identifier (OneDrive)
We'll need to explore the OneDrive system from the top-down, using code. First, we need the identifier for the root drive. It's possible for you to have multiple root drives, and if that's the case for you, you'll need to modify this code a bit. This code queries the service for your drives, and stores the identifier for just the first drive in a macro variable. We'll need that identifier later to retrieve a list of top-level items.
/* First we need the ID of the "drive" we are going to use. to list the drives the current user has access to you can do this */ filename resp TEMP; /* Note: oauth_bearer option added in 9.4M5 */ proc http url="https://graph.microsoft.com/v1.0/me/drives/" oauth_bearer="&access_token" out = resp; run; libname jresp json fileref=resp; /* I only have access to 1 drive, but if you have multiple you can filter the set with a where clause on the name value. This creates a data set with the one record for the drive. */ data drive; set jresp.value; run; /* store the ID value for the drive in a macro variable */ proc sql noprint; select id into: driveId from drive; quit; |
Note that this code uses the new OAUTH_BEARER option in PROC HTTP -- a convenient addition when working with OAuth2-compliant APIs. This is shorthand -- and more intuitive syntax -- for placing "Authorization: Bearer TOKEN-VALUE" in the HTTP headers.
Retrieving the top-level drive identifier (SharePoint Online)
The steps for SharePoint Online are nearly the same as for OneDrive, except that we need to reference the site hostname (yoursite.sharepoint.com, for example) and the /sites resource (instead of the /me/drives resource).
/* Note: oauth_bearer option added in 9.4M5 */ /* Using the /sites methods in the Microsoft Graph API */ /* May require the Sites.ReadWrite.All permission for your app */ /* Set these values per your SharePoint Online site. Ex: https://yourcompany.sharepoint.com/sites/YourSite breaks down to: yourcompany.sharepoint.com -> hostname /sites/YourSite -> sitepath This example uses the /drive method to access the files on the Sharepoint site -- works just like OneDrive. API also supports a /lists method for SharePoint lists. Use the Graph Explorer app to find the correct APIs for your purpose. https://developer.microsoft.com/en-us/graph/graph-explorer */ %let hostname = yourcompany.sharepoint.com; %let sitepath = /sites/YourSite; proc http url="https://graph.microsoft.com/v1.0/sites/&hostname.:&sitepath.:/drive" oauth_bearer="&access_token" out = resp; run; libname jresp json fileref=resp; /* This creates a data set with the one record for the drive. Need this object to get the Drive ID */ data drive; set jresp.root; run; /* store the ID value for the drive in a macro variable */ proc sql noprint; select id into: driveId from drive; quit; |
Retrieve a list of top-level folders/files
With the drive identifier in hand (whether OneDrive or SharePoint), I can use the /children verb on the Microsoft Graph API to get a list of all of the top-level objects in that drive. These represent the folders and files that are at the root.
/* To list the items in the drive, use the /children verb with the drive ID */ 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; |
Here's what I'm keeping in my OneDrive right now. It's not too disorganized, is it?
List the files in a particular folder
If I'm interested in exploring a particular folder, I'll need to find the folder identifier as it's known to OneDrive. Using PROC SQL and SELECT INTO, I can find the folder by its name and store its ID in another macro variable. Then, I use the /children verb again, but this time with the folder ID instead of the "root" constant.
/* At this point, if you want to act on any of the items, you just replace "root" with the ID of the item. So to list the items in the "SASGF" folder I have: - find the ID for that folder - list the items within by using the "/children" verb */ /* Find the ID of the folder I want */ proc sql noprint; select id into: folderId from paths where name="SASGF"; quit; filename resp TEMP; proc http url="https://graph.microsoft.com/v1.0/me/drives/&driveId./items/&folderId./children" oauth_bearer="&access_token" out = resp; run; /* This creates a data set of the items in that folder, which might include other folders. */ libname jresp json fileref=resp; data folderItems; set jresp.value; run; |
Here are the items from my SASGF folder. Can you tell that I don't throw anything away?
Download a file from OneDrive and import into SAS
I know that I keep a spreadsheet named "sas_tech_talks_18.xlsx" in this SASGF folder. With the /content verb, I can download the file from OneDrive and store it in the file system that is local to my SAS session. Then, I can use PROC IMPORT to read it into a SAS data set.
/* With a list of the items in this folder, we can download any item of interest by using the /content verb */ /* Find the item with a certain name */ proc sql noprint; select id into: fileId from folderItems where name="sas_tech_talks_18.xlsx"; quit; filename fileout "&config_root./sas_tech_talks_18.xlsx"; proc http url="https://graph.microsoft.com/v1.0/me/drives/&driveId./items/&fileId./content" oauth_bearer="&access_token" out = fileout; run; /* Import the first sheet into a SAS data set */ proc import file=fileout out=sasgf dbms=xlsx replace; run; |
Boom! I've just downloaded my data from the cloud and brought it into my SAS session.
Add a new file to OneDrive
We can build wonderful documents from SAS too, and it's important to be able to share those. By using the PUT method with the /content verb, we can copy a file from the local SAS session into a target folder on OneDrive. Most often, this will probably be an Excel spreadsheet or maybe a PDF report. (But hey, maybe it's a good opportunity to try out the new ODS WORD destination in SAS 9.4 Maint 6?)
/* We can upload a new file to that same folder with the PUT method and /content verb Notice the : after the folderId and the target filename */ /* Create a simple Excel file to upload */ %let targetFile=iris.xlsx; filename tosave "%sysfunc(getoption(WORK))/&targetFile."; ods excel(id=upload) file=tosave; proc print data=sashelp.iris; run; ods excel(id=upload) close; filename details temp; proc http url="https://graph.microsoft.com/v1.0/me/drives/&driveId./items/&folderId.:/&targetFile.:/content" method="PUT" in=tosave out=details oauth_bearer="&access_token"; run; /* This returns a json response that describes the item uploaded. This step pulls out the main file attributes from that response. */ libname attrs json fileref=details; data newfileDetails (keep=filename createdDate modifiedDate filesize); length filename $ 100 createdDate 8 modifiedDate 8 filesize 8; set attrs.root; filename = name; modifiedDate = input(lastModifiedDateTime,anydtdtm.); createdDate = input(createdDateTime,anydtdtm.); format createdDate datetime20. modifiedDate datetime20.; filesize = size; run; |
Replace/update a file in OneDrive
If you want to replace an existing file, then you'll want to perform the additional step of retrieving the unique ID for that file from OneDrive. When you PUT the new version of the file into place, its history and sharing properties should remain intact. Here is my code for navigating the folder/file structure in my OneDrive and finally replacing an existing file.
/* If you want to replace a file instead of making a new file then you need to upload it with the existing file ID. If you don't replace it with the existing ID, some sharing properties and history could be lost. */ /* Create a simple Excel file to upload */ %let targetFile=iris.xlsx; filename tosave "%sysfunc(getoption(WORK))/&targetFile."; ods excel(id=upload) file=tosave; proc print data=sashelp.iris; run; ods excel(id=upload) close; /* Navigate the folder and file IDs from my OneDrive */ proc sql noprint; select id into: folderId from paths where name="SASGF"; quit; proc http url="https://graph.microsoft.com/v1.0/me/drives/&driveId./items/&folderId./children" oauth_bearer="&access_token" out = resp; run; libname jresp json fileref=resp; data folderItems; set jresp.value; run; /* Find the ID of the existing file */ proc sql noprint; select id into: fileId from folderItems where name="iris.xlsx"; quit; libname attrs json fileref=details; proc http url="https://graph.microsoft.com/v1.0/me/drives/&driveId./items/&fileId./content" method="PUT" in=tosave out=details oauth_bearer="&access_token"; run; |
As you can see from my OneDrive history for this file, I've tested this program a few times -- resulting in 23 revisions of this file in its history!
How to get this example code
You can find the source files for these examples on GitHub. If you're really just interested in a "ready to use" set of macros, try this standalone repository for using SAS with Microsoft 365.
I've organized this code into 5 different files in order to make it easy to reuse:
- onedrive_config.sas - read the fields from the conf.json and set them as global macro variables. This includes your client_id and tenant_id.
- onedrive_setup.sas - the SAS statements that represent code you will need to run just once to get your authorization code and first access code.
- onedrive_macros.sas - three utility macros that help you to create, refresh, and manage your access token and refresh token in your token.json file
- onedrive_example_use.sas - sample SAS steps that I used in this article. They won't quite work for you as-is, since you don't have the same files that I do. (Unless you do have the same files, in which case...creepy.) My hope is that you can read and adapt them for your own content.
- onedrive_sharepoint_example.sas - sample SAS steps for reading and writing files with SharePoint Online. The basic steps are the same as for OneDrive, except that you use the /sites resource instead of the OneDrive-specific methods.
I also included a template for the conf.json file, with obvious placeholders for the client_id and tenant_id that you'll substitute with your own values. You'll also need to change the statements that define &CONFIG_LOC -- the location of your configuration directory where you're storing these files. I developed these examples in SAS University Edition -- yes, this works there! I also ran the code from my full SAS environment via SAS Enterprise Guide.
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
- The Internet of Snacks: SnackBot data and what it reveals about SAS life
- How to test PROC HTTP and the JSON library engine
- Securing your REST API credentials in your SAS programs
116 Comments
Hello Chris,
Thank you so much for this very interesting post. I wonder if it is possible to make a short video tutorial. The step 1 was not straightforward for me. I think a video tutorial could be very helpful.
Best regards,
Mamadou
That's a good idea Mamadou. It's on my list -- it is a very long blog post with lots of steps, but they actually don't take that long if you've done it before ;)
We are still on SAS 9.3 running on Unix. Will this work with that setup?
The the techniques can work, but you'll need to retrofit some of the code. The PROC HTTP options are one small hurdle, but the biggest trick will be in dealing with the JSON responses. The JSON libname engine in SAS 9.4 makes this so easy.
Need admin approval
SAS via PROC HTTP
SAS via PROC HTTP needs permission to access resources in your organization that only an admin can grant. Please ask an admin to grant permission to this app before you can use it.
Are you trying to use *my* app named "SAS via PROC HTTP"? You won't be able to do that. You need to follow the steps to create your own app to get the client ID, etc. And in my org, it appears that anyone can create an app. But this might be a capability that Office 365 admins can restrict/control.
Yes, maybe in my organization group it needs admin permission, and also may be in your side, you have already been given admin permission.
In step "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:" , when I use IE to access the URL, it said "SAS via PROC HTTP needs permission to access resources in your organization that only an admin can grant. Please ask an admin to grant permission to this app before you can use it."
What can I do next ?
How did you get passed this hurdle Lianbo?
Is that a typo?
Step 1 "I created a file named conf.json"
but the Step1 code reads from config.json
Yes - good catch! I've fixed it.
Hi Chris,
I just wanted to say thanks for a well written and detailed blog on this. I've used this as a basis to work with a Microsoft Office 365 suite API to get a list of emails and retrieve email content.
It took a bit to get the auth token sorted, but after that it was relatively straight forward to use. I also used the json libname engine so that I could work with the response as if they were SAS tables.
Mark
Great to hear, Mark! Thanks for the feedback and validation that these steps work for someone other than me
Let’s say with a SAS Office Analytics install what if any steps are needed to then allow this interaction on enterprise level? In other words what are “setup” or configuration steps needed before the programming methods your article listed, if any? (Great article btw)
From the SAS side, all you need is Base SAS. So in SAS Office Analytics, you'll probably access the SAS programming environment from SAS Enterprise Guide, SAS Studio, or maybe even the SAS program task in SAS Add-In for Microsoft Office. No additional setup is needed.
From the "registering the app" step, you do need permission to do that within your Office 365 environment. That's a policy decision from the organization that administers the Office environment in the cloud.
Hi Chris.
Thank you for such an elaborate post. this is really helpful.
Is there a code to create an empty folder before copying over the file ?
For creating new folders with SAS, I use the DLCREATEDIR trick all of the time. If you're looking to create a new folder in OneDrive, you would use this method on the "children" element of the OneDrive API.
Hi Chris, Power BI can only work with Excel data formatted in a Table range. How can I create the excel in a table format in SAS before uploading it to One Drive ? I am trying to automate the reporting in Power BI using the data in one drive which I will upload from SAS, without having to manually changing the excel format.
thanks
This message will pop up in One drive if I try to import an excel file without formating as table:
"We couldn't find any data formatted as a table
To import from Excel into the Power BI service, you need to format the data as a table. Select all the data you want in the table and press Ctrl + T."
So I need to be able to create this excel table format in SAS code before uploading to One drive as I like to automate this refresh from SAS to onedrive in a daily schedule run.
Is this possible?
Any help would be appreciated thank you!
Thank you Chris, Thought of sharing my inputs for anyone trying this. Basically the Step of Getting Drive ID and Folder ID can be skipped
proc http method="GET" url=&_SP_Fetch_url. out = _resp;
headers "Authorization"="Bearer &access_token.";
run;
Listing of file in a OneDrive folders root directory
call symput('_SP_Fetch_url',cats("'https://graph.microsoft.com/v1.0/me/drive/root/children'"));
Listing of files in a OneDrive folder
call symput('_SP_Fetch_url',cats("'https://graph.microsoft.com/v1.0/me/drive/root:/", urlencode("&_SP_Fldr_Path.") ,":/children'"));;
Listing of file in a Sharepoint 365 folders root directory
call symput('_SP_Fetch_url',cats("'https://graph.microsoft.com/v1.0/sites/&_SP_Host_Name./drive/root/children'"));
Listing of files in a Sharepoint 365 folder
call symput('_SP_Fetch_url',cats("'https://graph.microsoft.com/v1.0/sites/&_SP_Host_Name./drive/root:/", urlencode("&_SP_Fldr_Path.") ,":/children'"));;
Where Macro Var _SP_Fldr_Path is the value begins after ?id=%2F (uptill any & sign if present exclude the & too )
For example for
https://mysp-cloud.xx.org/personal/smijo_x_simon_xx_org/_layouts/15/onedrive.aspx?id=%2Fpersonal%2Fsmijo_x_simon_xx_org%2FDocuments%2FDocuments%2FSkype%20Recordings
the Folder Path relative to root is :
personal%2Fsmijo_x_simon_xx_org%2FDocuments%2FDocuments%2FSkype%20Recordings
Uploading a file using path relative to root :
proc http url="https://graph.microsoft.com/v1.0/me/drive/root:/Documents%2FSkype%20Recordings/iris_smijo.xlsx:/content"
method="PUT"
in=tosave
out=details;
headers "Authorization"="Bearer &access_token." ;
run;
Also its best to first try out your HTTP URL on https://developer.microsoft.com/en-us/graph/graph-explorer (remember to click the "Sign In with Microsoft" on the Top Left )
Great suggestion -- I've updated the post to include and example of the explorer app.
Correction : the SP_Folder path begins from 4th %2F ( urlencoded value for / )
For a shared drive :
https://sp-cloud.xx.org/sites/SMIJOTEST/Monthly%20Files/Forms/AllItems.aspx?newTargetListUrl=%2Fsites%2FSMIJOTEST%2FMonthly%20Files&viewpath=%2Fsites%2FSMIJOTEST%2FMonthly%20Files%2FForms%2FAllItems%2Easpx&id=%2Fsites%2FSMIJOTEST%2FMonthly%20Files%2FMRN%20list
id=%2Fsites%2FSMIJOTEST%2FMonthly%20Files%2FMRN%20list%20for%20Rx%20Claims
or URL Decoded would be : /sites/SMIJOTEST/Monthly Files/MRN list/
1st value sites indicates it is a Sharepoint site ( personal would indicate its a personal OneDrive location)
2nd value : SMIJOTEST is the SP Host Name
3rd value : Monthly Files is the root Directory Name
from 4th onwards Path relative to the root directory.
Similarly for OneDrive example for
https://mysp-cloud.xx.org/personal/smijo_x_simon_xx_org/_layouts/15/onedrive.aspx?id=%2Fpersonal%2Fsmijo_x_simon_xx_org%2FDocuments%2FDocuments%2FSkype%20Recordings
the Folder Path relative to root is :
Documents%2FSkype%20Recordings
Please can you tell me if this instruction below will work for me if I am using SAS EG Version 7.1? really struggling from step 3.
Your EG version is not a factor -- but your version of SAS might be. The code I provided relies on PROC HTTP features that are present in SAS 9.4 Maint 5 and later.
Hello this looks awesome. Will it allow you to access One Drive while logged out of the computer?
I've been trying to get into a SharePoint online list while logged out and having issues, was thinking of maybe having Flow transfer to One Drive and then using these instructions you provided.
Thank you.
Yes, this process does not rely on being logged in from your local machine. It uses the API with the auth tokens to handle that.
Hi Chris,
OneDrive seems to be associated with each individual's account. Do you know if it is possible to have a shared OneDrive account for a group of people, if so, can your method be used to access the data stored in the shared OneDrive, just like the old way of using a shared network drive?
Thanks
Ya
"shared OneDrive" is often really "SharePoint Online". I recently updated these instructions to include specifics and examples for SharePoint Online. I hope that works for you.
Thanks Chris, I just tried to follow your example, and first step "register your application" is now showing this message: "Application registrations portal is no longer available to register and manage converged applications, except for in Azure AD B2C tenants. We recommend that you manage your existing applications and register new applications by using the App registrations (now Generally Available) experience in the Azure portal. ". Can you provide any update to work around this new blockage?
The proper link is now https://portal.azure.com. I've updated the link in the article.
Chris,
I have had a hard time trying to create a new folder in SharePoint using proc http. Please help. Code is
filename cf_log temp;
proc http url="https://graph.microsoft.com/v1.0/me/drive/items/&driveid/children"
oauth_bearer="&access_token"
method="POST"
verbose
in='
{
"name": "New Folder",
"folder": "New",
}'
out=cf_log
;
run;
I tried all combinations listed in https://docs.microsoft.com/en-us/graph/api/driveitem-post-children?view=graph-rest-1.0&tabs=http
but keep getting 'Bad Request' error.
Thank you for sharing your knowledge and expertise.
In the example in the doc, it shows an empty JSON body for the "folder" attribute:
{ "name": "New Folder", "folder": { }, "@microsoft.graph.conflictBehavior": "rename" }for "Step 2: Obtain an authorization code", it looks each session need re-obtain the authorization code, is it possible to get the new authorization code automatically?
With this approach (Auth code flow), I believe this is the only way to do it. However, there are other authentication flows that can be used for your app. These would probably require more approvals from your IT, and perhaps a service account to have a consistent authentication that you won't need to periodically touch/refresh.
Hi Chris, I have been looking at your instructions and video and both are very good and useful. I have a question though that I hope you can help with. In the video, you mention testing the authorization code after getting the token.json file by pasting the url with the authorization code into a browser window. What is the url? After doing this you show the results as either it worked and you click allow or we get a different message, and we need to go back to our IT people. Can you please tell me how to run this test?
I think you're referring to my Step 2, where you use SAS to build the authentication URL, then copy it to paste in a browser. Then, you will either see the prompt to grant permission to the app (Good!) or else you'll see a message that indicates you need Admin approval. If you get that second one, you need help from your IT.
No, I got past that step. In the video around 13:13 you just finished talking about getting the token file and then going to some url with the authorization code and again getting the permission window or some error. I am currious as to the url. Thanks
I'm referring to the URL we generated/copied at around 10:30 in the video -- that step 2 URL. I was just diving deeper into the permissions experience.
Ok, when I run you onedrive_example_use.sas program to get a list of items in a specific folder, I only get 200 observations in the dataset Folderitems yet I have more files in the folder. How do I get the rest of the files? What is limiting the list to only 200?
Craig, many APIs have a default limit -- which you might be able to increase with a URL parameter. Or, using paging, one call might return the first 200 results and a special token to use to get the next set. See this doc.
This new repo has a SAS macro that iterates through the complete list of files, even if it exceeds > 200 items:
https://github.com/sascommunities/sas-microsoft-graph-api
Chris, Thanks so much for this tutorial. I followed all your steps and was able to extract an authorization code, get a drive ID and a specific folder ID and upload and download data to my existing &driveid and &folderid (your code created the macro variables).
However, i tried to experiment with creating proc HTTP statements to run some additional APIs and they do not work. Specifically, i spend a frightening amount of time trying to use the 'share a link' API located here:
https://docs.microsoft.com/en-us/graph/api/driveitem-createlink?view=graph-rest-1.0&tabs=http
Despite many recodes and attempts. I can't get it to work. Any suggestions on what i am doing wrong? i keep getting a bad request error.
Here is my code that is not working
filename resp TEMP;
proc http
method="POST"
url="https://graph.microsoft.com/v1.0/me/drives/&driveId./items/&folderid./createlink"
ct="application/json"
in='{
"type": "view",
"password": "ThisIsMyPrivatePassword",
"scope": "anonymous"
}'
oauth_bearer="&access_token"
out=resp;
run;
Moshe,
I haven't tried the Share Link API. Did you check the HTTP result and the contents of the JSON (in resp) to see what the errors are?
Try this after your PROC HTTP:
data _null_; rc = jsonpp('resp','log'); run;Oh, also, the "password" option is valid only for OneDrive Personal, according to the doc.
Thanks so much Chris. I removed the password and now it worked.
Chris, Thank you much for sharing the connection to 365. I am successfully gotten the token.json, which contains the access_token and refresh token. yet while I run the proc http,
proc http url="https://graph.microsoft.com/v1.0/me/drives/"
oauth_bearer="&access_token"
out = resp;
run;
I keep on getting:
ERROR: Error connecting to 20.190.128.108:443. (The connection has timed out.)
ERROR: Unable to connect to Web server.
Do you know what I have don't wrong? I got the same error while I tried proc http to SharePoint too.
Is it possible that you have a proxy server in your organization? Check this blog for basic tests and remedies.
Thanks, Chris, it worked after I added the proxyhost and proxyport.
Pingback: Using Microsoft Excel functions in SAS - SAS Users
Great blog entry, Chris. I thought you might like to know that in the first step, creating an Azure app, the Microsoft Application Registration website no longer works. Now you have to go to this URL: https://portal.azure.com/#blade/Microsoft_AAD_RegisteredApps/ApplicationsListBlade. Once you're in, I think it looks the same as your instructions, although I didn't actually compare them. Thanks, Keith
Thanks Keith -- I've updated the links in the post. Of course, they will probably change again ;) I usually tell people to start at portal.azure.com and navigate from there.
Hi Chris,
this is an excellent article, which saved a lot of time for me.
One question though, is there a way I could read from onedrive SharePoint where I could read for any subfolders beyond the root folder? for example, I am able to export/import from sharepointRoot/Folder1 using your example, but I am trying to import/export from sharepointRoot/Folder1/Subfolder.
thanks heaps
My approach shows iterative "discovery" as you drill into each folder. The APIs I use rely on knowing the ID of each object (folder or document), so I have steps to list the folders, find the one that matches your search, and then use the ID of that folder to get to the next level. If you need to go into nested folders, you simply add another layer of those steps to get to the ID you need.
Thanks very much
Hi Chris,
I am trying to export a sas dataset from SAS viya to our sharepoint site as csv, it works fine for small files using the code sample that you have. However, for larger datasets like ~900rows (~300MB) the proc print statement takes forever and never seen it finishing. I also tried proc export first into a SAS location as csv, and then use proc HTTP. The proc HTTP runs for a minute or so, and then gives a "ERROR: Connection has been closed" error.
Can you please suggest a way to resolve this ?
PROC PRINT and ODS CSV or ODS EXCEL can take longer to generate, but it sounds like you already worked around it with PROC EXPORT. 900 rows isn't a lot, but 300MB is a large file size. Looking at some doc, it seems there is a 4MB limit for uploading, and to manage with large files you have to upload in "chunks". There is a special sequence of APIs for this, but I don't currently have an example in SAS.
This new repo contains a method that handles large files:
https://github.com/sascommunities/sas-microsoft-graph-api
Hello Chris,
Thank you so much for this very interesting post. Is it possible to attach a file in Sharepoint to a email using SAS?
Thank you.
I'm sure it's possible, but will be some work. One approach is to use SAS to download a file from SharePoint, then use FILENAME EMAIL to send as an attachment. Another possibility is to use the Microsoft Graph APIs to create a sharing link (my preferred way for sharing within an org), but that would require an additional app permission I think.
Thank you for such an informative post! This is very helpful. I am trying to download the user members of a SharePoint Online 365 security group into SAS. However, the drives folder doesn't appear to contain folders for security groups. Do you know if I can make small tweaks to the code to download members of a SPO security group? Any insight you can provide is much appreciated. Thank you!
There are two main steps:
I don't have a code sample handy, but I've used these APIs, specifically Teams and Team members.
https://graph.microsoft.com/v1.0/me/joinedTeams - gets my list of teams, then I filter the result to the team of interest
https://graph.microsoft.com/v1.0/groups/{group-id-for-teams}/members - plug in the group ID for the team – will return a batch of names, and then you also get a @odata.nextLink field that has the URL of the API call for the subsequent batch – keep calling until done.
You can can mock these up in Graph Explorer and try them out:
https://developer.microsoft.com/en-us/graph/graph-explorer
Hello Chris-
Thank you so much for putting this all together. I'm trying to pull a Sharepoint list into a SAS dataset and am having a problem with the correct syntax to get the list items. I did click on the link mentioning /site options; however, the code seemed to only reference the drive unless I'm missing it. Could you provide me that link? Thanks in advance!
Laura
I think you want the /lists resource here.
Hi Chris,
Thanks for this very helpful post.
Having an issue when using POST in attempting to create a new folder in SharePoint using proc http. The same url is successful when testing it with MS Graph Explorer, however, I am getting a 400 Bad Request note in SAS log when using same url and payload in SAS.
Here is the base url I am using: https://graph.microsoft.com/v1.0/drives/{drive-id}/items/{parent-item-id}/children
Here is my actual SAS code.
filename cf_log temp;
proc http url="https://graph.microsoft.com/v1.0/drives/b!dYye2aim7UWlT_-wafJ_mDQXAWPkBJJLmS_5l4U-npObPPZMHXT9R74BJPSxi8SI/items/01GHATQLKL3RNY2FSZFFBJC3VJA3R6GJKT/children"
oauth_bearer="&access_token"
proxyhost="10.160.1.123"
proxyport=7600
method="POST"
verbose
in='
{
"name": "Test Folder",
"folder": { },
"@microsoft.graph.conflictBehavior": "rename"
}'
out=cf_log
;
run;
Any advice you can offer would be greatly appreciated.
Do you have Sites.ReadWrite.All added as a permission on your app? It's required for SharePoint.
I was having the same issue and then I added ct="application/json" and it worked.
Hello Chris,
Is it possible to have multiple SAS users/sessions connect to the O365 via the same app registration in the Azure portal?
Thanks,
Robert
Yes, it should be. Each use needs their own credentials/access token though -- if you re-use a single access token, it will be all using just that one Azure identity. I'm sure you don't want that.
Hi, Chris.
Thank you for the useful post. I have successfully deployed the technique with a single access token and multiple SAS users. I agree that "Each user needs their own credentials/access token though -- if you re-use a single access token, it will be all using just that one Azure identity. I'm sure you don't want that." But the manual steps in the post: Step 2: Obtain an authorization code and Step 3: Obtain an access token for each of >100 SAS users and multiple batch service accounts seem dauntingly labor-intensive.
When Lianbo asked on July 28, 2020 11:26 pm, 'for "Step 2: Obtain an authorization code", it looks each session need re-obtain the authorization code, is it possible to get the new authorization code automatically?', you replied "With this approach (Auth code flow), I believe this is the only way to do it. However, there are other authentication flows that can be used for your app. These would probably require more approvals from your IT, and perhaps a service account to have a consistent authentication that you won't need to periodically touch/refresh."
Would you please expound on the "other authentication flows that can be used for your app" ... "perhaps a service account to have a consistent authentication that you won't need to periodically touch/refresh" because my team are members of the IT department and should be able to get the approvals.
Thank you.
There are different flows/options described in the Microsoft doc, but my experience is only with the user authorization flow.
Nicely done! Great article and really is jump-starting our foray into using sharepoint files in this way. Our organization is contemplating moving completely away from from classic mapped drives and moving to cloud-based file system such as sharepoint. I am feeling a bit skeptical about this move in terms of ease of use for our staff, especially during transition. I am wondering if you might comment a bit on where you see some opportunity to streamline this a bit more so it is as facile and intuitive as working with data in the local file system, network shares, or even relational databases.
oh and I should mention, we work with quite large datasets often and I'm a bit concerned how this will play out when working with large files.
Rob, I agree it can be complicated. In the SAS programming world, SAS macros can help. And I'm sure there is some middleware/connectors that can be used to emulate the old way of working, but maybe only from Windows machines (sort of like how OneDrive can sync to your desktop).
My updated repo with new SAS macros do address the upload of large files:
https://github.com/sascommunities/sas-microsoft-graph-api
I am trying to send a file over 4mb , we can use the concept of create upload session in ms graph
but it accepts file in bytes. how can we convert the file in bytes and get ascii string in sas
$fileInBytes = [System.IO.File]::ReadAllBytes($filePath)
$fileInAscii = [System.Text.Encoding]::ASCII.GetString($fileInBytes)
I haven't played with the upload session. Looks like you are using PowerShell. But keep in mind that bytes != characters in text unless you are CERTAIN the file uses ASCII encoding. Most data files these days use UTF-8, which uses multiple bytes per character.
Hi Chris, Thank you very much for this article. After you grant your application permission and IT grants your app permission at the admin level, where should the authorization URL take you? Currently it is taking me to a blank page but is giving me an authorization code that is consistently the same every time. I plug that into the macro variable just as instructed and run the %get_token macro with the a debug level of 3. What stands out in the log are the following notes "NOTE: Variable expires_on is uninitialized." and "HTTP/1.1 400 Bad Request". I am running in EG SAS 9.04.01M7. My full log after including the %get_token call are below. I've noticed that several of your comments are well past 6/30/3030 so I'm assuming the message I see on Azure is moot to this application (Message: Starting June 30th, 2020 we will no longer add any new features to Azure Active Directory Authentication Library (ADAL) and Azure AD Graph. We will continue to provide technical support and security updates but we will no longer provide feature updates. Applications will need to be upgraded to Microsoft Authentication Library (MSAL) and Microsoft Graph.)
Any help would be appreciated.
35 %get_token(&client_id.,&auth_code.,&resource.,token,tenant=&tenant_id.,debug=3); NOTE: The quoted string currently being processed has become more than 262 characters long. You might have unbalanced quotation marks. > POST /xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx/oauth2/token HTTP/1.1 > User-Agent: SAS/9 > Host: login.microsoft.com > Accept: */* > Connection: Keep-Alive > Content-Length: 944 > Cookie: fpc=AqcAurAqJsFOp-i5nTAF1PxqtnpbAgAAAL0rCdkOAAAA8GvX0gIAAABQLwnZDgAAAA; x-ms-gateway-slice=estsfd; stsservicecookie=estsfd > Content-Type: application/x-www-form-urlencoded > NOTE: Variable expires_on is uninitialized. NOTE: Missing values were generated as a result of performing an operation on missing values. Each place is given by: (Number of times) at (Line):(Column). 1 at -3975:-2 NOTE: There were 1 observations read from the data set OAUTH.ROOT. NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.00 secondsWhen you copy that code= value, be sure to capture only the code value and leave off the very end (before the next ampersand). I think maybe you grabbed a little too much.
I am actually not including the &session_state= string at the end of the resulting URL after resolving the authorization URL from the code. I'm only including the characters between nativeclient?code= and &session_state. Should the redirection of the authorization URL take me to something other than a blank page or is that normal? Could there be another reason the get_token is not getting the expires_on value? Thank you.
The blank page is expected, with just the URL changing to include the code= value. Seems like things are failing before that though. Does the resulting token.json file appear to contain expected content? Should look something like this:
{"token_type":"Bearer","scope":"Files.ReadWrite.All User.Read","expires_in":"3599","ext_expires_in":"3599","expires_on":"1627495493","not_before":"1627491593","resource":"https://graph.microsoft.com","access_token":"eyJ0..."}You might need to open a track with SAS Tech Support so we can gather more info to help.
Hi Chris, I will open a track with SAS Tech Support. Thank you for your help. I am a bit confused by your statement "Seems like things are failing before that though." I should not have a token.json file at the point I paste the url, and then I use that code to generate my first token.json file. Because of the errors I get a json file that only has error codes in it. I tried to put it in my initial post but its format was not legible.
The "expires_on" is meant to be read from token.json, but since that's not built properly that's why you get the initialization error. So it's a problem with the first API call that uses the auth code. Tech Support should be able to help suss it out.
I am using cloud-based email (like Microsoft 365) using Microsoft Graph APIs.
I am unable to send content of any table in email body. In SAS 9.4 i was able to send that using 'ods html body' but its not working in SAS Viya 3.5.
Please suggest any alternative for same function. Thanks
Can you tell me which API method you are using?
Hi Chris
I am using same method mentioned in this article for sending mail .
proc http method="POST" url="https://graph.microsoft.com/v1.0/me/sendMail"
ct="application/json" in=%unquote(%nrbquote('{
"message": {
"subject": "&Subj.",
"body": {
"contentType": "json",
"content": "&Body."
},
"toRecipients": [
"emailAddress": {
"address": "&ToAddress."
}
}
],
"ccRecipients": [
{
"emailAddress": {
"address": "&ccAddress."
}
}
]
}
}')) oauth_bearer="&access_token" out=resp;
debug level=3;
run;
The code is working completely fine. I want to send content of any table in body of mail but unlike sas 9.4 where 'ods html body' used to work its not working in above method.
Can you please help help how to send content of any table in body of the mail.
And thanks for this great article. It helps a lot of user.
Please correct i am using "contentType": "text", in above code. I tried using different method but none of them worked.
Can you try ODS HTML5 instead? That destination is more "native" for SAS Viya as it's used by SAS Studio.
I am still working through the Azure piece of this with my security people but I have a question that I have not seen any discussion on. I am hoping to use this to upload CSV files from z/OS SAS to our companies SharePoint folders. My question is about translation from EBCDIC on z/OS to ASCII on SharePoint. Will PROC HTTP be aware of the platform differences and translate automatically or do I need to specifically tell PROC HTTP to translate in some way like I would with FTP?
Are the CSV files represented in EBCDIC on your z/OS file system (presumable using HFS or Unix-style folders)? PROC HTTP should use a binary transfer, so text encoding won't be a factor there. Ideally the CSV should be utf-8 encoded (for best compatibility with all characters).
Thanks for responding. The CSV files are currently in EBCDIC in standard z/OS files but can be translated to ASCII or stored on zFS if required. My biggest problem right now is getting the GET_TOKEN macro to run correctly. I am using the sample macros you placed on github but I can't get past a 400 Bad Request error where the output returned contains "error_description AADSTS900114 The request body must contain the following parameter: grant_type" even though my IN= parameter contains grant_type. Have you ever used this process with z/OS SAS (9.4 TS1M5)? I can send log output if you like.
I've never used/tested this with z/OS. I recommend that you work with SAS Tech Support on this one -- they have access to test systems to trying things out. Using zFS and its unix-style paths might be a better option, but I can't promise it will work differently.
Do you have any examples of sending an excel workbook with multiple tabs that is created in a batch SAS process to Share Point in it's entirety? I'm experimenting with this and seem to have hit a wall?
Thanks
Brett
If you use PROC EXPORT to create workbook, you can add multiple tables by running it with different SHEET= options. Or ODS EXCEL can be used to create a multi-tabbed report with various options for SHEET_INTERVAL.
Once created, the upload process looks like this (excerpt):
Full code at https://github.com/sascommunities/sas-dummy-blog/blob/master/onedrive/onedrive_sharepoint_example.sas.
Thanks... I have opened a track (7613553153) and sent the SASLOG output from my get_token run on z/OS.
Chris,
I am working with Bari in tech support and she is doing some testing on z/OS. There seems to be a problem with the get_token and refresh processing on z/OS but she suggested a workaround in the short term and I am now able to get the drive id and list the children on my sharepoint site. Unfortunately I only see one folder, "Shared Documents", and there are numerous other folders present when I list them online including the one that I created to hold my data. I am assuming that this is some type of authorization issue on the sharepoint site. Have you ever run into anything like this?
I haven't seen that. Often it's helpful to try some of the same operations in Postman or another client to ensure that the APIs, authorization, and permissions are working the way you expect.
Hi Chris,
Thanks for your post. It is very instructive. I have a basic question about the integration of SAS with Office 365 Sharepoint / Onedrive. Right now, we are using Office 365 Cloud Sharepoint / Teams / Onedrive for all our work including data storage and analysis. I have a SAS 9.2 windows installed on my company laptop. All the Sharepoint project data are synched on my laptop and I can use SAS libname to directly specify the Sharepoint project folder and get access to the data there. Since some of the data are sensitive, we needs to save any data including the intermediate data in the folder itself instead of on the laptop. It has slowed the data analysis process for large data files. I am wondering if SAS can be directly deployed on our company's Office 365 account instead of on my laptop. Besides I am thinking if the online SAS may also be able to accommodate other SAS users of our company at the same time. Do you have any suggestions or any references that I can review.
SAS now has an offering called SAS for Microsoft 365 which might help.
if the folder has more than 200 items, how via sas do I get the additional items so I can get the correct ID Value?
Check out the new macros I created here -- these handle the case of > 200 items.
https://github.com/sascommunities/sas-microsoft-graph-api
I have all of the connections set up between my SAS session and O365 so that I successfully read the SharePoint structure and upload various types of files to it. Now I need to read the data from a SAS data set and write that into a SharePoint list on our O365 site. Is there any sample code available that I can use as a starting point?
I don't currently have examples for SharePoint lists, but using the APIs I know this is possible. Some colleagues have done it, but I don't have examples on hand.
Hi we needed your help accessing SharePoint LIST data using FileRef URL
• The XML-Map file of a SharePoint LIST was saved into SasMart (our SASGrid is on LINUX x64)
• When we use FileRef URL for the SharePoint LIST, the Output displays Column headers without data-rows…
I tried FileRef URL with and without Proxy= option but, it gives the same output (DataSet without rows)
FILENAME XML_FIL URL 'http://sharepoint.abcdefghijklmnopqustubwxyz.com/sites/abc_abc_abc/_vti_bin/owssvr.dll?XMLDATA=1&List={XXXX_XXXX…}&View={XXXX_XXXX…}&RowLimit=1000'
USER= 'XXXXXXXX' PASS= 'XXXXXXXX' CONNECT DEBUG;
FILENAME XML_MAP '/sasmart/xml_map.map';
LIBNAME XML_FIL XMLV2 XMLMAP= XML_MAP ACCESS= READONLY;
DATA WORK.ROW;
SET XML_FIL.ROW;
RUN;
My SAS EG Log looks like (FYI I have full access to the SharePoint);
NOTE: Processing XMLMap version 2.1.
NOTE: Libref XML_FIL was successfully assigned as follows:
Engine: XMLV2
Physical Name:
http://sharepoint.abcdefghijklmnopqustuvwxyz.com/sites/Abc_abc_abc/_vti_bin/owssvr.dll?XMLDATA=1&List={XXXX_XXXX…}&View={XXXX_XXXX…}&RowLimit=1000
33
34 DATA WORK.ROW; SET XML_FIL.ROW; RUN;
NOTE: >>> GET
/sites/Abc_abc_abc/_vti_bin/owssvr.dll?XMLDATA=1&List={XXXX_XXXX…}&View={XXXX_XXXX…}&RowLimit=1000 HTTP/1.0
NOTE: >>> Host: sharepoint.abcdefghijklmnopqustuvwxyz.com
NOTE: >>> Accept: */*
NOTE: >>> Authorization: Basic bmJrMmw4bTpTdW1tZXIwMQ==
NOTE: >>> Proxy-Authorization: Basic bmJrMmw4bTpTdW1tZXIwMQ==
NOTE: >>> Accept-Language: en
NOTE: >>> Accept-Charset: iso-8859-1,*,utf-8
NOTE: >>> User-Agent: SAS/URL
NOTE: >>>
NOTE: <<< HTTP/1.1 401 Unauthorized
NOTE: <<< Content-Type: text/plain; charset=utf-8
NOTE: <<< Server: Microsoft-IIS/10.0
NOTE: <<< SPRequestGuid: f80c6ba0-8cab-d00d-f763-33b4e97b75a8
NOTE: <<< request-id: f80c6ba0-8cab-d00d-f763-33b4e97b75a8
NOTE: <<< X-FRAME-OPTIONS: SAMEORIGIN
NOTE: <<< SPRequestDuration: 4
NOTE: <<< SPIisLatency: 0
NOTE: <<< WWW-Authenticate: Negotiate
NOTE: <<< WWW-Authenticate: NTLM
NOTE: <<< X-Powered-By: ASP.NET
NOTE: <<< MicrosoftSharePointTeamServices: 16.0.0.5305
NOTE: <<< X-Content-Type-Options: nosniff
NOTE: <<< X-MS-InvokeApp: 1; RequireReadOnly
NOTE: <<< Date: Mon, 03 Oct 2022 16:16:45 GMT
NOTE: <<< Connection: close
NOTE: <<< Content-Length: 16
NOTE: <<<
NOTE: There were 0 observations read from the data set XML_FIL.ROW.
NOTE: The data set WORK.ROW has 0 observations and 22 variables
With single-signon systems it's unusual to have a user/password method that can actually work with SharePoint. You need to use APIs with an authenticated token, not web-page access via PROC HTTP. Most customers these days are moving to SharePoint Online (hence this blog post) and not on-premise SharePoint.
Hi Chris, thanks a lot for this - have saved me a lot (!) of work.
Nevertheless, I had problems with your uploadFile macroFunction when the returned uploadUrl is a string containing an ampersand (which I had a lot of in my returned url). In that case the "&uploadURL." will not work as the compiler will try to resolve macrovars within the string defined by &uploadURL. I solved it using proc http url= "%superq(uploadURL)" on line 552 in your code...
Thanks for pointing that out! I will do some testing and apply that change if it works.
Hi Chris. If I am using the SAS 9.4 Desktop Version, can I still use the steps above to access OneDrive or SharePoint?
Yes, you sure can!
Hi Chris, Thanks for this detailed blog, with the help of this blog, I managed to integrate SharePoint access in our SAS Studio environment. One thing I noticed that, when I am trying to move some files from SAS Studio to SharePoint site around 1 GB of data, getting error as "NOTE: HTTP Status code=413 : Request Entity Too Large".
Could you please suggest how to handle this situation for large data move.
Thanks,
Sandip Kumar
I created another set of macro routines that includes a large file upload method. See it here on GitHub: https://github.com/sascommunities/sas-microsoft-graph-api
Hi Chris-
I've found this detailed blog very helpful and useful for answering most of my questions; however, I'm running into some issues when attempting to pull items from a Sharepoint List. My code worked fine until the list was over 5000 records and then it still pulls only the first 5000.
I've tried the top= option, but the most it will pull is 5000 even if I pass in other values. When I attempt to use the value from the @odata.nextLink to get records after 5000, I get a 404 error when I pass in the url.
I've also tried to use the macro you provide which loops through pulling 200 at a time, but the @odata.nextLink value also returns the 404 error when I attempt the 2nd pull. Can you suggest how I might get the nextLink to actually work to return the next set of records?
That's a big list! I don't have a ton of experience with Lists at this point, so not sure if the odata.nextLink protocol is the same for Lists as it is for files (although...why wouldn't it be)? I'll have to investigate. If you solve it in the meantime, let me know!
Thanks for the informative post! I’m working on downloading SharePoint Online 365 security group members into SAS, but I’m having trouble finding the right folders. Can you offer any advice on tweaking the code to include members of a SPO security group? Any help would be greatly appreciated!
I think for this you would use the List Group Members API:
https://learn.microsoft.com/en-us/graph/api/group-list-members?view=graph-rest-1.0&tabs=http
Your app also needs GroupMember.Read.All permission.