Take a look at the Microsoft Graph API (Microsoft Graph REST API v1.0 reference - Microsoft Graph v1.0 | Microsoft Docs). This SAS Blog article will get you up and running with utilizing the Graph API within SAS programs (Using SAS with Microsoft 365 (OneDrive, Teams, and SharePoint) - The SAS Dummy). I know the title only mentions OneDrive, Teams, and SharePoint but the Graph API can be used to access a plethora of Microsoft cloud services, presumably ADLS as well. You can probably put together a few programs that will handle grabbing your data sets and use an HTTP PUT request to upload the data to your desired ADLS location. I developed some "wrapper" utility programs (recycling some of the code base provided in the SAS Blog article referenced above) that construct & submit the HTTP requests for various "functions" that take user-specified parameters. Below is an example of a function that gets a file from SharePoint and performs a PROC IMPORT (user provides the SharePoint site name, document library aka drive, path, filename, file format, and output data set name). /* === EXAMPLE === */
/* GET_FILE() */
%let user_site_path = /corpsite;
%let user_drive_name = Shared Documents;
%let user_folder_name = /2021 Budget/Estimates;
%let user_file_name = Admin_Expense.xlsx;
%let file_type = xlsx;
%let sds_name = aebe2021;
%get_file(&user_site_path,&user_drive_name,&user_folder_name,&user_file_name,&file_type,&sds_name,&access_token); All function arguments are required. All arguments except the access_token, per usual, are provided by the user. The access_token is provided by your token_manager.sas program and stored in the macro variable &access_token. Hence our invocation of the function in this example takes the form: %get_file( &user_site_path,.............. the target site path &user_drive_name,........... the target drive name &user_folder_name,......... the target folder_name (or path if nested folders) &user_file_name,............... the target file name &file_type,........................... a SAS DBMS type (e.g. XLSX, CSV, etc.) &sds_name,......................... a valid SAS data set (use dot notation to save to library other than WORK) &access_token); Source code for %get_file SAS macro: %let hostname = ecorp.sharepoint.com;
%let base_api_endpoint = https://graph.microsoft.com/v1.0;
%macro get_file(
site_path
,drive_name
,folder_name
,file_name
,file_type
,sds_name
,access_token);
/*
=== ABOUT THIS MACRO ===
This macro simplifies the process of getting a file from a given folder in a
given drive at a SharePoint site path. Simply pass the desired site_path, drive name,
folder_name, and file_name along with your access_token and it will retrieve the
file content, storing it to a local SAS session file system object.
=== ARGUMENTS ===
site_path...........REQUIRED
the root relative path of the SharePoint site being queried
This is everything after https://ecorp.sharepoint.com/sites/
Ex] "/corpsite" is the site_path (minus the quotes) for https://ecorp.sharepoint.com/sites/corpsite
drive_name..........REQUIRED
the name of the drive (aka document library) being queried
folder_name.........REQUIRED
the name of the folder being queried (can be folder path in form of "/root_folder/nested_folder_1")
file_name...........REQUIRED
the name of the file being retrieved
file_type...........REQUIRED
the SAS DBMS value to use in the PROC IMPORT
(e.g. "xlsx" for a modern Excel workbook, "csv" for a CSV file, etc.)
sds_name............REQUIRED
the name out the data set written to the WORK library
access_token........REQUIRED
the access token of the querying user (obtained using your "token_manager.sas"
program). See documentation for configuration of "token_manager.sas".
=== RESOURCES & REFERENCE ===
/// Reference ///
[O] Microsoft Graph API [https://docs.microsoft.com/en-us/azure/active-directory/develop/microsoft-graph-intro]
[O] Using SAS with Microsoft 365 [https://blogs.sas.com/content/sasdummy/2020/07/09/sas-programming-office-365-onedrive/]
[O] The ABCs of PROC HTTP [https://www.sas.com/content/dam/SAS/support/en/sas-global-forum-proceedings/2019/3232-2019.pdf]
[O] How Do You Use SAS to Access Data and APIs From the Web? [https://communities.sas.com/t5/Ask-the-Expert/How-Do-You-Use-SAS-to-Access-Data-and-APIs-From-the-Web-Q-amp-A/ta-p/699613]
/// Resources ///
[O] Microsoft Graph API Explorer [https://developer.microsoft.com/en-us/graph/graph-explorer]
=== METADATA ===
AUTHOR: ALEX CANNON
CREATED: 2021-07-03
VERSION: 1.0
RELEASED: 2021-07-03
*/
/*
First we get the SharePoint identifiers for the site, drive, and folder of
interest. We use the get_folder_contents() utility of the Microsoft Graph API
Utilities for SAS to get a folder contents listing for the given folder on
the given drive of the given site. When get_folder_contents() executes it will
call get_drive_contents() to get the folder listing. get_drive_contents()
will call get_drives_by_site_path() in order to get the drive listing for
the site. With the drive, drive contents, and folder contents listings created
as a result of these function calls we now have the needed identifiers to
target the folder containing the file of interest. We already have the site
identifier stored in macro variable &site_id as we resolved it when calling
get_drives_by_site_path() but we don't really need it since we already have
the identifiers of all the drives and will be using the /drives verb to get
at our file of interest. We select the "sharepoint_id" of the drive from the
drive listing with the name matching the "drive_name" argument passed to this
macro, the "sharepoint_id" of the folder from the drive contents listing with
the name matching the "folder_name" argument passed to this macro, and the
"sharepoint_id" of the file from the folder contents listing with the name
matching the "file_name" argument passed to this macro.
*/
%get_folder_contents(&site_path,&drive_name,&folder_name,&access_token);
proc sql noprint;
select
sharepoint_id
into :file_id
from
work.folder_contents
where
item_name = "&file_name";
select
sharepoint_id
into: folder_id
from
work.drive_contents
where
item_name = "&folder_name.";
select
sharepoint_id
into: drive_id
from
work.sharepoint_libraries
where
document_library = "&drive_name.";
quit;
/* We set the base_api_service to the target service */
%let base_api_service = /drives;
/* We set a file object called "fileout" to hold the file content returned by API service. */
filename fileout "&user_data_root.\&file_name.";
/* We also set a file object called "hdrs" to store the HTTP response headers for debugging and analysis */
filename hdrs "&user_data_root.\gf-http-hdr.txt";
/* Note: oauth_bearer option added in 9.4M5 */
/*
Use PROC HTTP to make API call to Microsoft Graph service. The base URL for
Graph is "https://graph.microsoft.com/v1.0/"
The "oauth_bearer" option on the PROC HTTP is set to the macro variable &access_token
that we pass to this macro. We obtain our current access token using the "token_manager.sas"
program configured for your user ID.
Append the request parameters to the base URL in order to query the service
which will return the file content. We write the file content to "fileout".
*/
proc http url="&base_api_endpoint.&base_api_service./&drive_id./items/&file_id./content"
method="GET"
oauth_bearer="&access_token"
out = fileout
headerout = hdrs;
run;
/* Now we use a PROC IMPORT to import the file from the local file system where the SAS session is running */
%if %sysfunc(exist(fileout)) %then %do;
proc import file=fileout
out=&sds_name
dbms=&file_type
replace;
run;
%end;
%mend get_file; Happy coding! /ac
... View more