BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
arial34
Fluorite | Level 6

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?

1 ACCEPTED SOLUTION

Accepted Solutions
ChrisHemedinger
Community Manager

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;
It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.

View solution in original post

9 REPLIES 9
arial34
Fluorite | Level 6
It would be a big overhead for our problem 😞
Kurt_Bremser
Super User

@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).

TomKari
Onyx | Level 15

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

arial34
Fluorite | Level 6

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.

Kurt_Bremser
Super User

@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).

ChrisHemedinger
Community Manager

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;
It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
arial34
Fluorite | Level 6
Thank you for your help. It works great.
LinusH
Tourmaline | Level 20
Well if you have the add in, why use Excel format for storage?
The addin could access a SAS data set on the server. That should be registered in meta data, and then easily accessed from DI Studio.
Data never sleeps

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 2721 views
  • 1 like
  • 5 in conversation