Desktop productivity for business analysts and programmers

Importing multiple xlsx files with multiple xlsx sheets into SAS 9.4

Reply
Occasional Contributor
Posts: 6

Importing multiple xlsx files with multiple xlsx sheets into SAS 9.4

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.XLSmiley Happy does not contain any members.

44 run;

 

Frequent Contributor
Posts: 96

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

Please run PROC OPTIONS to verify that XCMD is set.

 

Occasional Contributor
Posts: 6

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

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

 

NOXCMD Disables the X command in SAS.

 

Frequent Contributor
Posts: 96

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

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...

Super User
Posts: 19,063

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

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

Ask a Question
Discussion stats
  • 4 replies
  • 231 views
  • 0 likes
  • 3 in conversation