Skip to content
Tutorials Center
My learning

Topics

Lesson 12 of 37 ยท Reading and Writing Data

INFILE and PROC IMPORT: Reading CSV and Excel Files into SAS

Read external files in SAS: FILENAME and INFILE options (DLM, DSD, FIRSTOBS, MISSOVER), PROC IMPORT for CSV and Excel, GUESSINGROWS, and the XLSX LIBNAME engine.

Most real data lives in files: CSV exports, text reports, Excel workbooks. SAS offers two approaches. The INFILE statement gives you full control, and PROC IMPORT guesses the structure for you. Knowing both lets you choose speed or precision. Because SAS cannot run on this site, paths below are examples to adapt, and the stand-in at the end is a Python model of the parsing rules.

FILENAME: nicknames for files

A FILENAME statement is a global statement that assigns a fileref (a nickname) to a file or folder:

SAS
filename sales "C:\training\data\sales.csv";

You can then write infile sales;. You can skip the fileref and write the quoted path directly: infile "C:\training\data\sales.csv";. A fileref is useful when the same file appears in several steps.

Reading a CSV with INFILE

Imagine sales.csv begins with a header line:

TEXT
region,month,units,price
East,2024-01,120,9.99
West,2024-01,95,10.49

A DATA step that reads it:

SAS
data sales;
   infile "C:\training\data\sales.csv"
          dsd dlm="," firstobs=2 truncover;
   length region $ 8 month $ 7;
   input region month units price;
run;

The options you will use constantly:

Option Meaning
dsd Quotes and consecutive delimiters handled correctly (comma default)
dlm="," Delimiter; dlm="09"x for tab, `dlm="
firstobs=2 Begin at line 2, skipping a header
obs=n Stop after line n (good for testing)
missover / truncover Handle short lines without reading the next line
lrecl=n Maximum record length, when lines are very long
encoding="utf-8" Specify a character encoding
end=eof Create a variable that becomes 1 on the last line

Because list input cannot guess lengths, declare length for character variables and use informats for dates and special numbers. For example, input region $ month :yymmn6. .... For files with irregular lines, truncover is more forgiving than missover. The _infile_ automatic variable holds the raw line and helps debugging: put _infile_;.

Fixed-width and other text files

For fixed-width layouts, drop dsd and dlm and use column or formatted input:

SAS
data report;
   infile "C:\training\data\report.txt" pad;
   input @1 id 5. @6 name $20. @26 balance comma10.2;
run;

pad pads short records with blanks, so the last field is read even on a short line.

PROC IMPORT

For a quick start, let SAS look at the file and build the data set:

SAS
proc import datafile="C:\training\data\sales.csv"
            out=work.sales
            dbms=csv
            replace;
   getnames=yes;
   guessingrows=max;
run;
  • datafile= names the input; out= names the output data set.
  • dbms=csv says what kind of file it is (tab, dlm with delimiter=, xlsx for Excel and others).
  • replace allows overwriting an existing output.
  • getnames=yes uses the first row as variable names.
  • guessingrows= sets how many rows SAS scans to decide a column's type and length. The default is small, so a text value that appears on row 5,000 may be cut or cause an error. guessingrows=max scans all rows and is the safest choice.

PROC IMPORT guesses, and guesses can be wrong. Zip codes with leading zeros become numbers, a column with mostly numbers and a few n/a entries may import as character or as numeric with missing values, and long text may be truncated. For production jobs write an explicit DATA step. A neat compromise: run PROC IMPORT once, copy the DATA step code it writes to the log, and edit it.

Reading Excel workbooks

With the SAS/ACCESS Interface to PC Files (or the XLSX engine available in modern SAS), you can import a sheet:

SAS
proc import datafile="C:\training\data\budget.xlsx"
            out=work.budget dbms=xlsx replace;
   sheet="Q1";
   getnames=yes;
run;

or treat the whole workbook as a library, where each sheet is a data set:

SAS
libname wb xlsx "C:\training\data\budget.xlsx";
proc print data=wb.q1; run;
libname wb clear;

Clear the libref afterwards, otherwise Excel (or the file system) may keep the workbook locked. Availability of these engines depends on your licenses and platform, so check with your administrator if dbms=xlsx fails.

Checklist after reading a file

  1. proc contents and proc print data=... (obs=10) to inspect types and values.
  2. Search the log for Invalid data, lost card and truncated messages.
  3. Compare the observation count with the number of data lines (minus the header).
  4. Check key columns for unexpected missing values.

A parser model

This Python stand-in (not SAS) shows firstobs, dsd and truncover behavior on an in-memory CSV, so you can experiment with ragged lines.

Python Python stand-in: INFILE with FIRSTOBS, DSD and TRUNCOVER

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which option skips a one-line header when reading a CSV with INFILE?

    firstobs=2 tells SAS to start reading at line 2.
  2. 2. What does guessingrows=max in PROC IMPORT do?

    A larger scan reduces wrong guesses such as truncated text or wrong types.
  3. 3. Why use INFILE with explicit INPUT rather than PROC IMPORT in production?

    Guesses can change between runs when the data changes; an explicit step is stable.
  4. 4. What does the DSD option do about 'North, Inc' in a comma-delimited file?

    DSD honours quotation marks and strips them.

Summary

  • filename gives a nickname to a file; infile points a DATA step at it.
  • For CSV use dsd dlm="," firstobs=2 truncover, declare lengths and use informats.
  • proc import guesses structure (dbms=, getnames=, guessingrows=) and is quick but not guaranteed to be right.
  • Excel can be read with dbms=xlsx or the xlsx libname engine, subject to your licenses.
  • Always inspect the result and the log after reading.

Next: Writing Data with FILE, PUT and PROC EXPORT