SAS FAQ
How do I read multiple raw data files with the same structure in one data step?

Let's say that we have multiple raw data files in a folder with the same data structure and we need to read them into SAS to form a single SAS data set. This can actually be done in SAS in a single data step. Here is an example demonstrating the steps to accomplish that for Windows operating system environment. There are mainly two data steps. Step one is to create a data file consisting of all the file names. Step two is to create a single data file combining all the data read from the files named in the first step.

For the example, we assume that we are working on a Windows machine and that we are going to read in all the text data files with .txt extension in a folder. 

  1. We start with storing the path to the folder to a macro variable. On a Windows machine, then, we can use the DOS command "dir" with the /B option to list only the file names. The SAS keyword filename is used to point to the list via the pipe option.
    %let dirname = c:\work\raw_data_files;
    filename DIRLIST pipe "dir /B &dirname\*.txt";
    
    data dirlist ;
         length fname $256; 
         infile dirlist length=reclen ;
         input fname $varying256. reclen ;
    run;
    proc print data = dirlist;
    run;
    Obs    fname
    
      1    file01.txt
      2    file3.txt
      3    file7.txt
  2. Now we are going to make use the data set created in previous step as follows.
    • Creating a variable filepath that has the path and the file name;
    • Issuing an infile statement using the value in filepath as the physical file name;
    • Using the end = option in the infile statement to indicate the end of the current data file;
    • Looping through the current file specified in the infile statement to read in all the observations;
data all_text (drop=fname);
  length myfilename $100;
  length name $25;
  set dirlist;
  filepath = "&dirname\"||fname;
  infile dummy filevar = filepath length=reclen end=done missover;
  do while(not done);
    myfilename = filepath;
    input name $  x1 x2 x3;  
    output;
  end;
run;
proc print data=all_text;
run;
Obs            myfilename                    name      x1     x2    x3
  1    C:\work\raw_data_files\file01.txt    John       12    354     7
  2    C:\work\raw_data_files\file01.txt    Carl       43    657     9
  3    C:\work\raw_data_files\file01.txt    Mary      343      7     9
  4    C:\work\raw_data_files\file3.txt     adam       12    354     7
  5    C:\work\raw_data_files\file3.txt     brad       43    657     9
  6    C:\work\raw_data_files\file3.txt     tyler     343      7     9
  7    C:\work\raw_data_files\file7.txt     mary      343     56     2
  8    C:\work\raw_data_files\file7.txt     robert    243     67     8
  9    C:\work\raw_data_files\file7.txt     brad       43    657     9
 10    C:\work\raw_data_files\file7.txt     tyler     343      7     9

How to cite this page

Report an error on this page or leave a comment

The content of this web site should not be construed as an endorsement of any particular web site, book, or software product by the University of California.