topic Re: MACRO Data import in SAS Programming

MACRO Data import

emelergul — Tue, 21 Mar 2023 20:33:28 GMT

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;

Re: MACRO Data import

Kurt_Bremser — Tue, 21 Mar 2023 20:47:40 GMT

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:

Re: MACRO Data import

Tom — Tue, 21 Mar 2023 21:54:02 GMT

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;