LIBNAME Statement: PC Files on Linux, UNIX, and 64-Bit Microsoft Windows

Syntax for PC Files on Linux, UNIX, and 64-Bit Microsoft Windows

Associates a SAS libref with a workbook, database, or ODBC data source.

Valid in:

Anywhere

Syntax
Options
PC Files Server Connection Options
Details
Access Data Directly from a PC File
Disassociating a Libref from a SAS Library
Writing SAS Library Attributes to the SAS Log
Examples
	Example 1: Assigning a Libref to a Microsoft Access Database
	Example 2: Assigning a Libref to a Microsoft Excel Workbook
	Example 3: Assigning a Libref to a Microsoft SQL Server Database
	Example 4: Assigning a Libref to an Oracle Database

Syntax

LIBNAME libref PCFILES
<connection-options>
<LIBNAME-options>;

LIBNAME libref CLEAR|_ALL_ CLEAR;

LIBNAME libref LIST| _ALL_ LIST;

Options

_ALL_

specifies that CLEAR= and LIST= arguments apply the argument to all librefs.

CLEAR

clears one or all librefs.

Specify libref to clear single libref. Specify _ALL_ to clear all librefs.

PC Files Server 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.

LIBNAME options

define how 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 Options

libref

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

LIST

lists the attributes of one or all SAS/ACCESS libraries or SAS libraries to the SAS log.

Specify the libref argument to list the attributes of a single library. Specify _ALL_ to list the attributes of all the librefs in your current session.

PCFILES

is the SAS/ACCESS engine for the interface to PC files on Linux and UNIX.

PC Files Server Connection Options

CONNECT_STRING=connection-string

specifies connection options for your data source or database. Separate multiple options with a semicolon. This is an advanced connection method that you should use only when you know the exact syntax of all connection options that the ODBC driver requires for a successful connection.

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.

DSN=data-source-name

specifies the name of the ODBC data source that is used to access PC data through an ODBC driver on the PC.

Restriction:

This ODBC data source must be defined on the PC where the PC Files Server is running.

MSENGINE=ACE | JET

determines the database engine to use for accessing Microsoft Excel files or Microsoft Access databases. The Microsoft Jet engine supports Microsoft formats up to 2003. The Microsoft Ace engine supports 2007 formats and formats in subsequent releases of Windows.

Default:	ACE
Restriction:	It is recommended that this option is used only to create a Windows 95 format file.

PASSWORD=user-password

specifies a password required by the data source for the user account.

Aliases:	PWD\|PW\|PASS\|PASSWORD
Note:	Passwords are case sensitive.

PATH=pathname

specifies the full path and filename for your Microsoft Access database or Microsoft Excel file.

This example assigns the libref db to a Microsoft Excel file.LIBNAME db PCFILES SERVER=D2323 PATH='c:\demo.xls';

Note:

Always use the .mdb file extension for Microsoft Access files and the .xls extension for Microsoft Excel files.

PORT=port-number

specifies the port number or service name that PC Files Server is listening to on the PC.

Aliases:	SERVICE\|SERVICE_NAME
Default:	8621

SERVER=pc-server-host-name

specifies the name of the PC running PC Files Server. This name is required for Linux and UNIX users to connect to the server.

Note:	The name can be a simple computer name (wxp320), a fully qualified network name (wxp320.domain.com), or an IP address.
Restriction:	Omitting the SERVER= value on 64-Bit Windows clients invokes Autostart.

SERVERPASS=server-user-password

specifies the password for the PC Files Server for the User ID given. If the account has no password, omit this option. Always enclose the value in quotes in order to preserve the case of the password.

Alias:	SERVERPASSWORD\|SERVERPW\|SERVERPWD
Note:	Passwords are generally case sensitive.
Note:	Use the PASSWORD= option for database passwords.
Example:	LIBNAME using explicit user name and password. LIBNAME db PCFILES PATH='C:\myfile.mdb' SERVER=fileserv; SERVERUSER='mydomain\myusername' SERVERPASS='mypassword';

SERVERUSER='domain\server-user-name'

specifies the domain name and User ID for the PC running PC Files Server. Always enclose the value in quotes, otherwise the backslash can be misinterpreted by the SAS parser.

Alias:	SERVERUID
Note:	If you are not on a domain, omit the domain name and the backslash.
Note:	Use the USER= option for database User IDs.
Example:	LIBNAME using explicit user name and password. LIBNAME db PCFILES PATH='C:\myfile.mdb' SERVER=fileserv; SERVERUSER='mydomain\myusername'; SERVERPASS='mypassword';

SSPI=YES|NO

enables the PC Files Server to allow Integrated Windows Authentication. This is a mechanism for the Windows client and server to exchange credentials.

Default:	NO
Note:	SSPI can also be enabled by specifying the -SSPI option on the SAS command line.
Restriction:	Microsoft Windows 64-Bit only.
Example:	LIBNAME using SSPI. LIBNAME db PCFILES PATH='C:\myfile.mdb' SERVER=localhost; SSPI='yes'; RUN;

TYPE=file-type

specifies the file type in the PATH= statement.

Valid values:	EXCEL\|ACCESS.
Note:	Use TYPE= if the file identified in the PATH= statement does not have an .xls or .mdb file extension.

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.

Note:	Use the SERVERUSER= option to connect to a server.
Alias:	UID
Restriction:	Microsoft Access database files only.

VERSION=2007|2003|2002|2000|97|95|5

sets the version for a new Excel workbook.

Alias:	VER
Default:	97
Note:	Excel 2007, 2003, 2000, and 97 share the same file format. Excel 5 and 95 share a different file format.
Restriction:	Excel workbooks only.
Note:	You do not need to specify this option for an existing Excel file.

Details

Access Data Directly from a PC File

You can use SAS/ACCESS Interface to PC Files on Linux and UNIX to directly access PC data from Linux and UNIX. You can read from and write to a variety of PC file data residing on a PC, including Excel, Microsoft Access, and any other ODBC data source.

The engine uses ODBC to support assigning a libref to Excel and Microsoft Access files on a PC from Linux and UNIX. You can reference spreadsheets, databases, and other ODBC data sources directly in a DATA step or SAS procedure. You can also directly read from and write to a Microsoft Access file or a Microsoft Excel file.

Disassociating a Libref from a SAS Library

To clear a libref, use a LIBNAME statement, specifying the libref, and the CLEAR option as shown;

LIBNAME mypclib CLEAR;

Clear all librefs by submitting LIBNAME CLEAR; .

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

LIBNAME mypclib LIST;

LIBNAME _ALL_ LIST;

Examples

Assigning a Libref to a Microsoft Access Database

LIBNAME mymdb PCFILES SERVER=D2323 PATH='c:\demo.mdb';

The demo.mdb database contains a number of objects, including several tables, such as Staff. After you assign a libref, you can reference the Microsoft Access table like a SAS data set. You can 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;

Create a SAS data set with Microsoft Access.

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

SAS procedures such as PROC SQL, PROC PRINT, PROC CONTENTS, and PROC DATASETS , use the libref.

List all database objects in the library with the DATASETS procedure

PROC DATASETS LIBRARY=mymdb;
QUIT;

To improve performance, it is recommended that you use the data set options INSERTBUFF= for writing and READBUFF= for reading and set an appropriate value.

Create a table named Invoice in a Microsoft Access database from a SAS data set named Invoice:

PROC SQL;
    CREATE TABLE mymdb.Invoice (INSERTBUFF=25) as SELECT * FROM invoice; 
QUIT ;

Assigning a Libref to a Microsoft Excel Workbook

Create a libref, myxls, for a Microsoft Excel workbook:

LIBNAME myxls PCFILES SERVER=D2323 PATH='c:\demo.xls';

The demo.xls workbook contains a number of sheets, such as sheet1. After you assign the libref, you can reference the Excel spreadsheet like a SAS data set and use it as a data source in any DATA step or SAS procedure. In this example, a SAS data set is created from a Microsoft Excel sheet:

DATA a; 
   SET myxls.'sheet1$'n;
RUN;

When using a LIBNAME statement with Excel, refer to Excel sheets as n-literals because of the "$" character. If you are referencing a named range in a Microsoft Excel spreadsheet, it is not necessary to refer to it as an n-literal.

Reference a named range called page one in a Microsoft Excel workbook:

DATA a;
   SET myxls.pageone;
RUN;

Create a Microsoft Excel file and use a SAS data set to populate a sheet in that file. Create a named range for the sheet:

DATA myxls.air;
   SET sashelp.air;
RUN;

Use the libref with any SAS procedures such as PROC SQL, PROC PRINT, PROC CONTENTS, and PROC DATASETS.

This SAS program uses the DATASETS procedure to list all database objects in the library.

PROC DATASETS LIBRARY=mymdb; 
   QUIT;

To improve performance, it is recommended that you use the data set option READBUFF= and set an appropriate value. This example reads in data from a range called Invoice in a Microsoft Excel workbook.

PROC SQL;
    SELECT * FROM myxls.Invoice (READBUFF=25); 
QUIT;

Note: When writing to a Microsoft Excel file, the PC Files Server does not support the INSERTBUFF= option with value greater than 1. [cautionend]

Assigning a Libref to a Microsoft SQL Server Database

Create a libref, mysqlsrv, to a SQL Server database via ODBC, using the server on the PC:

LIBNAME mysqlsrv PCFILES SERVER=D2323 DSN=MQIS USER=scott 
            PWD=tiger SCHEMA=dbo;

Using the mysqlsrv libref, create a SAS data set called sqltest from the crime table in the SQL Server database:

DATA work.sqltest;
   SET mysqlsrv.crime;
RUN;

PROC sql;
   CREATE TABLE work.sqltest AS SELECT * FROM mysqlsrv.crime;
QUIT;

Using the mysqlsrv libref, create a SQL Server table called newtable from the SAS data set, sqltest:

DATA mysqlsrv.newtable;
   SET sqltest;
RUN;

Assigning a Libref to an Oracle Database

Create a libref, ora, to an Oracle database table via ODBC, using the PC Files Server on the PC:

LIBNAME ora PCFILES SERVER=D2323 DSN=ORA9MS
            USER=scott  PRESERVE_TAB_NAMES=yes;

Using the ora libref, an Oracle table, oratab, is created from a SAS data set sashelp.class:

DATA ora.oratab;
   SET sashelp.class;
RUN;

Using the ora libref, a SAS data set, sastab, is created from the Oracle table emp:

DATA sastab;
   SET ora.emp;
RUN;

Top of Page