topic Re: Import Multiple Excel Files Into Single SAS Data Set in SAS Programming

Import Multiple Excel Files Into Single SAS Data Set

deleted_user — Wed, 25 Mar 2009 16:41:07 GMT

Hello,

I have 20 Microsoft Excel files that I would like to import into a single SAS Data Set for analysis. The variables within each Excel file are the same, only the observations differ, as each Excel file represents output from a single department, and we have reports from 20 departments.

There is report header information in the first 6 rows of the Excel spreadsheet, so I would like to import the data starting at line 7, if that's possible.

Any information on the best way to accomplish this task is appreciated. I have access to local SAS as well as having a BI implementation (with access to all SAS tools) at my disposal, so I am would be interested in hearing any and all options that may be at disposal.

Thanks!

Re: Import Multiple Excel Files Into Single SAS Data Set

WendyT — Mon, 27 Apr 2009 17:00:44 GMT

Hello everyone-

I'm facing much the same problem as the OP - I need to import the data from a single tab from multiple Excel 2007 files. Our upgrade of 9.2 to phase 2 to read 2007 is in the works.

I have done multiple file reads from .csv files into one dataset, but we have had problems with corrections not getting done through both source files, thus the switch to direct read.

I found a macro that will read multiple sheets out of multiple Excel files, and I could take out the multiple tab section for my case.

http://www2.sas.com/proceedings/sugi31/034-31.pdf

I will be reading only the tab 'finals' from Excel (*.xlsx) files from a specific directory, and the Excel files will not have consistent naming.

Does anyone have a simpler way to do this?

Thanks for your help!

Wendy

LIBNAME GETIT EXCEL "test.xlsx" MIXED=NO SCAN_TIMETYPE=ANY ;
DATA NEW ;
SET GETIT.'FINALS$'n ;
RUN ;
LIBNAME GETIT CLEAR ; RUN ;

Re: Import Multiple Excel Files Into Single SAS Data Set

LinusH — Tue, 28 Apr 2009 07:15:17 GMT

Reading the Excel files could be done with PROC IMPORT. Reading from row 7 can be accomplished, see on-line doc for PROC IMPORT. For each Excel-file, you can do SQL INSERT to a common table. If not present in the Excel-files, you might wish to add a department id column.

Reading all Excel-files could be done either via a macro-loop, or by reading all files in a specific directory. There have been many previous threads in this forum discussing these topics.

/Linus