Directory Scan Macro

Ever need to read in an entire directory of files into SAS? This macro was developed with Excel files in mind, but you can specify the extension of your choosing.

First – the macro:

/*-------------------------------------------------------------------*\
| directory_scan macro
| MSP 2010-08
|
| this macro will open the directory specified and extract a full
| path and data set name to use for future PROC IMPORTS
|
| the default behavior is to scan for excel files and create a dataset
| called excellist. however, this can be modified using the optional
| keyword parameters defined below.
\*-------------------------------------------------------------------*/

%macro directory_scan(
  /* MANDATORY POSITIONAL PARAMETERS */
  path,              /* the path that you wish to scan */
  /* OPTIONAL KEYWORD PARAMETERS */
  /* EVERYTHING WILL DEFAULT TO EXCEL UNLESS SPECIFIED */
  dsn=excelList,     /* the dataset name to store the information */
  ext=.xls           /* optional parameter, to be used
                        if the extension isn't xls */
  );
data &dsn.(keep= dsName fullPath);
  rc = filename('mydir',&path.);
  dirid=dopen('mydir');
  memcount=dnum(dirid);
  extension = prxparse("|&ext.|");
  do i = 1 to memcount;
    /*-------------------------------------*\
    | for each file in the directory,
    | create a sas dataset name that
    | depends on the filename. The SAS
    | dataset is truncated to 27 characters
    | so that other datasets can be created
    | named after the ones created here.
    \*-------------------------------------*/
    dsName = dread(dirid,i);
    fullPath = &path. || dsName;
    extPos = prxmatch(extension,dsName);
    if extPos > 0 then do;
      dsName = translate(dsName,'_',' ','_','-','_','.','_','&');
      dsName = substr(dsName,1,extPos-1);
      if length(dsName) > 27 then dsName = substr(dsName,1,27);
      output;
    end;
  end;
run;
%mend;

The end result is a dataset that contains a dataset name (based on the filename) and the absolute path to each file in a specified directory.

This makes for a nice combination with the CALL EXECUTE statement – you can create a bunch of different SAS datasets with very little effort.