BookmarkSubscribeRSS Feed
jayakumarmm
Quartz | Level 8

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;

 

7 REPLIES 7
Reeza
Super User

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

jayakumarmm
Quartz | Level 8

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
Super User

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
Quartz | Level 8

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
Super User

 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
Barite | Level 11
  • 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
Garnet | Level 18

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

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 5192 views
  • 0 likes
  • 4 in conversation