Hello,
I'm seeking the advices to deal with my current issue.to understand SAS flexibility when connecting SAS data sets on Azure.
I've been run my SAS code(base PC SAS9.4 M7) to access array of SAS data sets(via libname statement) stored on the internal shared drive.
Each SAS data set is created on the monthly bases with about 5K obs and 300ish variables. Everything works perfectly.
However, our new company policy requires us to move those SAS data sets onto Azure cloud blob.
My question is how to have programmatic ways move the array of SAS data sets(without any changes) to Azure so that we’re still able to continue access those SAS data sets like now.
I’m very much appreciative of your insights.
Ethan
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
Hi @t75wez1 ,
Just wondering if you came up with a solution you like for using SAS 9.4 to read/write to Azure blobs? I see posts about how to do this from Viya, but haven't found anything yet about doing it with 9.4, except the PROC HTTP approach mentioned here. I'm hoping I'm missing something easier. : )
--Q.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!