LIBNAME Statement Syntax

Arguments

libref

is any SAS name that serves as an alias to associate SAS with a spreadsheet or database. Like the global SAS LIBNAME statement, the SAS/ACCESS LIBNAME statement creates shortcuts or nicknames for data storage locations. A SAS libref is an alias for a virtual or physical directory. A SAS/ACCESS libref for PC files is an alias for the spreadsheet or database where your data is stored.

engine-name

is the SAS/ACCESS engine name for your PC file format. The engine name is optional if physical-file-name is specified. The SAS/ACCESS LIBNAME statement associates a libref with a SAS/ACCESS engine that supports connections to a particular PC file. Here are the valid values for engine-name:

EXCEL	for Microsoft Excel data (5, 95, 97, 2000, 2002, 2003, or 2007).
ACCESS	for Microsoft Access data (97, 2000, 2002, 2003, or 2007).

physical-file-name

is the path and filename, including extension (.xls, .xlsb, .xlsm, .xlsx, .mdb, or .accdb), of the data source.

Note:   Omitting the physical-file-name, the Connection options should identify the data source. You are prompted for a filename, unless PROMPT=NO or NOPROMPT is set in the engine connection options.  

CLEAR

disassociates one or more currently assigned librefs.

Specify libref to disassociate a single libref. Specify _ALL_ to disassociate all librefs.

_ALL_

specifies that the CLEAR or LIST argument applies to all librefs that are currently assigned.

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 libraries that have librefs in your current session.

SAS/ACCESS-engine-connection-options

provide connection information 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 of the arguments 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 Options for detailed information about connection options.

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, you do not need to specify any of these advanced options.

See LIBNAME Statement Syntax for detailed information about LIBNAME options.

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

INIT "connection-string"

specifies an initialization string that SAS uses when connecting to a data source.

Example:

LIBNAME db ACCESS init="Provider=Microsoft.Jet.OLEDB.4.0 
Data Source=c:\temp\sasdemo.mdb"

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

PATH="data-source full-path and file-name"

specifies the the full path and filename for the data source file. For the Microsoft Access database file or Microsoft Excel workbook file. This option value is treated the same as the physical filename. . Use of this option requires the engine name to be specified. Always enter file extension .mdb for Microsoft Access and .xls for Excel.

Alias:	DATASRC] DS
Note:	use this only when the physical filename is not specified in the LIBNAME statement

PROMPT=YES|NO |REQUIRED|NOPROMPT|PROMPT|UDL

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	does not enable prompting with a Data Link Properties dialog box, and you must specify the data source (physical filename or PATH ).
REQUIRED	enables connection without prompting for more information only if a valid physical filename is specified for a successful connection. Otherwise, you are prompted for the connection options with a dialog box that enables you to change the data source file and other properties.
NOPROMPT	disables prompting with the Data Link Properties dialog box.
PROMPT	enables prompting for connection information that supplies the data source information.
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);

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

Alias:	UDL_FILE
Note:	this option should not be used with a physical filename or other connection options, such as PATH and INIT.

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, in MyPCLib.Employees_Q2, MyPCLib is a SAS libref that points to a particular group of external data objects, and 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.

Disassociating a Libref from a SAS Library

To disassociate or clear a libref, use a LIBNAME statement, specifying the libref (MyPCLib, for example) and this CLEAR option:

LIBNAME mypclib CLEAR;

You can clear a single specified libref or all current librefs.

SAS/ACCESS disconnects from the data source and closes any free threads or resources that are associated with that libref's connection.

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. Specify libref to list the attributes of a single SAS/ACCESS library or SAS library, as follows:

LIBNAME mypclib LIST;

Specify _ALL_ to list the attributes of all libraries that have librefs in your current session.

Assigning a Libref with a SAS/ACCESS LIBNAME Statement

This statement creates a libref, mymdb, as 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 also use the libref and data set with any other SAS procedure. This statement prints the information in 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";

Copyright © 2009 by SAS Institute Inc., Cary, NC, USA. All rights reserved.