You can use x-commands to read the files in a folder and then import. If you don't have access to run x-commands then the following approach might work for you.
FILENAME _folder_ "%bquote(/usr/folder/)";/* Your path here */
data filenames(keep=memname);
FORMAT memname $100.;
handle=dopen( '_folder_' );
if handle > 0 then do;
count=dnum(handle);
do i=1 to count;
memname=dread(handle,i);
output filenames;
end;
end;
rc=dclose(handle);
run;
filename _folder_ clear;
The above program reads all the files that are in the folder. No you can use the logic to filter your files (ie. by extensions or file names or etc.). Put those names in a macro using PROC SQL INTO .
/* Count total files in the location and add all the file names into a macro */
PROC SQL noprint;
SELECT COUNT(*),memname INTO: Total_Files, : All_Files separated by ","
FROM filenames;
QUIT;
%PUT "Total Files:" &Total_Files "All Files:" &All_Files;
Using a macro to loop through each file and import them.
%MACRO IMPORT_FILES();
%DO I=1 %TO &Total_Files ;
%LET File=%SCAN("&All_Files",&i,",");
proc import
datafile="/usr/folder/&File." /* Your folder path here */
dbms=xlsx
out=GRIDWORK.TASK&i.
replace;
sheet="sheet1"; /* Your desired sheet here */
RUN;
%END;
%MEND IMPORT_FILES;
%IMPORT_FILES();
Finally Append all the datasets.
/* Append imported data into a single dataset */
%MACRO APPEND();
%DO I=2 %TO &Total_Files;
PROC APPEND BASE=WORK.Task1 DATA=WORK.Task&i. FORCE;
RUN;
%END;
%MEND APPEND;
%APPEND();
Thanks,
Suryakiran
