Why are macros needed for this task?
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 🙂
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.
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;
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.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: