PROC EXPORT Statement

DATA=<libref.>SAS data-set

identifies 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, which SAS keeps track of with the system variable _LAST_. However, in order to be certain that the EXPORT procedure uses the correct data set, you should identify the SAS data set.

Restriction:

The EXPORT procedure can export data only if the format of the data is supported by the data source or the amount of data is within the limitations of the data source. For example, some data sources have a maximum number of rows or columns, and some data sources cannot support SAS user-defined formats and informats. 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.

OUTFILE="filename"

specifies the complete path and filename, or a fileref for the output PC file, spreadsheet, or delimited external file. You can omit the quotation marks you specify a fileref or if the complete path and filename do not include special characters (such as the backslash in a path), lowercase characters, or spaces. 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.

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: You must specify the full path and filename of the file that you want to import when you are running SAS/ACCESS software on UNIX to access data that is stored on a PC server. Using a fileref is not supported.

OUTTABLE="table-name"

specifies the table name of the output 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 you export a DBMS table, you must specify the DBMS= option.

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.

DBMS=data-source-identifier

specifies the type of data to export. To export a DBMS table, you must specify DBMS= by using a valid database identifier. For example, DBMS=ACCESS specifies to export a table into a Microsoft Access 2000, 2002, 2003, or 2007 database. You do not have to specify DBMS= to EXPORT PC files, spreadsheets, and delimited external files if the filename that you specify in OUTFILE= contains a valid extension. The EXPORT procedure can recognize the type of data if you specify a valid extension. For example, the EXPORT procedure recognizes the filename ACCOUNTS.WK1 as a Lotus 1-2-3 Release 2 spreadsheet and the filename MYDATA.CSV as an external file that contains comma-separated data values. Therefore, a DBMS= specification is not necessary.

Note:

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.

The following values are valid for the DBMS= option. For detailed information about supported platforms and SAS releases, see File Format-Specific Reference for the IMPORT and EXPORT Procedures.

Data Source Identifier	Output Data Source	Extension	Version of File Created
ACCESS*	Microsoft Access 2000, 2002, 2003 or 2007 table (using the LIBNAME statement)	.mdb .accdb	2000 2007
ACCESS97	Microsoft Access 97 table (using the LIBNAME statement)	.mdb	97
ACCESSCS	Microsoft Access table (using the LIBNAME statement)	.mdb .accdb	2000*** 2007
CSV	delimited file (comma-separated values)	.csv
DBF	dBASE 5.0, IV, III+, and III files	.dbf	5.0
DBFMEMO	dBASE 5.0, IV, III+, and III files with memos FoxPro and VisualPro with memos	.dbf .fpt	5.0
DLM	delimited file (default delimiter is a blank)	.*
DTA	Stata file	.dta	Version 8
EXCEL4	Excel 4.0 spreadsheet (using proc DBLOAD)	.xls	4.0
EXCEL5	Excel 5.0 or 7.0 (95) spreadsheet (using proc DBLOAD)	.xls	5.0
EXCEL*	Excel 97, 2000, 2002, 2003 or 2007 spreadsheet (using the LIBNAME statement)	.xls .xlsb .xlsx	97 2007 2007
EXCELCS	Excel spreadsheet (using the LIBNAME statement)	.xls .xlsb	97** 2007
XLS	Excel 97, 2000, 2002, or 2003 spreadsheet (using file formats)	.xls	97
JMP	JMP file	.jmp
PARADOX	Paradox DB files	.db
PCFS	JMP files	.jmp
SAV	SPSS file, 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 Release 4 and 5 spreadsheet	.wk4

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

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 are available.

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

• Microsoft Access 2000, 2002, and 2003 share the same internal file formats. The SAS LIBNAME engine recognizes ACCESS2000, ACCESS2002, ACCESS2003, and ACCESS2007 as aliases for the identifier ACCESS. The SAS export file can be read by any of these versions of Microsoft Access.

• 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. The SAS export file can be read by any of these versions of Microsoft Excel.

• To export a data set to an existing Microsoft Access database, the EXPORT procedure can write to Access 97, Access 2000, Access 2002, or Access 2003 regardless of your specification. For example, 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.

  However, if you specify a DATABASE= option for an Access database .mdb file that does not exist, a new database is created using the format specified in the DBMS= option. For example, if you specify DBMS=ACCESS to create a new file, the result is an MDB file that Access 2000, 2002, or 2003 can read, but Access 97 cannot. For more information about the DATABASE= option, see Microsoft Access Database Files.

  . The following table summarizes the DBMS= option specifications and indicates which version of Microsoft Access can access the resulting database.

  Identifier	Access 2007	Access 2000, 2002, 2003	Access 97
  ACCESS	yes	yes	no
  ACCESS 97	yes	yes	yes

  ACCESS 2007

  Restriction:	Only Access 2007 and above can open Access 2007 file formats.
  Feature:	Access 2007 can open all previous formats.

• To export a Microsoft Excel spreadsheet, the EXPORT procedure creates an XLS file for the specified version. This table summarizes the DBMS= specifications and indicates which version of Microsoft Excel can open the resulting spreadsheet:

  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) if the data set does not contain character or numeric values.

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

Microsoft Excel

Restriction:	Only Excel 2007 and above 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.

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.

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
Requirement:	When you export a DBMS table, you must specify the DBMS= option.

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