PIPE command failing with job scheduled using Management Console

[Anna_dlC]

We have code that works perfectly fine when run manually in EG but for some reason fails when run through Management Console on a schedule. The error seems to revolve around the PIPE command, with the error message stating "insufficient authorization" . But I have no problem using it in EG.

The input file containing the files to be ready can have multiple files in it at any given time. We use the pipe command to parse the file name list so that each file can go through our data validation code one by one. 

Can anyone please tell me why it's not working? We need this code scheduled ASAP to run in Production

[Tom]

This is usually because

• you are running as a different user
• you are running on a different machine
• cron jobs do not run your normal logon scripts which mess with your search path and other settings.

Reading a list of filenames from a text would be easy to do with SAS code. Say more about what it is that using PIPE is letting you do that you couldn't do with SAS code? 

[Anna_dlC]

The PIPE is allowing me to read a list files from a server folder.   The pipe reads in the contents of this entire folder and then my SAS code after that cherry picks only those starting with SFMC or Generate to be processed.

[Tom]

You should be able to do the same with DOPEN() and DREAD() functions.

Check out this macro to get a list of files in a directory:

https://github.com/sasutils/macros/blob/master/dirtree.sas

[Anna_dlC]

My work is blocking this site for some reason.  Would you be able to post the macro here?

[Tom · Accepted solution]

---

@Anna_dlC wrote:

My work is blocking this site for some reason.  Would you be able to post the macro here?

---

https://github.com/sasutils/macros/blob/master/dirtree.sas

%macro dirtree
/*---------------------------------------------------------------------------
Build dataset of files in directory tree(s)
----------------------------------------------------------------------------*/
(directory    /* Pipe delimited directory list (default=.) */
,out=dirtree  /* Output dataset name */
,maxdepth=120 /* Maximum tree depth */
);
/*---------------------------------------------------------------------------
Use SAS functions to gather list of files and directories

directory - Pipe delimited list of top level directories

out - Dataset to create
maxdepth - Maximum depth of subdirectories to query

Output dataset structure
--NAME-- Len  Format      Description
FILENAME $256             Name of file in directory
TYPE       $1             File or Directory? (F/D)
SIZE        8 COMMA20.    Filesize in bytes
DATE        4 YYMMDD10.   Date file last modified
TIME        4 TOD8.       Time of day file last modified
DEPTH       3             Tree depth
PATH     $256             Directory name

Size is not available for the directories.
LASTMOD timestamp is only available on Unix for directories.

Will not scan the subtree of a directory with a path that is
longer then 256 bytes. For such nodes TYPE will be set to L .

----------------------------------------------------------------------------*/
%local fileref ;
%let fileref=__FL__ ;
%if 0=%length(&directory) %then %let directory=. ;

* Setup dataset and seed with starting directory list ;
data &out;
  length filename $256 type $1 size 8 date time 4 depth 3 path $256 ;
  retain filename ' ' depth 0 type ' ' date . time . size . ;
  format size comma20. date yymmdd10. time tod8. ;
  do _n_=1 to countw(symget('directory'),'|');
    path=scan(symget('directory'),_n_,'|');
    output;
  end;
run;

%* Allow use of empty OUT= dataset parameter ;
%let out=&syslast;

data &out;
  modify &out;
  retain sep "%sysfunc(ifc(&sysscp=WIN,\,/))";
  retain maxdepth &maxdepth;
* Create FILEREF pointing to current file/directory ;
  rc1=filename("&fileref",catx('/',path,filename));
  if rc1 then do;
    length message $256;
    message=sysmsg();
    put 'ERROR: Unable to create fileref for ' path= filename= ;
    put 'ERROR- ' message ;
    stop;
  end;
* Try to open as a directory to determine type ;
  did=dopen("&fileref");
  type = ifc(did,'D','F');
  if type='D' then do;
* Make sure directory name is not too long to store. ;
    if length(catx('/',path,filename)) > vlength(path) then do;
      put 'NOTE: Directory name too long. ' path= filename= ;
      type='L';
      rc3=dclose(did);
    end;
    else do;
* Move filename into the PATH and if on Unix set lastmod ;
      path=catx(sep,path,filename);
      filename=' ';
      if sep='/' then do;
        lastmod = input(dinfo(did,doptname(did,5)),nldatm100.);
        date=datepart(lastmod);
        time=timepart(lastmod);
      end;
    end;
  end;
  else do;
* For a file try to open file and get file information ;
    fid=fopen("&fileref",'i',0,'b');
    if fid then do;
      lastmod = input(finfo(fid,foptname(fid, 5)), nldatm100.);
      date=datepart(lastmod);
      time=timepart(lastmod);
      size = input(finfo(fid,foptname(fid,ifn(sep='/',6,4))),32.);
      rc2 = fclose(fid);
    end;
  end;
* Update the observation in the dataset ;
  replace;
  if type='D' then do;
* When current file is a directory add directory members to dataset ;
    depth=depth+1;
    if depth > maxdepth then put 'NOTE: ' maxdepth= 'reached, not reading members of ' path= ;
    else do i=1 to dnum(did);
      filename=dread(did,i);
      output;
    end;
    rc3=dclose(did);
  end;
* Clear the fileref ;
  rc4=filename("&fileref");
run;

%mend dirtree;

Since you seem to want CSV files that are directly in that folder (not in some sub-directory) then use the MAXDEPTH=1 and filter the resulting dataset with same criteria you are using now to filter the output of whatever PIPEd command you are using.

[Anna_dlC]

Hi, I tried your macro but I'm a little confused at the results.  This is was old code with the PIPE

 When I create that 'num_files' macro variable, my result is 3, which is how many of my target files are in that directory.

But when I run the code using your macro, num_files only resolves to 1?  Here is the log

56 %dirtree(/rsmprod/landing/RMD/OpticNerveSftp/SFMC/recv/);
MLOGIC(DIRTREE): Beginning execution.
MLOGIC(DIRTREE): This macro was compiled from the autocall file
/sasdata/cdm/mktgecicm/CLM_Operations/03SFMC/CJB_PRODUCTION_VALIDATION/sasautos/dirtree.sas
MLOGIC(DIRTREE): Parameter DIRECTORY has value /rsmprod/landing/RMD/OpticNerveSftp/SFMC/recv/
MLOGIC(DIRTREE): Parameter OUT has value dirtree_list
MLOGIC(DIRTREE): Parameter MAXDEPTH has value 1
MLOGIC(DIRTREE): %LOCAL FILEREF
MLOGIC(DIRTREE): %LET (variable name is FILEREF)
SYMBOLGEN: Macro variable DIRECTORY resolves to /rsmprod/landing/RMD/OpticNerveSftp/SFMC/recv/
MLOGIC(DIRTREE): %IF condition 0=%length(&directory) is FALSE
SYMBOLGEN: Macro variable OUT resolves to dirtree_list

NOTE: The data set WORK.DIRTREE_LIST has 1 observations and 7 variables.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

MLOGIC(DIRTREE): %LET (variable name is OUT)
SYMBOLGEN: Macro variable SYSLAST resolves to WORK.DIRTREE_LIST
SYMBOLGEN: Macro variable OUT resolves to WORK.DIRTREE_LIST
SYMBOLGEN: Macro variable OUT resolves to WORK.DIRTREE_LIST
SYMBOLGEN: Macro variable SYSSCP resolves to LIN X64
SYMBOLGEN: Macro variable MAXDEPTH resolves to 1
SYMBOLGEN: Macro variable FILEREF resolves to __FL__
SYMBOLGEN: Macro variable FILEREF resolves to __FL__
SYMBOLGEN: Macro variable FILEREF resolves to __FL__
SYMBOLGEN: Macro variable FILEREF resolves to __FL__

NOTE: maxdepth=1 reached, not reading members of path=/rsmprod/landing/RMD/OpticNerveSftp/SFMC/recv//BKUP
NOTE: The data set WORK.DIRTREE_LIST has been updated. There were 28 observations rewritten, 27 observations added and 0
observations deleted.
NOTE: DATA statement used (Total process time):
real time 0.04 seconds
cpu time 0.03 seconds

MLOGIC(DIRTREE): Ending execution.
57
58 DATA _NULL_;
59 call symput ('num_files',strip(_n_)); /* store the record number in a macro variable */
60 RUN;

NOTE: Numeric values have been converted to character values at the places given by: (Line):(Column).
59:34
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

SYMBOLGEN: Macro variable NUM_FILES resolves to 1

[Tom]

Looks like the macro worked:

NOTE: The data set WORK.DIRTREE_LIST has been updated. There were 28 observations rewritten, 27 observations added and 0
observations deleted.

So it found 28 directories and files.

What do want in the macro variable NUM_FILES?

Right now you are just converting the number one into a string , removing the resulting spaces and storing that into the macro variable by running this empty data step.

DATA _NULL_;
  call symput ('num_files',strip(_n_)); /* store the record number in a macro variable */
RUN;

It would be a lot easier to just do:

%let num_files=1;

if that is what you want to put into NUM_FILES.

[Anna_dlC]

From the list in the directory, num_files should resolve to 3

[Tom]

If you want limit WHICH files then do that.

From your photograph perhaps you want only take the files whose names start with 'GenerateFileSample'?  It is not clear whether or not the file extension matters and cannot see those in the photograph.

So look at the dataset, DIRTREE_LIST, your macro call generated.  Explore it. Figure out what it has in it.  Then you can write code to filter it just the observations you want.

Spoiler

data want;
  set DIRTREE_LIST end=eof;
  where filename=:'GenerateFileSample';
  if eof then call symputx('num_files',_n_);
run;
  

[Anna_dlC]

That did the trick.

[SASKiwi]

Most likely it is because the SAS OPTION NOXCMD is set for SAS batch jobs. To change this modify the sasbatch_usermods.bat in each of your SAS server instances (SASApp, SASApp2 etc.) and add this option:

Set USERMODS_OPTIONS=-XCMD

[Anna_dlC]

I've sent this solution to our admin to see if it can be implemented.

[Anna_dlC]

Ultimately, I believe this is the easiest solution but I am having a devil of a time getting it implemented!   Thank you!