CONNECT Statement

Optional Arguments

alias

specifies for the connection an optional alias that has 1 to 32 characters. If you specify an alias, the keyword AS must appear before the alias. If an alias is not specified, the DBMS name is used as the name of the SQL pass-through connection.

database-connection-arguments

specifies the DBMS-specific arguments that PROC SQL needs to connect to the DBMS. These arguments are optional for most databases. However, if you include any, you must enclose them in parentheses. See the documentation for your SAS/ACCESS interface for information about these arguments.

connect-statement-arguments

specifies arguments that indicate whether you can make multiple connections, shared or unique connections, and so on, to the database. These arguments let the SQL pass-through facility use some of the connection management features of the LIBNAME statement. Although these arguments are optional, if you include any, you must enclose it in parentheses.

• CONNECTION=
• CONNECTION_GROUP=
• DBCONINIT=
• DBCONTERM=
• DBGEN_NAME=
• DBMAX_TEXT=
• DBPROMPT=
• DEFER=
• VALIDVARNAME=

  Note: In addition to the arguments listed here, several other LIBNAME options are available for use with the CONNECT statement. See the SQL pass-through facility reference section for your SAS/ACCESS interface to determine which LIBNAME options are available in the SQL pass-through facility for your DBMS. When used with the SQL pass-through facility CONNECT statement, these options have the same effect as they do in a LIBNAME statement.

  CONNECTION= SHARED | GLOBAL

  indicates whether multiple CONNECT statements for a DBMS can use the same connection.

  The CONNECTION= option enables you to control the number of connections, and therefore transactions, that your SAS/ACCESS engine executes and supports for each SQL pass-through CONNECT statement.

  When CONNECTION=GLOBAL, multiple CONNECT statements that use identical values for CONNECTION=, CONNECTION_GROUP=, DBCONINIT=, DBCONTERM=, and any database connection arguments can share the same connection to the DBMS.

  When CONNECTION=SHARED, the CONNECT statement makes one connection to the DBMS. Only SQL pass-through statements that use this alias share the connection. SHARED is the default value for CONNECTION=.

  In this example, the two CONNECT statements share the same connection to the DBMS because CONNECTION=GLOBAL. Only the first CONNECT statement actually makes the connection to the DBMS. The last DISCONNECT statement is the only statement that disconnects from the DBMS.

  proc sql;
  /*…SQL Pass-Through statements referring to mydbone…*/
  connect to oracle as mydbone
     (user=testuser pw=testpass
       path='myorapath'
       connection=global);
  /*…SQL Pass-Through statements referring to mydbtwo…*/
  connect to oracle as mydbtwo
     (user=testuser pw=testpass
       path='myorapath'
       connection=global);
  disconnect from mydbone;
  disconnect from mydbtwo;
  quit;

  CONNECTION_GROUP=connection-group-name

  specifies a connection that can be shared among several CONNECT statements in the SQL pass-through facility.

  Default: none

  By specifying the name of a connection group, you can share one DBMS connection among several CONNECT statements. The connection to the DBMS can be shared only if each CONNECT statement specifies the same CONNECTION_GROUP= value and specifies identical DBMS connection arguments.

  When CONNECTION_GROUP= is specified, it implies that the value of the CONNECTION= option is GLOBAL.

  DBCONINIT=<'>DBMS-user-command<'>

  specifies a user-defined initialization command to be executed immediately after the connection to the DBMS.

  You can specify any DBMS command that can be passed by the SAS/ACCESS engine to the DBMS and that does not return a result set or output parameters. The command executes immediately after the DBMS connection is established successfully. If the command fails, a disconnect occurs, and the CONNECT statement fails. You must specify the command as a single, quoted string, unless it is an environment variable.

  DBCONTERM='DBMS-user-command'

  specifies a user-defined termination command to be executed before the disconnect from the DBMS that occurs with the DISCONNECT statement.

  Default: none

  The termination command that you select can be a script, stored procedure, or any DBMS SQL language statement that might provide additional control over the interaction between the SAS/ACCESS engine and the DBMS. You can specify any valid DBMS command that can be passed by the SAS/ACCESS engine to the DBMS and that does not return a result set or output parameters. The command executes immediately before SAS terminates each connection to the DBMS. If the command fails, SAS provides a warning message but the disconnect still occurs. You must specify the command as a quoted string.

  DBGEN_NAME= DBMS | SAS

  specifies whether to automatically rename DBMS columns containing characters that SAS does not allow, such as $, to valid SAS variable names. See DBGEN_NAME= LIBNAME Option for more information.

  DBMAX_TEXT=integer

  determines the length of any very long DBMS character data type that is read into SAS or written from SAS when using a SAS/ACCESS engine. This option applies to reading, appending, and updating rows in an existing table. It does not apply when you are creating a table.

  Examples of a long DBMS data type are the SYBASE TEXT data type or the Oracle LONG RAW data type.

  DBPROMPT=YES | NO

  specifies whether SAS displays a window that prompts the user to enter DBMS connection information before connecting to the DBMS.

  Default: NO

  Interaction: DEFER= LIBNAME option

  If you specify DBPROMPT=YES, SAS displays a window that interactively prompts you for the DBMS connection arguments when the CONNECT statement is executed. Therefore, it is not necessary to provide connection arguments with the CONNECT statement. If you do specify connection arguments with the CONNECT statement and you specify DBPROMPT=YES, the connection argument values are displayed in the window. These values can be overridden interactively.

  If you specify DBPROMPT=NO, SAS does not display the prompting window.

  The DBPROMPT= option interacts with the DEFER= LIBNAME option to determine when the prompt window appears. If DEFER=NO, the DBPROMPT window appears when the CONNECT statement is executed. If DEFER=YES, the DBPROMPT window appears the first time a pass-through statement is executed. The DEFER= option normally defaults to NO, but defaults to YES if DBPROMPT=YES. You can override this default by explicitly setting DEFER=NO.

  DEFER=NO | YES

  determines when the connection to the DBMS occurs.

  Default: NO

  If DEFER=YES, the connection to the DBMS occurs when the first SQL pass-through statement is executed. If DEFER=NO, the connection to the DBMS occurs when the CONNECT statement occurs.

  VALIDVARNAME=V6

  indicates that only SAS 6 variable names are considered valid. Specify this connection argument if you want the SQL pass-through facility to operate in SAS 6 compatibility mode.

  By default, DBMS column names are changed to valid SAS names, following these rules:

  • Up to 32 mixed-case alphanumeric characters are allowed.
  • Names must begin with an alphabetic character or an underscore.
  • Characters that are not permitted are changed to underscores.
  • Any column name that is not unique when it is normalized is made unique by appending a counter (0,1,2,…) to the name.

  When VALIDVARNAME=V6 is specified, the SAS/ACCESS engine for the DBMS truncates column names to eight characters, as it does in SAS 6. If required, numbers are appended to the ends of the truncated names to make them unique. Setting this option overrides the value of the SAS system option VALIDVARNAME= during (and only during) the SQL pass-through connection.

  This example shows how the SQL pass-through facility uses VALIDVARNAME=V6 as a connection argument. Using this option causes the output to show the DBMS column "Amount Budgeted$" as AMOUNT_B and "Amount Spent$" as AMOUNT_S.

  proc sql;
  connect to oracle (user=gloria password=teacher
                     validvarname=v6)
  create view budget2000 as
   select amount_b, amount_s
   from connection to oracle
       (select "Amount Budgeted$", "Amount Spent$"
        from annual_budget);
  quit;
  proc contents data=budget2000;
  run;

  For this example, if you omit VALIDVARNAME=V6 as a connection argument, you must add it in an OPTIONS= statement in order for PROC CONTENTS to work.

  options validvarname=v6;
  proc contents data=budget2000;
  run; 

  So using it as a connection argument saves you coding later.