PIPE and ways to get round it in Enterprise Guide

[SteveDavies]

Hi

I usually run a bit of code in Base SAS and it always runs fine. We have moved to EG and it now doesn't allow me to run it due to "Insufficient Authorization to access PIPE".

The code is below, is anyone able to provide a solution to reading in lots of differently named spreadsheets and retain the information required from each spreadsheet (where DIRECTORY_NAME is my library path)?

Thanks

%LET dir = "DIRECTORY_NAME";

%let DIRLIST = %UNQUOTE(%STR(%'for /r &dir. %I in (*.XLS*) do @echo %~ztfI%'));

filename DIRLIST pipe &DIRLIST.;

data out1_orig;

infile dirlist length=reclen truncover;

input line $varying256. reclen;

FORMAT DATE DDMMYY10.

TIME $5.

FILESIZE comma15.

FILENAME $200.

;

reclen=reclen;

Date = input(substr(line,1,10), ?? ddmmyy10.);

Time = scan(line,2,' ');

Filesize = scan(line,3,' ');

call scan(line,4,pos,len,' ');pos = pos/*-1*/;

FileName = substr(line,pos,reclen-pos);

MonthGrp = PUT(intnx('month',Date,0,'e'),YYMMN7.);

GrpNum = intnx('month',Date,0,'e');

posFS = prxmatch("|\\[^\\]*$|", FileName);

FileFolderOnly = substr(line,pos,posFS);

FileNameOnly = substr(line,pos+posFS,reclen-pos);

run;

[Kurt_Bremser]

In order to use filename pipe, XCMD needs to be enabled by the SAS administrator for that workspace server via SAS Management Console. It's not a problem with EG, but the default setting for workspace servers in BI setups.

[SteveDavies]

Thank you, I will see if our SAS administrator can do that. Is it a function that is easily amended or is it something that may not be available on all systems?

Is there a way round it if it doesn't work?

Thanks

[Kurt_Bremser]

It's a simple setting. In the Server manager, navigate to the Workspace Server entry (the one below the "Logical Wokspace Server"), right-click, Properties, Options, Advanced, Start Properties. There's a button for Allow XCMD.

Alternatively, use the dopen(), dinfo() and dread() data step functions.

[ChrisHemedinger]

If you need more background and perhaps some justification, see The case for XCMD privileges in SAS Enterprise Guide.

[Reeza]

That looks like a program to get a file listing and then parsing out certain components. 

If that's correct, there are other ways to get that information, 

Here's one approach:

http://documentation.sas.com/?docsetId=mcrolref&docsetTarget=n0ctmldxf23ixtn1kqsoh5bsgmg8.htm&docset...

Slightly modified and needs testing approach:

https://github.com/statgeek/SAS-Tutorials/blob/master/Import_all_files_one_type

---

@SteveDavies wrote:

Hi

I usually run a bit of code in Base SAS and it always runs fine. We have moved to EG and it now doesn't allow me to run it due to "Insufficient Authorization to access PIPE".

The code is below, is anyone able to provide a solution to reading in lots of differently named spreadsheets and retain the information required from each spreadsheet (where DIRECTORY_NAME is my library path)?

Thanks

%LET dir = "DIRECTORY_NAME";

%let DIRLIST = %UNQUOTE(%STR(%'for /r &dir. %I in (*.XLS*) do @echo %~ztfI%'));

filename DIRLIST pipe &DIRLIST.;

data out1_orig;

infile dirlist length=reclen truncover;

input line $varying256. reclen;

FORMAT DATE DDMMYY10.

TIME $5.

FILESIZE comma15.

FILENAME $200.

;

reclen=reclen;

Date = input(substr(line,1,10), ?? ddmmyy10.);

Time = scan(line,2,' ');

Filesize = scan(line,3,' ');

call scan(line,4,pos,len,' ');pos = pos/*-1*/;

FileName = substr(line,pos,reclen-pos);

MonthGrp = PUT(intnx('month',Date,0,'e'),YYMMN7.);

GrpNum = intnx('month',Date,0,'e');

posFS = prxmatch("|\\[^\\]*$|", FileName);

FileFolderOnly = substr(line,pos,posFS);

FileNameOnly = substr(line,pos+posFS,reclen-pos);

run;

---

[TomKari]

I haven't parsed out what all your code is doing, but here's a handy-dandy piece of code I keep around for situations like this. You may be able to modify it to get what you want. (Note - I use it on Windows, so no guarantees on Linux.)

Tom

/* Set the directory name to scan */
%let DirectoryName = /home;

data DatasetsInADirectory;

   keep DirectoryAndDataset;
   length DatasetName DirectoryAndDataset $1024 FileRef $8;

   /* Assign the fileref */
   call missing(FileRef); /* Blank, so SAS will assign a file name */
   rc1 = filename(FileRef, "&DirectoryName."); /* Associate the file name with the directory */
   if rc1 ^= 0 then
      abort;

   /* Open the directory for access by SAS */
   DirectoryID = dopen(FileRef);
   if DirectoryID = 0 then
      abort;

   /* Get the count of directories and datasets */
   MemberCount = dnum(DirectoryID);
   if MemberCount = 0 then
      abort;

   /* Get all of the entry names ... directories and datasets */
   do MemberIndex = 1 to MemberCount;
      DatasetName = dread(DirectoryID, MemberIndex);
      if missing(DatasetName) then
         abort;

      DirectoryAndDataset = cats("&DirectoryName.","/",DatasetName);
      output;
   end;

   /* Close the directory */
   rc2 = dclose(DirectoryID);
   if rc2 ^= 0 then
      abort;
run;

data Attributes;

   keep DirectoryAndDataset OptionName OptionVal;
   length EntryType $12 FileRef $8 OptionName $20 OptionVal $1024;

   set DatasetsInADirectory;

   /* Assign the fileref */
   call missing(FileRef); /* Blank, so SAS will assign a file name */
   rc1 = filename(FileRef, DirectoryAndDataset); /* Associate the file name with the dataset or directory */
   if rc1 ^= 0 then
      abort;

   /* Open the entry for access by SAS. If it is a directory, zero will be returned */
   EntryID = fopen(FileRef);

   /* Process the entries */
   if EntryID = 0
   then do;
      EntryType = "Directory";
      output;
   end;

   else do;
      /* Get the number of options for the dataset */
      OptNum  = foptnum(EntryID); 
      if OptNum = . then
         abort;

         EntryType = "Dataset";

         /* Get all of the options for the dataset */
         do OptCount = 1 to OptNum;
            OptionName = foptname(EntryID, OptCount);
            OptionVal = finfo(EntryID, OptionName);
            output;
         end;
      
      /* Close the directory */
      rc2 = fclose(EntryID);
      if rc2 ^= 0 then
         abort;

   end;

run;

proc sql;
drop table DatasetsInADirectory;
quit;

[SteveDavies]

Thank you to everyone who has responded. I have used bits of most of the replies and the code below now works for what I am needing.

%let path='FILEPATH';

filename folder "&path";

data FilesInFolder;

length Line 8 File $300;

List = dopen('folder');

do Line = 1 to dnum(List);

File = trim(dread(List,Line));

output;

end;

drop list line;

run;

data _NULL_;

set FilesInFolder end=final;

call symput(cats('File', _N_), trim(File));

call symput(cats('Name', _N_), trim(nliteral(substr(File,1,min(32, length(File)-4)))));

if final then call symputx(trim('Total'), _N_);

run;

%macro loop_test;

%do i = 1 %to &Total;

%put &&File&i;

%put %sysfunc(find(&&File&i,xls));

%if %sysfunc(find(&&File&i,xls)) %then %do;

PROC IMPORT OUT=orig_test&i.

DATAFILE="FILEPATH"

DBMS=EXCEL REPLACE;

GETNAMES=YES;

SHEET="Sheet";

RUN;

%end;

%end;

%mend;

%loop_test;