tip00413

/*** tested only in a WINDOWS Environment - may not work on other OS*/
/* Purpose is to get a list of Files from a Windows Directory       */
/* First parameter is the directory of where your files are stored. */                                                                
/* Second parameter is the extension you are looking for.           */                                                                
/* Leave 2nd paramater blank if you want a list of all the files.   */
/* 3rd parameter is name of SAS Data Set AND SAS Var Name to hold   */
/* the list of files generated                                      */
/* 4th parameter is name of macro var to store how many files retrieved */

%macro list_of_files(dir=C:\, ext= , sasdsn=list_of_files , num_files=numfiles ); 
  %global &num_files;
  %let filrf=mydir;                                                                                                                      
                                                                                                                                        
  /* Assigns the fileref of mydir to the directory and opens the directory */                                                                    
  %let rc=%sysfunc(filename(filrf,&dir));                                                                                                
  %let did=%sysfunc(dopen(&filrf));                                                                                                      
                                                                                                                                        
  /* Returns the number of members in the directory */
  %global memcnt; 
  %let memcnt=%sysfunc(dnum(&did));                                                                                                      
                                                                                                                                        
   /* Loops through entire directory */                                                                                                  
   %do _l_ = 1 %to &memcnt;                                                                                                                
    %global fn_&_l_; /*** global macro var that holds name of file retrieved ***/
     /* Returns the extension from each file */                                                                                                                                    
     %let name=%qscan(%qsysfunc(dread(&did,&_l_)),-1,.);                                                                                   
                                                                                                                                        
     /* Checks to see if file contains an extension */                                                                                     
     %if %qupcase(%qsysfunc(dread(&did,&_l_))) ne %qupcase(&ext) %then %do;                                                                  
                                                                                                                                        
     /* Checks to see if the extension matches the parameter value */                                                                      
     /* If condition is true store the full name to a macro var    */                                                                      
       %if (%superq(ext) ne and %qupcase(&name) = %qupcase(&ext)) or                                                                       
           (%superq(ext) = and %superq(name) ne) %then 
            %let fn_&_l_ = %qsysfunc(dread(&did,&_l_)); /*** store name of file in macro var ***/
     %end;                                                                                                                               
   %end;                                                                                                                                 
                                                                                                                                        
  /* Closes the directory */                                                                                                            
  %let rc=%sysfunc(dclose(&did));                                                                                                        
  
  /*** num_files - initialize to zero ***/
  %let &num_files = 0;

  /*** now read the macro vars and store their values in a SAS dataset ***/
  %if &memcnt > 0 %then %do;
    data &sasdsn; 
     length &sasdsn $256.;
    %do i = 1 %to &memcnt;
     &sasdsn = "&&&fn_&i";
	 if &sasdsn ne ' ' then output;
    %end;
    run;

	data _null_;
	 IF 0 THEN SET &SASDSN NOBS=NUMOBS;
     call symput( "&num_files", PUT(NUMOBS , BEST.));
    run;; 

    /*** delete the user defined macro vars from macro symbol table ***/
    %do i = 1 %to &memcnt;
     %symdel fn_&i / nowarn;
    %end;
  %end;
 
%mend list_of_files;                                                                                                                            
                                                                                                                                        
/*** example calls to macro ***/
/***
%list_of_files(dir=C:\FCSI\BigFile\Auto\turnaround_spreadsheets_RD2, ext=xls, 
               sasdsn=list_of_files1, num_files=numfiles1);
%put &numfiles1 ;

%list_of_files(dir=C:\FCSI\BigFile\Auto\excel_queries, ext=sas , 
               sasdsn=list_of_files2, num_files=numfiles2);
%put &numfiles2 ;
***/

/*** end of tip 00413 ***/