Pass in quoted string list with space in macro

[qw213]

Hi,

I am trying to read in selected tabs from a excel file and append them into one dataset. I have created a macro variable called TabList where the values are quoted strings with spaces in them (e.g. "aaa bbb","bbb ccc"...) as this is how the sheets are named. I am having trouble passing the list in macro. I am getting the following error with the code below:

ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric operand is required.

Thank you for your help!

proc sql;
select Tab into :TabList separated by ","
from mapping;
quit;

%macro process;
%let n=%sysfunc(countw("&TabList.", ',' ));
%put &n;
%do i=1 %to &n;
%let val = %scan(&TabList.,&i);

%put &val &i &n;

OPTION VALIDVARNAME=V7;
PROC IMPORT DATAFILE= &in_dir.
OUT= data
DBMS=XLSX
REPLACE;
SHEET= &val;
GETNAMES=NO;
datarow=3;
RUN;

%end;
%mend;

%process;

[Reeza]

Use a libname instead?

libname myXL xlsx 'path to file';



proc copy in=myXL out=work;

run;

---

@qw213 wrote:

Hi,

I am trying to read in selected tabs from a excel file and append them into one dataset. I have created a macro variable called TabList where the values are quoted strings with spaces in them (e.g. "aaa bbb","bbb ccc"...) as this is how the sheets are named. I am having trouble passing the list in macro. I am getting the following error with the code below:

ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric operand is required.

Thank you for your help!

proc sql;
select Tab into :TabList separated by ","
from mapping;
quit;

%macro process;
%let n=%sysfunc(countw("&TabList.", ',' ));
%put &n;
%do i=1 %to &n;
%let val = %scan(&TabList.,&i);

%put &val &i &n;

OPTION VALIDVARNAME=V7;
PROC IMPORT DATAFILE= &in_dir.
OUT= data
DBMS=XLSX
REPLACE;
SHEET= &val;
GETNAMES=NO;
datarow=3;
RUN;

%end;
%mend;

%process;

---

[qw213]

Thank you for your response. I don't have issue reading in the sheets. The issue mainly was that I have to loop through the wanted sheets and perform a few operations, but I can't pass the list with quoted strings into the macro function.

Specifically I can't even count the number of strings correctly to begin with.

[SuryaKiran · Accepted solution]

You don't have to put quotes, just separate by comma and then extract the sheet name using SCAN with delimiter as comma and mention the macro for sheet name in quotes.

%let TabList=aaa bbb,bbb ccc;


%macro process;
%let n=%sysfunc(countw("&TabList.", ',' ));
%put &n;
%do i=1 %to &n;
%let val = %scan(%bquote(&TabList.),&i,',');

%put &val &i &n;

OPTION VALIDVARNAME=V7;
PROC IMPORT DATAFILE= &in_dir.
OUT= data
DBMS=XLSX
REPLACE;
SHEET= "&val";
GETNAMES=NO;
datarow=3;
RUN;

%end;
%mend;

%process;

[qw213]

Thanks! This works perfectly.

[ballardw]

---

@qw213 wrote:
Thanks! This works perfectly.

---

I generally avoid using commas inside macro value lists as you then have to add code to pass them to other macros. If the sole purpose is to have a delimiter other than space try a different character that shouldn't appear in the values such as # and use that for the delimiter in countw, scan, %scan etc.

[pink_poodle]

This part is giving a problem:

%let val = %scan(&TabList.,&i);

Try replacing it with:

%let val %scan(%bquote(&TabList.),&i.,%str(,));

Reference

https://stackoverflow.com/questions/39889137/error-in-simple-sas-macro