Stat Computing > SAS > FAQ				Help the Stat Consulting Group by giving a gift
Loading

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

UCLA Researchers are invited to our Statistical Consulting Services
We recommend others to our list of Other Resources for Statistical Computing Help
These pages are Copyrighted (c) by UCLA Academic Technology Services