topic Re: How to: List all the files in a folder in SAS Programming

How to: List all the files in a folder

MS_Bhati — Mon, 21 Dec 2020 02:30:10 GMT

Hi, I am using SAS University Edition and trying to get names of all the file available in a Folder (Folder inside Shared Folder). So that, I can prepared a Macro to import and append all the data sets in one.
I need help to get names for all the CSV files available in my Folder.

Re: Need alternative of "Filename" statement with Pipe operator for SAS University Edition

ChrisNZ — Mon, 03 Aug 2020 08:00:07 GMT

This question has been asked over and over. Maybe this topic should be a sticky @AnnaBrown @BeverlyBrown ?

Search list files for example.

Out of many replies, here is a clever way, devised by Tom.

data FILELIST;
  format DIR LEVEL 3. PATH FILENAME $256. LASTMOD datetime.;
  retain DIR 0 LEVEL -1 FILENAME ' ' LASTMOD .;
  input PATH;
cards;
c:\temp\
x:\ 
run;
data FILELIST;
  retain SEP "%sysfunc(ifc(&sysscp=WIN,\,/))";
  modify FILELIST;
  RC    = filename('tmp',catx(SEP, PATH, FILENAME));
  DIRID = dopen('tmp');
  DIR   = (DIRID>0);
  if DIR then do;
    PATH     = catx(SEP, PATH, FILENAME);
    FILENAME = ' ';
    if SEP='/' then LASTMOD  = input(dinfo(DIRID, doptname(DIRID, 5)), nldatm100.);
    replace;
    DIR  =0;
    LEVEL=LEVEL+1;
    do I=1 to dnum(DIRID);
      FILENAME=dread(DIRID, I);
      output;
    end;
    RC=dclose(DIRID);
  end;
  else if scan(FILENAME,-1,'.') ne 'lck' then do;
    FID=fopen('tmp','i',0,'b');
    %* == FOPTNAME values for single files ==
         Unix                Win
    1  Filename            Filename
    2  Owner name          RECFM
    3  Group name          LRECL
    4  Access Permission   File Size (bytes)
    5  Last Modified       Last Modified
    6  File Size (bytes)   Create Time      ;
    if FID  then do;
      LASTMOD = input(finfo(FID, foptname(FID, 5)), nldatm100.);
      SIZE    = input(finfo(FID, foptname(FID, ifn(SEP='/',6,4))), 32.);
      RC      = fclose(FID);
      replace;
    end;
  end;
run;

Re: Need alternative of "Filename" statement with Pipe operator for SAS University Edition

Kurt_Bremser — Mon, 03 Aug 2020 08:05:59 GMT

Use the built-in SAS functions:

data filenames;
length fref $8 fname $200;
did = filename(fref,'/folders/myfolders');
did = dopen(fref);
do i = 1 to dnum(did);
  fname = dread(did,i);
  output;
end;
did = dclose(did);
did = filename(fref);
keep fname;
run;

Re: Need alternative of "Filename" statement with Pipe operator for SAS University Edition

ScottBass — Wed, 14 Oct 2020 05:42:22 GMT

Yet another approach...

https://github.com/scottbass/SAS/blob/master/Macro/dirlist.sas

Re: How to: Listing all the files in a folder

LittlesasMaster — Tue, 01 Dec 2020 03:03:00 GMT

You can also see an example from SAS Documentation , a dynamic way of doing this

A Macro which gets you Sub Dir and File Names

%macro list_files(dir,ext);
%local filrf rc did memcnt name i;
%let rc=%sysfunc(filename(filrf,&dir));
%let did=%sysfunc(dopen(&filrf));

%if &did eq 0 %then %do; 
%put Directory &dir cannot be open or does not exist;
%return;
%end;

%do i = 1 %to %sysfunc(dnum(&did));

%let name=%qsysfunc(dread(&did,&i));

%if %qupcase(%qscan(&name,-1,.)) = %upcase(&ext) %then %do;
%put &dir\&name;
%end;
%else %if %qscan(&name,2,.) = %then %do; 
%list_files(&dir\&name,&ext)
%end;

%end;
%let rc=%sysfunc(dclose(&did));
%let rc=%sysfunc(filename(filrf));

%mend list_files;
%list_files(c:\temp,sas)

Re: Need alternative of "Filename" statement with Pipe operator for SAS University Edition

librasonali — Fri, 25 Dec 2020 14:00:54 GMT

hi Kurt ,

May you please explain me the above approach ??
thank you in advance

Re: Need alternative of "Filename" statement with Pipe operator for SAS University Edition

Kurt_Bremser — Fri, 25 Dec 2020 15:02:41 GMT

It first creates a file reference for the directory (FILENAME), opens the directory and creates a handle (DOPEN), and then loops over all (DNUM) entries in the directory and reads each (DREAD). At the end, it deassigns the handle and the file reference.

I recommend studying the documentation of all these functions.

Re: Need alternative of "Filename" statement with Pipe operator for SAS University Edition

Kevin_Viel — Thu, 31 Dec 2020 17:57:51 GMT

I wrote a macro that allows recursive functionality and adds FINFO data. This works on both Windows and Linux, even when the X statement cannot be used. On LSAF, the datetime stamps are moot since the file is copied to the transient directory.

HTH,

Kevin

Re: Need alternative of "Filename" statement with Pipe operator for SAS University Edition

Kurt_Bremser — Thu, 31 Dec 2020 18:12:53 GMT

@Kevin_Viel wrote:

I wrote a macro that allows recursive functionality and adds FINFO data.

You're not the first one 😉

Re: Need alternative of "Filename" statement with Pipe operator for SAS University Edition

Kevin_Viel — Thu, 31 Dec 2020 18:36:04 GMT

No doubt and I did not search, but it might make a suitable SUG paper if one does not already exist or does not have some functionalities. I use it quite frequently. I have read entire directories in minutes, which is fast given the structure and volume, but I am sure it could be faster.

Re: Need alternative of "Filename" statement with Pipe operator for SAS University Edition

Kurt_Bremser — Thu, 31 Dec 2020 19:49:16 GMT

I would have presented it this year in Washington (was an invited speaker), but COVID ...

Actually, a complete session about interacting with the base operating system, with and without XCMD.

I plan it for the next in-person Global Forum.

Re: How to: List all the files in a folder

Peter_C — Sun, 07 Feb 2021 23:23:50 GMT

I know
It's been done (to death😎)
But for such a simple request it's nice to provide the simple solution:
Read all csv files in one folder into one dataset.....
Data resultDS( compress= yes) ;
Length file filename col1-col100 $300 ;
INFILE "yourfolder/?*.csv" lrecl= 32000 dsd filename= file truncover ;
Input col1-col100 ;
Filename=file ;
Run ;

Re: How to: List all the files in a folder

Kevin_Viel — Mon, 08 Feb 2021 14:10:52 GMT

That is a simple approach if the number of files is small and the file sizes are also small. It is a good demonstration. For a single level, i.e. not recursive, Kurt snippet is a great approach. Have you tried this with other file formats, life PDF? I suspect it would still work. I also wonder about using *.*?

Re: How to: List all the files in a folder

Peter_C — Mon, 08 Feb 2021 22:37:49 GMT

Hi Kevin
Any extension of this simple routine should be cautious about handling binary files like .xlsx format. The simple precision of the DSD option in handling "xxxxx separated values" won't work for binary data. However, it only needs a DLM= '09'x added to the INFILE statement to switch to tab separated values. If there is acceptable standard that .csv files need DLM= ',' and .tsv files need DLM= '09'x then these could be dynamically supported in folders with a the mix of file types, because the DLM= value can name a variable which you change depending on the file type/extension. Timing takes care and probably, practise. My guess is the added complexity is not well rewarded.
By the way, the DLM= value can hold more than one character. These then are alternative delimiters. So, if tabs only appear as delimiters in .csv files and likewise commas are protected (by quotes) when not to be treated as delimiters .... then the DLM could be that combination '2c09'x

Re: How to: List all the files in a folder

Kevin_Viel — Tue, 09 Feb 2021 00:28:04 GMT

Peter,

I thought that you were just using this technique to get the filenames. You could forgo the variables and just use INPUT ;

data pdf_files ;

  length file 
         filename $ 256
          ;

  infile "C:\Users\kviel\Documents\My SAS Files\9.4\Documentation\*.pdf" 
         filename = file
         eov = eov
         ;

  input ;

  filename = file ;

  if    _n_ = 1
     or eov = 1
  then 
    do ; 
       output ;
       eov = 0 ;
    end ;

run ;

This is not recursive and, unless one is CERTAIN that the folder contains no child folder, then it reads only files of the specified extension. I toyed with the MEMVAR= option, but I ran out of time. I am not even sure if that is the right direction. Again, this reads every record of every file, so it is not as efficient as Kurt's suggestion.

Kind regards,

Kevin

Re: How to: List all the files in a folder

Peter_C — Tue, 09 Feb 2021 08:57:44 GMT

Hi Kevin
My suggestion was only for readkng contents of the files. I wouldn't use it for file names. However, with some scenarios (e.g. expecting only small files) your proposal works, but excludes empty files. I don't remember what happens for subfolders.

Peter

Re: How to: List all the files in a folder

ScottBass — Tue, 09 Feb 2021 09:13:37 GMT

@Peter_C wrote:
Hi Kevin
My suggestion was only for readkng contents of the files. I wouldn't use it for file names. However, with some scenarios (e.g. expecting only small files) your proposal works, but excludes empty files. I don't remember what happens for subfolders.

Peter

1) If the red text above is correct, then your solution, while it works, isn't on topic. Well, the code is on topic, just not your red text above. The subject was "List all the files in a folder".

2) Even if the proposed code works, I just can't see how reading every line in a file just to get the filename is a good approach? Sure, for small text files in a directory, it might even work faster than the SAS functions used in previous solutions - the data step is very fast at reading text files.

But put 100,000 1MB each XML or binary files in a directory and test the performance of the all the various solutions posted in this thread. You don't know a priori the contents of the directory and can't assume its contents.

Re: How to: Listing all the files in a folder

ScottBass — Tue, 09 Feb 2021 09:15:42 GMT

@LittlesasMaster wrote:

You can also see an example from SAS Documentation , a dynamic way of doing this

A Macro which gets you Sub Dir and File Names

%macro list_files(dir,ext);
%local filrf rc did memcnt name i;
%let rc=%sysfunc(filename(filrf,&dir));
%let did=%sysfunc(dopen(&filrf));

%if &did eq 0 %then %do; 
%put Directory &dir cannot be open or does not exist;
%return;
%end;

%do i = 1 %to %sysfunc(dnum(&did));

%let name=%qsysfunc(dread(&did,&i));

%if %qupcase(%qscan(&name,-1,.)) = %upcase(&ext) %then %do;
%put &dir\&name;
%end;
%else %if %qscan(&name,2,.) = %then %do; 
%list_files(&dir\&name,&ext)
%end;

%end;
%let rc=%sysfunc(dclose(&did));
%let rc=%sysfunc(filename(filrf));

%mend list_files;
%list_files(c:\temp,sas)

This macro looping would be orders of magnitude slower than using corresponding functions in a data step, although it does have the benefit of being recursive.

Re: How to: List all the files in a folder

Peter_C — Tue, 09 Feb 2021 09:16:25 GMT

thanks Scott
Probably put it down to my ARADD
(age related attention deficit disorder)😎

Re: How to: List all the files in a folder

Kevin_Viel — Tue, 09 Feb 2021 14:20:18 GMT

Peter,

It fails when a folder is present:

ERROR: Invalid file, C:\Users\kviel\Documents\My SAS Files\9.4\Documentation\New folder.
NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.PDF_FILES may be incomplete.  When this step was stopped there were 0 observations and 1 variables.
WARNING: Data set WORK.PDF_FILES was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds

Kind regards,

Kevin