LIBNAME Statement Syntax

Associates a SAS libref with a workbook or database.

Valid in:

Anywhere

Syntax

LIBNAME < libref> engine-name <physical-path and filename>
<SAS/ACCESS engine-connection-options>
<SAS/ACCESS LIBNAME-options> ;

LIBNAME libref CLEAR| _ALL_;

LIBNAME libref LIST| _ALL_;

Optional Arguments

libref: is any SAS name that associates SAS with the SAS library where the spreadsheet or database is stored.

engine-name

is the SAS/ACCESS engine name for your PC file format. The SAS/ACCESS LIBNAME statement associates a libref with a SAS/ACCESS engine that supports connections to a particular PC file type. The supported engine-names are as follows:

EXCEL	for Microsoft Excel 5, 95, 97, 2000, 2002, 2003, 2007, and 2010.
ACCESS	for Microsoft Access 97, 2000, 2002, 2003, 2007, and 2010.

Note:The engine name is optional if the physical-path-filename.ext is specified. The file extension provides enough information for SAS.

Examples:This example illustrates two options for using LIBNAME statement with physical filename, including the file's extension. The accdb file extension provides SAS information about the data type. The PATH= option provides SAS with the location of the data.

LIBNAME libref ACCESS PATH='C:\PCFData\Demo.accdb';

LIBNAME libref 'C:\PCFData\Demo.accdb';

This LIBNAME statement specifies xdb as a reference to a SAS library. The EXCEL engine specifies the engine that supports the connection to the file type .XLSX.

LIBNAME xdb EXCEL PATH='C:\PCFData\Demo.xlsx';

physical-path- filename.ext

is the physical-path and filename.ext of the data source.

Microsoft Excel data source extensions include: .XLS, .XLSB, .XLSM, .XLSX.
Microsoft Access extensions include: .MDB and .ACCDB.

Note:Providing the physical-path–filename.ext sets the NOPROMPT ENGINE option.

See: "Connection Details" Details

Example: Physical Path and Filename Omitting Engine Name:

     LIBNAME xdb 'C:\PCFData\Demo.xlsx';

     LIBNAME adb 'C:\PCFData\Demo.accdb';

CLEAR: clears one libref.

Specify libref to disassociate a single libref.

_ALL_: specifies that the CLEAR or LIST argument applies to all librefs.

LIST

writes the attributes of one or more SAS/ACCESS libraries or SAS libraries to the SAS log.

Specify libref to list the attributes of a single SAS/ACCESS library or SAS library. Specify _ALL_ to list the attributes of all librefs in your current session.

Examples: List the attributes of a single library:

    LIBNAME LIBREF LIST;

List the attributes of all the libraries.

    LIBNAME _ALL_ LIST;

SAS/ACCESS engine-connection-options: provide connection options to SAS/ACCESS to connect to your PC files. If the connection options contain characters that are not allowed in SAS names, enclose the values in quotation marks. In some instances, if you specify the appropriate system options or environment variables for your data source, you can omit the connection options.

See:“Connection Details”

SAS/ACCESS LIBNAME-options: define how SAS interacts with your data source, providing enhanced control of the way that SAS processes data source objects. For example, some LIBNAME options can improve performance. For many tasks that you do not need to specify any of these advanced options.

See:LIBNAME Statement Syntax

Connection Options

SAS/ACCESS provides many ways to connect to your PC files.

INIT= connection-string: used for OLEDB, specifies an initialization string that SAS uses when connecting to a data source.

Alias: INIT_STRING

Restriction: This option should not be used with a physical filename or other connection options, such as PATH and UDL.

Note: This is used rarely, specifically in SAS 9.2 and later, with the Microsoft ACE engine being dominant.

MSENGINE= ACE | JET: determines the database engine used for accessing the Microsoft Excel file or Microsoft Access database. The Microsoft Jet engine is older and supports formats up to 2003. The Microsoft Ace engine supports 2007 and older formats.

Default: ACE

PATH= data-source full-path and file-name with extension: specifies the full path and filename for the data source file. The PATH= option can be for the Microsoft Access database file or Microsoft Excel workbook file. The PATH= value is treated the same as the physical filename.

Alias: DATASRC | DS

Restriction:Use PATH= only when the physical filename is not specified in the LIBNAME statement

Requirement: Use of this option requires the engine name to be specified. The file extensions .mdb for Microsoft Access and .xls for Excel are also required.

determines whether you are prompted for connection information that supplies the data source information

YES enables prompting with a Data Link Properties dialog box. To write the initialization string to the SAS log, submit this code immediately after connecting to the data source:

%PUT %SUPERQ (SYSDBMSG);

NO prompting is not available. You must specify the data source as a physical filename or complete path.

REQUIRED enables connection without prompting for more information. If a valid connection is not specified, you are prompted for the connection options. The prompt enables you to change the data source file and other properties.

Note: You must specify a valid physical filename for a successful connection.

NOPROMPT prompting is not available.

PROMPT enables prompting for connection information for the data source.

UDL enables browsing so you can select an existing data link file (.udl).

UDL= path-for-udl-file

specifies the path and filename for a UDL file (a Microsoft data link file) as in this example.

UDL='C:\WinNT\profiles\me\desktop\MyDBLink.UDL';
%PUT %SUPERQ(SYSDBMSG);

Alias: UDL_FILE

Restrictions:This option should not be used with a physical filename or other connection options, such as PATH and INIT.

This option does not support SAS filerefs. The SYSDBMSG macro variable is set on successful completion. For more information, refer to Microsoft documentation about using data link.

Options for Access LIBNAME Statements Only

The following options are for Access LIBNAME statements only.

DBPASSWORD= database-file-password: enables you to access database files with database-level security. This security level can be defined instead of user-level security.

Alias: DBPWD | DBPW | PASS | PASSWORD

Restriction: Microsoft Access Database only.

Note: Database password is case sensitive.

DBSYSFILE= workgroup-information-file: specifies the workgroup information file. This file contains a collection of information defined for the Microsoft Access database. User, group accounts, and passwords that you create, are saved in the workgroup information file.

Alias: SYSTEMDB

Restriction: Microsoft Access database files only.

PASSWORD= user-password: specifies a password required by the data source for the user account.

Alias: PWD | PW | PASS | PASSWORD

Note: Passwords are case sensitive.

USER= user-id: specifies a user account name, if one is required to connect to the data source. For Microsoft Access, if you have user-level security set on your .mdb file, you need to use the USER= and PASSWORD= options to access your file.

Alias: UID

Restriction: Microsoft Access database files only.

Note: Use the SERVERUSER= option to connect to a server.

Option for Excel LIBNAME Statements Only

VERSION= 2010 | 2007 | 2003 | 2002 | 2000 | 97 | 95 | 5: sets the version for a new Excel workbook.

Alias: VER

Default: 97 for .xls files; 2007 for .xlsb and .xlsx files.

Restriction: Excel workbooks only.

Notes: Excel 2010, 2007, 2003, 2000, and 97 share the same .xls file format. Excel 5 and 95 share a different file format.

You do not need to specify this option for an existing Excel file.

Details

Using Data from a PC File. You can use a LIBNAME statement to read from and write to a data source table or view as if it were a SAS data set. The LIBNAME statement associates a libref with a SAS/ACCESS engine to access tables or views in a spreadsheet or database. The SAS/ACCESS engine enables you to connect to a particular data source and to specify an external data object name in a two-level SAS name.

For example,

MyPCLib.Employees_Q2

MyPCLib is a SAS libref that points to a particular group of external data objects.
Employees_Q2 is a table name.

When you specify MyPCLib.Employees_Q2 in a DATA step or procedure, you dynamically access the external data object. SAS supports reading, updating, creating, and deleting external data objects dynamically.

Clearing Libref from a SAS Library. To disassociate or clear a libref, use a LIBNAME statement. Specify the libref and the CLEAR option. SAS/ACCESS disconnects from the data source and closes any free threads or resources that are associated with that libref's connection.

To clear a single libref:

LIBNAME mypclib CLEAR;

To clear all User-defined librefs:

LIBNAME CLEAR;

Writing SAS Library Attributes to the SAS Log. Use a LIBNAME statement and the LIST option to write the attributes of one or more SAS/ACCESS libraries or SAS libraries to the SAS log.

To list attributes of a single library:

LIBNAME mypclib LIST;

To list attributes of all libraries:

LIBNAME _ALL_ LIST;

Assigning a Libref with a SAS/ACCESS LIBNAME Statement. This statement assigns the libref, mymdb to a Microsoft Access database file:

LIBNAME mymdb 'c:\demo.mdb';

The Demo.mdb database contains a number of objects, including several tables, such as Staff. After you assign the libref, you can reference the Microsoft Access table like a SAS data set. You can also use it as a data source in any DATA step or SAS procedure.

In this PROC SQL statement, MyMdb.Staff is the two-level SAS name for the Staff table in the Microsoft Access database Demo.

PROC SQL;
   SELECT idnum, lname
   FROM mymdb.staff
   WHERE state='NY'
   ORDER BY lname;
QUIT;

You can use the Microsoft Access data to create a SAS data set:

DATA newds;
 SET mymdb.staff(KEEP=idnum lname fname);
RUN;

You can use the libref and data set with any other SAS procedure. This statement prints the Staff table:

PROC PRINT DATA=mymdb.staff;
RUN;

This statement lists the database objects in the MyMdb library:

PROC DATASETS LIBRARY=mymdb;
QUIT;

This statement associates the SAS libref MYXLS with an Excel workbook:

LIBNAME myxls 'c:\demo.xls';