Desktop productivity for business analysts and programmers

PIPE and ways to get round it in Enterprise Guide

Reply
Occasional Contributor
Posts: 7

PIPE and ways to get round it in Enterprise Guide

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;

 

Super User
Posts: 10,599

Re: PIPE and ways to get round it in Enterprise Guide

Posted in reply to SteveDavies

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Occasional Contributor
Posts: 7

Re: PIPE and ways to get round it in Enterprise Guide

Posted in reply to KurtBremser

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

Super User
Posts: 10,599

Re: PIPE and ways to get round it in Enterprise Guide

Posted in reply to SteveDavies

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Community Manager
Posts: 3,463

Re: PIPE and ways to get round it in Enterprise Guide

Posted in reply to SteveDavies

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

Super User
Posts: 24,026

Re: PIPE and ways to get round it in Enterprise Guide

Posted in reply to SteveDavies

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;

 


 

 

 

 

PROC Star
Posts: 1,334

Re: PIPE and ways to get round it in Enterprise Guide

Posted in reply to SteveDavies

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;
Occasional Contributor
Posts: 7

Re: PIPE and ways to get round it in Enterprise Guide

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;

 

Ask a Question
Discussion stats
  • 7 replies
  • 129 views
  • 2 likes
  • 5 in conversation