topic PIPE and ways to get round it in Enterprise Guide in SAS Enterprise Guide

PIPE and ways to get round it in Enterprise Guide

SteveDavies — Mon, 09 Jul 2018 12:32:25 GMT

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;

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

Kurt_Bremser — Mon, 09 Jul 2018 13:03:20 GMT

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.

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

SteveDavies — Mon, 09 Jul 2018 13:06:20 GMT

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

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

Kurt_Bremser — Mon, 09 Jul 2018 13:13:24 GMT

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.

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

ChrisHemedinger — Mon, 09 Jul 2018 14:00:24 GMT

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

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

Reeza — Mon, 09 Jul 2018 15:05:33 GMT

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&docsetVersion=9.4&locale=en

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;

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

TomKari — Mon, 09 Jul 2018 15:36:15 GMT

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;

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

SteveDavies — Tue, 10 Jul 2018 10:02:54 GMT

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;

%mend;

%loop_test;