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;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.