HomeSAS Fundamentals

Proc Import in SAS7 min read

Like Tweet Pin it Share Share Email

PROC IMPORT in SAS is used to read data to SAS. Reading data from an external file is the most frequent task of a SAS programmer.

One thing to remember is that PROC IMPORT procedure can import data only that data type is supported by SAS. SAS can import only numeric and character data types.


PROC IMPORT DATAFILE=filename OUT=sas-dataset<data-set-options> DBMS= data-source-identifier REPLACE;
SHEET= sheet-name GETNAMES=Yes/No DATAROW=n;


DATAFILE=filename: It is used to specify the complete path and filename or fileref for the input file. A fileref is a SAS name that is associated with the physical location of the output file.

Note: A pathname for a file can have a maximum length of 201 characters.

OUT= SAS data-set: This tells SAS to create a SAS dataset. By default, SAS dataset is created in your work library. You can also specify a two-level SAS dataset name.

Read, Working with SAS libraries

DBMS=data-source-identifier: This is an optional argument and is used to specify the type of data to import. To import a DBMS table, specify DBMS = using a supported database identifier.

For example, to import a tab-delimited file you must specify TAB as the identifier. If you have a delimited file that does not end in .csv specify the DLM as the identifier and the  delimiter= option. The default delimiter is blank.

The below table shows some of the values that are frequently used for the DBMS identifier.

DBMS Identifiers Supported in Base SAS

IdentifierOutput Data SourceExtension
CSVDelimited file (comma-separated values).csv
DLMDelimited file (other than blank).dat or .txt
TABDelimited file (tab-delimited values).txt
XLSExcel 97-2003 workbooks.xls
XLSXMicrosoft Excel 2007 and later.xlsx
ACCESSMicrosoft Access 2000 and later.mdb


You can find the Other DBMS here in the SAS Help Center.

REPLACE: This is also an optional argument. If you specify the REPLACE option, it overwrites an existing SAS data set.

If you do not specify REPLACE, the IMPORT procedure and the dataset already exists, the PROC IMPORT procedure does not overwrite the existing dataset and a NOTE is written in the log with the below message:

NOTE: Import cancelled. Output dataset dataset-name already exists. Specify REPLACE option to overwrite it.

SHEET: When you have to import data from a specified sheet in excel, you can use this option to specify the sheet name. By default, SAS will import sheet1 from excel.

GETNAMES: SAS imports the first row of an excel sheet as the Variable name for the SAS variable. Similarly, if you specify No option, it will tell SAS, not to use the first row of data as variable names and instead, SAS creates variable names as VAR1, VAR2 and so on.

DATAROW= Using this option you can specify the starting row from where SAS would import the data. If you omit this option, SAS will import data starting from the 1st row of excel.

An important point to note here is:

When GETNAMES=YES, DATAROW must be greater than or equal to 2.
When GETNAMES=NO, DATAROW must be greater than or equal to 1

RANGE= For specifying the range of rows and columns of an excel sheet, use the range option with the sheet range as arguments.

Importing a Delimited File

The below code snippets is used to import a delimited .txt file and the records are separated by ‘|’. Note the use of DBMS=DLM and delimiter='|' options.

Dulce|Abril|Female|United States|32
Mara|Hashimoto|Female|Great Britain|25
Kathleen|Hanner|Female|United States|25
Nereida|Magwood|Female|United States|58


proc import datafile="/home/9to5sas/examples/data.txt" dbms=dlm 
		out=mydata replace;
proc print data=mydata;

proc import in sas

Note: To import a tab-delimited file specify the delimiter option as below.


Importing a Tab-Delimited File into SAS

The below code snippet is an example of importing a Tab-delimited file in SAS. Note the delimiter=’09’x;

PROC IMPORT DATAFILE= "/home/9to5sas/examples/data.txt"
OUT= mydata replace

Importing a Space-Delimited File

To import a space-delimited file , specify delimiter = ’20’x

PROC IMPORT DATAFILE= "/home/9to5sas/examples/data.txt"
OUT= mydata replace

Importing a Comma-Delimited File with TXT extension

To import a comma-separated file that has a  txt extension, specify delimiter = ‘,’

PROC IMPORT DATAFILE= "/home/9to5sas/examples/data.txt"
OUT= mydata replace

Importing a Specific Delimited File Using a Fileref

When you use a fileref to specify a delimited file to import, the logical record length (LRECL) defaults to 256, unless you specify the LRECL= option in the FILENAME statement. The maximum LRECL that the PROC IMPORT procedure supports is 32767.

FirstName LastName Gender Country Age
Dulce Abril Female 'United States' 32
Mara Hashimoto Female 'Great Britain' 25
Philip Gent Male France 36
Kathleen Hanner Female 'United States' 25
Nereida Magwood Female 'United States' 58
filename mydata2 '/home/9to5sas/examples/data2.txt' lrecl=100;
proc import datafile=mydata2 dbms=dlm out=mydata2 replace;
proc print data=mydata2;

Proc Import Output

Importing a Comma-Delimited File with a CSV Extension

By default PROC IMPORT procedure recognizes .csv as an extension for a comma-separated file so if you are importing a .csv file DBMS option is not required. However, it is required if you are importing a.txt file that has comma-delimited data.

proc import datafile="/home/9to5sas/examples/mydata3.csv"
proc print data=mydata3;

Proc Import Output

Importing a file containing multiple delimiters

If two or more delimiters are present in the input file, quote each of the delimiters following the delimeter= option.

PROC IMPORT DATAFILE= "/home/9to5sas/examples/mydata.txt"
OUT= outdata
delimiter=','09'x ';

Importing records from a specified row

You can tell SAS to start reading from a specified row using the DATAROW= option.

Here is an example.

proc import datafile=mydata2
proc print data=mydata4;

Data will be read from row 5 due to the DATAROW= option.

Proc Import datarow option

Importing variable names other than the first row

Suppose you have variable names starting from the 2nd row in an excel file. In this case, you can use DATAROW= and STARTROW=

variable variable variable variable variable
First Name Last Name Gender Country Age
Dulce Abril Female United States 32
Mara Hashimoto Female Great Britain 25
Philip Gent Male France 36
Kathleen Hanner Female United States 25
Nereida Magwood Female United States 58
proc import datafile="/home/subhroster20070/examples/mydata5.xls"
        out=mydata5 dbms=xls replace;
proc print;

NAMEROW=2 tells SAS to read variable names from the second row and STARTROW=3 is used to read values from starting from the third row. You need to use both the options otherwise the variable names will also be read in the 2nd observation.

Note: NAMEROW only works with XLS and not with XLSX format. For XLSX formats you can use the RANGE= option discussed later in this post.

Using the RANGE= option

To import a specific range of values, you can use the RANGE= option as below.

proc import datafile="/home/9to5sas/examples/data8.xls"
        out=mydata5 replace dbms=xls;
        range="data8$a15:e28" ;
proc print;


Using the GUESSINGROWS= option

For delimited files, SAAS scans the first 20 rows are scanned to determine the variable type and length attributes. You can increase the number of rows that are scanned by using the GUESSINGROWS= statement.

Here is a scenario.

Suppose you have first 30 rows as numeric then the remaining are character values. SAS would make all the character values blank. To avoid this you can use specify the GUESSINGROWS=100 option.

See the column last name and Age in the below input file.

input file

Row Number 29 has a character value in the Age field and Row Number 35 has a length more than the first 20 rows.

Proc Import guessing rows

While importing this file, SAS writes an error message in the log. This is because SAS has determined the datatype of Age as Numeric by scanning the first 20 rows. And there is a character variable in the Numeric field. Hence the error.

Also, see the output below where there is a missing value at observation 28 and at observation 34, the value of the last name variable has been truncated.


To avoid these error and truncation of values, use the guessing rows option with a value of more than the rows in your data or use guessingrows=Max.

Note that if your input data contains thousands of records it would make the import process slow. Here, I have used GUESSINGROWS=100.

proc import datafile="/home/9to5sas/examples/data8.csv"
        out=mydata5 replace;
proc print;

Also read, Tips for using the IMPORT procedure to read files that contain delimiters

Comments (0)

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.