macro for importing multiple excel sheets and multiple tabs within each excel sheet different names

[sms1891]

Hi experts,

I am looking for some advice on how to import several excel sheets and  multiple tabs within each excel sheet  into SAS. I have used a macro before (attached SAS code here) but I had only one tab for each excel sheet. I found another SAS code (second code attached here) to import multiple tabs from one excel sheet, but it is importing only the first tab. Ideally I am looking for a combination of both...I need to import several excel sheets and data from multiple tabs within each excel sheet. I am also attaching excel sheets for example. Thank you in advance

%macro import (df, outname);
proc import datafile="&df"
     out=&outname
     dbms=xlsx replace; 
    getnames=yes;
run;
%mend import;

%import (C:\Users\sm\Downloads\S Files\0h\DPA 0.5 0h\Example.xlsx, Example);
%import (C:\Users\sm\Downloads\S Files\0h\DPA 0.5 0h\Example1.xlsx, Example1);



%macro pim(sheet);
proc import out= DPA_05_1_0h_U7
    datafile = 'C:\Users\sm\Downloads\S Files\0h\DPA 0.5 0h\Example.xlsx'
    dbms = Excel;
    sheet = "&sheet";
    getnames = yes;
run;
%mend pim;
%pim(forces 10s (2));
%pim(moments 10s (2));

for this!

SM

[Kurt_Bremser]

Use LIBNAME XLSX for a workbook, and PROC COPY to transfer all sheets into SAS. Once that works for a single workbook file, wrap it into a macro which you can CALL EXECUTE from a DATA step that finds the files. 

[Tom]

If you know the filename (workbook), sheetname and target dataset name for each worksheet then a macro with THREE input parameters should do what you want.

%macro import (workbook,outname,sheetname);
proc import datafile="&workbook" dbms=xlsx out=&outname replace ;
  sheetname="&sheetname";
run;
%mend import;

You could even make it smart enough to not require the sheetname. 

%macro import (workbook,outname,sheetname);
proc import datafile="&workbook" dbms=xlsx out=&outname replace ;
%if %length(&sheetname) %then %do;
  sheetname="&sheetname";
%end;
run;
%mend import;

In such cases it will just find the first worksheet in the workbook, like the macro you posted.

[sms1891]

Hi Tom,

Thanks for the reply. I am getting an error with the code.  If my workbook name is Example and the sheet name is forces.

Thanks,

SM

%macro import (workbook,outname,sheetname);
proc import datafile="&workbook" dbms=xlsx out=&outname replace ;
  sheetname="&sheetname";
run;
%mend import;
%import (C:\Users\sm\Downloads\Example.xlsx, forces, Example);

[Tom]

Always check the documentation.  I think the statement you want to have the macro generate is SHEET= not SHEETNAME=.  

But if the sheet is named forces why did you ask it to look for a sheet named Example?

Remember you can pass parameter values by NAME even when the macro is defined to also allow you pass them by position.

%import
(workbook=C:\Users\sm\Downloads\Example.xlsx
,sheetname=forces
,outname=Example
);

[Patrick]

Here some sample code to build upon for what @Kurt_Bremser suggested.

libname src xlsx "c:\temp\Example.xlsx";
proc datasets lib=src nolist; 
  copy in=src out=work;
  run;
quit;
libname src clear;