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.
Problem is below statement did not executed in your code.
if last then call symputx('nobs',_n_);
Please share the full log.
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.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.