I changed a few things to Ksharp's answer and it worked for me:
%let subdir=C:\path\;
filename dir "&subdir.*.xlsx ";
data new;
length filename fname $ 200;
infile dir eof=last filename=fname;
input ;
last: filename=fname;
run;
proc sort data=new nodupkey;
by filename;
run;
data null;
set new;
call symputx(cats('filename',_n_),filename);
call symputx(cats('dsn',_n_),compress(scan(filename,-2,'\.'), ,'ka'));
call symputx('nobs',_n_);
run;
%put &nobs.;
%macro import;
%do i=1 %to &nobs;
proc import datafile="&&filename&i" out=&&dsn&i dbms=excel replace;
getnames=yes;
mixed=yes;
run;
%end;
%mend import;
%import
The %for Macro will answer the original question (and work on subfolders as well)
http://www.sascommunity.org/wiki/Streamlining_Data-Driven_SAS_With_The_%25FOR_Macro
Hi PaigeMiller,
I tried this:
%let topfolderpath=C:\Users\Desktop\Test_Import;
%for(filepath, in=<&topfolderpath>, do=%nrstr(
%let subfolderpath=&filepath;
%for(filepath shortname, in=<&subfolderpath>, do=%nrstr(
proc import out=&shortname
datafile="&filepath"
DBMS=EXCEL REPLACE;
RANGE="ALL_INJ$";
GETNAMES=YES;
MIXED=NO;
SCANTEXT=YES;
USEDATE=YES;
SCANTIME=YES;
run;
))
))
And I got this error. I tested these paths given below and they should work fine.
ERROR: cant open directory path=C:\Users\Desktop\Test_Import\all_inj_8-28-14.xlsx
ERROR: cant open directory path=C:\Users\Desktop\Test_Import\all_inj_8_26_14.xlsx
ERROR: cant open directory path=C:\Users\Desktop\Test_Import\all_inj_8_27_14.xlsx
ERROR: cant open directory path=C:\Users\Desktop\Test_Import\all_inj_9-2-14.xlsx
ERROR: cant open directory path=C:\Users\Desktop\Test_Import\all_inj_9-3-14.xlsx
ERROR: cant open directory path=C:\Users\Desktop\Test_Import\all_inj_9-4-14.xlsx
Hi, I tried exactly the same syntax that you posted and got error messages highlighting the "=" in the "dbms=excel" Would appreciate help with what I need to change. Thank you!
NOTE 137-205: Line generated by the invoked macro "IMPORT".
35 proc import datafile="&&filename&i" out=&&dsn&i dbms=excel replace;
-
22
35 ! getnames=yes; mixed=yes;
35 ! run;
ERROR 22-322: Syntax error, expecting one of the following: DATAFILE, DATATABLE, DBMS, FILE, OUT,
TABLE.
NOTE: Line generated by the invoked macro "IMPORT".
35 proc import datafile="&&filename&i" out=&&dsn&i dbms=excel replace;
-
76
35 ! getnames=yes; mixed=yes;
35 ! run;
ERROR 76-322: Syntax error, statement will be ignored.
thanks for sharing this Sir
Can you please explain (eof and filename) in the below code ?
%let subdir=c:\temp\;
filename dir "&subdir.*.xls ";
data new; length filename fname $ 50; infile dir eof=last filename=fname; input ; last: filename=fname; run;
Thanks!
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.