SAS/SHARE User's Guide

Syntax

The SQL procedure Pass-Through facility consists of three statements and a FROM-clause component. The SQL syntax follows:

PROC SQL;

CONNECT TO REMOTE <AS alias >

(SERVER=serverid <SAPW=server-access-password>

<USER=userid>

<PASSWORD=password>

<DBMS=dbms-name>

<PT2DBPW=passthrough-to-DBMS-password>

<DBMSARG=(dbms-argument-1=value < . . . dbms-argument-n=value>)>);

SELECT . . . FROM CONNECTION TO REMOTE | alias (dbms-query);

EXECUTE (SQL-statement) BY REMOTE | alias;

DISCONNECT FROM REMOTE | alias;

CONNECT TO REMOTE <AS alias>

establishes a connection to a remote DBMS or to remote SAS data through a SAS server. This statement is required (RSPT does not support implicit connection). You can connect more than once to the same server specifying different DBMS= values. You can also connect to more than one server at a time.

SERVER= serverid

specifies the name of the remote SAS server. If the server is a multi-user server, serverid is the name specified for the ID= option in the PROC SERVER statement. If the server is a single-user server running in a SAS/CONNECT remote session, serverid is the name of the SAS/CONNECT remote session. In either case, serverid is the same name specified for the SERVER= option in a LIBNAME statement.

SAPW=server-access-password

is the password for controlling user access to a multi-user server specified for the UAPW= option in the PROC SERVER statement. If UAPW= is specified when the server is started, you must specify SAPW= in a CONNECT TO REMOTE statement that specifies that server.

USER=username | _PROMPT_

specifies the userid of the accessing client on the server. The host on which the client runs can also affect username conventions. For details about username conventions imposed by the host, see Communications Access Methods for SAS/CONNECT and SAS/SHARE Software.

Nicknames for USER are USERNAME, USERID, and UID.

Valid values that can be assigned to USER are:

username: For details about a valid username, see Username and Password Naming Conventions.
_PROMPT_: a secure method, specifies that SAS prompt the user for a valid username.

PASSWORD=password | _PROMPT_

specifies the password of the accessing client on the server. The host on which the client runs can also affect password naming conventions. For details about password naming conventions imposed by the host, see Communications Access Methods for SAS/CONNECT and SAS/SHARE Software.

Nicknames for PASSWORD are PASSWD, PASS, PWD, and PW.

Valid values for PASSWORD are:

password: For details about a valid password, see Username and Password Naming Conventions.
_PROMPT_: a secure method, specifies that SAS prompt the user for a valid password.

DBMS=dbms-name

is the name of the remote DBMS you want to connect to. This is the same name you would specify in a CONNECT TO statement if you were connecting directly to the DBMS. This option is used if you want to connect to a remote DBMS instead of the remote SAS SQL processor.

PT2DBPW=passthrough-to-DBMS-password

is the password for controlling pass-through access to remote DBMS databases specified for the PT2DBPW= option in the PROC SERVER statement. If PT2DBPW= is specified when the server is started, you must specify PT2DBPPW= in a CONNECT TO REMOTE statement that specifies the same server and specifies DBMS=.

DBMSARG=(dbms-argument-1=value ... <dbms-argument-n=value>)

are the arguments required by the remote DBMS to establish the connection. These are the same arguments that you would specify in a CONNECT TO statement if you were connecting directly to the DBMS.

FROM CONNECTION TO REMOTE | alias (dbms-query)

specifies the connection to the remote SAS SQL processor or the remote DBMS as the source of data for the SELECT statement and the recipient of the dbms-query. For remote SAS data accessed through the PROC SQL view engine, dbms-query is any valid PROC SQL SELECT statement. For a remote DBMS, dbms-query is the same SQL query you would specify if you were connected directly to the DBMS

EXECUTE (SQL-statement) BY REMOTE | alias

specifies an SQL statement to be executed by the SAS SQL processor or by the remote DBMS in the server SAS session. For remote SAS data accessed through the PROC SQL view engine, SQL-statement is any valid PROC SQL statement except SELECT. For a remote DBMS accessed through a single-user server in a SAS/CONNECT session, SQL-statement is the same SQL statement you would specify if you were connected directly to the DBMS.

DISCONNECT FROM REMOTE | alias

ends the connection to the remote DBMS or to the SAS SQL processor in the server SAS session.

Examples

Here are examples of processing data by using the RSPT statements.

This example joins two remote SAS data sets. (RSPT through a server).

proc sql;
   connect to remote(server=sdcmvs.prx6xhsrv);
   select *
   from connection to remote
      (select p.idnum   label='ID Number'
              p.jobcode label='Job Code'
              s.city    label='City'
       from rmtshr.staff s,
            rmtshr.payroll p
       where s.idnum=p.idnum
       orderby jobcode);

This example uses RSPT to connect to a remote server to read data from a DB2 table. (Accessing a remote DBMS with RSPT).

proc sql;
   connect to remote(server=sdcmvs.mktserv
                     dbms=db2 dbmsarg=(ssid=db2));
   select *
   from connection to remote
      (select flight#, orig, destination, delay
       from educ.db2delay
       where delay > 11);

Chapter Contents
Previous
Next
Top of Page