Data Set Options for Relational Databases

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.

Default value:	DBMS-specific
Valid in:	DATA and PROC steps (when accessing DBMS data using SAS/ACCESS software)
DBMS support:	Aster nCluster, Greenplum, HP Neoview, Netezza

Syntax
Details
Example
See Also

Syntax

BL_DELIMITER='<any-single-character>'

Details

To specify this option, you must first set BULKLOAD=YES or BULKEXTRACT=YES.

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

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
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, \003 or \016, not \3 or \16.

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

Example

Data in this example contains the pipe symbol:

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

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

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