BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
qw213
Calcite | Level 5

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;

1 ACCEPTED SOLUTION

Accepted Solutions
SuryaKiran
Meteorite | Level 14

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;
Thanks,
Suryakiran

View solution in original post

6 REPLIES 6
Reeza
Super User

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
Calcite | Level 5

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
Meteorite | Level 14

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;
Thanks,
Suryakiran
qw213
Calcite | Level 5
Thanks! This works perfectly.
ballardw
Super User

@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
Barite | Level 11

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

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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
  • 6 replies
  • 957 views
  • 2 likes
  • 5 in conversation