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 forum 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, which understands the format of the database or workbook and converts it into form that WPS can use.
The contents of a library are specified in a SAS language program using the following format:
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
|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 using WPS Analytics, you must first set-up client connections for the database servers. How you do this is explained in Connecting to a MySQL database from WPS Analytics 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:
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).