The LIBNAME Statement for Relational Databases

DBPROMPT= LIBNAME Option

Specifies whether SAS displays a window that prompts the user to enter DBMS connection information before connecting to the DBMS in interactive mode.

Default value:	NO
Valid in:	SAS/ACCESS LIBNAME statement
Interaction:	DEFER= LIBNAME option
DBMS support:	Aster nCluster, DB2 under UNIX and PC Hosts, Greenplum, HP Neoview, Informix, Microsoft SQL Server, MySQL, Netezza, ODBC, Oracle, Sybase, Sybase IQ, Teradata

Syntax
Syntax Description
Details
Examples
See Also

Syntax

DBPROMPT=YES | NO

Syntax Description

YES: specifies that SAS displays a window that interactively prompts you for the DBMS connection options the first time the libref is used.
NO: specifies that SAS does not display the prompting window.

Details

If you specify DBPROMPT=YES, it is not necessary to provide connection options with the LIBNAME statement. If you do specify connection options with the LIBNAME statement and you specify DBPROMPT=YES, then the connection option values are displayed in the window (except for the password value, which appears as a series of asterisks). You can override all of these values interactively.

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

The DBPROMPT window usually opens only once for each time that the LIBNAME statement is specified. It might open multiple times if DEFER=YES and the connection fails when SAS tries to open a table. In these cases, the DBPROMPT window opens until a successful connection occurs or you click Cancel.

The maximum password length for most of the SAS/ACCESS LIBNAME interfaces is 32 characters.

Oracle: You can enter 30 characters for the USERNAME and PASSWORD and up to 70 characters for the PATH, depending on your platform.

Teradata: You can enter up to 30 characters for the USERNAME and PASSWORD.

Examples

In the following example, the DBPROMPT window does not open when the LIBNAME statement is submitted because DEFER=YES. The DBPROMPT window opens when the PRINT procedure is processed, a connection is made, and the table is opened.

libname mydblib oracle dbprompt=yes
   defer=yes;

proc print data=mydblib.staff;
run;

In the following example, the DBPROMPT window opens while the LIBNAME statement is processing. The DBPROMPT window does not open in subsequent statements because the DBPROMPT window opens only once per LIBNAME statement.

libname mydblib oracle dbprompt=yes
   defer=no;

In the following example, values provided in the LIBNAME statement are pulled into the DBPROMPT window. The values testuser and ABC_server appear in the DBPROMPT window and can be edited and confirmed by the user. The password value appears in the DBPROMPT window as a series of asterisks; it can also be edited by the user.

libname mydblib oracle
        user=testuser pw=testpass
        path='ABC_server' dbprompt=yes defer=no;