Hello all,
I use an Excel file as a source for an ETL Process. The file will be edit by users in Excel and opened / saved with the SAS Excel Addin (function Extras -> save in sas folder). Also the file is stored in metadata.
My problem is: how can I access the content of the file in EG or as user written code in DI-Studio?
Any ideas?
When a file is "saved in metadata", usually that means in a SAS metadata folder. The information is registered in metadata, while the physical file is stored in the SAS Content Server. The SAS Content Server is a WebDAV-compliant HTTP server.
PROC HTTP can be used to access, if you know the URL/port and the metadata path and have the proper credentials. Here's an example:
filename xlsout "%sysfunc(getoption(WORK))/file.xlsx";
%let server=http://yourserver.company.com:7980/SASContentServer;
proc http
url="&server./repository/default/sasfolders/Shared%20Data/stp_samples/file.xlsx"
WEBUSERNAME="xxxxx"
webpassword="xxxxx"
method="GET"
OUT=xlsout
;
run;
proc import datafile=xlsout
out=result
replace
dbms=xlsx;
run;
If you need a way for users to input data, wouldn't it be easier to write a stored process that stores directly to a SAS dataset?
@arial34 wrote:
It would be a big overhead for our problem 😞
Absolutely not. It would take the execrable Excel out of the process, only need a web browser for users, and you could even code sanity checks into the STP (or the web form, using javascript).
Can you elaborate a little on "also the file is stored in metadata". In what form? How do you get it into the metadata server?
Tom
User opens an excel sheet using the SAS excel addin. Then after edit user saves file with the same addin in a defined folder. So the file content is stored not in the file system.
@TomKari, I just tried that for the first time. The MS Office Add-In allows storing of Office documents into the metadata folders. Some checks did not yet reveal if the documents are put into the metadata repository itself or stored via webdav into the SAS Content Server (with a link in the metadata).
When a file is "saved in metadata", usually that means in a SAS metadata folder. The information is registered in metadata, while the physical file is stored in the SAS Content Server. The SAS Content Server is a WebDAV-compliant HTTP server.
PROC HTTP can be used to access, if you know the URL/port and the metadata path and have the proper credentials. Here's an example:
filename xlsout "%sysfunc(getoption(WORK))/file.xlsx";
%let server=http://yourserver.company.com:7980/SASContentServer;
proc http
url="&server./repository/default/sasfolders/Shared%20Data/stp_samples/file.xlsx"
WEBUSERNAME="xxxxx"
webpassword="xxxxx"
method="GET"
OUT=xlsout
;
run;
proc import datafile=xlsout
out=result
replace
dbms=xlsx;
run;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.