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.
- 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:workraw_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
- 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 x31 C:workraw_data_filesfile01.txt John 12 354 7 2 C:workraw_data_filesfile01.txt Carl 43 657 9 3 C:workraw_data_filesfile01.txt Mary 343 7 9 4 C:workraw_data_filesfile3.txt adam 12 354 7 5 C:workraw_data_filesfile3.txt brad 43 657 9 6 C:workraw_data_filesfile3.txt tyler 343 7 9 7 C:workraw_data_filesfile7.txt mary 343 56 2 8 C:workraw_data_filesfile7.txt robert 243 67 8 9 C:workraw_data_filesfile7.txt brad 43 657 9 10 C:workraw_data_filesfile7.txt tyler 343 7 9