There's an ampersand in my file name! HELP!

[SnippyMcPhail]

So I'm trying to pull in an excel file from the company directory. Unfortunately there is a an "&" in the file name that I'm unable to alter. Its near the root of the structure and I don't have access below that point to store files.

Normally, the solution would be to use single quotes and directly quote the file name. I need to be able to use macro variables to automate a process of bringing in a file that is of the previous month.

How can I successfully pull this file in without SAS assuming that C is a macro variable?

/*Import File For Previous Month */
proc import out=os.input
	datafile = "\\cmutual.com\fsroot\MPR\MSG\MP&C\EAGLE\Onsite Marketing Reporting\Onsite Monthly Reports\&short_year.\Input\Onsite_Lift_Monthly_&yyyymm..xls"
	DBMS=Excel2000 REPLACE; 
	Sheet = "ALL";
run;

 

[ChrisW75]

Tricky.
There are a few functions you can use to mask special characters in macro code.
%NRQUOTE and %NRSTR both mask the & character, but I think they can only be used in maco code.
Therefore, I think the best thing to do would be to resolve your macro variables early.
Something like this

%let dfl = '\\cmutual.com\fsroot\MPR\MSG\MP&C\EAGLE\Onsite Marketing Reporting\Onsite Monthly Reports\%NRQUOTE(&short_year.)\Input\Onsite_Lift_Monthly_%NRQUOTE(&yyyymm.).xls';

This will allow your macro variable C to resolve correctly, then you can use the macro variable in place of the filename...

/*Import File For Previous Month */
proc import out=os.input
    datafile = &DFL    DBMS=Excel2000 REPLACE; 
    Sheet = "ALL";
run;

I think that should work.


Chris
Business Analyst, Code Monkey, Data Wrangler.
SAS Guru.

 

[SnippyMcPhail]

The single quotes cause the string to be read "as is". So its looking for a file name with %NRQUOTE.

If you change it to double quotes, it goes right back to insisting that it cannot resolve C.

Would this be easier if I macro coded instead of just going straight in like I am?