SAS: IDENTIFY AND EXTRACT ALL EXCEL FILE NAMES IN A SPECIFIED FOLDER in SAS

In SAS, there are various functions to process external files. These functions can open a
directory, determine the number of members of the directory, identify the file names that can be used to process the
data, and close the directory. File processing can be done dynamically without ever knowing the physical file name.

Few functions are DOPEN, DNUM, DREAD

DOPEN - This external file function opens a directory using a file reference as the argument and returns a numeric directory identifier value. If the value returned is a 0, then the directory was not found. Otherwise, a returned value greater than 0 identifies the opened directory and can be used in other SAS external file functions.

DNUM - This external file function returns the number of members in a directory using the directory identifier returned by the DOPEN function.

DREAD - This external file function uses the identifier returned by the DOPEN function and returns a directory member name. By using the value of the total number of files in a directory returned by DNUM, all files in a directory can be processed in a loop.


Example - IDENTIFY AND EXTRACT ALL EXCEL FILE NAMES IN A SPECIFIED FOLDER - Assume you have a folder which contains many files of different types and you want to extract the file names of one particular type (e.g., Excel workbooks); The following DATA step will extract their file names:



DATA xls_files(keep=file_name);
LENGTH file_name $30 ;
rc= filename("dir","~");
d=DOPEN("dir");
n=DNUM(d);
do i=1 to n;
file_name=DREAD(d,i) ;
if LOWCASE(SCAN(file_name,2,'.')) EQ 'xls' then output;
end;
run;