I'm looking at this code %*Creates a list of all files in the DIR directory with the specified extension (EXT);
%macro list_files(dir,ext);
%local filrf rc did memcnt name i;
%let rc=%sysfunc(filename(filrf,&dir));
%let did=%sysfunc(dopen(&filrf));
%if &did eq 0 %then
%do;
%put Directory &dir cannot be open or does not exist;
%return;
%end;
%do i = 1 %to %sysfunc(dnum(&did));
%let name=%qsysfunc(dread(&did,&i));
%if %qupcase(%qscan(&name,-1,.)) = %upcase(&ext) %then
%do;
%put &dir\&name;
%let file_name = %qscan(&name,1,.);
%put &file_name;
data _tmp;
length dir $512 name $100;
dir=symget("dir");
name=symget("name");
path = catx('\',dir,name);
the_name = substr(name,1,find(name,'.')-1);
run;
proc append base=list data=_tmp force;
run;
quit;
proc sql;
drop table _tmp;
quit;
%end;
%else %if %qscan(&name,2,.) = %then
%do;
%list_files(&dir\&name,&ext)
%end;
%end;
%let rc=%sysfunc(dclose(&did));
%let rc=%sysfunc(filename(filrf));
%mend list_files;
%*Macro to import a single file, using the path, filename and an output dataset name must be specified;
%macro import_file(path, file_name, dataset_name );
proc import
datafile="&path.\&file_name."
dbms=xlsx
out=&dataset_name replace;
run;
%mend;
*Create the list of files, in this case all XLSX files;
%list_files(c:\_localData\temp, xlsx);
%*Call macro once for each entry in the list table created from the %list_files() macro;
data _null_;
set list;
string = catt('%import_file(', dir, ', ', name,', ', catt('test', put(_n_, z2.)), ');');
call execute (string);
run; But I have 8 xlsx files in a common folder: C:\Deskop\Budget\Budget, q1q2\'and the 8 excelfiles' (that is for the first 8 months): Budget_jan.xlsx, Budget_feb.xlsx, Budget_march.xlsx, Budget_april.xlsx, Budget_may.xlsx, Budget_june.xlsx, ....... Budget_dec.xlsx. after the first macro %macro list_files(dir,ext); I %put &=path.;
%put &=filename.;
%put &=datasetname.; and path is correct, but the other two, filname and datasetname, is not resolved. Where do I print it out in that code? thought that SAS creating it by itself? And then, when I get to the second macro: %macro import_file(path, file_name, dataset_name );
proc import
datafile="BUDGETPATH\Budget2023\*.xlsx /*I don't know how to tell SAS to get all of the 12 excelfiles with the *-wildcard */
dbms=xlsx
out=&dataset_name replace;
run;
%mend; Beacuse in this line: %list_files(MyPath\the Budget-folder, xlsx); it creates that list of all the xlsx.files. (the 12 of them) and in the last code: data _null_;
set alla_styrfiler;
string = catt('%import_file(', dir, ', ', name,', ', catt('ALL_BUDGETS_XSLX_FILES', put(_n_, z2.)), ');');
call execute (string);
run; it just copy paste the file in the proc import. I tried to specifiy for example januray in that proc import statement, since the wildcard dosen't work, but it just creates 12 duplicates. Merry x-max!
... View more