The EXPORT Procedure

PROC EXPORT Statement

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

Syntax

Required Arguments

Optional Arguments

Syntax

PROC EXPORT DATA=<libref.>SAS data set <(SAS data set options)>

OUTFILE="filename" | OUTTABLE="tablename"

<DBMS=identifier> <REPLACE> <LABEL>;
<file-format-specific-statements>

Required Arguments

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 SAS system option USER is 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.

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

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

For client/server applications: Specify the full path and filename of the import file when you are runningSAS/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

Restriction:Used for only MSACCESS files.

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

Optional Arguments

<SAS data-set-option(s)>: 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.

Note: Transcoding is not supported for DBMS=XLS. The output yields unpredictable results. Use DBMS=EXCEL as an alternative.

Data Source Identifier Summary

Data Source Identifier	Output Data Source	File Extension
ACCESS	Microsoft Access 2000, 2002, 2003, 2007, or 2010 table (using the LIBNAME statement)	.mdb .accdb
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, 2007 or 2010 workbook (using the LIBNAME statement)	.xls .xlsb .xlsx
EXCEL4	Excel 4.0 workbook (using PROC DBLOAD)	.xls
EXCEL5	Excel 5.0 or 7.0 (95) workbook (using PROC DBLOAD)	.xls
EXCELCS	Excel workbook 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) Note: Transcoding is not supported for DBMS=XLS. The output yields unpredictable results. Use DBMS=EXCEL as an alternative.	.xls

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 workbooks under Microsoft Windows and 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, ACCESS 2007, and ACCESS 2010 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 .

The files created by SAS can be opened and read by various Microsoft Access versions, as indicated in the following table.

Exported Data: Microsoft Access Readability

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

ACCESS 2007

Note: Only Access 2007 and later can open Access 2007 file 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.

The files created by SAS can be opened and read by various Microsoft Excel versions, as indicated in the following table.

Exported Data: Microsoft Excel Workbook Readability

Identifier	Excel 2007, 2010	Excel 97, 2000, 2002, 2003
XLS	Yes	Yes
EXCEL	Yes	Yes
EXCEL5	Yes	Yes
EXCEL4	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.

Restrictions:The availability of an output data source depends on:

Only Excel 2007 and later can open Excel 2007 formats.

Note:All DBMS= specifications refer to local access, except for: These files are accessed remotely by connecting to PC Files Server on Microsoft Windows.

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

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

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.

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