LIBNAME Statement to Connect External Databases in SAS

In this tutorial we will see how we can use LIBNAME Statement to Connect External Databases in SAS what are the databases supported by SAS how to access table once we have created the libname to External Databases in SAS.

Few popular External Databases in SAS which is widely getting used :

• Teradata
• Oracle
• Hadoop
• MySQL
• DB2

LIBNAME Statement to Connect Teradata in SAS

In this example we have created C_TD libname to establish connection to teradata database and same can be used to refernce table from teradata database i.e. C_TD.<TABLE NAME>

LIBNAME C_TD TERADATA SERVER=<TERADATA SERVER NAME> SCHEMA=<TERADATA SCHEMA NAME> USER=<TERADATA USERID> PASSWORD="<TERADATA PASSWORD>" ;

PROC SQL ;
SELECT
<COLUMN NAME>
FROM <LIBNAME>.<TABLE NAME>
QUIT;

LIBNAME Statement to Connect Oracle in SAS

In this example we have created C_ORA libname to establish connection to Oracle database and same can be used to refernce table from Oracle database i.e. C_ORA.<TABLE NAME>

LIBNAME C_ORA Oracle SERVER=<Oracle SERVER NAME> SCHEMA=<Oracle SCHEMA NAME> USER=<Oracle USERID> PASSWORD="<Oracle PASSWORD>" ;

LIBNAME Statement to Connect HADOOP in SAS

In this example we have created C_HADOOP libname to establish connection to HADOOP database and same can be used to refernce table from Oracle database i.e. C_HADOOP.<TABLE NAME>

In Hadoop all string columns are by default converted into character format with 32767 length . Which means if hive have status column with length as 1 will be converted into 32767 length . This leads to performance degradation and heavy space consumption on SAS server for queries extracting character data from Hive  that’s why we are using DBMAX_TEXT option to limit length.

LIBNAME C_HADOOP HADOOP URI="<URI ADDRESS>" SERVER=<HADOOP SERVER NAME> SCHEMA=<HADOOP SCHEMA NAME> USER=<HADOOP USERID> PASSWORD="<HADOOP PASSWORD>" DBMAX_TEXT=N;

LIBNAME Statement to Connect MYSQL in SAS

In this example we have created C_MYSQL libname to establish connection to MYSQL database and same can be used to refernce table from Oracle database i.e. C_MYSQL.<TABLE NAME>

LIBNAME C_MYSQL MYSQL SERVER=<MYSQL SERVER NAME> SCHEMA=<MYSQL SCHEMA NAME> USER=<MYSQL USERID> PASSWORD="<MYSQL PASSWORD>" ;

LIBNAME Statement to Connect DB2 in SAS

In this example we have created C_DB2 libname to establish connection to Oracle database and same can be used to refernce table from Oracle database i.e. C_DB2.<TABLE NAME>

LIBNAME C_DB2 DB2 SERVER=<DB2 SERVER NAME> SCHEMA=<DB2 SCHEMA NAME> USER=<DB2 USERID> PASSWORD="<DB2 PASSWORD>" ;

Hope you are now more clear to LIBNAME Statement to Connect External Databases in SAS feel free to comment if you have any query or error you’re while executing this code.

Happy Learning !!