Previous Page | Next Page

The IMPORT Procedure

PROC IMPORT Statement

The IMPORT procedure reads external data and writes the data to a SAS data set.
PROC IMPORT
DATAFILE=<'filename'>|DATATABLE=<'tablename'>
<DBMS>=<data-source-identifier>
<OUT>=<libref.SAS data-set-name> <SAS data-set-option(s)>
<REPLACE>;
Statement Options

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 the data set options section in SAS Language Reference: Dictionary.

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
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: When running SAS/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.
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 FoxPro. 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.
DBMS=data-source-identifier

specifies the type of data to import. To import a DBMS table, specify DBMS = using a supported database identifier. For example DBMS=ACCESS specifies to import a Microsoft Access 2000, 2002, 2003, or 2007 database.

See: Supported Data Sources and Environments

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
ACCESS 97 Microsoft Access (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 spreadsheet using the LIBNAME statement. .xls

.xlsb

.xlsm

.xlsx

EXCEL 4

EXCEL 5

 Microsoft Excel 4.0, Excel 5.0 or 7.0 (95) spreadsheet. .xls
EXCELCS Microsoft Excel spreadsheet connecting remotely through PC Files Server. xls, .xlsb
JMP JMP Files
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 spreadsheet using file formats .xls

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

  • DBMS=ACCESSCS

  • DBMS=EXCELCS

  • DBMS=PCFS

These files are accessed remotely by connecting to PC Files Server on Microsoft Windows.
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 and JMP files are supported.

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

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

  • 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 Microsoft Windows 64-bit operating environments. These files are accessed remotely by connecting to a PC Files Server on Microsoft Windows.

  • 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 97, 2000, 2002, and 2003 share the same internal file formats. The SAS LIBNAME engine recognizes EXCEL97, EXCEL2000, EXCEL2002, EXCEL2003, and EXCEL2007 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.

  • 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 Table Specifications
    Identifier Access 2007 Access 2000, 2002, 2003 Access 97
    ACCESS Yes Yes No
    ACCESS 97 Yes Yes Yes
    ACCESS 2007 Yes Yes Yes

    ACCESS 2007
    Restriction: Only Access 2007 and later can open Access 2007 file formats.
    Feature: Access 2007 can open all formats.
Excel Spreadsheet Specifications
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

DBMS Specifications for Excel
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
EXELCS PC Files LIBNAME engine PC Files Server

Excel Driver on 32-bit Windows

 64-bit Windows,

Linux

UNIX

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.
REPLACE

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

Previous Page | Next Page | Top of Page

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