Hi everyone. I currently have all the excel files in Linux server. What i want to do is loading all excel files into SAS and create dataset based on the original file name. I have read a lot of online tutorial and most of them are only on single excel files. However, i managed to find some about loading multiple excel in multiple folders. I actually copied the code from here: https://github.com/statgeek/SAS-Tutorials/blob/master/Import_all_files_one_type However, i am getting the error of directory or path does not exist when the same exact path is accessible using mobaxterm. My code below(Slight change on the path) %*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="&source./tnbt/Profile_List_Files/Central/ABBA/&file_name."
dbms=xlsx
out=&dataset_name replace;
run;
%mend;
*Create the list of files, in this case all XLSX files;
%list_files(&source./tnbt/Profile_List_Files/Central/ABBA/, 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; However in the log, it says this: Directory /sasdata/source/tnbt/Profile_List_Files/Central/ABBA/ cannot be open or does not exist I have no clue what is going on as I am pretty certain that the path is 100% accessible. What could be wrong here?
... View more