The LIBNAME statement: Specifying a database server or workbook

IanBD

The SAS language can read information from, and write information to, a variety of different file types, including SAS-language datasets, database tables, IBM z/OS VSAM files, text files (including comma-separated values files) and so on.

This article only describes how to use the LIBNAME global statement to specify a database server or Microsoft Excel workbook for input and output. For information on how to use other file types, see Introduction to the LIBNAME statement: SAS language libraries.

The LIBNAME global statement can be used to create a connection to a database server or to a workbook. You can then use this connection to access database tables or worksheets. The connection between the SAS language program and the database or workbook is made using a data engine.

The contents of a library are specified in a SAS language program using the following format:

libref.member

where libref is a library reference and member is a table in a database, or worksheet in a workbook. This is known as a two-level name.

To specify a library reference for a database server, or an Excel file, the LIBNAME statement has the format:

LIBNAME libref data-engine db-or-workbook-name options

where:

libref	The name of the library reference that will be used in SAS language programs.
data-engine	The name of the data engine used to read to or write to the specified database server or file.
db-or-workbook-name	The name of the database server or the workbook that contains the tables you want to access.
options	One or more options that affect how a table or workbook is accessed (such as authorisation details), provide information about how to handle the file, specify what to do with SQL commands, and so on.

For example, to access a MySQL database you use the MYSQL data engine:

LIBNAME mysv MySQL server=mysv user=username password=pwd qualifier=user1;

This creates a connection to the MySQL server mysv, using the login credentials specified.

Note: To connect to a database, you must first set-up client connections for the database servers. How you do this is explained in Connecting to a MySQL database from Altair SLC on Windows.

To read the data in a database table, you specify the table name as a member of the library; that is, as the second element of a two-level name:

libref.table-name

where libref is the library reference you created for the database server using LIBNAME, and table-name is the name of the table you want to access in the database. In the previous example, the library reference is mysv.

To create a new table in the database server specified in the example above:

PROC SQL;   CREATE TABLE mysv.newtb (x varchar(3)); QUIT;

This creates a new table called newtb in the database mysv; the table contains the column x.

You can read data from and write data to a table:

DATA mysv.newtb;   SET mysv.tab1;   WHERE x GT 2; RUN;

This reads all the rows in the table tab1 where the value of x is greater than 2 and writes those rows to the table newtb in the same database.

Similarly, you can read a worksheet in an Excel workbook. First, you connect to the workbook using the LIBNAME statement:

LIBNAME mywb XLSX 'c:\temp\wb1.xlsx';

Here, XLSX is the data engine used to connect to a workbook. You can then use the library reference mywb to connect to the workbook in other programs. A DATA step can access a worksheet using the libref.sheetname format; for example:

DATA outds;   SET mywb.sheet1;   WHERE x GT 2; RUN;

This reads all the rows in the worksheet sheet1 where the value of x is greater than 2 and writes those rows to the dataset outds in the Work library (the default library).