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.
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.