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.
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.
You want to use the %nrstr() function.
Example:
https://documentation.sas.com/?docsetId=mcrolref&docsetTarget=p0pnc7p9n4h6g5n16g6js048nhfl.htm&docse... (scroll down)
This "problem" only exists, because you are using special chars in a directory name, so the solution is quite simple: don't do that.
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.
Thank you on two accounts, first for properly interpreting the problem I faced and second for providing this elegant solution. Well done.
Don't use special symbols in directory names; the ampersand is particularly bad, because it has special meaning (not only in SAS).
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.