PROC IMPORT Statement

DATAFILE="file-name"

specifies 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. To assign a fileref, use the FILENAME= statement. You can omit the quotation marks if the file-name does not include certain characters such as these:

• backslash

• lowercase characters

• spaces

Alias:	FILE
See also:	The FILENAME statement in SAS Language Reference: Dictionary
Restriction:	The IMPORT procedure does not support device types or access methods for the FILENAME statement except for DISK. For example, the IMPORT procedure does not support the TEMP device type, which creates a temporary external file.
Restriction:	For client server applications. When running SAS/ACCESS on UNIX, to access data stored on a PC server, you must specify the full path and filename of the import file. The use of a fileref is not supported.
Restriction:	The IMPORT procedure can import data if the data type is supported by SAS. SAS supports numeric and character types of data but not (for example, binary objects). If the data that you want to import is a type that SAS does not support, the IMPORT procedure might not import it correctly. In many cases, the procedure attempts to convert the data to the best of its ability. However, at times this is not possible.
Interaction:	For some input data sources such as a Microsoft Excel spreadsheet, the first eight rows of data are scanned. The most prevalent data type (numeric or character) is used for a column. This is the default. If most of the data in the first eight rows is missing: SAS defaults to data type: character any subsequent numeric data for that column is set to missing. If most of the data in the first eight rows is character data: SAS assigns data type: character any subsequent numeric data for that column is set to missing.
Default:	character
Note:	For information about how SAS converts data types, see the specific information for the data source file format that you are importing.
Note:	To import DBF files created with Microsoft Visual FoxPro, you must export to an appropriate dBASE format using Visual Fox PRO. Import the dBASE file to SAS.

DATATABLE="table-name"

specifies the table name of the input DBMS table. If the name does not include special characters, such as question marks, lowercase characters, or spaces, you can omit the quotation marks. The DBMS table name might be case sensitive.

Alias:	TABLE
Requirement:	When importing Microsoft Access tables, SAS/ACCESS converts the table name to a SAS member name. SAS does not support member names longer than 32 bytes.
Requirement:	When you import a DBMS table, you must specify the DBMS= option.

OUT=<libref.>SAS-data-set

identifies the output SAS data set with either a one- or two-level SAS name (library and member name). If the specified SAS data set does not exist, The IMPORT procedure creates it. If you specify a one-level name, by default the IMPORT procedure uses either the SASUSER library if assigned, or the WORK library if SASUSER not assigned.

Featured in:

All examples.

(SAS data-set-options)

specifies SAS data set options. For example, to assign a password to the resulting SAS data set, you can use the ALTER= , PW= , READ= , or WRITE= data set options. To import only data that meets a specified condition, you can use the WHERE data set option.

For information about all SAS data set options, see the data set options section in SAS Language Reference: Dictionary.

DBMS=data-source-identifier

specifies the type of data to import. To import a DBMS table, you must specify DBMS = using a valid database identifier; DBMS=ACCESS specifies to import a Microsoft Access 2000, 2002, or 2003 table. Only specify the DBMS= option to import files, if DATAFILE contains an extension that the IMPORT procedure cannot recognize.

For example, the IMPORT procedure recognizes the filename ACCOUNTS.WK1 as a Lotus 1-2-3 Release 2 spreadsheet and the filename MYDATA.CSV as a delimited external file that contains comma-separated data values. Therefore, a DBMS= specification is not necessary.

The following values are valid for the DBMS= option. For detailed information about supported data sources and environments, see Supported Data Sources and Environments. For information about accessing PC Files Data, see Methods for Accessing PC Files Data.

Data Source Identifier	Input Data Source	Extension
Access	Microsoft Access 2000, 2002, 2003, or 2007 table (using the LIBNAME statement)	.mdb .accdb
Access97	Access 97 table (using the LIBNAME statement)	.mdb
Accesscs	Access table (using the LIBNAME statement)	.mdb .accdb
CSV	Delimited file (comma-separated values)	.csv
DBF	dBASE 5.0, IV, III+, and III files	.dbf
dbfMemo	dBASE 5.0, IV, III+, and III files with memos FoxPro and Visual FoxPro files with memos	.dbf .fpt .dbt
DLM	Delimited file (default delimiter is a blank)	.*
DTA	Stata file	.dta
Excel	Excel 97, 2000,or 2002 spreadsheet (using the LIBNAME statement)	.xls .xlsb .xlsm .xlsx
Excel 4	Excel 4.0 spreadsheet (using PROC ACCESS)	.xls
Excel 5	Excel 5.0 or 7.0 (95) spreadsheet (using PROC ACCESS)	.xls
Excelcs	Excel spreadsheet (using the LIBNAME statement)	.xls, .xlsb
jmp	JMP Files
Paradox	Paradox .DB files	.db
PCFS	PC Files Server	.jmp
SAV	SPSS file	.sav
TAB	delimited file (tab-delimited values)	.txt
WK1	Lotus1-2-3 Release 2 spreadsheet	.wk1
WK3	Lotus 1-2-3 Release 3 spreadsheet	.wk3
WK4	Lotus 1-2-3 Release 4 or 5 spreadsheet	.wk4
XLS	Excel 5.0, 95, 97, 2000, 2002, or 2003 spreadsheet (using file formats)	.xls

Restriction:

The availability of a data source depends on: The operating environment and in some cases the platform. Whether your site has a SAS/ACCESS Interface for PC Files license. If your site does not have a license, only delimited files are supported.

When you specify a value for DBMS= consider the following:

• When you specify DBMS=XLS for an Excel file, you can read and write Excel spreadsheets under UNIX directly, without having to access the PC Files Server.

• Microsoft Access versions 2000, 2002, and 2003 share the same internal file formats. The SAS LIBNAME engine recognizes ACCESS 2000, ACCESS 2002, ACCESS 2003, and ACCESS 2007 as aliases for the identifier ACCESS. By specifying DBMS=ACCESS, SAS can read any of these versions of files that are saved in Microsoft Access applications.

• Microsoft Excel versions 97, 2000, 2002, and 2003 share the same internal file formats. The SAS LIBNAME engine recognizes EXCEL 97, Excel 2000, Excel 2002, Excel 2003, and Excel 2007 as aliases for the identifier Excel.

This table summarizes the versions of Excel files and whether the IMPORT procedure can distinguish among them based on the DBMS= specification.

Excel Spreadsheet Specification Compatibility

Identifier	Excel 2007	Excel 97, 2000, 2002, 2003	Excel 5.0, 95	Excel 4.0
XLS	no	yes	yes	no
EXCEL	yes	yes	yes	yes
EXCEL5	no	no	yes	yes
EXCEL4	no	no	yes	yes

This table summarizes the access technology used based on the DBMS specifications.

DBMS=	Uses	Requires	Operating Platform
Excel	SAS Excel LIBNAME engine	Microsoft Jet or Ace Provider	32-bit Windows
xls	File formats technology		32-bit Windows 64-bit Windows Linux, UNIX
Excelcs	PC Files LIBNAME engine	PC Files Server, Excel Driver on 32-bit Windows	64-bit Windows, Linux, UNIX

Note:

Although Excel 4.0 and Excel 5.0 spreadsheets are often interchangeable, it is recommended that you specify the exact version.

REPLACE

overwrites an existing SAS data set. If you do not specify REPLACE, the IMPORT procedure does not overwrite an existing data set.

Copyright © 2009 by SAS Institute Inc., Cary, NC, USA. All rights reserved.