topic Re: file name contains & in SAS Procedures

file name contains &

piggy — Sat, 02 Oct 2010 07:14:44 GMT

Help please.

proc import
out=mailling_working
datafile ="C:\sample\PT&TT.xls";
dbms =excel replace;
sheet = "All Mailed";
mixed =yes;

run;

Since the file PT&TT.xls contains &, SAS would think &TT.xls is macro variable. This problem can be easily solved by changing the file name, but any suggestion to solve this problem without changing the file name? Because I have lots of files contain &.

Re: file name contains &

Cynthia_sas — Sat, 02 Oct 2010 16:03:01 GMT

Hi:
If you read up on SAS Macro processing, you will find out that there are special "Macro Quoting Functions" that protect or hide the normal meaning of special characters, such as % and &. (And do quoting, unquoting, protection at compile time, protection at execution time, etc, etc -- it's a long list.)

Your code -- a single PROC IMPORT step -- without any other macro variable references -- only needs a simple level of protection. The %NRSTR function will do what you want to do -- for the code that you've shown. The correct way to use the %NRSTR function is:
[pre]
datafile ="C:\temp\%nrstr(PT&TT.xls)"
[/pre]

The documentation is here:
http://support.sas.com/documentation/cdl/en/mcrolref/61885/HTML/default/viewer.htm#z3514str.htm

In the long run, it might be better to avoid the use of & and % especially in the names of files, because you will continue to have this problem and need to work around it. And eventually, if you -do- need to use & and/or % in their context as macro triggers (such as when you might need to write a macro program), having the & in the file name just complicates things.

cynthia

Re: file name contains &

piggy — Sat, 02 Oct 2010 20:32:00 GMT

Thanks for your help.

I am still at beginner level.
This forum is such a good place, it helps me a lot.

Re: file name contains &

ballardw — Tue, 05 Oct 2010 16:24:16 GMT

A simplier solution IF you do not need to resolve any macro variables elsewhere within the datafile name is to use a single quote ' instead of double quotes ".

Unlike many programming languages SAS supports both types of quotes with the difference involving macro variable resolution.