topic Re: Data step to generate table with all the reports it can find generates an undetermined error in SAS Programming

Data step to generate table with all the reports it can find generates an undetermined error

RockSolid — Tue, 14 Jan 2025 11:13:56 GMT

Hi,

New to the forum. I'm facing an issue when executing the following data step (Using SAS Viya 4):

data GEN_Reports_&region.;
    set GEN_Folders_&region.; /* Start with the existing filenames dataset */
	length region $10 plant $4 report $200 fparent $200 folderpath $500 fullpath $500 description $500;

    /* Check if the entry is a folder */
	region="&region.";
    rc = filename('dirrf', '', 'FILESRVC', cats("folderpath='", fullpath, "'"));
    did = dopen('dirrf');

    if did > 0 then do; /* If it's a folder, explore its contents */
        folderpath = fullpath; /* Update folderpath to the current folder */
        do i = 1 to dnum(did);
            report = dread(did, i); /* File or subfolder name */
            fullpath = cats(folderpath, "/", report);

            /* Check if it's another subfolder */
            rc_sub = filename('checksub', '', 'FILESRVC', cats("folderpath='", fullpath, "'"));
            did_sub = dopen('checksub');
            if did_sub > 0 then do; /* It's a subfolder */
                /*fparent = folderpath;
                output;*/
                did_sub = dclose(did_sub);
            end;
            else do; /* It's a file */
                fparent = folderpath;
				fc= filename('filrf','','FILESRVC',cats("folderpath='", fparent, "' filename='", report, "'"));
                fid=fopen('filrf');
				optval=finfo(fid,'File Identifier');
				description=finfo(fid,'Description');
				hyperlink=cats('https://****/SASVisualAnalytics/?reportUri=/reports/reports/',optval,'&reportViewOnly=true&sas-welcome=false');
				fid=fclose(fid);
				fc= filename('filrf');
				*output; /* Save the file information */
            end;
			rc_sub = filename('checksub');
        end;
        did = dclose(did);
    end;

	rc= filename('dirrf');
	keep region plant report folderpath fullpath optval hyperlink description;
run;

What the data step is trying to accomplish is to list all VA reports in the subfolders (representing different locations my company) of a folder (defined by the macro &region).

The thing is that, if I run this step in isolation it will successfully generate the table but the logs will show the following message:

NOTE: The SAS System stopped processing this step because of errors.
NOTE: There were 5 observations read from the data set WORK.GEN_FOLDERS_REU.
WARNING: The data set WORK.GEN_REPORTS_REU may be incomplete. When this step was stopped there were 5 observations and 8 variables.
WARNING: Data set WORK.GEN_REPORTS_REU was not replaced because this step was stopped.

I've been completely unable to find out what those "because of errors" mean. I've run it in debug and it generates no error. I'm almost sure that I'm closing all the file and directory references.

Any Idea?

Re: Data step to generate table with all the reports it can find generates an undetermined error

whymath — Wed, 15 Jan 2025 02:47:13 GMT

Since the log indicate SAS stops after the 5th iteraction, I think check who should be the 6th observation and why it doesn't will be helpful.

Re: Data step to generate table with all the reports it can find generates an undetermined error

Tom — Wed, 15 Jan 2025 04:01:18 GMT

How many levels of directories does your source file system have?

I don't think your logic can handle more than one level of nesting.

You might want to use the MODIFY trick of this macro instead so that it can simulate recursion.

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

Just adjust the code to use your FILESRV engine to open the files instead of the normal direct file access.

Re: Data step to generate table with all the reports it can find generates an undetermined error

RockSolid — Wed, 15 Jan 2025 07:38:15 GMT

Hi @whymath, the starting dataset has only 5 observations, so I assume there's an issue when "getting out" of the data step. I tried to debug the Data Step, going line by line, but I got no issue at the individual iteration.

Re: Data step to generate table with all the reports it can find generates an undetermined error

RockSolid — Wed, 15 Jan 2025 07:42:16 GMT

Hi Tom,

The starting dataset has 5 observations representing 5 folders inside the directory I want to look at. Inside those folders there are only report files. No subfolders. The amount of levels is fixed by design. I know my logic is not really advanced. I still have to learn if and how SAS programming can handle recursivity.
I'll check the link, and try to update the post asap

Re: Data step to generate table with all the reports it can find generates an undetermined error

Tom — Wed, 15 Jan 2025 18:56:06 GMT

Try putting in more testing for errors.

Also try using QUOTE() function to add the quotes in case there are any single quote characters or leading spaces in one of the folder or file names.

And it really looks to me like you are using the report name twice in generating that last fileref. Is that how it really works?

data GEN_Reports_&region.;
/* Start with the existing filenames dataset */
  set GEN_Folders_&region.;
  length region $10 plant $4 report $200 fparent $200 folderpath fullpath description $500;

/* Check if the entry is a folder */
  region="&region.";
  rc = filename('dirrf', '', 'FILESRVC', cats("folderpath=",quote(trim(fullpath),"'")));
  if rc then do;
    put 'ERROR: Unable to find folder. ' _n_= folderpath=:$quote.;
    return;
  end;
  did = dopen('dirrf');
/* When a folder, explore its contents */
  if did then do; 
/* Update folderpath to the current folder */
    folderpath = fullpath; 
    do i = 1 to dnum(did);
/* File or subfolder name */
      report = dread(did, i); 
      fullpath = cats(folderpath, "/", report);
/* Check if it's another subfolder */
      rc_sub = filename('checksub', '', 'FILESRVC', cats("folderpath=", quote(trim(fullpath),"'")));
      did_sub = dopen('checksub');
/* Ignore folders */
      if did_sub then did_sub = dclose(did_sub);
      else do;
/* It's a file */
        fparent = folderpath;
        fc=filename('filrf','','FILESRVC'
           ,catx(' ',cats("folderpath=",quote(trim(fparent)))
                    ,cats("filename=",quote(trim(report),"'"))
                )
           );
        if fc then put 'ERROR: Unable to find report. ' _n_= i= report= fparent= ;
        else do;
          fid=fopen('filrf');
          if fid then do;
            optval=finfo(fid,'File Identifier');
            description=finfo(fid,'Description');
            hyperlink=cats('https://****/SASVisualAnalytics/?reportUri=/reports/reports/',optval,'&reportViewOnly=true&sas-welcome=false');
  /* Save the file information */
            output; 
            fid=fclose(fid);
          end;
          else put 'ERROR: Unable to open report file. ' _n_= i= report= fparent= ;
          fc= filename('filrf');
        end;
      end;
      rc_sub = filename('checksub');
    end;
    did = dclose(did);
  end;
  rc= filename('dirrf');
  keep region plant report folderpath fullpath optval hyperlink description;
run;

Re: Data step to generate table with all the reports it can find generates an undetermined error

RockSolid — Tue, 21 Jan 2025 16:55:55 GMT

Hi @Tom,

I've tried your version and I get the same result as I used to (thanks for the information on Quote() as it is new for me). The process "works" but it gets out of the data step with an error message:

I tried to do a bit of debugging, starting from scratch and adding new lines to the code, step by step. It seems to me that the issue is with the Filename function and the usage of the File Service ('FILESRVC'). If I use define the file name it will read the files correctly but it will generate an error at the end it contains any folder. Like this:

fc= filename('filrf','','FILESRVC',cats("folderpath='", fparent, "' filename='", report, "'"));

If instead I define the full path inside the folderpath it will work well only if all the children are folders. The moment there's a file in the directory it will not read it correctly and get out of the data step with an error message.

rc = filename('dirrf', '', 'FILESRVC', cats("folderpath='", fullpath, "'"));

So, I just don't know how to get something that works for directories that contain both files and folders. I tried to follow the github that you shared but that person is not using 'FILESRVC' and without it I get no results. I'll keep looking

Re: Data step to generate table with all the reports it can find generates an undetermined error

Tom — Tue, 21 Jan 2025 17:40:20 GMT

Huh?

I am not sure what you are saying works and what does not work.

Can you run the FILENAME() function for a normal file with either method? Or are you required to use the complicated one?
Same for a directory (aka "folder"). Can you run the FILENAME() for a directory with either method? Or do you have to use the simpler one.

Please show examples that with text literals only. So something like this:

data test;
   rc1 = filename('file1',,'FILESRVC','folderpath="/dir1/" filename="report.pdf"');
   rc2 = filename('file2',,'FILESRVC','folderpath="/dir1/report.pdf"');
   rc3 = filename('dir1',,'FILESRVC','folderpath="/dir1/" filename="subdir1");
   rc3b = filename('dir1b',,'FILESRVC','folderpath="/dir1/" filename="subdir1/");
   rc4 = filename('dir2',,'FILESRVC','folderpath="/dir1/subdir1"');
   put (rc:) (=);
run;

Once you know what syntax works you can then begin to write code to generate that syntax.

Re: Data step to generate table with all the reports it can find generates an undetermined error

RockSolid — Tue, 21 Jan 2025 18:21:01 GMT

Hi Tom,

Yes, I see that I explained quite badly.

I ran a variation on your snippet:

data test1;
	* this is a VA Report, a file;
	rc1_a = filename('file1',,'FILESRVC','folderpath="/Public/ODAP_REPORTS/" filename="A0_ODAP_Welcome"');
run;

data test2;
	* this is the same VA Report, a file;
	rc1_b = filename('file2',,'FILESRVC','folderpath="/Public/ODAP_REPORTS/Resources/A0_ODAP_Welcome"');
run;

data test3;
	* this is a directory;
	rc2_a = filename('dir1',,'FILESRVC','folderpath="/Public/ODAP_REPORTS/" filename="Resources"');
run;

data test4;
	* this is the same directory;
	rc2_b = filename('dir2',,'FILESRVC','folderpath="/Public/ODAP_REPORTS/Resources"');
run;

Just to see which version "fails". All of them generate an output table but it is actually the test2 that generates this error message (saying that my code stopped and that the output might be incomplete).

I was using it to check if the elements inside the directory were directories them selves and, if not, I was trying to open it as files.
What I just realized is that doing it the other way around opening everything as a file (defining the filename) and then checking if it is really a file or not, seems to be the way, because it doesn't generate any errors... I guess that's the way to do it?

Re: Data step to generate table with all the reports it can find generates an undetermined error

Tom — Tue, 21 Jan 2025 19:45:36 GMT

Yes. To check if the "file" is a directory you need to:

assign a fileref pointing to it
make sure the fileref is pointing to an existing file.
try to open the fileref as a directory using DOPEN().
If that fails then it is a FILE and not a directory.
if it succeeds you can use DREAD() to get the members. Make sure to close it when done.

The data step running was not the test. You need to check the return code from the FILENAME() function. The return code will tell you if it worked or not. Note that a filename will work when the file does not exist (SAS does not know at that point if you intend to READ or WRITE using the fileref). So you need to check the actual value of the return code to tell whether or not the file was found.

For example check this section of the %DIRTREE() macro:

  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;

A non zero value indicates that either there was a problem with defining the fileref, or the fileref worked but it is not pointing to an existing file.

Once you have a valid fileref you can then try to open it as a directory.

* Try to open as a directory to determine type ;
  did=dopen("&fileref");
  type = ifc(did,'D','F');
  if type='D' then do;

The returned value is pointer to the open directory. A zero value in this case indicates that the directory could not be opened. Do not try to call DOPEN() if the FILENAME() function did not succeed. If the DOPEN() request succeeds make sure to eventually call DCLOSE() to close it again.

If you want to try modifying DIRTREE to work for the FILESRVC fileref engine that line with the FILENAME() function call should be the only thing you need to change.

Re: Data step to generate table with all the reports it can find generates an undetermined error

RockSolid — Wed, 22 Jan 2025 08:23:23 GMT

Hi Tom.

The steps that you mention are precisely what, I'm telling you, will generate an error status at the end of the data step execution. And it's going to happen on the function filename, when I'm trying to assign the fileref pointing to a file (not a directory), using the FILESRVC engine, and using only the folder path:

	rc1_b = filename('file2',,'FILESRVC','folderpath="/Public/ODAP_REPORTS/Resources/A0_ODAP_Welcome"');

That's what I was trying to tell you with my version of the test. Nevermind what the filename returns (your code returned something like this--> rc1_a = 0 rc1_b=10730045 rc2_a = 0 rc2_b = 0) and whether you can open it or not with a DOPEN(). I'm going to get the same error status when the system steps out of the data step:

I did run a version of DIRTREE with FILESERVC. But I always get the same result, as I'm getting from the beginning:

No error message.
Warning telling me that the table might be incomplete and that my code stop.
The correct output table
Error status of the execution --> making subsequent steps fail

Maybe it is just a problem of the FILESERVC engine, and the scheme of trying to open first as a directory is not the right approach, for this engine.

Re: Data step to generate table with all the reports it can find generates an undetermined error

Tom — Wed, 22 Jan 2025 15:03:22 GMT

Perhaps.

What exactly is the WARNING message? You posted a picture of the wrong part of the SAS log. You are just posted the summary that whatever tool (SAS/Studio? EG?) you used to submit the code generated. Instead look for the actual line in the LOG with the WARNING. If you cannot find it then turn off the option that is hiding the autogenerated pre/post code that is added to your code when code is submitted using that tool.

To debug further try hard coding the folder and filenames. So run a step that generates calls the filename() function. Then add one that adds the dopen() call (make sure to remember to add a dclose() if it works). Then add one that adds the DO loop to read the names of all of the files in the folder. etc. etc. until you are able to get the warning again.