LIBNAME Statement for the Amazon Redshift Engine

Overview

For general information, see LIBNAME Statement for Relational Databases.

Here is the LIBNAME statement syntax for accessing the Amazon Redshift interface.

LIBNAME libref redshift <connection-options> <LIBNAME-options>;

For general information about the LIBNAME statement that is not specific to SAS/ACCESS, see LIBNAME Statement in SAS Global Statements: Reference.

Arguments

libref

specifies any SAS name that serves as an alias to associate SAS with a database, schema, server, or group of tables and views.

redshift

specifies the SAS/ACCESS engine name for the Amazon Redshift interface.

connection-options

provide connection information and control how SAS manages the timing and concurrence of the connection to the DBMS. Here is how these options are defined.

Note: All of these connection options are valid when used in the CONNECT statement with the SQL procedure.

SERVER=<'>Amazon Redshift-server-name<'>

specifies the server name or IP address of the Amazon Redshift server to which you want to connect. This server accesses the database that contains the tables and views that you want to access. If the server name contains spaces or nonalphanumeric characters, you must enclose it in quotation marks.

DATABASE=<'>Amazon Redshift-database-name<'>

specifies the name of the database on the Amazon Redshift server that contains the tables and views that you want to access. If the database name contains spaces or nonalphanumeric characters, you must enclose it in quotation marks.

Alias: DB=

PORT=port

specifies the port number that is used to connect to the specified Amazon Redshift server.

Default: 5439

USER=<'>Amazon Redshift-user-name<'>

specifies the Amazon Redshift user name (also called the user ID) that you use to connect to your database. If the user name contains spaces or nonalphanumeric characters, you must enclose it in quotation marks.

PASSWORD=<'>Amazon Redshift-password<'>

specifies the password that is associated with your Amazon Redshift user name. If the password contains spaces or nonalphanumeric characters, you must enclose it in quotation marks.

Alias: PASS=, PW=, PWD=

DSN=<'>Amazon Redshift-data-source<'>

specifies the configured Amazon Redshift ODBC data source to which you want to connect. Use this option if you have existing Amazon Redshift ODBC data sources that are configured on your client. This method requires additional setup—either through the Administrator control panel on Windows platforms or through the odbc.ini file or a similarly named configuration file on UNIX platforms. It is recommended that you use this connection method only if you have existing, functioning data sources that have been specified.

COMPLETE=<'>CLI-connection-string<'>

specifies connection information for your database for PCs only. Separate multiple options with a semicolon. When a successful connection is made, the complete connection string is returned in the SYSDBMSG macro variable. If you do not specify enough correct connection options, you are prompted with a dialog box that displays the values from the COMPLETE= connection string. You can edit any field before you connect to the database. See your PostgreSQL documentation for more details.

This option is not available on UNIX platforms.

PROMPT=<'>PostgreSQL-connection-options<'>

specifies connection options for your database. Separate multiple options with a semicolon. When connection succeeds, the complete connection string is returned in the SYSDBMSG macro variable. PROMPT= does not immediately try to connect to the DBMS. Instead, it displays a dialog box in SAS Display Manager that contains the values that you entered in the PROMPT= connection string. You can edit values or enter additional values in any field before you connect to the database.

Restriction: This option is not available on UNIX platforms.

NOPROMPT=<'>PostgreSQL-connection-options<'>

specifies connection options for your database. Separate multiple options with a semicolon. If you do not specify enough correct connection options, an error is returned. No dialog box is displayed to help you with the connection string.

LIBNAME-options

specify how SAS processes DBMS objects. Some LIBNAME options can enhance performance, and others determine locking or naming behavior. The following table describes the LIBNAME options for SAS/ACCESS Interface to Amazon Redshift with the applicable default values. This table also identifies LIBNAME options that are valid in the CONNECT statement in the SQL procedure. For more information, see LIBNAME Options for Relational Databases.

SAS/ACCESS LIBNAME Options for Amazon Redshift
Option	Default Value	Valid in CONNECT
ACCESS=	none
AUTHDOMAIN=	none
AUTOCOMMIT=	YES	●
BL_AWS_CONFIG_FILE=	~/.aws/config
BL_AWS_PROFILE_NAME=	none
BL_BUCKET=	none
BL_COMPRESS=	NO
BL_CONFIG=	none
BL_DEFAULT_DIR=	location that is specified by the UTILLOC system option
BL_DELETE_DATAFILE=	YES
BL_DELIMITER=	the bell character (ASCII 0x07)
BL_ENCKEY=	none
BL_IAM_ASSUME_ROLE_ARN=	none
BL_IAM_ASSUME_ROLE_NAME=	none
BL_KEY=	none
BL_NUM_READ_THREADS=	4
BL_OPTIONS=	none
BL_REGION=	none
BL_SECRET=	none
BL_TOKEN=	none
BL_USE_ESCAPE=	NO
BL_USE_SSL=	YES
BULKUNLOAD=	NO
CONNECTION=	SHAREDREAD	●
CONNECTION_GROUP=	none
CONOPTS=	none	●
CURSOR_TYPE=	DYNAMIC	●
DBCLIENT_MAX_BYTES=	matches the maximum number of bytes per single character of the SAS session encoding	●
DBCOMMIT=	1000 (when inserting rows), 0 (when updating rows)
DBCONINIT=	none	●
DBCONTERM=	none
DBCREATE_TABLE_OPTS=	none
DBGEN_NAME=	DBMS
DBLIBINIT=	none
DBLIBTERM=	none
DBMAX_TEXT=	1024	●
DBMSTEMP=	NO
DBNULLKEYS=	YES
DBPROMPT=	NO
DBSASLABEL=	COMPAT
DBSERVER_MAX_BYTES=	none	●
DEFER=	NO
DELETE_MULT_ROWS=	NO
DIRECT_EXE=	none
DIRECT_SQL=	YES
IGNORE_READ_ONLY_ COLUMNS=	NO
INSERT_SQL=	YES
INSERTBUFF=	1
KEYSET_SIZE=	0	●
LOGIN_TIMEOUT=	0	●
MULTI_DATASRC_OPT=	NONE
POST_STMT_OPTS=	none
PRESERVE_COL_NAMES=	NO
PRESERVE_TAB_NAMES=	NO
QUALIFIER=	none
QUERY_TIMEOUT=	0	●
QUOTE_CHAR=	none
READ_ISOLATION_LEVEL=	RC (see Locking in the Amazon Redshift Interface)	●
READ_LOCK_TYPE=	ROW	●
READBUFF=	0	●
REREAD_EXPOSURE=	NO
SCHEMA=	none
SKIP_IDENTITY_COL=	NO
SPOOL=	YES
SQL_FUNCTIONS=	none
SQL_FUNCTIONS_COPY=	none
SQLGENERATION=	none
STRINGDATES=	NO	●
SUB_CHAR=	none
TRACE=	NO	●
TRACEFILE=	none	●
UPDATE_ISOLATION_LEVEL=	RC (see Locking in the Amazon Redshift Interface	●
UPDATE_LOCK_TYPE=	ROW
UTILCONN_TRANSIENT=	NO

Best Practice: Specify the Schema

By default, the value of schema is assumed to be PUBLIC in the connection to the database when you omit SCHEMA= from a LIBNAME statement. However, if you omit SCHEMA= from a LIBNAME statement, other SAS procedures, such as the DATASETS procedure, assume that the schema is the same as the user name. This might lead to unexpected results when you call the DATASETS procedure. In addition, to use the KILL functionality with the DATASETS procedure, you must specify SCHEMA= in the LIBNAME statement. Therefore, the best practice is to always specify the value for SCHEMA= in a LIBNAME statement that connects to an Amazon Redshift database.

Amazon Redshift LIBNAME Statement Examples

In this example, SERVER=, DATABASE=, USER=, and PASSWORD= are connection options. No DSN style is specified. This is the default method, which is recommended.

libname A1 redshift server=mysrv1 port=5439 
   user=myusr1 password='mypwd1' schema=public database=mydb1;

This example requires that you specify a DSN style.

libname B1 redshift dsn=rsfttest 
   user=myusr1 password=mypwd1 schema=public;

Last updated: February 3, 2026