2.4 Importing Delimited & Excel Data with PROC IMPORT

2.4 Importing Delimited & Excel Data with PROC IMPORT

Data analysts and programmers frequently exchange data between SAS and external file formats such as comma-separated values (.csv), tab-delimited text (.txt), and Microsoft Excel (.xlsx) workbooks. PROC IMPORT is the built-in utility that reads those external files into structured SAS data sets without writing a single INPUT statement. Understanding its option syntax, defaults, and data-ingestion pitfalls is directly tested by the "Access data" objective of the A00-231 content guide. (The mirror-image task — writing SAS data out to CSV, tab, JMP, and Excel files — is covered in the Export section of the ODS and data-export chapter.)

---

1. PROC IMPORT Architecture & Mechanics

Unlike a SAS DATA step (which requires explicitly declaring variable names, informats, and lengths with INFILE and INPUT statements), PROC IMPORT scans an external raw data file, automatically analyzes column contents, infers variable data types (character vs. numeric), determines required variable lengths, and generates an internal DATA step to write the resulting SAS dataset.

PROC IMPORT DATAFILE="external-file-path"
    OUT=output-sas-dataset
    DBMS=file-type-identifier
    REPLACE;
    GETNAMES=YES | NO;
    DATAROW=row-number;
    GUESSINGROWS=n | MAX;
RUN;

Essential PROC IMPORT Options & Statements

---

2. Importing Delimited Text Files (CSV, Tab, & Custom Delimiters)

Comma-Separated Values (DBMS=CSV)

Comma-separated files treat commas as field separators. PROC IMPORT automatically applies Delimiter-Sensitive Data (DSD) logic when processing CSV files.

proc import datafile="/folders/myshortcuts/data/customer_records.csv"
    out=work.customers
    dbms=csv
    replace;
    getnames=yes;
    datarow=2;
run;

Custom Delimited Files (DBMS=DLM)

When raw files use pipe (|), semicolon (;), or other non-standard delimiters, specify DBMS=DLM and include the DELIMITER= statement inside the procedure block:

proc import datafile="C:\Data\inventory_pipe.txt"
    out=work.inventory
    dbms=dlm
    replace;
    delimiter='|';
    getnames=yes;
run;

DSD (Delimiter-Sensitive Data) Rules in Delimited Import:

1. Consecutive Delimiters: Treats two consecutive delimiters (e.g., ,, or ||) as a missing value.
2. Strip Quotation Marks: Automatically strips surrounding single or double quotes from character values.
3. Embedded Delimiters: Preserves delimiters enclosed within quotes (e.g., "Chicago, IL" is read as a single field rather than split into two columns).

---

3. Importing Microsoft Excel Workbooks (DBMS=XLSX)

SAS allows direct reading of modern .xlsx workbooks via DBMS=XLSX. You can target specific worksheets or cell ranges using procedure statements:

/* Importing a specific worksheet */
proc import datafile="C:\Projects\Financial_Q4.xlsx"
    out=work.q4_regional
    dbms=xlsx
    replace;
    sheet="Regional_Breakdown";
    getnames=yes;
run;

/* Importing a specific cell range */
proc import datafile="C:\Projects\Financial_Q4.xlsx"
    out=work.q4_range
    dbms=xlsx
    replace;
    range="Sheet1$A1:D50";
    getnames=yes;
run;

Header Normalization Note: When importing Excel files, column names with spaces or special characters (e.g., Monthly Revenue ($)) are normalized by SAS into valid SAS variable names (e.g., Monthly_Revenue___) unless OPTIONS VALIDVARNAME=ANY; is set.

---

4. The GUESSINGROWS Problem & Character Truncation Pitfalls

One of the most frequent exam questions involves how PROC IMPORT determines variable character lengths.

By default in Base SAS, PROC IMPORT reads only the first 20 rows (GUESSINGROWS=20) of the raw file to infer variable data types and column lengths:

• If a text column contains strings up to 10 characters long in the first 20 rows, SAS assigns a length of $10. to that variable in the dataset descriptor.
• If row 50 contains a string that is 40 characters long, SAS truncates the value to 10 characters during execution without halting the program!

To prevent silent truncation errors on large datasets, explicitly specify GUESSINGROWS=MAX; or a higher row limit:

proc import datafile="C:\Data\large_survey.csv"
    out=work.survey_results
    dbms=csv
    replace;
    guessingrows=max; /* Scans all rows in the file to set exact max lengths */
run;

---

5. Comparative Overview: PROC IMPORT vs. DATA Step INFILE