SAS proc import .xls with several spreadsheet and append -

situation: have workbook .xls 4 spreadsheets named "sheeta", "sheetb", "sheetc", "sheetd".

for import 1 spreadsheet following.

proc import     out = outputtablea     datafile = "c:\user\desktop\excel.xls"     dbms = xls replace;     sheet = 'sheeta';     namerow = 3;     startrow = 5; run; 

all spreadsheet have same number of variables , format. combine 4 outputtablex using data step:

data combinedata;     set outputtablea outputtableb outputtablec outputtabled; run; 

i new sas, m thinking whether array , do-loop can help.

i not use loop (as they're overly complicated). instead, make data driven. use reese's solution if can; if must use proc import due namerow/datarow options, works.

first, create libname.

libname mylib excel "c:\blah\excelfile.xls"; 

we won't use it, if prefer xls options, lets sheets.

proc sql;   select cats('%xlsimport(sheet=',substr(memname,1,length(memname)-1),')')     :importlist separated ' '     dictionary.tables     libname='mylib' , substr(memname,length(memname))='$'; quit;  libname mylib clear; 

now we've got list of macro calls, 1 per sheet. (a sheet dataset has '$' on end.)

now need macro. thing wrote already. let's substitute few things in here.

%macro xlsimport(sheet=);   proc import     out = out&sheet.     datafile = "c:\user\desktop\excel.xls"     dbms = xls replace;     sheet = "&sheet.";     namerow = 3;     startrow = 5;   run; %mend xlsimport; 

and call it.

&importlist. 

i leave exercise viewers @ home wrapping of in macro able run given filename macro parameter; once have done have entire macro operates little no work import entire excel libname.