topic Re: Trouble with folder macro in New SAS User

Trouble with folder macro

bm343 — Mon, 24 Feb 2025 20:18:26 GMT

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!

Re: Trouble with folder macro

PaigeMiller — Mon, 24 Feb 2025 20:17:51 GMT

@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

Re: Trouble with folder macro

bm343 — Mon, 24 Feb 2025 20:20:21 GMT

that seems to have resolved it, thank you!

Re: Trouble with folder macro

Tom — Mon, 24 Feb 2025 21:50:13 GMT

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)