CONNECT Statement

Specifies the SAS engine that provides SQL explicit pass-through access.

Valid in:

SPD Server

Syntax

CONNECT TO engine-name <AS alias> (connection-argument(s) );

Required Arguments

engine-name

specifies the name of the engine. There are two valid values:

SASSPDS	specifies to obtain SQL pass-through to the server’s SQL processor from PROC SQL in SAS. Most SQL pass-through connections will use this value.
SPDSENG	specifies to access SQL pass-through from a secondary connection (that is, a connection that is held by the server’s SQL processor).

Note: SPDSENG is the engine that you use to reference an SPD Server from within an existing SQL explicit pass-through connection. For more information about nesting connections, see Nesting SQL Pass-Through Access.

Tip

If you would prefer not to use the SPDSENG engine to reference a server, you can use the LIBGEN=YES option. Libraries with the LIBGEN=YES option are automatically available in SQL environments. For more information, see LIBGEN= LIBNAME Statement Option.

connection-argument(s)

identify the SPD server domain and name server. The following connection-args arguments are for the SPD Server engines, SASSPDS and SPDSENG. Submit them as keyword=value pairs.

Note: Some connection-arguments are required and some are optional.

USER=server-user-ID

specifies a server user ID to access the server’s SQL processor. Enclose the value in single or double quotation marks.

Note: USER= option is required on Windows. On UNIX, it is not necessary to specify USER= in a CONNECT statement because the server assumes the UNIX user ID.

PASSWORD=password

PASSWD=password

specifies the password associated with the server user ID. This value is case sensitive. You should not specify a password in a text file that another user can view. You should use this argument in a batch job that is protected by file system permissions, which prohibit other users from reading the text file.

Note: PASSWORD= option is required unless you use PROMPT=YES or unless USER='anonymous'.

HOST=host-name

specifies a node name or an IP address for the SPD Server host server. Enclose the string in single or double quotation marks. If you do not specify a value, the server uses the current value of the SAS macro variable SPDSHOST= to determine the node name.

Note: The HOST= option is optional.

SERVICE=port-number

specifies the network address (port number) for the name server. Enclose the value in single or double quotation marks. If you do not specify a port number for the name server, SPD Server determines the network address from the named service spdsname in the/etc/services file.

PROMPT=YES

specifies to issue a password prompt to access the server’s SQL processor. The prompter is case sensitive.

Note: The PROMPT=YES option is required unless you use PASSWORD= or unless USER='anonymous'.

Optional Argument

AS alias

specifies an alias for the connection. When you specify an alias to identify the connection, use a string that is not enclosed in quotation marks. Refer to this name in subsequent SQL explicit pass-through statements.

Note: For the alias, you must specify the connection that executes the statement.

The following two examples show how to use an alias:

execute(...) by alias

select * from connection to alias(...)

Example: Using the Explicit Pass-Through Facility

In this example, you issue a CONNECT statement to connect from a SAS session to the server SQL processor. After the connection is made, the first EXECUTE statement creates a table named EMPLOYEE_INFO with three columns: EMPLOYEE_NO, EMPLOYEE_NAME, and ANNUAL_SALARY. The second EXECUTE statement inserts a row into the table. The subsequent SELECT FROM CONNECTION TO statement retrieves all of the records from the new EMPLOYEE_INFO table. The DISCONNECT statement terminates the connection.

proc sql;
connect to sasspds
    (dbq='mydomain'
     host='workstation1'
     service='spdsname'
     user='me'
     passwd='noway');

execute (create table employee_info
(employee_no num, employee_name char(30),
annual_salary num)) by sasspds;

execute (insert into employee_info
values (1, 'The Prez', 10000)) by sasspds;

select * from connection to sasspds
(select * from employee_info);

disconnect from sasspds;
quit;

Last updated: February 8, 2017