BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
emelergul
Calcite | Level 5

Hi there- puzzled with the macro I frequently use w/o any issues.

Anything you catch that is causing err? I have multiple sheets in a spreadsheet and want to import them. This is a really simple task.

Thank you in advance.

 

 

%Let dir =C:\Users\myproject\;

%let data_in= clinsub;

 

%MACRO clin_sub(sheet);

Proc import

DATAFILE = "&dir.clinsub"

DBMS = xlsx

OUT = clinsub  REPLACE;

SHEET ="&sheet";

GETNAMES = YES

RUN;

%MEND clin_sub;

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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 solution in original post

2 REPLIES 2
Kurt_Bremser
Super User

Whenever you have an ERROR, we must see your log. Post the complete (all code and messages) by copy/pasting it into a window opened with this button:

Bildschirmfoto 2020-04-07 um 08.32.59.jpg

Tom
Super User Tom
Super User

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;

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 2 replies
  • 527 views
  • 1 like
  • 3 in conversation