EXPORT Procedure
PROC EXPORT Statement
The EXPORT procedure reads a SAS data set (or CAS table if you have a connection to CAS) and writes the data to an external data file.
| See: | EXPORT Procedure in Base SAS Procedures Guide |
|---|
Syntax
Required Arguments
DATA=<libref.> SAS-data-set | caslib.table-name
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).
caslib.table-name specifies the input CAS table. You must use the DATA= two-level name (caslib and table) because you cannot specify only a one-level name for a CAS table.
| Default | If you do not specify a SAS data set or CAS table, the EXPORT procedure uses the most recently created SAS data set or CAS table. SAS keeps track of data set order with the system variable _LAST_. To ensure that the EXPORT procedure uses the correct data set or table, identify the SAS data set or CAS table with a two-level name. |
|---|---|
| Restrictions | PROC EXPORT does not support the DROP | KEEP data set options with Name Range Lists for DBMS=CSV | TAB | DLM. |
| 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. | |
| The path name (which includes the file name) must be a total of 201 chars or less. |
OUTFILE="filename" | "fileref"
specifies the complete path and file name or a fileref for the output PC file 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 | For client/server applications: Specify the full path and file name of the export file when you are running SAS/ACCESS software on Linux to access data that is stored on a PC server. Use of a fileref is not supported. |
| PROC EXPORT does not support the DROP | KEEP data set options with Name Range Lists for DBMS=CSV | TAB | DLM. | |
| Note | For information about how SAS converts data types, see the specific information for the data source file format to which you are exporting. |
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 Microsoft Access database files. |
Optional Arguments
<SAS-data-set-options>
specify SAS data set options. For example, if the data set that you are exporting has an assigned password, you can use these options: ALTER=, PW=, READ=, or WRITE=. To export only data that meets a specified condition, you can use the WHERE= data set option.
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=CSV specifies to import a delimited file with comma-separated values.
This table shows the available DBMS= identifiers.
|
DBMS= Identifier |
Output Data Source |
File Extension |
|---|---|---|
|
ACCESSCS |
Microsoft Access table connecting remotely through SAS PC Files Server (uses the PC Files LIBNAME engine transparently) |
.mdb, .accdb |
|
CSV |
delimited file (comma-separated values) |
.csv |
|
DLM |
delimited file (The default delimiter is a blank. You must specify the delimiter using the DELIMITER= statement.) |
.* |
|
DTA |
Stata file |
.dta |
|
EXCELCS |
MicrosoftExcel workbook connecting remotely through the SAS PC Files Server, which uses the PC Files LIBNAME engine underneath |
.xls,.xlsb, xlsx |
|
JMP |
JMP files, Version 7, and later format |
.jmp |
|
PARADOX |
Paradox DB files |
.db |
|
WK4 |
Lotus 1-2-3 releases 4 and 5 spreadsheet |
.wk4 |
|
PCFS |
JMP, Stata, and SPSS files connecting remotely through the SAS PC Files Server Note: Microsoft Excel files are not supported. Use DBMS=EXCELCS. |
.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 |
Microsoft Excel 97, 2000, 2002, or 2003 spreadsheet using file formats Note: Transcoding is not supported for DBMS=XLS.Save your .xls file to a .xlsx file and use DBMS=XLSX to support transcoding. |
.xls |
|
XLSX |
Excel 2007, 2010, and later spreadsheet (using .xlsx file format.) |
.xlsx |
- DBMS=ACCESSCS
- DBMS=EXCELCS
- DBMS=PCFS
When you specify a value for DBMS=, consider these for specific data sources.
- EXCEL
- You can specify DBMS=XLSX as well as DBMS=XLS in order to read and write to Excel workbooks under Linux. You do not need to use the SAS PC Files Server. SAS recommends using .xlsx for its support and enhancements.
- When exporting to an existing
Excel workbook .xlsb file,
useDBMS=EXCELCS.
When exporting to existing Excel workbook .xls or .xlsx file, you can use DBMS=EXCELCS or DBMS=XLS (or DBMS=XLSX). However, a .bak file is created only when DBMS=XLS or DBMS=XLSX is used.
This table shows the files that SAS creates using various versions of Microsoft Excel that you can open and read.
Exported Data: Microsoft Excel Workbook Readability File-Extension
Excel 2007 and later
Excel 97, 2000, 2002, 2003
.xlsx
Yes
No
.xls
Yes
Yes
If you use the EXPORT procedure with DBMS=XLSX and the page has formulas that reference other pages, an error message is written to the log. The error states that the page cannot be replaced because it has formulas that reference other pages.
This table is a quick reference for which DBMS= data source identifier to use for Excel files.
DBMS Data Source Identifiers DBMS=
Linux
EXCEL
no
EXCELCS
yes
XLS
yes
XLSX
yes
Restriction Only Excel 2007 and later can use .xlsx format.
Note: Transcoding or multi-byte characters are not supported for DBMS=XLS. The output yields unpredictable results. If you use Linux, or if your file has more than 255 columns, save your file as .xlsx and use DBMS=XLSX for transcoding.See Delimited Files.
- PCFS
- Specify DBMS=PCFS for JMP, SPSS, and Stata files to use the client/server model. Doing this, you can export data from Linux to JMP, SPSS, or Stata files. Use the Compute Server to access the PC Files Server.
- For more information, see Importing and Exporting SAS JMP Files Data, Importing and Exporting SPSS Files, and Stata DTA Files.
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.
You can either replace an .xls or .xlsx worksheet in an existing workbook, or you can add a new .xlsx worksheet in an existing workbook. Adding a worksheet applies to an .xlsx file format but not to the .xls format.
You can replace a worksheet in an Excel workbook using DBMS=EXCELCS. You can also add a new worksheet to an existing workbook.
See also the NEWFILE= option, which specifies whether to delete the Excel file and load the data to a sheet in a new Excel file, when exporting a SAS data set to an existing Excel file (.xls or .xlsx file).
<file-format-specific-statements>
See Delimited Files for the supported syntax for your DBMS.