Importing multiple xlsx files with multiple xlsx sheets into SAS 9.4

[RensMeerman]

Dear community,

I have the following code but it gives me an error that I do not know how to fix. Please advise.

 ***************** CODE*********************

%macro impt(filename,i);

proc import DATAFILE="&filename" OUT=XL&i DBMS=XLSX REPLACE;

run;

%mend impt;

%let path = E:\Lev1\Data\1_INPUTDOCS\IMPDOC\;

data _null_;

command = "dir &path.*.xlsx /b";;

infile dummy pipe filevar=command end=eof truncover;

do i = 1 by 1 while(not eof);

input path $128.;

path=catt("&path.",path);

put 'NOTE: ' path=;

call execute(cats('%nrstr(%impt(',path,',',i,'));'));

end;

stop;

run;

data all;

set XL:;

run;

*********** ERROR LOG ******************

ERROR: Insufficient authorization to access PIPE.

command=dir E:\Lev1\Data\1_INPUTDOCS\IMPDOC\*.xlsx /b eof=0 i=. path= _ERROR_=1 _N_=1

NOTE: The SAS System stopped processing this step because of errors.

NOTE: DATA statement used (Total process time):

real time 0.03 seconds

cpu time 0.01 seconds

41

42 data all;

43 set XL:;

ERROR: The data set list (WORK.XL:) does not contain any members.

44 run;

[Kalind_Patel]

Please run PROC OPTIONS to verify that XCMD is set.

[RensMeerman]

I ran it. Got a list and 1 of the entries was:

NOXCMD Disables the X command in SAS.

[Kalind_Patel]

Okay please enable XCMD option to use pipe command,

your SAS Admin might help you on this.

Refer this link, it will give you clear explanation why we have to enable XCMD option before using pipe command:

http://support.sas.com/documentation/cdl/en/hostwin/69955/HTML/default/viewer.htm#p0xtd57b40ehdfn1jy...

[Reeza]

If you don't have permissions to change XCMD setting, which is highly likely, consider alternatives. 

Theres a task that should list all files and see the directory traversal example here

http://www2.sas.com/proceedings/forum2007/008-2007.pdf