sas - Importing all xlsx files in a folder

Question

I am using this code to import all xlsx files in a folder:

%*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;

I additionally use the following code to try understand the error being generated: options mprint mlogic;

MLOGIC(IMPORT_FILE):  Parameter PATH has value C:\Users\baidw002\Documents\1 

      BCH-LJAF\Real data transfer (BCH to UAB)\PreAnalysis\data\DLW cohorts\Cohort 1\1 

MLOGIC(IMPORT_FILE):  Parameter FILE_NAME has value BSL 

MLOGIC(IMPORT_FILE):  Parameter DATASET_NAME has value FB-1208-7 BSL.xlsx 

ERROR: More positional parameters found than defined. 

MLOGIC(IMPORT_FILE):  Ending execution. 

MLOGIC(IMPORT_FILE):  Beginning execution. 

MLOGIC(IMPORT_FILE):  Parameter PATH has value C:\Users\baidw002\Documents\1 

      BCH-LJAF\Real data transfer (BCH to UAB)\PreAnalysis\data\DLW cohorts\Cohort 1\1 

MLOGIC(IMPORT_FILE):  Parameter FILE_NAME has value BSL 

MLOGIC(IMPORT_FILE):  Parameter DATASET_NAME has value FB-1331-6 BSL.xlsx 

ERROR: More positional parameters found than defined. 

Question: How can I fix this error?

标签： sas

Answer 1

I was able to run it successfully using a few test files. This may be an issue with certain characters in your filenames or directories. To prevent this, modify %import_file by making the import file a single parameter named file that expects a quoted string, then change your call execute() string to support it.

*Macro to import a single file, using the path, filename and an output dataset name must be specified;
%macro import_file(file, dataset_name);

    proc import 
        datafile=&file.
        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;

    filename = quote(cats(dir, '\', name));
    out      = catt('test', put(_n_, z2.));

    string   = catt('%import_file(', filename, ',', out, ');');

    call execute (string);
run;