Error 22-322 with a CAT's

[edge900rr]

I have the following let statements that are causing an error 22-322: 

%let Excel_Sheet_Name = cat("'",&Fleet_Type, " P/N 12 MOnth Rolling'");
%let Excel_File_Name = cat("'",&Fleet_Type, " MTBR.xml'");

44 ods tagsets.excelxp path='\\Cmhprdfps22\data\MAINTENANCE\Reliability\Program Analyst Data\Dave P\SAS AutoBatch Exports\'
45 file=&Excel_File_Name
46 style=Grayscaleprinter;
NOTE: Line generated by the macro variable "EXCEL_FILE_NAME".
46 cat("'","CE-560XL", " MTBR.xml'")
2 The SAS System 10:54 Tuesday, August 21, 2018

___
22
76
ERROR 22-322: Syntax error, expecting one of the following: DYNAMIC, NO_BOTTOM_MATTER, NO_TOP_MATTER, TITLE, URL.
ERROR 76-322: Syntax error, statement will be ignored.
47
48 ods tagsets.ExcelXP options(sheet_label=' ' sheet_name=&Excel_Sheet_Name autofilter='yes');
NOTE: Line generated by the macro variable "EXCEL_SHEET_NAME".
48 cat("'","CE-560XL", " P/N 12 MOnth Rolling'")
___
22
202
ERROR 22-322: Expecting a quoted string.
ERROR 202-322: The option or parameter is not recognized and will be ignored.

thanks for the help.

[Astounding · Accepted solution]

Macro language does not typically use CAT to combine strings.  In fact, if you want to use CAT in macro language, you need to enclose it within %SYSFUNC.  Easier to read (once you are used to it), and able to overcome the need to resolve a macro variable in single quotes, might be:

%let Excel_Sheet_Name = %unquote(%str(%'&Fleet_Type P/N 12 MOnth Rolling%'));

Within %STR, refer to single quotes with a percent sign:  %'

[edge900rr]

Sweet.  Thanks!!!

[error_prone]

Cat is a data-step function, if you want to use it in a %let statement you have to wrap it in %sysfunc. If you want to concatenate strings in macro code you don't have to use cat at all. And, please avoid quotes in macro variables, add them when using the variable. Try:

%let Excel_Sheet_Name = &Fleet_Type. P/N 12 Month Rolling;
%let Excel_File_Name = &Fleet_Type. MTBR.xml;

And later: file= "&Excel_File_Name."
Same for the sheet-name option.

[Kurt_Bremser]

After the macro variable is resolved, you get this:

file=cat("'",&Fleet_Type, " P/N 12 MOnth Rolling'")

which is obviously invalid.

To use a data step function in a %let, you need to use %sysfunc. Also note that concatenation in macro is done simply by writing text in sequence:

&fleet_type._rest_of_the_filename

Also note that blanks in filenames are bad. Use underlines instead.