Your code worked for me as follows. My workbooks were in the directory d:\art\
you would have to correct the line, below, that states: %let path=d:\art\;
to point to the path were your workbooks exist.
%macro impt(filename,i);
proc import DATAFILE="&filename" OUT=XL&i DBMS=xlsx REPLACE;
run;
%mend impt;
%let path=d:\art\;
data _null_;
command = "dir &path.*.xls /b";;
infile dummy pipe filevar=command end=eof truncover;
do i = 1 by 1 while(not eof);
input path $128.;
path=catt("&path.",path);
put 'NOTE: ' path=;
call execute(cats('%nrstr(%impt(',path,',',i,'));'));
end;
stop;
run;
data all;
set XL:;
run;
How do you do this with pc file server since my OS is unix i cannot import on unix without pc file server. How can i proc import an xls file without pc file server? Is there a way?
I've seen some answers about import multiple excel files into SAS. Some are too tedious copying all the code in the reply and too specific to the author's own work. Some are too short by just leaving clues and learning materials. However, we can still learn something from those replies.
Here I want to give a more general answer and the logic behind my program and some explanation.
1. Get all the file list in the directory through "pipe"
2. Use macro loop to import excel files in the list.
/* 1. Get file lists in the directory*/
filename dirlist pipe 'dir "D:\..." /b';
/* 2. Resolve file names to SAS dataset "files" */
data files;
length fname $20;
infile dirlist truncover length= reclen;
input fname $varying20. reclen;
run;
/* 3. Store the number of files we want to import into macro variable "nfile" */
data _null_;
set files nobs= nobs;
call symput('nfile', nobs);
stop;
run;
/* 4. Import all the files we want into SAS dataset*/
%macro fileinput;
%local i;
%do i= 1 %to &nfile;
/* Store import path and output filename into macro variables*/
data _null_;
set files(firstobs= &i obs= &i);
/* The length of fpath can't be over 201 since windows limitation */
call symput('fpath', "D:\..." || fname);
call symput('foutname', scan(fname, 1, '.') );
run;
/* Excel file import*/
proc import out= work.&foutname
datafile= "&fpath"
dbms=excel replace;
range="sheet1$";
getnames=yes;
mixed=no;
scantext=yes;
usedate=yes;
scantime=yes;
run;
%end;
%mend;
%fileinput;
Explanation:
options symbolgen mprint;
%put ...;
Hope you like it!
Hi,
Thank you for the solution, I tried the code you posted trying to load multiple excel files from a directory where they are located in Windows.
I keep getting an error message :
ERROR: Physical file does not exist, /SHARED/MCL_Team/MCL_1117//abc.xlsx. (there are two forward slashes here, but SYMBOLGEN Macro variable FPATH resolves shows correct path. I wonder what might have added one extra slash to the
datafile="&fpath" directory.
Here is what the log file looks like:
25 GOPTIONS ACCESSIBLE;
SYMBOLGEN: Macro variable NFILE resolves to 24
26 %fileinput;
MPRINT(FILEINPUT): data _null_;
SYMBOLGEN: Macro variable I resolves to 1
SYMBOLGEN: Macro variable I resolves to 1
MPRINT(FILEINPUT): set files(firstobs= 1 obs= 1);
MPRINT(FILEINPUT): call symput('fpath', "/SHARED/MCL_Team/MCL_1117/" || fname);
MPRINT(FILEINPUT): call symput('foutname', outname);
MPRINT(FILEINPUT): run;
NOTE: There were 1 observations read from the data set WORK.FILES.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds
SYMBOLGEN: Macro variable FPATH resolves to /SHARED/MCL_Team/MCL_1117/xyz_November.xlsx (singles forward slash which is correct)
SYMBOLGEN: Macro variable FOUTNAME resolves to xyz_November
MPRINT(FILEINPUT): proc import datafile="/SHARED/MCL_Team/MCL_1117/xyz_November.xlsx
" dbms=xlsx out=xyz_November
replace;
MPRINT(FILEINPUT): RXLX;
MPRINT(FILEINPUT): range="Sheet1$A1:H518";
MPRINT(FILEINPUT): sheet='Sheet1';
MPRINT(FILEINPUT): getnames=yes;
MPRINT(FILEINPUT): run;
ERROR: Physical file does not exist, /SHARED/MCL_Team/MCL_1117//xyz_November.xlsx. (double forward slashes)
Any help is greatly appreciated.
@CCW, I am so in love with you right now! I have spent way too much time trying to import multiple files. Nothing worked, until I tried your code. THANK YOU!!!!
Hi, I used the code above to import multiple excel files but the problem I'm having is that instead of the variable 'foutname' being assigned to each dataset, I am just getting a dataset called 'Fname' and it is always whatever file was produced on the last iteration of the do loop. Do you know why SAS isn't capturing the file names and overwriting the same file over and over???
Dear ALL,
Try the simple code as follows:
%let n = 2;
data all;
run;
%macro test;
%do i=1 %to &n;
Proc Import Out= result&i
Datafile="E:\IAR_Rept&i..xlsx"
Dbms=Excel Replace;
Getnames=Yes;
Mixed=No;
Scantext=Yes;
Usedate=Yes;
Scantime=Yes;
Run;
data all;
set all result&i;
run;
%end;
%mend;
%test;
we can import multiple by storing a macro permanently
%macro maths(cust,dax)
proc import datafile="s:/workshop/prg1/&cust" dbms=csv out=&dax replace;
run;
%mend maths
%maths(customer.csv,mydata)
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.