@Reeza Thanks for your answer, but this doesn't solve the core of the problem. Your solution is hardcoded. You have to run an 'import_append' for each file. The problem is that I need to deliver some code to my clients and they add files to a certain folder. I don't know how many files they will use or how they will look like (except for some words in the file name). They don't have coding skills, but they can edit a 'settings' file where they can edit some variables, because that's easy. I found a workaround that is based on regular expressions. It's not really that difficult. First open de directory and put all the files that match a certain expression in a certain table. Then run over all the elements in this table and do a proc import and append for all those files. The only problem I still have is some format mismatches between the files, but I can manage to fix this. /* Gathers all the files in a certain location, based on a certain regular expression and store them in the filestable_regex table. */
%macro get_filenames(location, regex, filestable);
filename _dir_ "%bquote(&location.)";
data &filestable._®ex. (keep=fname);
handle=dopen( '_dir_' );
if handle > 0 then do;
count=dnum(handle);
do i=1 to count;
fname=dread(handle,i);
patternID = prxparse('m/' || "®ex." || '/i');
position = input(prxmatch(patternID, fname) - 1, best32.);
fname = "&location." || fname;
if position ne -1 then do;
output &filestable._®ex. ;
end;
end;
end;
rc=dclose(handle);
run;
filename _dir_ clear;
%mend;
/* Import the Excel sheets that are located in the filestable_regex table with the given sheet. */
%macro import_excel(sheet, output, filestable, regex);
%local nobs iter;
proc sql noprint;
select count(*) into : nobs
from &filestable._®ex.;
quit;
%let iter=1;
%do %while (&iter. <= &nobs.);
data _NULL_;
set filenames_®ex. (firstobs=&iter. obs=&iter.);
call symput("fname", fname);
run;
PROC IMPORT OUT=tmp_&output.
DBMS = xlsx
datafile = "&fname."
replace;
SHEET = "&sheet.";
getnames = yes;
RUN;
proc append base = &output. data = tmp_&output.;
run;
%let iter=%eval(&iter.+1);
%end;
%mend;
/* Initialization of the variables. */
data _NULL_;
location = "location of the files";
regex = "word that is ONLY in the excel files you want to work with";
sheet = "the sheet in the excel files";
output = "output table";
call symput('location', location);
call symput('regex', regex);
call symput('sheet', sheet);
call symput('output', output);
run;
%get_filenames(location=&location, regex=®ex, filestable=filenames);
%import_excel(sheet=&sheet, output=&output, filestable=filenames, regex=®ex);
/* Drop the temp tables. */
data _NULL_;
proc sql;
drop table filenames_®ex.;
drop table tmp_&output.;
quit;
run; Maybe there is a better solution based on a regex directly in the PROC IMPORT step, but this is what I was able to make.
... View more
