topic Re: How to pass multiple words parameters into a macro variable in SAS Programming

How to pass multiple words parameters into a macro variable

jayakumarmm — Mon, 05 Sep 2016 12:31:16 GMT

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;

Re: How to pass multiple words parameters into a macro variable

Reeza — Mon, 05 Sep 2016 05:31:38 GMT

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

Re: How to pass multiple words parameters into a macro variable

jayakumarmm — Mon, 05 Sep 2016 12:32:16 GMT

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;

Re: How to pass multiple words parameters into a macro variable

Reeza — Mon, 05 Sep 2016 11:49:00 GMT

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.

Re: How to pass multiple words parameters into a macro variable

error_prone — Mon, 05 Sep 2016 11:57:30 GMT

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?

Re: How to pass multiple words parameters into a macro variable

jayakumarmm — Mon, 05 Sep 2016 12:29:46 GMT

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;

Re: How to pass multiple words parameters into a macro variable

Shmuel — Mon, 05 Sep 2016 15:19:33 GMT

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);

Re: How to pass multiple words parameters into a macro variable

Reeza — Mon, 05 Sep 2016 16:23:18 GMT

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.