INFILE Statement

Details

• How to Use the INFILE Statement
• Reading from Multiple Input Files
• Reading Multiple External Files Using the FILEVAR= Option
  • Reading File Names Using the DATALINES Statement
  • Cautions When Using IF and DELETE with the FILEVAR= Option
  • Reading Multiple File Names from a SAS Data Set
• Updating External Files in Place
• Accessing the Contents of the Input Buffer
• Reading Delimited Data
• Reading Long Instream Data Records
• Reading Past the End of a Line

How to Use the INFILE Statement

Because the INFILE statement identifies the file to read, it must execute before the INPUT statement that reads the input data records. You can use the INFILE statement in conditional processing, such as an IF-THEN statement, because it is executable. The INFILE statement enables you to control the source of the input data records.

Usually, you use an INFILE statement to read data from an external file. When data is read from the job stream, you must use a DATALINES statement. However, to take advantage of certain data-reading options that are available only in the INFILE statement, you can use an INFILE statement with the file-specification DATALINES and a DATALINES statement in the same DATA step. For more information, see Reading Long Instream Data Records.

When you use more than one INFILE statement for the same file specification and you use options in each INFILE statement, the effect is additive. To avoid confusion, use all the options in the first INFILE statement for a given external file.

Reading from Multiple Input Files

You can read from multiple input files in a single iteration of the DATA step in one of two ways:

• Specify multiple INFILE statements to keep multiple files open and change which file is read. The DATA step reads from multiple input files during each iteration of the DATA step. As SAS switches from one file to the next in a single iteration, each file remains open until the first row of each file is read. The input pointer remains in place for each file to begin reading from that location the next time the INPUT statement executes in the next DATA step iteration.
• Specify the FILEVAR= option in the INFILE statement to dynamically change the current input file within a single DATA step iteration. The FILEVAR= option uses a second INPUT statement in a DO loop to open a file and read all of its rows. It then closes the file and opens the next file listed in the DATALINES statement, and so on, until all the files are read.

See Also

• Reading from Multiple External Files.

Reading Multiple External Files Using the FILEVAR= Option

Reading File Names Using the DATALINES Statement

The FILEVAR=variable in the INFILE statement provides a simple and efficient means of reading multiple external files into a single SAS data set. In this example, SAS uses multiple INFILE statements and a DO loop to read three external files (file1, file2, and file3) into the output data set, output. Here are the contents of the external files:

Contents of External Text Files

In the example, the first INFILE statement specifies the DATALINES option to tell SAS where the input data is located (in the DATALINES statement as a list of file names). The second INFILE statement causes SAS to store the file names read from DATALINES in a temporary variable, file2read. On the next iteration of the DATA Step, the next file name listed in DATALINES is stored in file2read and then opened by the INPUT statement. These iterations continue until the last file listed in DATALINES is read.

data output;                   
   length file2read $40;                         /*1*/
   input file2read $;                            /*2*/
   infile datalines;                             /*3*/
   infile dummy filevar =  file2read end = done; /*4*/
                                                
   do while(not done);                           /*5*/
      input Var1 $ Var2;                        
      output;                                    /*6*/
   end;                                         
datalines;                                       /*7*/
C:\Users\sasuser\file1
C:\Users\sasuser\file2
C:\Users\sasuser\file3
;
proc print data=output; run;

Output SAS Data Set

If you do not use a DO loop that contains the INPUT statement, the DATA step reads only the first record from each of the external files. This is because the DATA step automatically stops processing a file when an INPUT statement reads the last record in the file. When the last record is read, DO processing stops and passes control to the next statement that follows the DO loop. For more information about DATA step processing, see how the DATA step processes input data.

The DO loop usually takes the form of a DO WHILE statement. Note the following about the two types of loops:

• DO UNTIL evaluates the condition at the end of the DO loop. As a result, DO UNTIL statements always execute the loop code at least once. If any of the files being read in are empty, DO UNTIL causes the DATA step to terminate prematurely.
• DO WHILE evaluates the condition at the top of the DO loop. As a result, there might be cases in which the DO WHILE statement does not execute the code in the loop at all. If any of the files being read in are empty, DO WHILE does not cause the DATA step to terminate prematurely.

Cautions When Using IF and DELETE with the FILEVAR= Option

The subsetting IF statement and the DELETE statement might produce unexpected results if either is used with the FILEVAR= option.

When the IF statement condition in a DO loop is not satisfied, SAS stops processing the current iteration of the DATA step and returns processing control to the first executable statement after the DO loop. The same cautions apply for the DELETE statement. When SAS stops processing the current iteration of the DATA step, no more records are read by the DO loop, the current file is closed, and the next file is opened for processing.

See Also

• How the DATA Step Processes Data in SAS Programmer’s Guide: Essentials
• IF Statements in SAS Programmer’s Guide: Essentials

Reading Multiple File Names from a SAS Data Set

You can list the names of the input files in a SAS data set instead of in the DATALINES statement. To do this, you use a SET statement with the INFILE FILEVAR statement to read the input files from the data set.

data one;
   set two;
   infile dummy filevar= file2read end=done;
   do while(not done);
       input var1 $ var2;
       output;
   end;
run;

See Also

• Reading from Multiple External Files.

Updating External Files in Place

You can use the INFILE statement with the FILE statement to update records in an external file:

1. Specify the INFILE statement before the FILE statement.
2. Specify the same fileref or physical file name in each statement.
3. Use options that are common to both the INFILE and FILE statements in the INFILE statement instead of the FILE statement. (Any such options that are used in the FILE statement are ignored.)

See Updating an External File.

To update individual fields within a record instead of the entire record, see the SHAREBUFFERS option.

Accessing the Contents of the Input Buffer

In addition to the _INFILE_ variable, you can use the automatic _INFILE_ variable to reference the contents of the current input buffer for the most recent execution of the INFILE statement. This character variable is automatically retained and initialized to blanks. As with other automatic variables, _INFILE_ is not written to the data set.

When you specify the _INFILE_= option in an INFILE statement, the _INFILE_ variable is also indirectly referenced by the automatic _INFILE_ variable. If the automatic _INFILE_ variable is present and you omit _INFILE_ in a particular INFILE statement, SAS creates an internal _INFILE_ variable for that INFILE statement. Otherwise, SAS does not create the _INFILE_ variable for a particular FILE.

During execution and at the point of reference, the maximum length of this character variable is the maximum length of the current _INFILE_ variable. However, because _INFILE_ references only other variables whose lengths are not known until before the execution phase, the designated length is 32,767 bytes during the compilation phase. For example, if you assign _INFILE_ to a new variable whose length is undefined, the default length of the new variable is 32,767 bytes. You cannot use the LENGTH statement and the ATTRIB statement to set or override the length of _INFILE_. You can use the FORMAT statement and the ATTRIB statement to assign a format to _INFILE_.

As with other SAS variables, you can update the _INFILE_ variable in an assignment statement. You can also use a format with _INFILE_ in a PUT statement. For example, this PUT statement writes the contents of the input buffer using a hexadecimal format.

put _infile_ $hex100.;

Any modification of _INFILE_ directly modifies the current input buffer for the current INFILE statement. The execution of any PUT _INFILE_ statement that follows this buffer modification reflects the contents of the modified buffer.

_INFILE_ accesses only the contents of the current input buffer for an INFILE statement, even when you use the N= option to specify multiple buffers. You can access all the N= buffers, but you must use an INPUT statement with the # line pointer control to make the desired buffer the current input buffer.

Reading Delimited Data

By default, the delimiter that is used to read input data records with list input is a blank space. The delimiter-sensitive data (DSD) option, the DELIMITER= option, the DLMSTR= option, and the DLMSOPT= option affect how list input handles delimiters. The DELIMITER= option or DLMSTR= option specifies that the INPUT statement use a character other than a blank as a delimiter for data values that are read with list input. When the DSD option is in effect, the INPUT statement uses a comma as the default delimiter.

To read a value as missing between two consecutive delimiters, use the DSD option. By default, the INPUT statement treats consecutive delimiters as a unit. When you use DSD, the INPUT statement treats consecutive delimiters separately. Therefore, a value that is missing between consecutive delimiters is read as a missing value. To change the delimiter from a comma to another value, use the DELIMITER= option or DLMSTR= option.

For example, this DATA step program uses list input to read data that is separated with commas. The second data line contains a missing value. Because SAS allows consecutive delimiters with list input, the INPUT statement cannot detect the missing value.

data scores;
   infile datalines delimiter=',';
   input test1 test2 test3;
   datalines;
91,87,95
97,,92
,1,1
;

With the FLOWOVER option in effect, the data set SCORES contains two, not three, observations. The second observation is built incorrectly.

To correct the problem, use the DSD option in the INFILE statement.

data scores;
   input test1 test2 test3;
   datalines;
91,87,95
97,,92
,1,1
;
   infile datalines dsd;

Now the INPUT statement detects the two consecutive delimiters and therefore assigns a missing value to variable TEST2 in the second observation.

The DSD option also enables list input to read a character value that contains a delimiter within a quoted string. For example, if data is separated with commas, DSD enables you to place the character string in quotation marks and read a comma as a valid character. SAS does not store the quotation marks as part of the character value. To retain the quotation marks as part of the value, use the tilde (~) format modifier in an INPUT statement. See Changing How Delimiters Are Treated.

If your data is longer than the default length, you need to use informats. For example, date or time values, names, and addresses can be longer than eight characters. In such cases, you either need to add an INFORMAT statement to the DATA step or add informats directly in the INPUT statement. If you add informats in the INPUT statement, you must add a colon (:) in front of the informat. This tells SAS to read from the first character after the current delimiter to the number of characters specified in the informat.

data a;
   infile 'c:\sas\example.csv' dlm='09'x dsd truncover;
   input fname :$20. lname :$30. address1 :$50. address2 :$50. city :$40.
         state :$2. zip phone :$12.;
run;

Here are some other INFILE options that you might consider using when you read delimited data.

FIRSTOBS=

Use this option to skip a header record by specifying FIRSTOBS=2.

TERMSTR=

Use this option to specify the end-of-line character. This option is useful when you want to share data files that are created on one operating system with another operating system. For example, if you are running SAS in a Windows environment and you need to read a file that was created under UNIX, use TERMSTR=LF.

In SAS 9.4, SAS automatically normalizes imported copies of files that are created in Windows when you are reading the files in a UNIX environment This enables easier sharing of files between the environments.

TRUNCOVER=

Use this option to specify that SAS use only the available input data in the current record to populate as many variables as possible. By default, if the INPUT statement reads past the end of a record without finding enough data to populate the variables listed, the statement continues to read data from the next record. Use the TRUNCOVER= option to keep SAS from reading the next record.

Here are some troubleshooting problems and solutions.

data _null_;
   infile 'c:\_today\mike.csf' recfm=n sharebuffers;
   file 'c:\_today\mike.csv' recfm=n;
   input a $char1.;
   retain open 0;
/* toggle the open flag */
   if a='"' then open=not open;
   if a='0A'x and open then put ' ';
run;

Reading Long Instream Data Records

You can use the INFILE statement with the DATALINES file specification to process instream data. An INPUT statement reads the data records that follow the DATALINES statement. If you use the CARDIMAGE system option, SAS processes the data lines exactly like 80-byte punched card images that are padded with blanks. The default FLOWOVER option in the INFILE statement causes the INPUT statement to read the next record if SAS does not find values in the current record for all of the variables in the statement. To ensure that your data is processed correctly, use an external file for input when record lengths are greater than 80 bytes.

Reading Past the End of a Line

By default, if the INPUT statement tries to read past the end of the current input data record, the statement moves the input pointer to column 1 of the next record to read the remaining values. This default behavior is specified by the FLOWOVER option. A message is written to the SAS log.

NOTE: SAS went to a new line when INPUT
statement reached past the end of a line.

Several options are available to change the INPUT statement behavior when an end of line is reached. The STOPOVER option treats this condition as an error and stops building the data set. The MISSOVER and TRUNCOVER options do not allow the input pointer to go to the next record when the current INPUT statement is not satisfied. The SCANOVER option, used with @'character-string', scans the input record until it finds the specified character-string. The FLOWOVER option restores the default behavior.

The TRUNCOVER and MISSOVER options are similar. The MISSOVER option causes the INPUT statement to set a value to missing if the statement is unable to read an entire field because the value is shorter than the field length that is specified in the INPUT statement. The TRUNCOVER option writes whatever characters are read to the appropriate variable.

For example, an external file with variable-length records contains these records:

----+----1----+----2
1
22
333
4444
55555

The following DATA step reads this data to create a SAS data set. Only one of the input records is as long as the informatted length of the variable TESTNUM.

data numbers;
   infile 'external-file';
   input testnum 5.;
run;

The DATA step creates the three observations from the five input records because by default the FLOWOVER option is used to read the input records.

If you use the MISSOVER option in the INFILE statement, the DATA step creates five observations. All the values that were read from records that were too short are set to missing. Use the TRUNCOVER option in the INFILE statement if you prefer to see what values were present in records that were too short to satisfy the current INPUT statement.

infile 'external-file' truncover;

The DATA step now reads the same input records and creates five observations. The following table compares the SAS data sets.