BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
SASAna
Quartz | Level 8

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);

 

1 ACCEPTED SOLUTION

Accepted Solutions
Quentin
Super User

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.

BASUG is hosting free webinars Next up: Mike Raithel presenting on validating data files on Wednesday July 17. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.

View solution in original post

2 REPLIES 2
Quentin
Super User

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.

BASUG is hosting free webinars Next up: Mike Raithel presenting on validating data files on Wednesday July 17. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.
SASAna
Quartz | Level 8
Small changes but definitely got to learn new things about MACRO's.

Thank you,
Ana

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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