Specifies whether SAS displays a window in the SAS windowing environment that prompts the user to enter DBMS connection information before connecting to the DBMS in interactive mode.
| Valid in: | SAS/ACCESS LIBNAME statement |
|---|---|
| CONNECT statement | |
| Category: | Data Set Control |
| Default: | NO |
| Restrictions: | This option is not applicable to SAS Viya. |
| The maximum password length for most of the SAS/ACCESS LIBNAME interfaces is 32 characters. | |
| Interaction: | 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 LIBNAME statement is executed. If DEFER=YES, the DBPROMPT window appears when you first open a table or view. The DEFER= option normally defaults to NO, but it defaults to YES if DBPROMPT=YES. You can override this default by explicitly specifying DEFER=NO. |
| Data source: | Amazon Redshift, Aster, DB2 under UNIX and PC Hosts, Greenplum, HAWQ, Impala, Informix, Microsoft SQL Server, MySQL, Netezza, ODBC, Oracle, PostgreSQL, SAP ASE, SAP HANA, SAP IQ, Snowflake, Teradata, Vertica, Yellowbrick |
| Note: | Support for Yellowbrick was added in SAS 9.4M7. |
| See: | DBPROMPT= data set option, DEFER= LIBNAME option |
Table of Contents
specifies that SAS displays a window that interactively prompts you for the DBMS connection options the first time the libref is used.
specifies that SAS does not display the prompting window.
If you specify DBPROMPT=YES, it is not necessary to provide connection options with the LIBNAME statement. If you use the LIBNAME statement to specify connection options and DBPROMPT=YES, connection option values are displayed in the window. The value of the password appears as a series of asterisks. You can override all of these values interactively.
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 such cases, the DBPROMPT window appears until a successful connection occurs or you click Cancel.
Oracle: You can enter 30 characters for the user name and password and up to 70 characters for the path, depending on your platform.
Teradata: You can enter up to 30 characters for the user name and password.
In this example, the DBPROMPT window does not open when the LIBNAME statement is submitted because DEFER=YES. The DBPROMPT window appears 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 this example, the DBPROMPT window appears while the LIBNAME statement is processing. The DBPROMPT window does not appear in subsequent statements because the DBPROMPT window appears only once per LIBNAME statement.
libname mydblib oracle dbprompt=yes
defer=no;In this example, values
provided in the LIBNAME statement are pulled into the DBPROMPT window.
The values myusr1 and mysrv1 appear
in the DBPROMPT window, and the user can
edit and confirm them. The password value appears in the DBPROMPT window
as a series of asterisks, so the user can also edit it.
libname mydblib oracle
user=myusr1 pw=mypwd1
path='mysrv1' dbprompt=yes defer=no;