BookmarkSubscribeRSS Feed
deleted_user
Not applicable
Can someone please tell me what the escape character in SAS is?

I am trying to run the following program which uses a file name with an "&" character:

%let filenm ='C:\Documents and Settings\Test&Learn.xls';

data initial_list;
proc import datafile=&filenm out=initial_list
dbms=excel replace;
getnames=yes;
run;


This program works successfully, however I get the warning message below each time. While not probelmatic now, if i were to define a macro variable named "learn", it would be.

WARNING: Apparent symbolic reference LEARN not resolved.

What would I have to do to get rid of this warning message?

Thanks in advance!
13 REPLIES 13
Doc_Duke
Rhodochrosite | Level 12
I'm surprised you got that warning message. Did your "real" job use single quotes or double quotes? If a string with a leading "&" is enclosed in double quotes, SAS tries to resolve it as a macro variable. If the same string is in single quotes, it leaves it as is.
deleted_user
Not applicable
The warning message I got did not appear when i executed the "%let" statement. It seemed to accept the "&" because I was using single quotes. The warning showed up when I tried to use that variable (&filenm) in proc import as follows:

419 proc import datafile=&filenm out=initial_list
420 dbms=excel replace;
421 getnames=yes;
422 run;

WARNING: Apparent symbolic reference CONTROL not resolved.
NOTE: .INITIAL_LIST was successfully created.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 14.64 seconds
cpu time 4.07 seconds
Cynthia_sas
SAS Super FREQ
Hi:
Do you really need an & in the filename??? Is that an acceptable character on your operating system for file names? You need to look up Macro Quoting Functions in the documentation for the SAS Macro facility.
cynthia
deleted_user
Not applicable
%let filenm =%NRSTR(C:\Documents and Settings\Test&Learn.xls);
data initial_list;
proc import datafile="&filenm" out=initial_list dbms=excel replace;
getnames=yes;
run;
deleted_user
Not applicable
Cynthia@sas:

I dont have to use an "&", however there can be situations where I do not have a choice and i'm trying to understand how I would deal with this situation. I have read the Macro Quoting Functions documenations but still cannot get around this particular case. I have tried using NRSTR and SUPERQ, however the issue seems to be that i am defining a macro variable and then attempting to use it. Then I try to use it, I get the warning message.

Vasile:

Thanks for the suggestion. However, I tried it and I'm still gettting a warning message when i use proc import.

Any other suggestions? It seems hard to believe there is no work around for this situation.
deleted_user
Not applicable
Try using this before your code:
OPTIONS NOSERROR;
deleted_user
Not applicable
Hi Vasile,

This approach just turns off the errors related to missing macro definitions, it doesnt actually solve the problem. If I had a much larger SAS program and had another macro variable defined as %let learn = 'some variable';. My code would use the 'some variable' value as part of the filenm in the proc import. It would be wrong in that context since what I had intended was to use the literal "&learn" string.

It seems to me there must be a way to prevent this situation from happening.
deleted_user
Not applicable
No, you are not right in ampersand problem -the initial problem.
NOSERROR option just won't issue the SERROR message warning. Message was edited by: vasile
deleted_user
Not applicable
Vasile,

I am not sure what you mean. NOSERROR is defined as

NOSERROR
issues no warning messages when the macro processor cannot match a macro variable reference to an existing macro variable.


meaning, i will not see the warning message if i have this option. however i will not be able to prevent the following situation:

CODE:
OPTIONS NOSERROR;
%let filenm ='C:\Test&Learn.xls';
%let Learn = 'random string';

data initial_list;
proc import datafile="&filenm" out=initial_list
dbms=excel replace;
getnames=yes;
run;

DESIRED OUTPUT:

import data from file "C:\Test&Learn.xls"

ACTUAL OUTPUT:

57 %let filenm ='C:\Test&Learn.xls';
58 %let Learn = 'random string';
60 data initial_list;
61 proc import datafile="&filenm" out=initial_list
62 dbms=excel replace;
63 getnames=yes;
64 run;

ERROR: Unable to import, file 'C:\Test'random string'.xls' does not exist.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 0.11 seconds
cpu time 0.09 seconds
Cynthia_sas
SAS Super FREQ
Hi:
There are several things I don't understand:
1) why you use single quotes when you assign values to your macro variables
2) what the purpose of your data statement is.
[pre]
data initial_list;
[/pre]
Since the DATA statement is immediately followed by a PROC statement, the DATA step program is exactly 1 statement which is immediately terminated by the PROC statement (which is a step boundary).

At any rate....I created an Excel spreadsheet called:
c:\temp\Testrandom string.xls


As you can see from my SAS log, I was able to read the file with no issues:
[pre]
20 %let Learn = random string;
21 %let filenm =C:\temp\Test&Learn..xls;
22
23
24 proc import datafile="&filenm" out=work.initial_list
25 dbms=excel replace;
26 getnames=yes;
27 run;

NOTE: WORK.INITIAL_LIST was successfully created.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 0.43 seconds
cpu time 0.18 seconds

[/pre]

Here are some comments on my code:
1) Because I assign a value to the macro variable LEARN before the assignment of a value to FILENM, there is a value for &LEARN that can be resolved when &FILENM is used.

2) As you can see, I do not use ANY quotes in the creation of my macro variables, nor do I really need them. If the assumption is that &LEARN will always be part of the file name that changes, then it is reasonable to assign a value to &LEARN before you use &LEARN in the creation of FILENM.

3) When concatenating macro variables into a string, there is a "universal" delimiter that tells SAS where a macro variable ends. That delimiter is a dot or period. When you need a period in the resolved string (such as with 2 level SAS names or a full filename/file extension), then the solution is to provide 2 dots, as shown in my assignment for FILENM (note the 2 dots before xls):
[pre]
%let filenm =C:\temp\Test&Learn..xls;

[/pre]

The first dot ends &LEARN and the second dot is the dot that's needed for the full name of the Windows file.

4) It doesn't make sense to me to put quotes around 'random string' because on my version of Windows XP I get an error message when I try to use quotes in a file name. Interestingly, I do NOT get error messages when I try to use an & in a file name, however, I really think that special characters (other than slashes and underscores and spaces) should probably be avoided as file names -- if I ever had to move these files to another operating system, those characters might not be allowed.

5) Back to the DATA statement issue. Assume that I do have a valid macro variable for &FILENM...even so, if I did this
[pre]
%let Learn = random string;
%let filenm =C:\temp\Test&Learn..xls;

data trythis;
proc import datafile="&filenm" out=work.trythis
dbms=excel replace;
getnames=yes;
run;

[/pre]

what would happen is this -- note the message that the dataset has 1 observation and 0 variables -- essentially, I have created an empty dataset that is pretty much useless. It is the PROC IMPORT that is creating the actual data set from the Excel file.
[pre]
46 data trythis;

NOTE: The data set WORK.TRYTHIS has 1 observations and 0 variables.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

47 proc import datafile="&filenm" out=work.trythis
48 dbms=excel replace;
49 getnames=yes;
50 run;

NOTE: WORK.TRYTHIS was successfully created.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 0.46 seconds
cpu time 0.29 seconds

[/pre]

6) Meanwhile, back at Macro variables, it looks like you really don't need quoting functions, either. Here are some examples of macro variable creation that may help you learn more about how macro variables operate. If you cut and paste this code into an editor window and submit the code, you can review what you get in the SAS log and for the title statements in the PROC PRINT output:
[pre]
%let statement = My favorite Sesame Street;
%let kf = Kermit the Frog;
%let cm = Cookie Monster;
%let bb = Big Bird;
%let num = 2;
%let mac1 = &statement characters are &kf and &cm and &bb!;
%let mac2 = But my most favorite character is &kf!;
%put ----- -----;
%put My opinion is: &mac1 &mac2;
%put ----- -----;
%put And I say num is &num: &&mac#
%put ----- -----;


%let lib=sashelp;
%let dsn=class;
proc print data=&lib..&dsn;
title "Proc Print for &lib..&dsn";
title2 "&mac1";
title3 "&&mac&num";
run;
[/pre]

cynthia
deleted_user
Not applicable
I think i'm not succeeding in my explaination, let me try agian.

Cynthia:

Thank you for the detailed response.

the point i'm trying to make is that I DO NOT have a file name "c:\temp\Testrandom string.xls", I have a file named "c:\temp\Test&Learn.xls". I have to use this file name.

In my previous post I am trying to say that I have code that works but it produces warning messages. I am concerned b/c if my code were ever to be incorporated into a larger SAS program which just happened to declare a marco variable called "Learn". Then, in that case, my code would NOT work properly. I want to write my code such that this would NEVER be possible. This is why i am asking about escape characters.

I want to have the following code and i want it to use "C:\temp\Test&Learn.xls", is this possible?

%let Learn = "random string";

...more SAS code...

%let filenm = "C:\temp\Test&Learn.xls";

/* &filenm should resolve to C:\temp\Test&Learn.xls */
proc import datafile="&filenm" out=initial_list
dbms=excel replace;
run;


if i run this program as is, the program will try to import from "C:\temp\Testrandomstring.xls". This is NOT what i want to hapen. I want proc import to resolve &filenm to "C:\temp\Test&Learn.xls". Does this make sense?

answers to your questions: 1 - i use single and double quotes interchagebly, this is probably in appropriate, i will work on it. 2 - you are right, the data step is unnecessary, i have taken it out.
rab24
Calcite | Level 5
The answer to your original question for escape characters would be the str() or nrstr() functions around your macro definition
%let filenm =%nrstr(C:\Documents and Settings\Test&Learn.xls);

But your first code works for me without any trouble as you have it written. Your last code seems to have a problem because it is calling three quotes in a row (single quote around macro definition and double quotes around the macro call). All I did to make the following work was remove the single quotes from the macro call at datafile=.

%let filenm = '/operations/FRAG/AQD&test.csv';
%let test = 'random string';

proc import datafile=&filenm out=initial_list
dbms=csv replace;
getnames=yes;
run;

While this solution works, %nrstr() is what you need if you ever want to call your macro as part of a title name or anywhere that has to be inside double quotes.

BTW, is there an advantage to creating an empty dataset first?
BobC
Calcite | Level 5

I believe that you can fix this problem with the following steps:

(1) use SERROR, *not* NOSERROR, as the latter only maskes problems you may have; besides, if everything works as it should, you will not get a message - the presence of a message just tells you that you are doing something wrong

 

(2) instead of

%let filenm = '/operations/FRAG/AQD&test.csv';

use

%let filenm = %NRSTR(/operations/FRAG/AQD&test.csv); /* NRSTR "escapes" almost everything - see the manual for exceptions */

 

(3) instead of

 

proc import datafile=&filenm out=initial_list 
   dbms=csv replace;

use:

 

proc import datafile=%NRBQUOTE(&filenm) /* %NRBQUOTE escapes the expansion of &filenm here */
   out=initial_list dbms=csv replace;

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

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
  • 13 replies
  • 36283 views
  • 9 likes
  • 5 in conversation