The IMPORT Procedure

PROC IMPORT Statement

The IMPORT procedure reads external data and writes the data to a SAS data set.

Syntax

PROC IMPORT
DATAFILE= <'filename'> | DATATABLE= <'tablename'> (Not used for Microsoft Excel files)
<DBMS>= <data-source-identifier>
<OUT>= <libref.SAS data-set-name> <SAS data-set-option(s)>
<REPLACE>;
<file-format-specific-statements>

Required Arguments

DATAFILE=filename

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 filename does not include certain characters such as these:

backslash
lowercase characters
spaces

Alias:FILE

Default:character

Restrictions: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.

When runningSAS/ACCESS on UNIX, to access data stored on a PC server, specify the full path and filename of the import file. The use of a fileref is not supported.

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 workbook, 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) and any subsequent numeric data for that column is set to missing.

Notes:For information about how SAS converts data types, see the specific information for the data source file format that you are importing.

To import DBF files created with Microsoft Visual FoxPro, you must export to an appropriate dBASE format using Visual FoxPro. Import the dBASE file to SAS.

See:The FILENAME statement in SAS Statements: Reference.

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 and is generally used for MSACCESS tables, but not for Microsoft Excel sheets.

Alias:TABLE

Requirements: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.

When you import a DBMS table, you must specify the DBMS= option.

Optional Arguments

SAS data-set-option(s): Specify 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 SAS Data Set Options: Reference.

DBMS= data-source-identifier

specifies the type of data to import. To import a DBMS table, specify DBMS= using a supported database identifier listed in DBMS Specifications. For example, DBMS=ACCESS specifies to import a Microsoft Access 2000, 2002, 2003, or 2007 database. All DBMS= specifications refer to local access, except where noted in this table.

Note: Transcoding is not supported for DBMS=XLS. Attempted execution of this operation yields unpredictable results. As an alternative, use DBMS=EXCEL or DBMS=EXCELCS with PC Files Server.

DBMS Specifications

Data Source Identifier	Output Data Source	File Extension
ACCESS	Microsoft Access 2000, 2002, 2003, or 2007 table using the LIBNAME statement.	.mdb .accdb
ACCESSCS	Microsoft Access table connecting remotely through PC Files Server	.mdb .accdb
CSV	Delimited file with 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	Microsoft Excel 97, 2000, 2002, 2003, or 2007 workbook using the LIBNAME statement.	.xls .xlsb .xlsm .xlsx
EXCEL4 EXCEL5	Microsoft Excel 4.0, Excel 5.0 or 7.0 (95) workbook.	.xls
EXCELCS	Microsoft Excel workbook connecting remotely through PC Files Server.	xls, .xlsb
JMP	JMP Files	.jmp
PARADOX	Paradox .DB files	.db
PCFS	JMP files, SPSS files, and Stata files connecting remotely through PC Files Server.	.jmp, .sav, .dta
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	Microsoft Excel 5.0, 95, 97, 2000, 2002, or 2003 workbook using file formats	.xls
XLSX	Microsoft Excel 2007 or 2010 workbook using file formats Note: Transcoding is not supported for DBMS=XLS. Attempted execution of this operation yields unpredictable results. Use DBMS=EXCEL or DBMS=EXCELCS with PC Files Server as an alternative.	.xlsx

Note: All DBMS= specifications refer to local access, except for these specifications:

DBMS=ACCESSCS
DBMS=EXCELCS
DBMS=PCFS

These files are accessed remotely by connecting to PC Files Server on Microsoft Windows.

Microsoft Excel

When you specify DBMS=XLS or DBMS=XLSX for an Excel file, you can read and write to Excel workbooks under UNIX directly without having to access the PC Files Server. The following example demonstrates the use of DBMS=XLSX specifying a range of cells.

		proc import datafile="fieldtypes.xlsx" 
			out=small dbms=xlsx;
			range=colsb_d;
		run;

Microsoft Excel 97, 2000, 2002, and 2003 share the same internal file formats. The SAS LIBNAME engine recognizes EXCEL97, EXCEL2000, EXCEL2002, EXCEL2003, EXCEL2007, and EXCEL2010 as aliases for the identifier EXCEL. By specifying DBMS=EXCEL, the IMPORT procedure can read any version of these files that are saved in Microsoft Excel workbooks.

Microsoft Excel Workbook Specifications

Identifier	Excel 2007, 2010	Excel 97, 2000, 2002, 2003	Excel 5.0, 95	Excel 4.0
XLS	No	Yes	Yes	No
XLSX	Yes	No	No	No
EXCEL	Yes	Yes	Yes	Yes
EXCEL5	No	No	Yes	Yes
EXCEL4	No	No	Yes	Yes

DBMS Specifications for Excel

DBMS	Uses	Requires	Operating Platform
EXCEL	SAS Excel LIBNAME engine	Microsoft ACE or Jet Provider	Microsoft Windows
XLS	File formats technology		Microsoft Windows, Linux, UNIX
XLSX	File formats technology		Microsoft Windows, Linux, UNIX
EXELCS	PC Files LIBNAME engine	PC Files Server Excel Driver on Microsoft Windows	Microsoft Windows, Linux, UNIX

PCFS: Specify DBMS=PCFS for JMP, SPSS, and Stata files to use the client/server model. This enables you to access data on Microsoft Windows from Linux, UNIX, or other Microsoft Windows operating environments. These files are accessed remotely by connecting to a PC Files Server on Microsoft Windows.

Microsoft Access

Microsoft Access versions 2000, 2002, and 2003 share the same internal file formats. The SAS LIBNAME engine recognizes ACCESS2000, ACCESS2002, ACCESS2003, ACCESS2007, and ACCESS2010 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.

To import a SAS data from an existing Microsoft Access database, the IMPORT procedure can read existing Access 97, Access 2000, Access 2002, or Access 2003 database files. If you specify DBMS=ACCESS2000 and the database is in Access 97 format, the IMPORT procedure imports the table, and the database remains in Access 97 format.

When the DATABASE= option is specified for an Access database .mdb file that does not exist, a database is created using the format specified in the DBMS= option. If you specify DBMS=ACCESS to create a file, the result is an MDB file that Access 2000, 2002, and 2003 can read. Access 97 cannot read this file.

For more information about the DATABASE= option, see Microsoft Access Database Files .

Access 2007 can open all formats. Only Access 2007 and later can open Access 2007 file formats.

Access Table Specifications

Identifier	Access 2007, 2010	Access 2000, 2002, 2003
ACCESS	Yes	Yes
ACCESS2007	Yes	Yes

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

See:Supported Data Sources and Environments.

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.

REPLACE

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

<file-format-specific-statements>: see File Format-Specific Reference for the IMPORT and EXPORT Procedures for the supported syntax for your DBMS.