ExamFinch.com My Study

Study guide

2.4 Importing Delimited & Excel Data with PROC IMPORT

Practice this exam

SAS Certified Specialist: Base Programming Using SAS 9.4 A00-231

Track your progress as you read

Finished sections get a check mark.

1 Chapter 1: SAS Base Programming Exam Overview

1.1 A00-231 Exam Format, Scoring & Test-Day Logistics 1.2 Official Content Areas, Weights & Study Plan

2 Chapter 2: Accessing & Creating SAS Data Structures

2.1 SAS Environment, Syntax Rules, & Data Step Basics 2.2 SAS Data Libraries & the LIBNAME Statement 2.3 Investigating Libraries with PROC CONTENTS & PROC DATASETS 2.4 Importing Delimited & Excel Data with PROC IMPORT 2.5 Raw Data Ingestion with INFILE and INPUT Statements

3 Chapter 3: Managing & Combining SAS Data Sets

3.1 Reading SAS Data Sets with the SET Statement 3.2 Combining Data Sets: Concatenating & Interleaving 3.3 Match-Merging with BY Statements & FIRST./LAST. Processing 3.4 Controlling Data Output & Variable Selection (KEEP, DROP, RENAME, OBS=)

4 Chapter 4: Data Transformation & Conditional Logic

4.1 Creating & Modifying Variables with Assignment Statements 4.2 Modifying Variable Attributes with LENGTH, LABEL, FORMAT & RENAME= 4.3 Conditional Processing: IF-THEN/ELSE Statements & WHERE Subsetting 4.4 Accumulating Totals & Subtotals (RETAIN & Sum Statement) 4.5 Processing Data Iteratively with DO Loops

5 Chapter 5: SAS Functions & Data Type Conversions

5.1 Character Functions for String Manipulation 5.2 Numeric & Mathematical Functions 5.3 Working with SAS Dates, Times, & Intervals 5.4 Reading & Displaying Dates with Informats and Formats 5.5 Explicit & Implicit Data Type Conversions (INPUT & PUT)

6 Chapter 6: Restructuring Data & Program Maintenance

6.1 SAS Array Processing for Multi-Variable Transformations 6.2 Sorting Data Sets with PROC SORT 6.3 Restructuring Data Sets with PROC TRANSPOSE 6.4 Creating & Applying Custom Formats with PROC FORMAT 6.5 Simplifying Programs with Macro Variables (%LET)

7 Chapter 7: Error Handling & Debugging SAS Programs

7.1 SAS Log Analysis & Diagnostic Messages 7.2 Syntax vs. Logic Errors & Phasing 7.3 Debugging Techniques with _ERROR_, _N_, & PUT Statements 7.4 Identifying Logic Errors with the PUTLOG Statement 7.5 Handling Missing Values & Preventing Calculation Propagations

8 Chapter 8: Summary & Detail Data Reporting

8.1 Detail Reporting with PROC PRINT 8.2 Descriptive Statistics with PROC MEANS & PROC SUMMARY 8.3 Frequency & Categorical Analysis with PROC FREQ 8.4 Screening Extreme & Missing Values with PROC UNIVARIATE 8.5 Custom Tabular Reports with PROC REPORT & PROC TABULATE

9 Chapter 9: ODS Output Delivery & Data Export

9.1 Output Delivery System (ODS) Architecture Basics 9.2 Generating PDF, HTML, & RTF Output Destinations 9.3 Exporting Reports to Excel with ODS EXCEL & XLSX 9.4 Generating CSV, PowerPoint & Other ODS Destinations 9.5 Customizing ODS Titles, Footnotes, & Global Options 9.6 Exporting SAS Data Sets with PROC EXPORT & the XLSX Engine

2.4 Importing Delimited & Excel Data with PROC IMPORT

Key Takeaways Explain This Topic AI

PROC IMPORT reads external raw files (CSV, tab-delimited, Excel) and automatically determines variable types, lengths, and informats.

The DBMS= option identifies file types, using CSV for comma-separated, TAB for tab-delimited, DLM for custom delimiters, and XLSX for Excel workbooks.

GETNAMES=YES causes PROC IMPORT to use the first line of the external file for SAS variable names; DATAROW= specifies the row where raw data records begin.

The GUESSINGROWS= option specifies how many rows PROC IMPORT scans (default is 20) to determine variable attributes, which can lead to character truncation if not increased.

Last updated: August 2026

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

Option / Statement Purpose & Exam Rules

DATAFILE="path" Specifies the complete physical file path and filename of the external file to be imported. Enclose in quotes.

OUT=libref.dataset Specifies the target SAS dataset name (two-level for permanent, single-level for WORK).

DBMS=identifier Identifies the physical format of the incoming file ( CSV , TAB , DLM , XLSX ).

REPLACE Instructs SAS to overwrite the output SAS dataset if it already exists in the target library without prompting.

GETNAMES=YES / GETNAMES=NO Determines whether SAS uses the first line of the raw file for SAS variable names. Default is YES .

DATAROW=n Specifies the starting row number where raw data records begin. Default is 2 when GETNAMES=YES , and 1 when GETNAMES=NO .

GUESSINGROWS=n Controls how many rows SAS scans to determine variable types and maximum character lengths (default is 20 rows).

  • 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:

Consecutive Delimiters : Treats two consecutive delimiters (e.g., ,, or || ) as a missing value.

Strip Quotation Marks : Automatically strips surrounding single or double quotes from character values.

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
  • Feature PROC IMPORT DATA Step ( INFILE / INPUT )
  • Attribute Determination Automated scanning ( GUESSINGROWS ) Manually specified informats and lengths

Speed & Control Fast setup, less control over formats Total control over informats, error log, and line pointers

  • Syntax Source DATAFILE="path" INFILE 'path' options;
  • Header Line Control GETNAMES=YES FIRSTOBS=2 (skips header line manually)