Importing all data files from a directory

SASEnthusiast — Thu, 30 Jun 2016 17:47:18 GMT

I am trying to import a lot of data files from a single directory. My code is as below.

Every time I am running the program I get the following error. Where Am I going wrong.

ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric operand is required. The condition was:
%scan((&RC_Files),&st,&x) ne
ERROR: The condition in the %DO %WHILE loop, %scan((&RC_Files),&st,&x) ne, yielded an invalid or missing value, . The macro will
stop executing.
ERROR: The macro READALLRATECARDFILES will stop executing.


%macro get_filenames(location);
filename _dir_ "%bquote(&location.)";
data filenames(keep=memname);
handle=dopen( '_dir_' );
if handle > 0 then do;
count=dnum(handle);
do i=1 to count;
memname=dread(handle,i);
output filenames;
end;
end;
rc=dclose(handle);
run;
filename _dir_ clear;
%mend;

%macro ReadInputFiles(filePath, Fformat, sheetName, outDS);

proc import out=inlib.&outDS DATAFILE="&filePath"
dbms=&Fformat replace;
getnames=yes;
%if %length(%cmpres(&sheetName)) > 0 %then %do;
Sheet=&sheetName;
%end;
run;
data inlib.&outDS;
set inlib.&outDS;
if compress(cats(of _all_),,'kad') = '' then delete;
run;
%mend;

%macro ReadAllRateCardFiles;
%get_filenames(&inXLRCDir);
proc sql;
select memname into :RC_Files separated by ','
from filenames;
quit;
%put &RCFiles;
%let st = 1; 
%let DSNamePrefix = Std_RC_Set_;
%let x = %str(,) ;
%do %while(%scan((&RC_Files),&st,&x) ne );
%let FileName = %cmpres(%scan(%bquote(&RC_Files),&st,%str(,)));
data ReadFile_Status1;
/* if exists('ReadFile_Status','DATA') then 
set ReadFile_Status;
end;*/
length path $5000 DSName $50;

%let FilePath = "%trim(&inXLDir./&FileName)";
path = &FilePath;
FileExists = fileexist(path);
DSName = "&DSNamePrefix.&st";
%put &FilePath;
if FileExists > 0 then do;
call execute('%ReadExcelInput('||path||',csv,,'||DSName||')');
end;

run;

%let st = %sysevalf(&st + 1); 
%end;
%mend;
%ReadAllRateCardFiles;

Re: Importing all data files from a directory

Astounding — Thu, 30 Jun 2016 18:46:36 GMT

Here's my suspicion. This statement is the culprit:

%do %while(%scan((&RC_Files),&st,&x) ne );

Some paths to files may contain a dash or a slash (- or /). When macro language evaluates whether the %DO %WHILE condition is still satisfied, it applies %EVAL to the comparison. And when it sees a dash or slash, it interprets those as "minus" or "divide".

Try switching from %SCAN to %QSCAN. The %SCAN function returns an unquoted result, even when the incoming string is quoted. Switching to %QSCAN will return a quoted result, treating - or / as text.

topic Importing all data files from a directory in SAS Programming

Importing all data files from a directory

Re: Importing all data files from a directory