topic Re: Importing an excel file using macros in SAS Programming

Importing an excel file using macros

Lisa31 — Wed, 03 Nov 2021 09:42:50 GMT

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

Re: Importing an excel file using macros

PaigeMiller — Wed, 03 Nov 2021 09:58:36 GMT

Why are macros needed for this task?

Re: Importing an excel file using macros

Lisa31 — Wed, 03 Nov 2021 09:59:31 GMT

I have been asked to use macros

Re: Importing an excel file using macros

PeterClemmensen — Wed, 03 Nov 2021 10:00:47 GMT

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 🙂

Re: Importing an excel file using macros

Lisa31 — Wed, 03 Nov 2021 10:02:03 GMT

We dont have to hardcode the sheet name

Re: Importing an excel file using macros

Lisa31 — Wed, 03 Nov 2021 10:09:44 GMT

%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

Re: Importing an excel file using macros

PaigeMiller — Wed, 03 Nov 2021 10:26:35 GMT

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.

Re: Importing an excel file using macros

Kurt_Bremser — Wed, 03 Nov 2021 10:36:31 GMT

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.

Re: Importing an excel file using macros

PaigeMiller — Wed, 03 Nov 2021 12:11:17 GMT

@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.

Re: Importing an excel file using macros

Kurt_Bremser — Mon, 08 Nov 2021 07:36:14 GMT

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;