BL_DELIMITER= Data Set Option

Specifies override of the default delimiter character for separating columns of data during data transfer or retrieval during bulk load or bulk unload.

Valid in:	DATA and PROC steps (when accessing DBMS data using SAS/ACCESS software)
Alias:	DELIM, DELIMIT [Hadoop]
Default:	DBMS-specific
Requirement:	To specify this option, you must first set BULKEXTRACT=YES or BULKLOAD=YES.
Data source:	Aster nCluster, Greenplum, Hadoop, HP Neoview, Netezza
See:	BL_DATAFILE= data set option, BL_DELETE_DATAFILE= data set 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_PIPE= data set option, BULKEXTRACT= data set option, BULKLOAD= data set option, BULKUNLOAD= LIBNAME option, BULKUNLOAD= data set option

Syntax

Details

Examples

Example 1: Specify the Default Delimiter

Example 2: Override the Default Delimiter

Example 3: Override the Default Hadoop Delimiter

Syntax

BL_DELIMITER='<any-single-character>'

Details

Here is when you might want to use this option:

to override the default delimiter character that the interface uses to separate columns of data that it transfers to or retrieves from the DBMS during bulk load (or bulk unload for Netezza)
if your character data contains the default delimiter character, to avoid any problems while parsing the data stream

Aster nCluster: The default is /t (the tab character).

Greenplum, Netezza: The default is the pipe symbol (ǀ).

Hadoop: 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 that 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.

HP Neoview: The default is the pipe symbol (ǀ). Valid characters that you can use are a comma (,), a semicolon (;), or any ASCII character that you specify as an octal number except for these:

upper- and lowercase letters (A through Z and a through z)
decimal digits 0 through 9
a carriage return (\015)
a linefeed (\012)

For example, specify BL_DELIMITER='\174' to use the pipe symbol (| or \174 in octal representation) as a delimiter. You must specify octal numbers as three digits even if the first couple of digits would be 0. For example, use ‘\003’ or ‘\016’, not ‘\3’ or ‘\16’, or ‘\136’ if you want to use the caret (^) symbol.

Sybase IQ: The default is | (the pipe symbol). You can specify the delimiter as a single printable character (such as |), or you can use hexadecimal notation to specify any single 8-bit hexadecimal ASCII code. For example, to use the tab character as a delimiter, you can specify BL_DELIMITER='\x09'.

Examples

Example 1: Specify the Default Delimiter

Data in this example contains the pipe symbol.

data work.testdel;
col1='my|data';col2=12;
run;

Example 2: Override the Default Delimiter

This example shows how you can override this default when BULKLOAD=YES.

/* Use a comma to delimit data */
proc append base=netlib.mydat(BULKLOAD=YES BL_DELIMITER=',')
data=work.testdel;
run;

Example 3: Override the Default Hadoop Delimiter

data db.joeapp (delim=007); set db.JoeTable2; run;
data db.joeapp (delim="127"); set db.JoeTable2; run;
data db.joeapp (delimiter=#); 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;