The code you posted does nothing since it only defines the macro and does not call the macro.
The macro has some coding issues:
There is no semicolon to end the GETNAMES statement.
The macro has some logic issues.
It always creates the same output dataset. So if you call it multiple times only the result of the last call will be available.
It uses a "magic" macro variable. In the middle of the macro you reference the macro variable named DIR which is not an input to the macro. So the macro is just assuming the macro variable DIR will appear as if by magic out of thin air.
So perhaps you wanted a macro like this:
%macro import_xlsx
(sheet
,file
,dataset
,dir=C:\Users\myproject\
);
proc import
DATAFILE = "&dir.&file"
DBMS = xlsx
OUT = &dataset REPLACE
;
SHEET ="&sheet";
GETNAMES = YES;
RUN;
%mend import_xlsx;
Which you could then call multiple times:
%import_xlxs(file=clinsub.xlsx,sheet=sheet1,dataset=first);
%import_xlxs(dataset=second,file=clinsub.xlsx,sheet=sheet2);
But if you want copy multiple sheets out of an XLSX file then just use the XLSX libname engine and skip the macro completely.
libname myxlsx xlsx "C:\Users\myproject\clinsub.xlsx";
proc copy inlib=myxlsx out=work;
run;
... View more