Pass-Through Facility: PC Files on Linux, UNIX, and 64-Bit Microsoft Windows

EXECUTE Statement

Sends data source-specific, nonquery SQL statements to the data source.

Valid in:

PROC SQL steps

Syntax
Arguments
Details
Useful Statements to Include in EXECUTE Statements
Example

Syntax

EXECUTE (data-source-specific-SQL-statement) BY PCFILES | alias;

Arguments

(data-source-specific-SQL-statement)

a dynamic nonquery, data source-specific SQL statement. This argument is required and must be enclosed in parentheses. However, the SQL statement cannot contain a semicolon because a semicolon represents the end of a statement in SAS. Depending on your data source, the SQL statement can be case-sensitive. It is passed to the data source exactly as you type it.

Any return code or message that is generated by the data source is available in the macro variables SQLXRC and SQLXMSG after the statement executes. See Return Codes for more information about these macro variables.

alias

specifies an alias for the connection that was defined in the CONNECT statement. (You cannot use an alias if the CONNECT statement is omitted.)

Details

The EXECUTE statement sends dynamic nonquery, data source-specific SQL statements to the data source and processes those statements.

The EXECUTE statement cannot be stored as part of a Pass-Through Facility query in a PROC SQL view.

Useful Statements to Include in EXECUTE Statements

You can pass the following statements to the data source by using the Pass-Through Facility's EXECUTE statement.

CREATE: creates a data source table, view, index, or other data source object, depending on how the statement is specified.
DELETE: deletes rows from a data source table.
DROP: deletes a data source table, view, or other data source object, depending on how the statement is specified.
GRANT: gives users the authority to access or modify objects such as tables or views.
INSERT: adds rows to a data source table.
REVOKE: revokes the access or modification privileges that were given to users by the GRANT statement.
UPDATE: modifies the data in the specified columns of a row in a data source table.

For more information about these and other SQL statements, see the SQL documentation for your data source.

Example

The following example, after the connection, uses the EXECUTE statement to drop a table, create a table, and insert a row of data:

EXECUTE(DROP TABLE  ` My Invoice ` ) BY PCFILES;
EXECUTE(CREATE TABLE  ` My Invoice ` (
 ` Invoice Number `  LONG NOT NULL,
 ` Billed To `  VARCHAR(20),
 ` Amount `  CURRENCY,
 ` BILLED ON `  DATETIME)) BY PCFILES;
EXECUTE(INSERT INTO  ` My Invoice ` 
VALUES( 12345, 'John Doe', 123.45, #11/22/2003#)) BY PCFILES;

Top of Page