08-31-2013 05:52 PM
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?
09-01-2013 04:46 AM
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)
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
09-01-2013 09:59 PM
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.
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*/
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.