BookmarkSubscribeRSS Feed
Sotarkadin
Calcite | Level 5

Good Day,

I am trying to understand if there is a way to write a macro or code that would import multiple excel sheets from one workbook.   Currently we use SAS EG, the server is SASApp as it is located on a remote server that runs linux.  The file(s) that I would import from are also located on that same server.  I am trying to set this up within the code as opposed to using the import step as we use LSF to auto run our codes. 

Each month there will be a excel workbook for the month's data.  Based on the amount of data it can vary in size(one month it could have 3 sheets the next month 5).  I would like to be able to have this run on the SASApp and import all the sheets within the workbook into one table.  I can not seem to find a method for varying number of sheets.

Thank you.

3 REPLIES 3
SASKiwi
PROC Star

To be able to import Excel workbooks in code you need to have installed and licensed the SAS product SAS/ACCESS to PC Files. In EG you can easily check this by viewing the Properties of your SASApp server in Server List.

Also since you are running on Linux, you will also need to have the SAS PC File Server installed, configured and working on a Windows server / computer that can communicate with your Linux server. I believe this is a free download.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Why use Excel at all.  It is not a data transfer format.  I would suggest you check the source, likely to be a database, and ask them to export one CSV file which you can then easily import.  Basically your doing extra work just to cover the fact that the data is in excel, what happens when something goes wrong - i.e. Excel hides certain information, or doesn't represent it correctly, then it will end up as your fault.

MadhuKorni
Quartz | Level 8

By using Libname we can bring all the spreadsheets in excel workbook to SAS environment in the form of datasets.

The spreadsheets names will be the Dataset names followed by $.

For example if Sample.xls is the workbook that consists of Acc, AccNums, CustIds as spreadsheets then if you use libname statement then Acc$, AccNums$, CustIds$ will be the dataset names.

Libname Libref Excel "Path\Sample.xls";

Libref is a user defined name.

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
  • 3 replies
  • 2980 views
  • 0 likes
  • 4 in conversation