How to pass multiple words parameters into a macro variable

[jayakumarmm]

I need to pass multiple words parameter into a variable. In the below code highlighted areas are the affected ones.

%let local_Filename = File1 File2 'File3 File4'n
%let Excel_table = Sheet1 Sheet2 'Third Sheet'n
%let SAS_DATASET = Data1 data2 data3

%macro file_process;
%let file_name=%qscan(%bquote(&local_Filename),&i);
%let dataset=%qscan(%bquote(&SAS_DATASET),&i);
%let table=%qscan(%bquote(&Excel_table),&i);
libname xlsFile XLSX "/user/(&file_name only).xlsm";
PROC SQL;
create table work.&dataset as 
(select * from xlsFile.'&table'n);
run;
%end;
%mend file_process;

[Reeza]

Highlighting doesn't appear in a code block. You'll need to identify the issues another way. 

[jayakumarmm]

Gvien below is the code. Multiple words parameter(highlighted) are not getting resolved it is passed differently.

1. Excel_table parameters reolves lik below which is throwing an error as well.

ERROR: File XLSFILE.'&table'n.DATA does not exist.

2. File_name is also resolved incorrectly like this - /user/2016 July 'File3.xlsm

CODE:

%let local_Filename = File1 File2 'File3 File4'n
%let Excel_table = Sheet1 Sheet2 'Third Sheet'n
%let SAS_DATASET = Data1 data2 data3

%macro file_process;
%let file_name=%qscan(%bquote(&local_Filename),&i);
%let dataset=%qscan(%bquote(&SAS_DATASET),&i);
%let table=%qscan(%bquote(&Excel_table),&i);
libname xlsFile XLSX "/user/(&file_name only).xlsm";
PROC SQL;
create table work.&dataset as
(select * from xlsFile.'&table'n);
run;
%end;
%mend file_process;

[Reeza]

Fix one issue at a time. Libname first...

I'm guessing your third parameter in file list is the issue. 

Do you actually have () inyour file path? And single quotes with the n? 

Remember macro are a literal text replacement. As mentioned previously, run with debugging options on. 

What does that line resolve to with MPRINT and SYMBOLGEN on?

Please show the log. 

[jayakumarmm]

I have attached the log. I dont have any single quotes in filename.

%let local_Filename = File1 File2 'File3 File4'n;
%let Excel_table = Sheet1 Sheet2 'Third Sheet'n;
%let SAS_DATASET = Data1 data2 data3;
options SYMBOLGEN MPRINT;
%macro file_process;
%let word_cnt = %sysfunc(countW(&local_Filename));
%do  i = 1 %to &word_cnt;
%let file_name=%qscan(%bquote(&local_Filename),&i);
%let dataset=%qscan(%bquote(&SAS_DATASET),&i);
%let table=%qscan(%bquote(&Excel_table),&i);
libname xlsFile XLSX "/user/&file_name only.xlsm";
PROC SQL;
create table work.&dataset as
(select * from xlsFile.'&table'n);
run;
%end;
%mend file_process;
%file_process;

[Reeza]

 Is this really your libname? Based on the macro variable it's going to resolve to:

%let local_Filename = File1 File2 'File3 File4'n;

i=1

libname xlsFile XLSX "/user/File1 only.xlsm";

 i=2

libname xlsFile XLSX "/user/File2 only.xlsm";

i=3

libname xlsFile XLSX "/user/'File3 only.xlsm";

Note the single quote and that since SCAN using space as a delimiter it uses just the File3 part.

How should this be resolved. 

If you do a similar exercise with your remaining macro variables you'll find your issues. It's tedious but how you debug. 

[error_prone]

• you need different separators to distinguish different names and specify that separator when calling scan-function
• macro-variable i is not defined
• your macro defintion lacks parameters, you are using global variables
• what is the purpose of the macro?

[Shmuel]

Just relating to one error message:

ERROR: File XLSFILE.'&table'n.DATA does not exist

uset double quotes in order to solve the &table macro variable:

that is - change line to:

create table work.data2 as (select * from xlsFile."&table"n);