topic Re: macro for importing multiple excel sheets and multiple tabs within each excel sheet different na in SAS Programming

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

sms1891 — Mon, 14 Oct 2024 12:23:08 GMT

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!

Re: macro for importing multiple excel sheets and multiple tabs within each excel sheet different na

Kurt_Bremser — Mon, 14 Oct 2024 12:46:33 GMT

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.

Re: macro for importing multiple excel sheets and multiple tabs within each excel sheet different na

Tom — Mon, 14 Oct 2024 12:58:38 GMT

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.

Re: macro for importing multiple excel sheets and multiple tabs within each excel sheet different na

sms1891 — Mon, 14 Oct 2024 17:09:13 GMT

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,

%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);

Re: macro for importing multiple excel sheets and multiple tabs within each excel sheet different na

Tom — Mon, 14 Oct 2024 18:32:15 GMT

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
);

Re: macro for importing multiple excel sheets and multiple tabs within each excel sheet different na

Patrick — Tue, 15 Oct 2024 05:25:48 GMT

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;