DATA Step, Macro, Functions and more

How to pass multiple words parameters into a macro variable

Reply
Contributor
Posts: 55

How to pass multiple words parameters into a macro variable

[ Edited ]

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;

 

Super User
Posts: 17,828

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

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

Contributor
Posts: 55

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

[ Edited ]

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;

Super User
Posts: 17,828

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

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. 

Contributor
Posts: 55

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

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;
Super User
Posts: 17,828

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

 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. 

Contributor
Posts: 33

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

  • 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?

 

Trusted Advisor
Posts: 1,378

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

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

 

Ask a Question
Discussion stats
  • 7 replies
  • 884 views
  • 0 likes
  • 4 in conversation