connection-options: provides 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: defines 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, UNIX, and Microsoft Windows.

Details

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

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.

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 or .accdb file extension for Microsoft Access files and the .xls, .xlsx, or .xlsb 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.

Alias:SERVICE|SERVICE_NAME

Default:9621

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.

Restriction:Omitting the SERVER= option on Windows clients invokes Autostart.

Note:The name can be a simple computer name (wxp320), a fully qualified network name (wxp320.domain.com), or an IP address.

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

Notes:Passwords are generally case sensitive.

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

Notes:If you are not on a domain, omit the domain name and the backslash.

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

Restriction:Microsoft Windows only.

Note:SSPI can also be enabled by specifying the –SSPI option on the SAS command line.

Example:LIBNAME using SSPI.

LIBNAME db PCFILES PATH='C:\myfile.mdb'
   SERVER=localhost;
   SSPI='yes';
 RUN;

TYPE= EXCEL | ACCESS: specifies the file type in the PATH= statement.

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.

Alias:UID

Restriction:Microsoft Access database files only.

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

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

Alias:VER

Default:97

Restriction:Excel workbooks only.

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

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

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

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

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

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

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

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

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