sas - 如何将多个文件夹中的多个 Excel 文件加载到 SAS 中并附加到一个最终表格中？

Question

我是一名 SAS 开发人员。

目前我有一个场景，Linux 服务器的许多子文件夹中有许多 excel 文件。目录是：

/sasdata/source/tttt/pppp_List_Files/Centre/ABBA/
/sasdata/source/tttt/pppp_List_Files/Centre/LPPL/
/sasdata/source/tttt/pppp_List_Files/Centre/XXXX/
and many more

也有excel文件：

/sasdata/source/tttt/pppp_List_Files/Corner/
/sasdata/source/tttt/pppp_List_Files/Top/
/sasdata/source/tttt/pppp_List_Files/Bottom/
/sasdata/source/tttt/pppp_List_Files/Store/

我尝试使用我在网上找到的一些示例代码，如下所示：

%*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./tttt/pppp_List_Files/Centre/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./tttt/pppp_List_Files/Centre/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;

但是，它会引发找不到目录或路径的错误，但我 100% 确定该路径存在，因为我现在正在访问它。

就如何实现这一目标寻求您的帮助。

标签： sas