DATA Step, Macro, Functions and more

MACRO parameter - Reading File name

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 84
Accepted Solution

MACRO parameter - Reading File name

[ Edited ]

Hi SAS users,

 

I need help with reading a FILENAME from macro parameter & import it.  I am able to read the file if i hardcode it in the program, but macro parameter is not working.

 

%MACRO TESTING(FILENAME=,KEY=,TABLE=);

 

proc import datafile= '/testpath/&FILENAME'    /* this is currently resoving as '/testpath/&FILENAME.xlsx not found */

out=TEST_DATASET
DBMS=XLSX
replace;
run;

 

DataTest_DS;

set TEST_DATASET;

run;

 

%MEND;

 

%TESTING (FILENAME ='TEST_FILE',KEY='GREEN',TABLE=GREEN_TABLE);

 


Accepted Solutions
Solution
‎10-24-2016 07:42 PM
PROC Star
Posts: 1,233

Re: MACRO parameter - Reading File name

I see two issues:

1. In the macro defintion, you should use double quotes around the file path, i.e. "/testpath/&FILENAME".  Macro variables do not resolved inside of single quotes. So currently it is looking for an Excel file that named &FILENAME.xlsx.

2.  When you call the macro,   you should not put quotes around the values you pass to the FileName parameter.  Try:

%TESTING (FILENAME =TEST_FILE,KEY='GREEN',TABLE=GREEN_TABLE);

If you put the quote marks, they become part of the value.  And your PROC EXPORT statement would have extra quote marks, so would look like:  proc import datafile= "/testpath/'TEST_FILE'"

 

One way to check these things is to turn on options MPRINT, which will show in the log the SAS code that is generated by the macro. It's very helpful for debuggin macros.

View solution in original post


All Replies
Solution
‎10-24-2016 07:42 PM
PROC Star
Posts: 1,233

Re: MACRO parameter - Reading File name

I see two issues:

1. In the macro defintion, you should use double quotes around the file path, i.e. "/testpath/&FILENAME".  Macro variables do not resolved inside of single quotes. So currently it is looking for an Excel file that named &FILENAME.xlsx.

2.  When you call the macro,   you should not put quotes around the values you pass to the FileName parameter.  Try:

%TESTING (FILENAME =TEST_FILE,KEY='GREEN',TABLE=GREEN_TABLE);

If you put the quote marks, they become part of the value.  And your PROC EXPORT statement would have extra quote marks, so would look like:  proc import datafile= "/testpath/'TEST_FILE'"

 

One way to check these things is to turn on options MPRINT, which will show in the log the SAS code that is generated by the macro. It's very helpful for debuggin macros.

Frequent Contributor
Posts: 84

Re: MACRO parameter - Reading File name

Small changes but definitely got to learn new things about MACRO's.

Thank you,
Ana
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 309 views
  • 0 likes
  • 2 in conversation