Trouble with folder macro

[bm343]

Hi,

I'm a novice at SAS macro and am having some issues writing a function which does the following:

takes 3 inputs

1. dataset name (e.g. from a data step)

2. a folder directory where all of various excel outputs will be stored

3. the desired excel file name for the dataset 

I have currently written my function as follows:

%make_excel(dataset= , output_path = , filname = );

ods excel file = "&output_path.\&filename..xlsx";

proc report data= &dataset.;

run;

ods excel close; 

%mend make_Excel

Presently if I call the function with something like:

%make_excel(dataset= ds, output_path = C:\Users\XXXXX, filename = YYYYY) 

everything works fine, which is great. But I need to call the function a bunch of times and I don't want to constantly copy/paste the output path. So I'm trying to set a macro variable such as:

%let out_tmp = C:\Users\XXXXX;

And then call the function by

%make_excel(dataset=ds, output_path = %out_tmp, filename = YYYYYYY)

However, when I am trying this I keep getting an error in the long:

"Error: physical file does not exist, C:\Windows\systsem32\%out_tmp\YYYYY.xlsx"

ChatGPT and google have unfortunately been no help so far. Any idea why this might be occurring?

Thanks!

[PaigeMiller · Accepted solution]

---

@bm343 wrote:

And then call the function by

make_excel(dataset=ds, output_path = out_tmp, filename = YYYYYYY)

---

There are two errors here, it should be

%make_excel(dataset=ds, output_path = &out_tmp, filename = YYYYYYY)

To call any macro, you must use a % before the macro name, so here it must be %make_excel 

To use any macro variable, you have to use an & before the macro variable name, so here it must be &out_tmp

[bm343]

that seems to have resolved it, thank you! 

[Tom]

Your posted code for defining the macro is not correct as the %MACRO statement is wrong.  But perhaps that is just a typo since you also say it works.

%macro make_excel(dataset= , output_path = , filname = );
ods excel file = "&output_path.\&filename..xlsx";
proc report data= &dataset.;
run;
ods excel close; 
%mend make_excel;

You are not referencing the macro variable OUT_TMP properly in your macro call.  You used % instead of &.  The % character is used to reference macros statements (like the %MACRO and %MEND in your code) and actual macro functions (like %SUBSTR() and %SCAN()) in addition to being used to reference macros (like %MAKE_EXCEL in your program). The  & character is used when you need to reference a macro variable (also known as a symbol).  You are using the & properly to reference the macro variables correctly in the body of your macro.

Here is the fixed macro call:

%make_excel(dataset=ds, output_path = &out_tmp, filename = YYYYYYY)