Microsoft Access Database Files

SAS/ACCESS Interface to PC Files Server works with Microsoft Access database 97, 2000, 2002, 2003, and 2007 files.

Microsoft Access is a desktop relational database system that uses the Microsoft Ace engine or the Microsoft Jet engine to store and retrieve data.

A database is a collection of information that is related to a particular subject or purpose, such as tracking customer orders or maintaining a music collection. All objects in an MS Access database ( tables, queries, forms, reports) are stored in the Jet .mdb or Ace .accdb format, if the size is greater than, or equal to MS Access database 2007.

The following table lists the maximum size limits for the components of .mdb and .accdb files.

While importing data from a table, SAS converts special characters in a table name to underscores ( _ ) in the corresponding data set name. If a field contains special characters, SAS converts special to underscores in the corresponding variable name.

Summary of field data types that are available in Microsoft Access; their uses; and their storage sizes.

In Microsoft Access database software:

• Date values are valid back to 30 December 1899 and is saved as integer value: 0.

• Date values are valid ahead to 31 December 9999, and is saved as the integer: 2,958,465.

• Considers years 4000 and 8000 as leap years.

• Time value is the decimal portion of a number that represents time as a proportion of a day.

• Numbers can have both a date and a time portion, the format is in date/time format.

• Displays a number in a date, time, or data/time format.

In SAS:

• Dates are valid back to A.D.1582.

• Date values before year 1582 is represented as a missing value.

• Dates are valid ahead to A.D. 9,999.

• Date values are represented by the number of days between January 01, 1960, and that date.

• Years 4000 and 8000 are not considered leap years.

• Time values are represented by the number of seconds between midnight and that time of day.

• Datetime values are represented by the number of seconds between midnight January 01, 1960, and that datetime.

• Time values can be imported as a date value 30 Dec 1899, ensure that you assign the correct format.

Before you use the IMPORT and the EXPORT procedures for Access files, it is helpful to be familiar with Access file formats. For an existing .mdb, you can specify DBMS=ACCESS on Windows platforms and SAS identifies the version.

• Access 2000, 2002, and 2003 share the same .mdb file formats. ACCESS2000, ACCESS2002, and ACCESS2003 are treated as aliases for Access in SAS.

• Access 2007 supports .accdb file formats

• Access 97 has different formats, which are incompatible with Access 200x versions. Specify DBMS=ACCESS97 on Windows platforms only, to export and create a new .mdb file with Access version 97 formats.

These tables list the statements that are available to import data from an Access database file, using the LIBNAME statement on a Windows operating system platform. See Example 1: Import a SAS Data Set from an Access 2007 Database Table.

On Linux, UNIX, and Windows 64-Bit operating platforms, you can use the client server model to access data in .mdb files and .accdb files. For more information, see PC Files Server Administration.

For an existing .mdb, you can specify DBMS=ACCESSCS when using the client server model and SAS identifies the version of Access for you. You can specify VERSION='97' when using the client server model only when you want to export and create a new .mdb file with Access version 97 formats.

Note:   If the client server model is used, the SAS client cannot access an Access database file with both database password protection and user level security protection. In this case, you must choose only one security protection to be able to access your Access database file from a SAS client.  

The following table lists the statements to import data from an Access database file using the client server model. The statements are valid on Linux, UNIX, and Windows 64-bit operating platforms.

DATABASE=database

specifies the complete path and filename of the Access database file that contains the specified DBMS table. If the database name does not contain lowercase characters, special characters, or national characters ($, #, or @), you can omit the quotation marks.

Note:

SAS does not generate a default value, but you can configure one in the DBMS client software

DBDSOPTS

data set options that are valid for the Access database LIBNAME engine

Enables you to take advantage of useful data set options for the LIBNAME engine such as READBUFF= , INSERTBUFF= , DBTYPE= , DROP= , FIRSTOBS= , and OBS=. This option is for advanced users who are familiar with the PC files LIBNAME engine.

Note:	For users who use the client server model to access data in an Access database, the default value for READBUFF= is 1. To improve performance for reading data, set READBUFF= to 25 or higher.
Note:	For users who use the client server model to access data in an Access database, the default value for INSERTBUFF= is 1. To improve performance for writing data, set INSERTBUFF= to 25 or higher.
See:	For a list of supported data set options, please refer to Data Set Options or Data Set Options for PC Files on Linux and UNIX, and 64-Bit Windows.
Restriction:	You must surround the options in single quotation marks.
Example:	DBDSOPTS='FIRSTOBS=10 READBUFF=25';
Restriction:	If the option string that you are specifying contains single quotations marks, you must use double quotation marks around it in your statement.
Example:	DBDSOPTS="DBTYPE=(BilledTo='CHAR(8)')";

DBPASSWORD=database-file-password

enables you to access a file if database-level security is set in the .mdb file. A database password is case sensitive. You can define a database password instead of user-level security.

Alias:

DBPWD|DBPW

DBSASLABEL=COMPAT|NONE

specifies whether the data source column names are saved as the corresponding SAS label names.

MEMOSIZE=1 to 32767

Specifies the maximum variable length in SAS that is allowed while importing data from memo columns of an Access database table. Any memo data in an Access database tables whose length exceeds 32767 is truncated when it is imported into SAS.

Alias:	DBMAX_TEXT
Restriction:	If the maximum length that SCANMEMO= option is greater than the value of the MEMOSIZE= option, the smaller value in the MEMOSIZE= option is applied as the SAS variable width.

PORT= PORT NUMBER

specifies the number of the port that is listening on the PC Files Server. The valid value is between 1 and 65535. This port or service name displays on the PC Files Server display when the application is started in server mode.

Default:	8621
Alias:	PORT_NUMBER
Restriction:	Available only for the client server model.
Restriction:	The PORT= statement option and the SERVICE= statement option should not be used in the same procedure

SCANMEMO=YES|NO

specifies whether to scan the memo data to determine the column length for each memo type source column.

YES

scans the length of memo data for a data source column. Uses the length of the longest memo text of data that it finds, as the SAS variable width.

Restriction:

if the maximum length that SCANMEMO= option is greater than the value of the MEMOSIZE= option, the smaller value in the MEMOSIZE= option is applied as the SAS variable width.

NO

does not scan the length of memo data for a data source column. The column length returned from the Microsoft Ace engine or the Microsoft Jet engine is used as the SAS variable length.

Restriction:	SCANMEMO= does not apply to text type columns.
Restriction:	This option applies only to memo data type columns.

SCANTIME=YES|NO

specifies whether to scan the date/time data while importing data from a date/time column from an Access database.

YES

scans the date/time column and assigns the TIME. format for a date/time column only if time values are found in the column.

Note:

The DATE. format is assigned for a date/time column if USEDATE=YES.

NO

specifies not to scan the date/time column.

Note:

The DATETIME. format is assigned for a date/time column if USEDATE=NO.

SERVER=PC server-name

specifies the name of the PC Files Server. You must bring up the listener on the PC Files Server before you can establish a connection to it. You can also configure:

• the service name

• the port number

• the maximum number of concurrent connections

• encrypt the data

Alias:	SERVER_NAME
Note:	This statement is a required.
Restriction:	Available only for the client server model.

SERVERPASS=server-user-password

specifies the password for the User ID given. If the account has no password, omit this option. Always enclose the value in quotes, 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, for PC Files Server: LIBNAME db pcfiles path='C:\myfile.mdb' server=fileserv; serveruser='mydomain\myusername'; serverpass='mypassword';

SERVERUSER=server-user-name

specifies a domain and User ID that is valid 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, for PC Files Server. LIBNAME db pcfiles path='C:\myfile.mdb' server=fileserv; serveruser='mydomain\myusername'; serverpass='mypassword';

SERVICE= service-name

specifies the service name that is defined on your service file for your client and server machines. This port number or service name is displayed on the PC Files Server control panel screen when it is started on the PC in server mode.

Alias:	SERVICE_NAME
Note:	This service name needs to be defined on both your UNIX machine and your PC Files Server.
Restriction:	Available only for client server model.
Restriction:	This statement and the PORT= statement option should not be used in the same procedure.

SSPI=YES|NO

enables PC Files Server to allow Integrated Windows Authentication. This is a mechanism for 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:	Valid on Windows 64-Bit only.
Example:	LIBNAME using SSPI. LIBNAME db pcfiles path='C:\myfile.mdb' server=localhost; sspi='yes'; run;

USEDATE=YES|NO

specifies whether to assign a DATE. or a DATETIME. format while importing a date/time column from a Microsoft ACCESS workbook.

YES

assigns the DATE. format for the corresponding date/time column in the Microsoft ACCESS table.

NO

assigns the DATETIME. format for the corresponding date/time column in the Microsoft ACCESS table.

See also:

The SCANTIME= statement option in order to assign the appropriate TIME format.

For processing of date/time values between SAS and Microsoft Access, see Processing Date/Time Values between SAS and Microsoft Access.

VERSION=file-version

specifies the version of the file that you want to create. Valid values are 2007, 2003, 2002, 2000, and 97. The default value depends on the extension of the file. Always surround the version value with single quotation marks.

Restriction:	Available only for client server model.
Restriction:	If the file exists on the PC Files Server, the statement is ignored.

Example 1: Import a SAS Data Set from an Access 2007 Database Table

This code imports a data set named CUSTOMER from the Customers table in a Microsoft Access database: demo.accdb. The Microsoft Access table was saved in version 2007 format.

PROC IMPORT OUT=WORK.CUSTOMER
            DATATABLE="Customers"
            DBMS=ACCESS REPLACE;
   DATABASE="&demodir.demo.accdb";
   USEDATE=YES;
   SCANTIME=NO;
   DBSASLABEL=NONE;
RUN;

Example 2: Export a SAS Data Set and Create an Access Database File

This code exports a SAS data set named EMPLOYEE and creates a new Microsoft Access database file named test97.mdb. Note that test97.mdb does exist before the EXPORT procedure is submitted. SAS loads and names the table Employees. It then creates and saves it in the new file, test97.mdb, in version 97 format.

X "DEL c:\temp\test97.mdb";
PROC EXPORT DATA=SDF.EMPLOYEE
            OUTTABLE="Employees"
            DBMS=ACCESS97 REPLACE;
   DATABASE="c:\temp\test97.mdb";
RUN;

Example 3: Import a Data Set from an Access Database File Using a Read Buffer

This code imports a data set named INVOICE from the Invoice table in a Microsoft Access database named demo.mdb. The read buffer is set to 10 rows.

PROC IMPORT OUT=SDF.INVOICE
            TABLE="Invoice"
            DBMS=ACCESSCS REPLACE;
   DATABASE="&pcfdir.demo.mdb";
   SERVER=&server;
   PORT=&port;
   DBDSOPTS="READBUFF=10";
RUN;

Example 4: Export a SAS Data Set to an Access Database File on a PC Files Server

This code exports a SAS data set named ORDERS to a new Microsoft Access database file named TEST.MDB, located on the PC Files Server.

The code performs these tasks:

LIBNAME SDF V9 "&sasdir";
PROC EXPORT DATA=SDF.ORDERS (DROP=SPECINST)
            OUTTABLE="Orders"
            DBMS=ACCESSCS REPLACE LABEL;

   SERVER=&server;
   PORT=&port;
   VERSION='97';
   DBDSOPTS="INSERTBUFF=20";
RUN;

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