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!
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.