Using SAS with Microsoft Sharepoint

Communicating with Sharepoint via SAS

To use Sharepoint with SAS, we will use a third-party app. This requires several setup steps. Fortunately, these need to be done just once, or at least infrequently. Here’s an outline of the steps:

1. The first step is registering a new client application at the Microsoft Azure Portal.
2. Obtain an authorization code for the app using your browser.
3. Hard code this authorization code into a SAS program to retrieve an OAuth2 access token (and a refresh token).
4. With the access token, you can now use PROC HTTP and the Microsoft 365 APIs to retrieve your Sharepoint folders and download, upload, 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 become invalid when certain events occur (such as you change your account password). When that happens, you’ll need to repeat steps 2 and 3 to get a new set of access/refresh tokens.

Step 1: Register your application

1. Search for and select Azure Active Directory

1. Under Add, select App registrations > New registration. 

As you register your application, you need to provide a redirect URL for the authorization flow. From the dropdown. select “Public client/native (mobile/desktop)“, and then, in the URL section, specify the URL value as:

 https://login.microsoftonline.com/common/oauth2/nativeclient 

When you create an app, you’ll receive a Client ID unique to your app and a Tenant ID unique to your organization. You’ll need these values to obtain your authorization code and tokens later.

Specifying your app permissions

Your app will need specific permissions to function. The permissions we need are:

• Files.ReadWrite.All: This will allow the app to read, create, update and delete all OneDrive files you can access.
• User. Read: This 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): This allows the app to read, create, update and delete SharePoint Online files for sites you can access.

To add these permissions to your app, click the API Permissions tab in the control centre and click on Grant admin Consent for your app. Next, click on the “Microsoft Graph(1)” link. A pop-up window opens, where you can search for the required permissions and select them.

This is what the permission should look like.

Configuring the SAS Code

Open the SharepointToken.sas program and hard code your client and tenant id. (Download link of source file at the end of this article)  

Step 2: Obtain an authorization code

Now that the application is created, it’s time to “sign into it” and grant permission to read and manage content in Sharepoint. 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.

* 3. 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/37688c7a-aa51-4c90-865d-216cd1d09bba/oauth2/authorize?client_id=8f7b34b3-e0f2-4f38-9168-d7258d7f16
67&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 or error 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. We’ll need that value for the next step.

Step 3: Obtain a Refresh and access token

In the token.sas program, the macro TOKEN_INITIAL will give us the Refresh and Access token. We need to run this macro once to get the refresh token.

* 4. AFTER CLICKING 'TRUST IT', PASTE AUTH CODE FROM LOCALHOST URL ;
%let auth_code=YOUR AUTH CODE HERE;

/*Get the Initial Refresh Token*/
* (CALL MACRO BY RUNNING THIS): %TOKEN_INITIAL;

%macro TOKEN_INITIAL;
%let auth_url="https://login.microsoftonline.com:443/&tenant_id./tokens/OAuth/2";
%let redirect_uri=https://login.microsoftonline.com/common/oauth2/nativeclient;
%let resource=https://graph.microsoft.com;

proc http url="https://login.microsoft.com/&tenant_id./oauth2/token" 
method="POST" in="%nrstr(&client_id)=&client_id.%nrstr(&code)
=&auth_code.%nrstr(&redirect_uri)=&redirect_uri%nrstr(&grant_type)
=authorization_code%nrstr(&resource)=&resource." 
out=resp;
run;

%echofile(resp);

%* Set up libname for JSON file output *; 
libname auth json fileref=resp;
title 'Initial Token';
proc print data=auth.root;
title;
%mend TOKEN_INITIAL;

The refresh token can be viewed from the proc print or the log. After you get the refresh token, copy it and hard code the value to the refresh_token variable.

* 5. AFTER YOU GET YOUR REFRESH TOKEN, PASTE IT HERE (AND REMOVE CARRIAGE RETURNS IF COPIED FROM THE LOG) ;

%let refresh_token=YOUR REFRESH TOKEN

Retrieving the top-level drive identifier (SharePoint Online)

All our one-time setup has been completed, and we have the refresh and access tokens. Now, it’s time to communicate with Sharepoint through SAS code.

We would need the identifier for the root drive and store the identifier in a macro variable. We’ll need that identifier later to retrieve a list of top-level items.

First, open the Sharepoint_Read_Write.sas program and update the below variables as per your organization. Set these values per your SharePoint Online site.

Example: https://yourcompany.sharepoint.com/sites/YourSite breaks down to:yourcompany.sharepoint.com -> hostname /sites/YourSite -> sitepath

Now, run the below lines of code.

%let hostname = 9to5sas.sharepoint.com;
%let sitepath = /sites/9to5sas;
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;

title 'Top-level drive identifier (SharePoint Online)';
proc print data=jresp.root;
run;
title;
/* 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 ID in hand,  we can use the /children verb on the Microsoft Graph API to get a list of all the top-level objects in that drive.

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;

title 'LIST TOP LEVEL FOLDERS/FILES';
proc print data=jresp.value(obs=5);
var createdDateTime id lastModifiedDateTime name webURL size;
run;
title;

/* Create a data set with the top-level paths/files in the drive */
data paths;
set jresp.value;
run;

List the files in a particular folder.

If you want to view the contents of a particular folder, you will need a folder identifier. Using PROC SQL and SELECT INTO, store the folder ID in a macro variable. Then, use the /children verb again, but this time with the folder ID instead of the “root” constant.

proc sql;
 select name,id,webURL into :name,:folderId,:webURL from paths
  where upcase(name)="SAS";
quit;

proc http url="https://graph.microsoft.com/v1.0/me/drives/&driveId./items/&folderId./children"
     oauth_bearer="&access_token"
     out = resp;
     run;

%echofile(resp);
libname jresp json fileref=resp;
/*proc datasets lib=jresp; quit; */
data folderItems;
 set jresp.value;
run;
title 'FOLDER ITEMS';
proc print data=jresp.value(obs=5);
var createdDateTime id lastModifiedDateTime name webURL size;
run;
title;

Save SAS Dataset to Sharepoint

To save a SAS dataset to Sharepoint, we would again need a folder ID and store this in a macro variable.

proc sql;
select name,id,webURL into :name,:folderId,:webURL from paths
where upcase(name)="DATASETS";
quit;

Next, we get the physical path of a SAS Library where the SAS dataset is saved. You can use the pathname function to get the physical pathname of a library.

/*Get the physical path of a library using PATHNAME function*/
%LET LOC = %SYSFUNC(PATHNAME(SASDSN)); 
%PUT &LOC.;

%let targetFile=buy.sas7bdat;

filename filein "&loc./&targetFile.";
filename details temp;
proc http url="https://graph.microsoft.com/v1.0/me/drives/&driveId./items/&folderId.:/&targetFile.:/content"
method="PUT"
in=filein
out=details
oauth_bearer="&access_token";
run;

%echofile(details);

libname attrs json fileref=details;
data fileDetails(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;
proc print;

As you can see, the SAS dataset from SAS is uploaded to my Sharepoint folder.

Save Excel files to Sharepoint

A SAS dataset can be exported from the local SAS session into a target folder on Sharepoint as an excel file.  Most often, this will probably be an Excel spreadsheet or maybe a PDF report.

This requires a temporary intermediate file to be created before the export request. The work library is used as the destination for these files to avoid the need to delete them after each export process is completed manually.

Note the line filename tosave "%sysfunc(getoption(WORK))/&targetFile."; that is used as the temporary destination for the export.

The first step is to get the folder ID of the SharePoint folder where we want our file to be uploaded.

The next step is to export the SAS dataset to Excel using ODS EXCEL. ODS Excel is one of the methods to export SAS data to excel.

It produces a native XLSX file.ODS Excel can write output generated from SAS procedures such as PROC PRINT, PROC REPORT, PROC FREQ, and PROC MEANS. 

ODS EXCEL method is available in SAS 9.4 Maintenance 3 and later.

The next step is to use PROC HTTP  “POST” request, but with the “content” action parameter in the URL to indicate that the file data is wanted and not just metadata.

proc sql;
select name,id,webURL into :name,:folderId,:webURL from paths
where upcase(name)="SAS";
quit;

/* Create a simple Excel file to upload */
%let targetFile=class.xlsx;
filename tosave "%sysfunc(getoption(WORK))/&targetFile.";
ods excel(id=upload) file=tosave;
ods exclude all;
proc print data=sashelp.class;
run;
ods exclude none;
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;
%echofile(details);
/*
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;

proc print;

As you can see in my Sharepoint folder, the file has been uploaded.

Read a file from Sharepoint and import it into SAS

To read files, first, get the Sharepoint folder ID, get the folder id and store it in a macro variable.

You can now download the file from Sharepoint and store it in the file system. Here, we have used %sysfunc(getoption(WORK)) to store the file in the Work library.

You can use any path within your local SAS system. Then, you can use the PROC IMPORT to read it into a SAS data set.

proc sql no print;
select id into: fileId from folderItems
where name="iris.xlsx";
quit;

* https://9to5sas.sharepoint.com/sites/9to5sas/Shared%20Documents/sas;
*proc http url="https://graph.microsoft.com/v1.0/me/drives/&driveId./items/iris.xlsx/content";

filename fileout "%sysfunc(getoption(WORK))/iris.xlsx";
proc http url="https://graph.microsoft.com/v1.0/me/drives/&driveId./items/&fileId./content"
oauth_bearer="&access_token"
out = fileout;
debug level = 2;
run;
%echofile(fileout);

proc import file=fileout 
out=iris
dbms=xlsx replace;
run;

You can find the source files for these examples on GitHub. We hope this article helped you to use SharePoint and SAS.

Moreover, if you have any other suggestions, suggest them below the comment section. We would take those lists in our further blog post.

Thanks for reading!! Please subscribe to our mailing list for weekly updates. You can also find us on Instagram and Facebook.

Helpful links

1. Microsoft Graph permissions reference
2. Access Tokens
3. PROC HTTP Procedure
4. Microsoft Graph REST API v1.0 reference