The EXPORT Procedure

PROC EXPORT Statement

The EXPORT procedure reads a SAS data set and writes the data to an external data file.

PROC EXPORT
DATA=<libref.SAS data-set (SAS data-set-option(s))>
DBMS=<data-source-identifier>
LABEL
OUTFILE=<'filename'>|OUTTABLE='tablename'
REPLACE;

Statement Options

When exporting data to a Microsoft Access table, SAS/ACCESS Interface for PC Files converts the table name to a SAS member name. SAS does not support member names that are longer than 32 bytes.

DATA=libref.SAS data-set

specifies the input SAS data set with either a one- or two-level SAS name (library and member name). If you specify a one-level name, by default, the EXPORT procedure uses either the SASUSER library (if assigned) or the WORK library (if USER not assigned).

Default:

If you do not specify a SAS data set, the EXPORT procedure uses the most recently created SAS data set. SAS keeps track of data set order with the system variable _LAST_. To ensure that the EXPORT procedure uses the correct data set, identify the SAS data set with a two-level name.

Restriction:

The EXPORT procedure can export data if the data format is supported and the amount of data is within the limitations of the data source. Some data sources have a maximum number of rows or columns. If the data that you want to export exceeds the limits of the data source, the EXPORT procedure might not be able to export it correctly. When SAS encounters incompatible formats, the procedure formats the data to the best of its ability.

(SAS data-set-options)

specifies SAS data set options. For example, if the data set that you are exporting has an assigned password, you can use the ALTER= option, the PW= option, the READ= option, or the WRITE= option. To export only data that meets a specified condition, you can use the WHERE= data set option. For information about SAS data set options, see "Data Set Options" in SAS Language Reference: Dictionary.

DBMS=data-source-identifier

DBMS= specifies the type of external data source the EXPORT procedure creates. To export to a DBMS table, specify DBMS= using a supported database identifier. For example, DBMS=ACCESS specifies to export a table into a Microsoft Access 2000, 2002, 2003, or 2007 database.

See:	File Format-Specific Reference for the IMPORT and EXPORT Procedures

Data Source Identifier	Output Data Source	File Extension
Access*	Microsoft Access 2000, 2002, 2003, or 2007 table (using the LIBNAME statement)	.mdb .accdb
Access97	Microsoft Access table (using the LIBNAME statement)	.mdb
ACCESSCS	Microsoft Access table connecting remotely through PC FIles Server	.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 VisualPro with memos	.dbf .fpt
DLM	delimited file (default delimiter is a blank)	.*
DTA	Stata file	.dta
Excel*	Excel 97, 2000, 2002, 2003, or 2007 spreadsheet (using the LIBNAME statement)	.xls .xlsb .xlsx
Excel4	Excel 4.0 spreadsheet (using PROC DBLOAD)	.xls
Excel5	Excel 5.0 or 7.0 (95) spreadsheet (using PROC DBLOAD)	.xls
EXCELCS	Excel spreadsheet connecting remotely through PC FIles Server	.xls .xlsb
JMP	JMP files	.jmp
Paradox	Paradox DB files	.db
PCFS	JMP files, Stata files, and SPSS files connecting remotely through PC FIles Server	.jmp, .dta, .sav
SAV	SPSS files, compressed and uncompressed binary files	.sav
TAB	delimited file (tab-delimited values)	.txt
WK1	Lotus 1-2-3 Release 2 spreadsheet	.wk1
WK3	Lotus 1-2-3 Release 3 spreadsheet	.wk3
WK4	Lotus 1-2-3 releases 4 and 5 spreadsheet	.wk4
XLS	Excel 97, 2000, 2002, or 2003 spreadsheet (using file formats)	.xls
* Not available for Microsoft Windows 64-Bit edition. ** Value listed is the default value. The real version of the loaded file depends on the version of the existing file, or the value that you specified for VERSION= option.

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 an output data source depends on: The operating environment, and in some cases the platform, as specified in the previous table. Whether your site has a license to SAS/ACCESS Interface for PC Files. If you do not have a license, only delimited files and JMP 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, the SAS export file can be read by 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, 2000, 2002, 2003, and EXCEL2007 as aliases for the identifier EXCEL. By specifying DBMS=EXCEL, the SAS export file can read any of these versions of files that are saved in Microsoft Excel workbooks.

To export a SAS data set to an existing Microsoft Access database, the EXPORT procedure can write to 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 EXPORT procedure exports 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.

To export a Microsoft Excel spreadsheet, the EXPORT procedure creates an XLS file for the specified version. When exporting to an existing Excel workbook .XLS file a .BAK file is created.

Excel Spreadsheet Specifications
Identifier	Excel 2007	Excel 97, 2000, 2002, 2003	Excel 5.0, 95	Excel 4.0
XLS	Yes	Yes	No	No
EXCEL	Yes	Yes	No	No
EXCEL 5	Yes	Yes	Yes	No
EXCEL 4	Yes	Yes	Yes	Yes

Missing values are translated to blanks when you export a SAS data set to a dBASE file (DBF).
Due to dBASE limitations, character variable values longer than 256 characters are truncated in the resulting dBASE file.

Microsoft Excel 2007

Restriction:	Only Excel 2007 and later can open Excel 2007 formats.
Feature:	Excel 2007 can open all previous formats. Later versions of Excel can open and update files in previous formats.

LABEL

writes SAS label names as column names to the exported table. If SAS label names do not exist, then the variable names are used as column names in the exported table.

Alias:

DBLABEL

OUTFILE=filename

specifies the complete path and filename, or a fileref for the output PC file, spreadsheet, or delimited external file. If the name does not include special characters (such as question marks), lowercase characters, or spaces, omit the quotation marks.

Alias:	FILE
Restriction:	The EXPORT procedure does not support device types or access methods for the FILENAME statement except for DISK. For example, the EXPORT procedure does not support the TEMP device type, which creates a temporary external file.
Restriction:	For client/server applications: Specify the full path and filename of the import file when you are running SAS/ACCESS software on UNIX to access data that is stored on a PC server. Use of a fileref is not supported.

OUTTABLE=table-name

specifies the DBMS output table. If the name does not include special characters (such as question marks), lowercase characters, or spaces, omit the quotation marks. The DBMS table name might be case sensitive.

Alias:	TABLE
Note:	For PC files the table-name is generally used for Microsoft Access databases.

REPLACE

overwrites an existing file. For a Microsoft Access database or an Excel workbook, REPLACE overwrites the target table or spreadsheet. If you do not specify REPLACE, the EXPORT procedure does not overwrite an existing file.

Top of Page