How would I go about importing Serveral Tables in a single Excel spreadsheet

[mitchymitch]

Hi,

I have an excel file that has 1 spreadsheet.  That spreadsheet has multiple tables on the 1 spreadsheet.  These tables are seperated by 1 row each. 

How would I go about importing each of these tables into SAS as a seperate SAS table?

[jakarman]

using libname?    How do I export from SAS to Excel files: Let me count the ways - The SAS Dummy

If the first sheet has been set up with many columns/ranges.

Name the ranges in SAS

5444 - How to read an Excel range into SAS using PROC IMPORT (name them your self)

19827 - PROC IMPORT cannot use RANGE option when EXCEL spreadsheet has specialCharacter in Sheet nam... (care special chars)

Using excel named ranges  http://www2.sas.com/proceedings/sugi31/024-31.pdf

reading the columns and all by yourself in one datastep and gnereate the sas-tables with that. 

SAS/ACCESS(R) 9.3 Interface to PC Files: Reference (getnames=no)

All depends on more not metnioned requirements/choices 

[Scott_Mitchell]

Hi Mitchy,

As has mentioned there are many approaches you can take in this situation.

A simple method is using EXCEL Ranges and PROC COPY.  I am using PCFILE SERVER to access the EXCEL Workbook.  If you aren't using the PC FILE SERVER, you just need to change the LIBNAME statement to use your preferred method.  I would suggest that you maintain the acceptable SAS naming conventions when importing the data for named ranges, however if you are unable to do so you will need to use SAS name literals, which means enclosing your name in single quotes  followed by an N  e.g. 'SUM TOTAL'N.  I have other methods to correct for names that do not meet the SAS naming conventions, however they are more complicated and perhaps irreverent in your scenario.

The following link shows how to create a dynamic in EXCEL range to ensure that you are capturing all your data when additional rows are added.

How to create a dynamic defined range in an Excel worksheet

LIBNAME XLSFILE PCFILES TYPE=EXCEL PORT=8621 SERVER=LOCALHOST PATH = "E:\NEWMICROSOFTEXCELWORKSHEET.XLSX";

PROC COPY INLIB=XLSFILE OUTLIB=WORK; /*THE LIBRARIES WHERE THE DATA ARE PRESENTLY LOCATED - INLIB AND THE OUTPUT LIBRARY - OUTLIB */

  SELECT SAMPLERANGE TESTRANGE VARRANGE;/*THE NAMES OF  THE RANGES YOU WISH TO IMPORT - NAMES ARE RETAINED IN THE OUTLIB*/

RUN;

LIBNAME XLSFILE CLEAR;

There are more dynamic ways of importing all ranges, however given you will have to set up the ranges in EXCEL initially anyway, it is probably overkill in this situation.

Regards,

Scott