topic Re: A character operand was found in the %EVAL function in SAS Programming

A character operand was found in the %EVAL function

Sir_Highbury — Fri, 26 Aug 2016 06:54:32 GMT

Dear experts,

I am using the following macro that I found online to import all the xls contained in a folder:

options validvarname=any;

%let subdir=G:\xxxxxxt\;
filename dir pipe "dir &subdir.*.xls /B";
data new;
infile dir truncover end=last;
input filename $100.;
filename=cats("&subdir",filename);
call symputx(cats('path',_n_),filename);
call symputx(cats('dsn',_n_),scan(scan(filename,6,'\'),1,'.'));
if last then call symputx('nobs',_n_);
run;
%put _user_;
%macro import;
%do i=1 %to &nobs;
proc import datafile="&&path&i" out=&&dsn&i dbms=excel replace;
getnames=yes;
mixed=yes;
run;
%end;
%mend import;

%import

But I get the error message in the attached picture.

Readin some post on the topic I assume that the problem is related to the variable NOBS... why the symbolic reference is not resolved. How should I debug it? Thank in adcance, SH.

Re: A character operand was found in the %EVAL function

RahulG — Fri, 26 Aug 2016 07:03:17 GMT

Problem is below statement did not executed in your code.

if last then call symputx('nobs',_n_);

Please share the full log.

Re: A character operand was found in the %EVAL function

Kurt_Bremser — Fri, 26 Aug 2016 07:27:36 GMT

When doing this:

filename dir pipe "dir &subdir.*.xls /B";
data new;
infile dir truncover end=last;
input filename $100.;
filename=cats("&subdir",filename);
call symputx(cats('path',_n_),filename);
call symputx(cats('dsn',_n_),scan(scan(filename,6,'\'),1,'.'));
if last then call symputx('nobs',_n_);
run;

if the directory is empty, the data step will stop in the first iteration at the infile statement (which actually is a "read from infile"), and never reach the call symputs.

Put the "if last ...." line before the infile statement and see what happens.

Re: A character operand was found in the %EVAL function

Kurt_Bremser — Fri, 26 Aug 2016 07:34:03 GMT

PS a much better solution:

filename dir pipe "dir &subdir.*.xls /B";
data new;
infile dir truncover end=last;
input filename $100.;
length dsn $100;
filename=cats("&subdir",filename);
dsn = scan(scan(filename,6,'\'),1,'.');
call execute('proc import datafile="'!!trim(filename)!!'" out='!!trim(dsn)!!' dbms=excel replace; getnames=yes; mixed=yes; run;');
run;