Concatenation of Quotes and Strings in Macros

Actuary — Tue, 01 Sep 2020 23:10:13 GMT

I am attempting to use PROC EXPORT to output many Excel workbooks with the following code:

%let dataset = time_off;
proc export
	data=&dataset
	dbms=xlsx
	outfile="C:\Datasets\R&R\Output\&dataset"
	replace;
run;

Unfortunately, it is necessary for the OUTFILE path to contain an ampersand which generates a warning upon execution.

WARNING: Apparent symbolic reference R not resolved.

I am trying to get around this by building the path using macros to combine quotes and strings. For example:

%let dataset = time_off;
%let path = C:\Datasets\R&R\Output\&dataset;
proc export
	data=&dataset
	dbms=xlsx
	outfile=&path
	replace;
run;

This generates the following error because, I assume, quotes are missing from the value for OUTFILE.

24         %let dataset = time_off;
25         %let path = C:\Datasets\R&R\Output\&dataset;
WARNING: Apparent symbolic reference R not resolved.
26         proc export
27         	data=&dataset
28         	dbms=xlsx
NOTE: PROCEDURE EXPORT used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      
WARNING: Apparent symbolic reference R not resolved.
NOTE: The SAS System stopped processing this step because of errors.
29         	outfile=&path
30         	replace;
NOTE: Line generated by the macro variable "PATH".
30         C:\Datasets\R&R\Output\time_off
            _
            22
            76
ERROR 22-322: Syntax error, expecting one of the following: ;, DATA, DBLABEL, DBMS, DEBUG, FILE, LABEL, OUTFILE, OUTTABLE, REPLACE, 
              TABLE, _DEBUG_.  

ERROR 76-322: Syntax error, statement will be ignored.

Therefore, I am trying to programmatically build a string using macros and concatenation with the code shown below.

%let dataset = time_off;
%let path = %sysfunc(cat("'",C:\Datasets\R&R\Output\,&dataset,"'"))

This, however, produces these errors:

24         %let dataset = time_off;
25         %let path = %sysfunc(cat("'",C:\Datasets\R&R\Output\,&dataset,"'"));
WARNING: Apparent symbolic reference R not resolved.
WARNING: Apparent symbolic reference R not resolved.
NOTE: Line generated by the macro function "SYSFUNC".
25          "'"C:\Datasets\R&R\Output\time_off"'"
            ___
            49
WARNING: Apparent symbolic reference R not resolved.

NOTE 49-169: The meaning of an identifier after a quoted string might change in a future SAS release.  Inserting white space 
             between a quoted string and the succeeding identifier is recommended.


26         proc export
27         	data=&dataset
28         	dbms=xlsx
NOTE: PROCEDURE EXPORT used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      
WARNING: Apparent symbolic reference R not resolved.
NOTE: The SAS System stopped processing this step because of errors.
29         	outfile=&path
30         	replace;
NOTE: Line generated by the macro variable "PATH".
30         "'"C:\Datasets\R&R\Output\time_off"'"
           ___
           49
              _
              22
              76
NOTE 49-169: The meaning of an identifier after a quoted string might change in a future SAS release.  Inserting white space 
             between a quoted string and the succeeding identifier is recommended.
2                                                          The SAS System


ERROR 22-322: Syntax error, expecting one of the following: ;, DATA, DBLABEL, DBMS, DEBUG, FILE, LABEL, OUTFILE, OUTTABLE, REPLACE, 
              TABLE, _DEBUG_.  

ERROR 76-322: Syntax error, statement will be ignored.

31         run;

Ultimately what I am trying to do is build the following string as the argument for OUTFILE:

'C:\Datasets\R&R\Output\time_off'

I would appreciate any assistance that could be provided.

Re: Concatenation of Quotes and Strings in Macros

PaigeMiller — Tue, 01 Sep 2020 23:40:16 GMT

You want to use the %nrstr() function.

Example:

https://documentation.sas.com/?docsetId=mcrolref&docsetTarget=p0pnc7p9n4h6g5n16g6js048nhfl.htm&docsetVersion=9.4&locale=en (scroll down)

Re: Concatenation of Quotes and Strings in Macros

andreas_lds — Wed, 02 Sep 2020 04:56:57 GMT

This "problem" only exists, because you are using special chars in a directory name, so the solution is quite simple: don't do that.

Re: Concatenation of Quotes and Strings in Macros

Astounding — Thu, 03 Sep 2020 03:01:36 GMT

It might be easier if you build the string in a data step rather than with macro language, such as:

data _null_;
outfile = '''C:\Datasets\R&R\Output\' || "&dataset'";
call symputx('outfile', outfile);
run;

The macro variable &OUTFILE should be useful in your existing PROC EXPORT.

Re: Concatenation of Quotes and Strings in Macros

Kurt_Bremser — Thu, 03 Sep 2020 06:45:14 GMT

Don't use special symbols in directory names; the ampersand is particularly bad, because it has special meaning (not only in SAS).

Re: Concatenation of Quotes and Strings in Macros

Actuary — Fri, 04 Sep 2020 20:53:23 GMT

Thank you on two accounts, first for properly interpreting the problem I faced and second for providing this elegant solution. Well done.

topic Re: Concatenation of Quotes and Strings in Macros in SAS Programming

Concatenation of Quotes and Strings in Macros

Re: Concatenation of Quotes and Strings in Macros

Re: Concatenation of Quotes and Strings in Macros

Re: Concatenation of Quotes and Strings in Macros

Re: Concatenation of Quotes and Strings in Macros

Re: Concatenation of Quotes and Strings in Macros