Help using Base SAS procedures

file name contains &

Occasional Contributor
Posts: 14

file name contains &

Help please.

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


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 &.
Posts: 9,365

Re: file name contains &

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:
datafile ="C:\temp\%nrstr(PT&TT.xls)"

The documentation is here:

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.

Occasional Contributor
Posts: 14

Re: file name contains &

Posted in reply to Cynthia_sas
Thanks for your help.

I am still at beginner level.
This forum is such a good place, it helps me a lot.
Super User
Posts: 13,498

Re: file name contains &

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.
Ask a Question
Discussion stats
  • 3 replies
  • 3 in conversation