Importing an excel file using macros

[Lisa31]

I need to import an Excel file with 4 sheets in it using MACROS in sas

[PaigeMiller]

Why are macros needed for this task?

[Lisa31]

I have been asked to use macros

[PeterClemmensen]

First, write 4 Proc Import steps with 4 different Sheet = Options. If you want to do this using a macro, do it from there. And ask again and post the code you have written if you run into trouble 🙂

[Lisa31]

We dont have to hardcode the sheet name

[Lisa31]

%macro import_macro(x=,y=); proc import datafile-&x |dbms=xlsx out=&y replace;

run;

%mend import_macro;

%import_macro(x='path',y-Excelname);


This is reading only the first sheet of the excel file

[PaigeMiller]

Part of learning a tool (like macros) is to know when NOT to use it. This isn't a great place to use it, but if you are going to create macros, you MUST create working SAS code without macros. If you don't have working SAS code without macros, then it simply will not work when you turn it into a macro. Most people (including you) ignore the advice to create working code without macros first. Please do things the proper way and the correct way and the way that will most likely produce positive results, by creating working code without macros first.

---

@Lisa31 wrote:
%macro import_macro(x=,y=); proc import datafile-&x |dbms=xlsx out=&y replace;
run;
%mend import_macro;
%import_macro(x='path',y-Excelname);

---

You don't have working code here. You need to straighten that out first, before writing a macro. This part will NEVER work, whether or not it is in a macro, or not: datafile-&x

Why does it only read the first sheet? Again, creating working code without macros will get you there. Then you can turn it into a macro if you desire.

[Kurt_Bremser]

To make source and target dynamic, wrap the code into a macro:

%macro imp_excel(source,target);

libname inex xlsx "&source.";

proc copy
  in=inex
  out=&target. /* target library */
;
run;

libname inex clear;

%mend;

[Kurt_Bremser]

In this case I recommend to use LIBNAME XLSX and PROC COPY:

libname inex xlsx "/path/filename.xlsx";

proc copy
  in=inex
  out=target /* target library */
;
run;

libname inex clear;

The code will copy all sheets in the Excel file to the target library.

Once you have this code running without issues, identify the parts that need to be dynamic, replace those with macro variables, and set the macro variables with %LET before testing again.

Then wrap the code in a macro definition where you set the macro variables as parameters.

[PaigeMiller]

@Lisa31 

So essentially, the solution from @Kurt_Bremser reads all tabs in the Excel file, regardless of what the tab names are, and turns them into SAS data sets. So no macro is needed. And therefore, the complications of writing working macros can be avoided, which in my mind is always a better solution.