LIBNAME Statement: PC Files on Microsoft Windows

LIBNAME Statement Syntax

Associates a SAS libref with a workbook or database.

Valid:

Anywhere

Syntax
LIBNAME Options
Connection Options
Details
	Using Data from a PC File
	Clearing Libref from a SAS Library
	Writing SAS Library Attributes to the SAS Log
	Assigning a Libref with a SAS/ACCESS LIBNAME Statement
See Also

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_;

LIBNAME Options

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, and 2007.

Example 1:

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';

ACCESS

for Microsoft Access 97, 2000, 2002, 2003, and 2007.

Example 2:

This example illustrates 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 PATH='C:\PCFData\Demo.accdb';

Note:

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

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 Options

Example: Physical Path and Filename Omitting Engine Name

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

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

CLEAR

clears one librefs

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.

Example: List the attributes of a single library.

LIBNAME SASHELP LIST;

Example: 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 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 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

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 is used rarely, specifically in SAS 9.2 and later, with the Microsoft ACE engine being dominant.
Restriction:	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 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
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.
Restriction:	Use PATH= only when the physical filename is not specified in the LIBNAME statement

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);

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.

Requirement:

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);

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.

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

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';