SAS Infile Statement – Read raw data

SAS INFILE Statement to Read Raw Data

The SAS INFILE statement identifies the source of input records in a DATA step. The source can be an external text file or instream data supplied with a DATALINES statement. The accompanying INPUT statement tells SAS how fields in each record should be assigned to variables.

Use INFILE for raw text formats such as CSV, tab-delimited, pipe-delimited, fixed-width, and log files. Binary workbook and database formats, including native Excel and Microsoft Access files, normally require an appropriate SAS engine, a LIBNAME statement, or an import procedure rather than a raw-data INFILE step. After reading the records, SAS can store them in temporary or permanent SAS datasets.

In a SAS DATA step, the two statements have separate responsibilities:

• INFILE: Identifies the input source and controls how SAS reads physical records. Options can define the delimiter, record length, first record, missing-value behavior, encoding, and other file characteristics.
• INPUT: Names the variables to create and describes how values in each record should be interpreted.

How the SAS INFILE statement works in a DATA step

An INFILE statement is executable and usually appears after the DATA statement and before the INPUT statement. SAS opens the specified input source, obtains a record, and places that record in the input buffer. The INPUT statement then reads values from the buffer into variables in the program data vector.

SAS repeats this process for subsequent records until it reaches the end of the input source. More advanced DATA steps can use multiple INFILE statements or select an input source conditionally, but most programs require only one INFILE statement.

SAS INFILE statement syntax

INFILE file-specification <options> <operating-environment-options>;
INFILE DBMS-specifications;

The first syntax form is used for an external file, a fileref, or instream data. The exact file specifications and operating-environment options available can depend on the SAS environment.

SAS INFILE file specifications

• 'external-file': Supplies the physical path and filename directly in the INFILE statement.
• fileref: Refers to an external file previously associated with a fileref, normally through a FILENAME statement.
• fileref(member): Identifies a member within an aggregate storage location supported by the operating environment.
• DATALINES: Reads data written directly in the SAS program after a DATALINES or CARDS statement.

A fileref separates the physical path from the DATA step and makes the program easier to maintain:

filename employees '/data/input/employees.csv';

data work.employees;
    infile employees;
    input employee_id name $ department $ salary;
run;

The path shown above is only an example. Replace it with a path that is accessible from the SAS session. When SAS runs on a server, a path on the user’s local computer is not automatically a path on that server.

Read instream raw data with SAS INFILE and DATALINES

The following program reads space-delimited records embedded in the SAS program. This is useful for small examples and test data.

Data prasanthdataset1;
Infile datalines;
input id name$ sex$ age sal;
Datalines;
001 Adarsh M 28 40000
002 Malli M 26 35000
003 Prasanth M 27 30000
;
Run;
Proc print data=prasanthdataset1 noobs;
title 'Customer details';
run;

Output from the SAS INFILE instream-data example

Because the records are separated by spaces, list input can read the values without a delimiter option. The dollar sign after name and sex identifies those variables as character variables. The remaining variables are numeric.

Although the variable is named id, it is numeric in this program. Consequently, a value such as 001 is stored as the number 1. Define an identifier as a character variable when its leading zeros must be retained.

Read comma-delimited data with the SAS DSD option

The DSD option changes how SAS processes delimiter-sensitive data. When DSD is used without a DLM= option, the default delimiter becomes a comma. Consecutive delimiters represent missing values, and quotation marks surrounding character values are removed during input.

The existing sample below reads comma-separated instream records:

Data Infileexample;
Infile datalines dsd;
input id name$ sex$ age sal;
Datalines;
001,Adarsh,M,28,40000
002,Malli,M,26,35000
003,Prasanth,M,27,30000
;
Run;

Proc print data=infileexample noobs;
Run;

Output from the comma-delimited SAS INFILE example:

Here, DSD instructs SAS to recognize the commas correctly. For a delimiter other than a comma, combine DSD with an explicit DLM= option.

Read a CSV file with a header using SAS INFILE

A typical CSV file contains a header row, quoted character fields, missing fields, and values that require informats. Assume that employees.csv contains these records:

employee_id,name,hire_date,salary
101,"Asha Rao",2024-01-15,"45,000.50"
102,"Ravi Sen",2024-03-08,
103,"Mina Das",2024-05-20,"38,750.00"

The following DATA step skips the header and reads the remaining records:

filename employee_file '/data/input/employees.csv';

data work.employees;
    length name $40;
    infile employee_file
        dsd
        dlm=','
        firstobs=2
        truncover
        lrecl=32767;

    input employee_id :8.
          name :$40.
          hire_date :yymmdd10.
          salary :comma12.2;

    format hire_date yymmdd10.
           salary comma12.2;
run;

proc print data=work.employees noobs;
run;

FIRSTOBS=2 starts input at the second record, thereby skipping the header. DSD handles quoted CSV fields and missing values. The colon input modifier allows the specified informat to read a delimited field without forcing SAS to consume a fixed number of columns. The YYMMDD10. informat converts date text into a SAS date value, while COMMA12.2 reads the salary after accounting for the comma.

Important SAS INFILE options for raw files

The appropriate INFILE options depend on the structure and quality of the source data.

INFILE option	Purpose
DLM=	Specifies one or more field delimiters, such as a comma, tab, or pipe.
DSD	Treats consecutive delimiters as missing values, removes surrounding quotation marks, and uses a comma as the default delimiter when DLM= is absent.
FIRSTOBS=	Identifies the first physical record SAS should read, commonly used to skip one header row.
OBS=	Identifies the last physical record to read from the input file.
MISSOVER	Prevents SAS from moving to the next record when the current INPUT statement requests more values than remain on the line. Unread variables receive missing values.
TRUNCOVER	Like MISSOVER, prevents the INPUT statement from moving to the next line, but it also allows a short final field to be read from the remaining characters.
FLOWOVER	Allows INPUT to continue reading from the next record when the current record does not contain enough values. This is the normal default behavior.
STOP	Stops the DATA step when an input line does not contain enough values.
LRECL=	Sets the logical record length when input records can be longer than the environment’s default.
ENCODING=	Specifies the character encoding used to read the external file.
EXPANDTABS	Expands tab characters to the appropriate number of spaces before fields are read.

MISSOVER and TRUNCOVER are not interchangeable in every program. TRUNCOVER is often suitable for delimited files when the last field may be shorter than its informat width. Test the selected behavior with incomplete and malformed records from the real source file.

Specify comma, pipe, and tab delimiters in SAS INFILE

Use DLM= to describe the delimiter explicitly. These syntax examples show common delimiter specifications:

/* Comma-delimited records */
infile input_file dsd dlm=',';

/* Pipe-delimited records */
infile input_file dsd dlm='|';

/* Tab-delimited records: hexadecimal 09 */
infile input_file dsd dlm='09'x;

DSD remains useful with pipe- and tab-delimited data when adjacent delimiters represent missing fields or character fields can be quoted.

Use SAS INPUT methods with the INFILE statement

The INFILE statement identifies the records, but the INPUT statement determines how values are extracted from those records. SAS supports several input styles.

• List input: Reads fields separated by delimiters, as in input id name $ age;.
• Modified list input: Uses modifiers and informats for delimited values, as in input name :$40. date :yymmdd10.;.
• Column input: Reads values from specified column positions, as in input id 1-3 name $ 5-24;.
• Formatted input: Uses pointer controls and informats to read values in a defined layout.
• Named input: Reads records containing values written as variable=value.

Select an INPUT method based on the file layout. Delimited files normally use list or modified list input. Fixed-width files normally use column or formatted input.

Read fixed-width raw data with SAS INFILE

In a fixed-width file, each field occupies known column positions. Suppose an input record places the employee ID in columns 1–3, the name in columns 5–24, the hire date in columns 26–35, and the salary in columns 37–46.

filename fixed_file '/data/input/employees_fixed.txt';

data work.employees_fixed;
    infile fixed_file truncover;
    input employee_id 1-3
          name $ 5-24
          hire_date yymmdd10. 26-35
          salary comma10.2 37-46;
    format hire_date yymmdd10.
           salary comma12.2;
run;

Column positions must match the physical file layout. Inspect the raw file in a monospaced editor and confirm whether its positions are defined in bytes or characters, particularly when the data contains multibyte characters.

Read multiple raw data files with one SAS DATA step

A fileref can represent multiple compatible files in environments that support wildcard filenames. The files should use the same record structure. This example reads monthly files that do not contain header rows and records the source filename for each observation:

filename monthly '/data/input/sales_*.csv';

data work.all_sales;
    length product $40 source_file $256;
    infile monthly
        dsd
        dlm=','
        truncover
        filename=current_file;

    input sale_date :yymmdd10.
          product :$40.
          quantity :8.
          amount :comma12.2;

    source_file = current_file;
    format sale_date yymmdd10.
           amount comma12.2;
run;

The FILENAME= option names a temporary variable that contains the current input filename. Wildcard support and filename rules can vary by operating environment. If every file contains its own header row, the DATA step must identify and skip each header rather than relying on a single FIRSTOBS=2 setting.

Difference between SAS INFILE, INPUT statement, and INPUT function

SAS feature	What it does	Typical example
INFILE statement	Identifies and controls the source of raw input records.	infile employee_file dsd firstobs=2;
INPUT statement	Reads fields from the current input record and creates or assigns DATA-step variables.	input id name $ salary;
INPUT() function	Converts a character expression to a value using an informat; it does not open or read an external file.	date_value=input(date_text,yymmdd10.);
PUT() function	Converts a value to character text using a format.	date_text=put(date_value,yymmdd10.);

The INPUT statement and INPUT function share a name but perform different jobs. The statement reads from the DATA step’s input buffer. The function converts a character value that is already available to the program.

Troubleshoot common SAS INFILE errors

• Physical file does not exist: Verify the filename, directory, fileref, server location, case sensitivity, and SAS process permissions.
• Invalid data messages: Check whether the INPUT informat matches the source representation, especially for dates, currency values, and numbers containing commas.
• Values shift into the wrong variables: Confirm the delimiter, quoted-field handling, missing fields, and field order.
• Character values are truncated: Define a sufficient variable length before the INPUT statement. An informat width alone does not always establish the intended storage length.
• SAS reads data from the next line: Review the default FLOWOVER behavior and consider MISSOVER or TRUNCOVER.
• Header text produces invalid-data notes: Skip the header with FIRSTOBS= when there is one header, or explicitly detect repeated headers when combining files.
• Long records are cut off: Set an appropriate LRECL= value and verify the actual record length.
• Accented or non-Latin characters are incorrect: Confirm the input file encoding and the SAS session encoding before specifying ENCODING=.

SAS INFILE statement FAQs

What is the difference between INFILE and DATALINES in SAS?

INFILE identifies the source that SAS should read. DATALINES marks raw data embedded directly in the SAS program. A program can use infile datalines; to apply INFILE options to those embedded records.

How does SAS INFILE skip a CSV header row?

Use FIRSTOBS=2 when a single-file CSV has one header record. When reading multiple files containing repeated headers, detect and skip each header within the DATA step instead of assuming that FIRSTOBS=2 applies separately to every file.

When should DSD and DLM= be used together in SAS?

Use DLM= to state the delimiter and DSD when consecutive delimiters should indicate missing fields or quoted character values should have their surrounding quotation marks removed. For ordinary CSV input, DSD DLM=',' makes both intentions explicit.

What is the difference between MISSOVER and TRUNCOVER in SAS INFILE?

Both options prevent the INPUT statement from moving to a new record when the current one is too short. MISSOVER assigns missing values to fields that cannot be read. TRUNCOVER also allows the remaining characters of a short final field to be used rather than requiring the complete informat width.

Can SAS INFILE read Excel files directly?

INFILE reads records from raw or text-oriented sources; it does not interpret the internal structure of a native XLSX workbook. Use a supported Excel LIBNAME engine, PROC IMPORT, or export the worksheet to CSV and then read the CSV with INFILE.

SAS INFILE tutorial editorial QA checklist

• Verify that every physical path is valid from the machine or server where the SAS session runs.
• Confirm that the file is raw text before recommending INFILE instead of an engine or import procedure.
• Compare the DLM= value with the delimiter present in the actual records.
• Test quoted fields, adjacent delimiters, embedded delimiters, and missing final fields when using DSD.
• Confirm that character variable lengths are defined before INPUT where truncation is possible.
• Check date, time, currency, and numeric fields against the informats used by INPUT.
• Verify whether FIRSTOBS= should skip one header or whether every combined file contains a separate header.
• Test incomplete records to confirm that FLOWOVER, MISSOVER, TRUNCOVER, or STOP gives the intended result.
• Review the SAS log for invalid-data notes, lost-card messages, uninitialized variables, and file-access errors.
• Compare the number of output observations with the expected number of source data records.