topic Re: Importing multiple xlsx files with multiple xlsx sheets into SAS 9.4 in SAS Enterprise Guide

Importing multiple xlsx files with multiple xlsx sheets into SAS 9.4

RensMeerman — Wed, 04 Jan 2017 11:12:28 GMT

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

42 data all;

43 set XL:;

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

44 run;

Re: Importing multiple xlsx files with multiple xlsx sheets into SAS 9.4

Kalind_Patel — Wed, 04 Jan 2017 11:22:29 GMT

Please run PROC OPTIONS to verify that XCMD is set.

Re: Importing multiple xlsx files with multiple xlsx sheets into SAS 9.4

RensMeerman — Wed, 04 Jan 2017 11:28:24 GMT

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

NOXCMD Disables the X command in SAS.

Re: Importing multiple xlsx files with multiple xlsx sheets into SAS 9.4

Kalind_Patel — Wed, 04 Jan 2017 11:44:39 GMT

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#p0xtd57b40ehdfn1jyk8yxemfrtv.htm

Re: Importing multiple xlsx files with multiple xlsx sheets into SAS 9.4

Reeza — Wed, 04 Jan 2017 12:39:08 GMT

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