BookmarkSubscribeRSS Feed
SteveDavies
Calcite | Level 5

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;

 

7 REPLIES 7
Kurt_Bremser
Super User

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
Calcite | Level 5

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
Super User

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
Community Manager

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

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
Reeza
Super User

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
Onyx | Level 15

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
Calcite | Level 5

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;

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 2104 views
  • 3 likes
  • 5 in conversation