Reading multiple external files - SAS FILEVAR Infile statement option

Suppose the scenario is; you need to combine different external files into one data set; such that external files have been placed in differenation locations and with different names but have the same sturucture (meaning number and type of variables and their sequence is same for all files). In this case one can use FILEVAR option. Using FILEVAR option we can read multiple external files at a time; however for this we need a list of external files to read. There might be two scenarios in this case

· When names of multiple input files are stored in another file

· When names of the files are stored in another SAS datase

Lets find out; how to wrok out in each of these cases above.

· When files names are stored in another txt file

Suppose there are 3 text files lying at the different locations on the hard drive.

And we need retrieve the from those files.

These files contain data for 3 variables file_name, id, user_name.

Those three files are having data as shown below.

SAS Code

DATA output_data_set;

INFILE ‘path of a file in which file names have been stored’

/* read the file references in variable called file_names */

INPUT file_names $20.;

INFILE IN FILEVAR = file_names END = end_of_file;

DO WHILE (end_of_file = 0)

INPUT ………;

OUTPUT;

END;

RUN;

First INFILE statement in the code above points to file in which names of files to be read are saved. When it encounters input statement first file name to read is stored in PDV, In second INFILE statement using FILEVAR option SAS opens the first file in memory and keeps the pointer on the first record, using Do While loop SAS reads all the file till the end using END= option and outputs to the output_data_set.

When Do While loop finishes reading first file, in next iteration of data set second files name gets assigned to file_names value, and with same processing above it reads rest of the files and appends all the records to the output_data_set.

· When file names are stored in another SAS dataset

SAS code

DATA output_data_set;

SET data_set_name_inwhich_filenames_are_stored;

INFILE IN FILEVAR = FILE_NAMES END = end_of_file;

DO WHILE (end_of_file = 0)

INPUT ………;

OUTPUT;

END;

RUN;

This code works same as the explanation given in above case, just the difference is that instead of reading the file names from another file it reads it from SAS data set; so we have SET statement in this case instead of INFILE statement.