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!
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.