SPDO Procedure

TRUNCATE Statement

Removes data from a table without deleting the table structure or metadata.

Requirements:	You must be the table owner or have been granted WRITE access to the table by the table owner in order to use this statement.
	You must set the context for the TRUNCATE statement with the SET ACLUSER statement.

Syntax

Details

Example

Syntax

TRUNCATE table-name;

Details

Use the TRUNCATE statement when you want to remove old data from a table and leave the table structure in place so that new data can be appended.

The TRUNCATE statement can be executed in the SQL explicit pass-through facility, or with PROC SPDO.

Example

The following code shows how the TRUNCATE statement is executed in PROC SPDO.

%let host=kaboom ;
%let port=5191 ;
%let domain=path2 ;

libname &domain sasspds "&domain"
   server=&host..&port
   user='siteusr1'
   password='mypasswd'
   ip=YES ;

/* create a table */
data &domain..staceys_table ;

   do i = 1 to 100 ;
   output ;
end ;
run ;

/* verify the contents of the created table */

proc contents data=&domain..staceys_table ;
run ;

/* SPDO Truncate statement deletes the table  */
/* data but leaves the table structure in   */
/* place so new data can be appended        */

proc spdo lib=&domain ;
set acluser ;
truncate staceys_table ;

quit ;

* verify that no rows or data remain in    */
/* the structure of staceys_table           */
proc contents data=&domain..staceys_table ;
run ;

Last updated: February 3, 2017