DIRECT_EXE= LIBNAME Statement Option

Lets you pass a SAS SQL procedure DELETE statement directly to a data source to handle, which can improve performance.

Valid in:	LIBNAME statement
Default:	none
Supports:	All

Syntax

Details

Example

Syntax

DIRECT_EXE=DELETE

Syntax Description

DELETE: specifies that a PROC SQL DELETE statement is passed directly to the data source for processing.

Details

If DIRECT_EXE=DELETE is not specified, the PROC SQL DELETE statement is processed by SAS, which reads the table and deletes one row at a time. Specifying DIRECT_EXE=DELETE can improve performance because the PROC SQL DELETE statement is passed directly to the data source, which then deletes all the rows in the table.

Example: Empty a Table from a Database

The following example demonstrates the use of DIRECT_EXE= to empty a table.

libname x fedsvr server="d1234.us.company.com" 
   port=2171 user=user1 pwd=pass1
   dsn=basedsn direct_exe=delete;
data x.db_dft; /*create the SAS data set of 5 rows */
   do col1=1 to 5;
   output;
   end;
run;
options sastrace=",,,d" sastraceloc=saslog nostsuffix;
proc sql;
   delete * from x.db_dft;  /*this delete statement
         is passed directly to the data source*/
quit;

By turning trace on, you should see something similar to the following:

SAS Log Output

SASTSE_2: Executed: on connection 1
delete from db_dft