topic Re: Pass in quoted string list with space in macro in SAS Programming

Pass in quoted string list with space in macro

qw213 — Wed, 17 Oct 2018 19:51:27 GMT

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;

Re: Pass in quoted string list with space in macro

Reeza — Wed, 17 Oct 2018 19:55:34 GMT

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;

Re: Pass in quoted string list with space in macro

qw213 — Wed, 17 Oct 2018 20:08:44 GMT

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.

Re: Pass in quoted string list with space in macro

SuryaKiran — Wed, 17 Oct 2018 20:12:34 GMT

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;

Re: Pass in quoted string list with space in macro

pink_poodle — Wed, 17 Oct 2018 20:27:18 GMT

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

Re: Pass in quoted string list with space in macro

qw213 — Wed, 17 Oct 2018 20:28:41 GMT

Thanks! This works perfectly.

Re: Pass in quoted string list with space in macro

ballardw — Wed, 17 Oct 2018 22:02:46 GMT

@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.