Specifies override of the default delimiter character for separating columns of data during data transfer or retrieval during bulk loading or unloading.
| Valid in: | DATA and PROC steps (when accessing DBMS data using SAS/ACCESS software) |
|---|---|
| Categories: | Bulk Loading |
| Data Set Control | |
| Alias: | DELIM=, DELIMIT= [Hadoop] |
| Default: | DBMS-specific |
| Requirement: | To specify this option, you must first specify BULKLOAD=YES. |
| Data source: | Amazon Redshift, Aster, Google BigQuery, Greenplum, Hadoop, HAWQ, Informix, Microsoft SQL Server, Netezza, PostgreSQL, Snowflake, Spark, Yellowbrick |
| Notes: | Support for Microsoft SQL Server and Yellowbrick was added in SAS 9.4M7. |
| Support for Informix and Spark was added in SAS 9.4M9. | |
| Tip: | You can also use this option with the BULKUNLOAD= option, if your interface supports that option. |
| See: | BL_DATAFILE= data set option, BL_DELETE_DATAFILE= data set option, BL_DELIMITER= LIBNAME option, BL_FORCE_NOT_NULL= data set option, BL_FORMAT= data set option, BL_NULL= data set option, BL_OPTIONS= data set option, BL_QUOTE= data set option, BL_USE_ESCAPE= data set option, BL_USE_PIPE= data set option, BULKLOAD= data set option, BULKUNLOAD= LIBNAME option, BULKUNLOAD= data set option |
Table of Contents
Here is when you might want to use this option:
You must ensure that the characters that are assigned to BL_DELIMITER= and BL_QUOTE= are different.
Amazon Redshift: The default is the bell character (ASCII 0x07).
Aster: The default is /t (the tab character).
Google BigQuery: The default is the bell character (ASCII 0x07).
Greenplum: The default is the pipe symbol (|).
Hadoop and Spark in HDFS: The default is \001 (Ctrl-A). To change the default delimiter, specify a value as either a single character or three-digit decimal ASCII value between 001 and 127. The value represents the ASCII value of the delimiter that you want to use. You cannot use other typical SAS or UNIX formats such as '\001', 0x01 or '01'x because these do not work. Also, for such procedures as APPEND, SQL, or INSERT, the existing delimiter of the base table—the one being appended to—overrides any specified value for the DELIMITER= option. Otherwise, data corruption would result because the original and appended parts of the resulting table would use different delimiters.
HAWQ: The default is the pipe symbol (|).
Informix: The default is the pipe symbol (|).
Microsoft SQL Server: The default is the bell character (ASCII 0x07).
Netezza: You can use any 7-bit ASCII character as a delimiter. The default is the pipe symbol (ǀ). To use a printable ASCII character, enclose it in quotation marks (for example, BL_DELIMITER="|"). However, to use an extended character, use the three-digit decimal number representation of the ASCII character for this option. For example, set BL_DELIMITER=202 to use ASCII character 202 as a delimiter. You must specify decimal number delimiters as three digits even if the first two digits would be zero. For example, specify BL_DELIMITER=003, not BL_DELIMITER=3 or BL_DELIMITER=03.
PostgreSQL: The default is the pipe character (|).
Spark in Databricks: The default is the comma character (,).
Yellowbrick: The default is the pipe symbol (|).
Data in Testdel data set contains the pipe character. Use the BL_DELIMITER= data set option to override the default ‘|’ pipe delimiter in PROC APPEND.
data work.testdel;
col1='my|data';col2=12;
run;
/* Use a comma to delimit data */
proc append base=netlib.mydat(BULKLOAD=YES BL_DELIMITER=',')
data=work.testdel;
run;data db.joeapp (delim=007); set db.JoeTable2; run;
data db.joeapp (delim="127"); set db.JoeTable2; run;
data db.joeapp (delimit=#); set db.JoeTable2; run;
data db.joeapp (delimit="#"); set db.JoeTable2; run;
proc sql;
create table db.joeapp (delim='#') as select * from db.JoeTable2;
quit;