topic Re: Import multiple sheets from one excel workbook. in SAS Enterprise Guide

Import multiple sheets from one excel workbook.

Sotarkadin — Tue, 14 Jul 2015 18:53:39 GMT

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.

Re: Import multiple sheets from one excel workbook.

SASKiwi — Wed, 15 Jul 2015 08:13:02 GMT

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.

Re: Import multiple sheets from one excel workbook.

RW9 — Wed, 15 Jul 2015 08:27:45 GMT

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.

Re: Import multiple sheets from one excel workbook.

MadhuKorni — Wed, 15 Jul 2015 09:28:52 GMT

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.