Base SAS 9.1 Procedures Guide, Volumes 1, 2, 3 and 4

Restriction: PROC EXPORT is available for the following operating environments:

• OpenVMS Alpha

• UNIX

• Microsoft Windows.

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

• OUTFILE=" filename " OUTTABLE=" tablename "

• <DBMS= identifier > <REPLACE>;

• < data-source-statement(s) ;>

PROC EXPORT Statement

Featured in: All examples

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

• OUTFILE=" filename " OUTTABLE=" tablename "

• <DBMS= identifier > <REPLACE>;

Required Arguments

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, PROC EXPORT uses either the USER library (if assigned) or the WORK library (if USER not assigned).

• Default: If you do not specify a SAS data set, PROC EXPORT 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 PROC EXPORT uses the correct data set, you should identify the SAS data set.

• Restriction: PROC EXPORT 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, PROC EXPORT may not be able to export it correctly. When incompatible formats are encountered , the procedure formats the data to the best of its ability.

• Restriction: PROC EXPORT does not support writing labels as column names. However, SAS does support column names up to 32 characters .

• Featured in: All examples

( 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=, PW=, READ=, or WRITE= data set option, or 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 .

• Restriction: You cannot specify data set options when exporting delimited, comma-separated, or tab-delimited external files.

• Featured in: Example 2 on page 412

OUTFILE=" filename "

• specifies the complete path and filename or a fileref for the output PC file, spreadsheet, or delimited external file. If you specify a fileref or if the complete path and filename does not include special characters (such as the backslash in a path ), lowercase characters, or spaces, you can omit the quotation marks. 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. For more information about PC file formats, see SAS/ACCESS for PC Files: Reference .

• Featured in Example 1 on page 409, Example 2 on page 412, and Example 3 on page 413

• Restriction: PROC EXPORT does not support device types or access methods for the FILENAME statement except for DISK. For example, PROC EXPORT does not support the TEMP device type, which creates a temporary external file.

• Restriction: For client/server applications: When running SAS/ACCESS software on UNIX to access data that is stored on a PC server, you must specify the full path and filename of the file that you want to import. The use of a fileref is not supported.

OUTTABLE=" tablename "

• 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. Note that the DBMS table name may be case sensitive.

  Requirement: When you export a DBMS table, you must specify the DBMS= option.

  Featured in: Example 4 on page 413

Options

DBMS= 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 or 2002 database. To export PC files, spreadsheets, and delimited external files, you do not have to specify DBMS= if the filename that is specified in OUTFILE= contains a valid extension so that PROC EXPORT can recognize the type of data. For example, PROC EXPORT 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.

  The following values are valid for the DBMS= option:

  Identifier	Output Data Source	Extension	Host Availability	Version of File Created
  ACCESS	Microsoft Access 2000 or 2002 table	.mdb	Microsoft Windows *	2000
  ACCESS97	Microsoft Access 97 table	.mdb	Microsoft Windows *	97
  ACCESS2000	Microsoft Access 2000 table	.mdb	Microsoft Windows *	2000
  ACCESS2002	Microsoft Access 2002 table	.mdb	Microsoft Windows *	2000
  ACCESSCS	Microsoft Access table	.mdb	UNIX	2000**
  CSV	delimited file (comma-separated values)	.csv	OpenVMS Alpha, UNIX, Microsoft Windows
  DBF	dBASE 5.0, IV, III+, and III files	.dbf	UNIX, Microsoft Windows	5.0
  DLM	delimited file (default delimiter is a blank)	.*	OpenVMS Alpha, UNIX, Microsoft Windows
  EXCEL	Excel 97 or 2000 or 2002 spreadsheet	.xls	Microsoft Windows *	97
  EXCEL4	Excel 4.0 spreadsheet	.xls	Microsoft Windows	4.0
  EXCEL5	Excel 5.0 or 7.0 (95) spreadsheet	.xls	Microsoft Windows	5.0
  EXCEL97	Excel 97 spreadsheet	.xls	Microsoft Windows *	97
  EXCEL2000	Excel 2000 spreadsheet	.xls	Microsoft Windows *	97
  EXCEL2002	Excel 2002 spreadsheet	.xls	Microsoft Windows *	97
  EXCELCS	Excel spreadsheet	.xls	UNIX	97**
  JMP	JMP table	.jmp	UNIX, Microsoft Windows
  PCFS	Files on PC server	.*	UNIX
  TAB	delimited file (tab-delimited values)	.txt	OpenVMS Alpha, UNIX, Microsoft Windows
  WK1	Lotus 1-2-3 Release 2 spreadsheet	.wk1	Microsoft Windows
  WK3	Lotus 1-2-3 Release 3 spreadsheet	.wk3	Microsoft Windows
  WK4	Lotus 1-2-3 Release 4 and 5 spreadsheet	.wk4	Microsoft Windows

• * Not available for Microsoft Windows 64-Bit Edition. ** Value listed here is the default value. The real version of file loaded depends on the version of the existing file or the value specified for VERSION= statement.

• 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 the SAS/ACCESS software for PC file formats. If you do not have a license, only delimited files are available.

• Featured in: Example 1 on page 409 and Example 4 on page 413

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

  • To export to an existing Microsoft Access database, PROC EXPORT can write to Access 97, Access 2000, or Access 2002 regardless of your specification. For example, if you specify DBMS=ACCESS2000 and the database is in Access 97 format, PROC EXPORT exports the table, and the database remains in Access 97 format. However, if you specify OUTFILE= for an Access database that does not exist, a new database is created using the format specified in DBMS=. For example to create a new Access database, specifying DBMS=ACCESS (which defaults to Access 2000 or 2002 format) creates an MDB file that can be read by Access 2000 or Access 2002, not by Access 97.

    The following table lists the DBMS= specifications and indicates which version of Microsoft Access can open the resulting database:

    Specification	Access 2002	Access 2000	Access 97
    ACCESS	yes	yes	no
    ACCESS2002	yes	yes	no
    ACCESS2000	yes	yes	no
    ACCESS97	yes	yes	yes

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

    Specification	Excel 2002	Excel 2000	Excel 97	Excel 5.0	Excel 4.0
    EXCEL	yes	yes	yes	no	no
    EXCEL2002	yes	yes	yes	no	no
    EXCEL2000	yes	yes	yes	no	no
    EXCEL97	yes	yes	yes	no	no
    EXCEL5	yes	yes	yes	yes	no
    EXCEL4	yes	yes	yes	yes	yes

    Note: Later versions of Excel can open and update files in earlier formats.

  • When exporting a SAS data set to a dBASE file (DBF), if the data set contains missing values (for either character or numeric values), the missing values are translated to blanks.

  • When exporting a SAS data set to a dBASE file (DBF), values for a character variable that are longer than 255 characters are truncated in the resulting dBASE file because of dBASE limitations.

REPLACE

• overwrites an existing file. Note that for a Microsoft Access database or an Excel workbook, REPLACE overwrites the target table or spreadsheet. If you do not specify REPLACE, PROC EXPORT does not overwrite an existing file.

  Featured in: Example 2 on page 412 and Example 4 on page 413

Data Source Statements

PROC EXPORT provides a variety of statements that are specific to the output data source.

Statements for PC Files, Spreadsheets, or Delimited Files

The following statement is available when you export delimited external files:

DELIMITER= char nn x ;

• specifies the delimiter to separate columns of data in the output file. You can specify the delimiter as a single character or as a hexadecimal value. For example, if you want columns of data to be separated by an ampersand, specify DELIMITER= & . If you do not specify DELIMITER=, PROC EXPORT assumes that the delimiter is a blank. You can replace the equal sign with a blank.

  Interaction: You do not have to specify DELIMITER= if you specify DBMS=CSV, DBMS=TAB, or if the output filename has an extension of .CSV or .TXT.

  Featured in: Example 1 on page 409

SHEET= spreadsheet-name ;

• identifies a particular spreadsheet name to load into a workbook. You use this statement for Microsoft Excel 97, 2000, or 2002 only. If the SHEET= statement is not specified, PROC EXPORT uses the SAS data set name as the spreadsheet name to load the data.

• For Excel data access, a spreadsheet name is treated as a special case of a range name with a dollar sign ($) appended. For example, if you export a table and specify sheet=Invoice , you will see a range (table) name INVOICE and another range (table) name INVOICES$ created. Excel appends a dollar sign ($) to a spreadsheet name in order to distinguish it from the corresponding range name.

  Note: You should not append the dollar sign ($) when you specify the spreadsheet name. For example, SHEET= Invoice$ is not allowed.

  You should avoid using special characters for spreadsheet names when exporting a table to an Excel file. Special characters such as a space or a hyphen are replaced with an underscore . For example, if you export a table and specify sheet='Sheet Number 1' , PROC EXPORT creates the range names Sheet_Number_1 and Sheet_Number_1$ .

  Featured in: Example 3 on page 413

Statements for DBMS Tables

The following statements are available to establish a connection to the DBMS when you are exporting to a DBMS table:

DATABASE=" database ";

• specifies the complete path and filename of the database to contain the specified DBMS table. If the database name does not contain lowercase characters, special characters, or national characters ($, #, or @), you can omit the quotation marks. You can replace the equal sign with a blank.

  Note: A default may be configured in the DBMS client software; SAS does not generate a default value.

  Featured in: Example 4 on page 413

DBPWD=" database-password ";

• specifies a password that allows access to a database. You can replace the equal sign with a blank.

PWD=" password ";

• specifies the user password used by the DBMS to validate a specific userid . If the password does not contain lowercase characters, special characters, or national characters, you can omit the quotation marks. You can replace the equal sign with a blank.

  Note: The DBMS client software may default to the userid and password that was used to log in to the operating environment; SAS does not generate a default value.

UID=" userid ";

• identifies the user to the DBMS. If the userid does not contain lowercase characters, special characters, or national characters, you can omit the quotation marks. You can replace the equal sign with a blank.

  Note: The DBMS client software may default to the userid and password that were used to log in to the operating environment; SAS does not generate a default value.

WGDB=" workgroup-database-name ";

• specifies the workgroup (security) database name that contains the USERID and PWD data for the DBMS. If the workgroup database name does not contain lowercase characters, special characters, or national characters, you can omit the quotation marks. You can replace the equal sign with a blank.

  Note: A default workgroup database may be used by the DBMS; SAS does not generate a default value.

Security Levels for Microsoft Access Tables

Microsoft Access tables have the following levels of security, for which specific combinations of security statements must be used:

• None

  • Do not specify DBPWD=, PWD=, UID=, or WGDB=.

• Password

  • Specify only DBPWD=.

• User-level

  • Specify only PWD=, UID=, and WGDB=.

• Full

  • Specify DBPWD=, PWD=, UID=, and WGDB=.

Each statement has a default value; however, you may find it necessary to provide a value for each statement explicitly.

Statement for Client/Server Model

The following statements are available to establish a connection from SAS running on UNIX to a PC server when you are exporting a table to Microsoft Access database or Excel workbook:

SERVER=" PC-server-name ";

• specifies the name of the PC server. You must bring up the listener on the PC server before you can establish a connection to it. You can configure the service name, port number, maximum number of connections allowed, and use of data encryption on your PC server. This is a required statement. Refer to your PC server administrator for the information that is needed.

  • Alias: SERVER_NAME=

SERVICE=" service-name ";

• specifies the service name that is defined on your service file for your client and server machines. This statement and the PORT= statement should not be used in the same procedure. Note that this service name needs to be defined on both your UNIX machine and your PC server.

  • Alias: SERVER_NAME=, SERVICE_NAME=

PORT= port-number ;

• specifies the number of the port that is listening on the PC server. The valid value is between 1 and 32767. This statement and the SERVICE= statement should not be used in the same procedure.

  • Alias: PORT_NUMBER=

VERSION=" file-version ";

• specifies the version of the file that you want to create with if the file does not exist on your PC server yet. The default version is data-source specific. For Microsoft Access database, the valid values are 2002 , 2000 and 97 , and its default value is 2000 . For Microsoft Excel workbook, the valid values are 2002 , 2000 , 97 , 95 and 5 , and its default value is 97 .

  Note: Always quote the version value.

  Note: If the file already exists in the PC Server, then this value can be ignored.