How to write a sas code to read file in different folders?

Question

I am wondering is it possible to write a macro to read file in different folders.

Like for example, I have 100 folders and within each folder, it has another folder, within the sub folder It may contain a txt file that I want to read with SAS. New folder can be created to include new file.

Folder1 folder2 file

A1 aa1 file1

A2 aa2 (nofile)

… .. …

A100 aa100 file100

Folder2 is within folder1 and file is stored in folder2.

Thanks

Chen Xu

1. do all files have the exact same structure 2. Is this in Windows or Unix 3. Can you run system commands through SAS on your computer, i.e. X commands or pipe? — Reeza, Dec 05 '14 at 01:58
You need to give a more concrete example. Is the 'may contain' at (effectively) random or patterned? Do you want to read all of these into ONE dataset, or do you want to read them into 100 (or more) datasets? — Joe, Dec 05 '14 at 18:39
If you wish to explore how similar problems have been approached by others you may wish to take a look [here](http://www.sascommunity.org/wiki/SAS_Filesystem_Toolbox) — SRSwift, Dec 07 '14 at 00:59
@SRSwift - fantastic stuff, thanks for helping this question poser and others. — mjsqu, Dec 07 '14 at 23:52

Dirk Horsten · Answer 1 · 2015-01-06T14:02:29.960

The following macro might do the job;

  %macro procesFilesInRoot(rootFolder,fileExt,procesMacro);  
        %put "dir ""%unquote(&rootFolder)"" /s /b";

      filename pipeTree pipe "dir ""%unquote(&rootFolder)"" /s /b" lrecl=32767; 
      data fullNames;
          infile pipeTree truncover;
          input fullName $char1000.;
              ext = upcase(scan(fullName,countw(fullName, '.'), '.'));
          if ext = "%upcase(&fileExt)";
      run;
      filename pipeTree clear; 

        title 'files found';
        proc print;
        run; 

      proc sql noprint;
          select count(*) into :nrFiles from fullNames;
          %let nrFiles = &nrFiles; /** to strip the leading blanks **/
              %if &nrFiles %then %do;
              select fullName into :fullName1-:fullName&nrfiles from fullNames;
              %end;
      quit;

      %do fileNr = 1 %to &nrFiles.;
          %&procesMacro(&&fullName&fileNr);
      %end;
  %mend;

Before using it, you need to write a macro that processes a single input file;

  %macro import1file(fullName);
      %let fileName = %scan(&fullName,%sysfunc(countw(&fullName, '\')), '\');
      %let fileExt = %scan(&fileName,%sysfunc(countw(&fullName, '.')), '.');
      %let dataName = %substr(&fileName,1, %length(&fileName)-%length(&fileExt)-1);

      proc import datafile = "&fullName" out=&dataName;
      run;
  %mend;

Then you can use it as follows;

  %procesFilesInRoot(D:\Horsten,txt,import1file);

How to write a sas code to read file in different folders?

1 Answers1