Microsoft Access Database Files

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

Microsoft Access is a desktop relational database system that uses 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 a Microsoft Access database (including tables, queries, forms, and reports) are stored in the native Jet .MDB file format.

The table below lists the maximum size limits for the components of .MDB files.

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

The following list summarizes all the field data types available in Microsoft Access, their uses, and their storage sizes.

The conversion of date and time values between SAS data sets and Microsoft Access database is transparent to users. However, you are encouraged to understand the differences between them.

In Microsoft Access database software, a date value is saved with the integer portion of a number that ranges from 30 December 1899 (saved as integer value: 0) to 31 December 9999 (saved as integer value: 2,958,465). A Microsoft Access database software time value is the decimal portion of a number that represents time as a proportion of a day. For example, 0.0 is midnight, 0.5 is noon, and 0.999988 is 23:59:59 (on a 24-hour clock). While a number can have both a date and a time portion, the formats in Microsoft Access database display a number in a date, time, or date/time format.

In SAS software, SAS dates are valid back to A.D. 1582 and ahead to A.D. 9,999. A date value is represented by the number of days between January 01, 1960 and that date. A time value is represented by the number of seconds between midnight and that time of day. A datetime value is represented by the number of seconds between midnight January 01, 1960 and that datetime.

A date value before year 1582 is imported as a missing value in SAS. You can import a time value as a date value 30DEC1899, so make sure that you have assigned the correct format. Years 4000 and 8000 are considered leap years in the Microsoft Access database system. However, they are considered as a nonleap year in the SAS software system. For more information about time values, see USEDATE= and SCANTIME=.

Before you use PROC IMPORT and PROC EXPORT for Microsoft Access files, it is helpful to be familiar with Microsoft Access file formats. Access Versions 2000, 2002, and 2003 share the same .MDB file formats. ACCESS2000, ACCESS2002 and ACCESS2003 are treated as an alias for ACCESS in SAS. However, Microsoft ACCESS Version 97 used different formats, which are incompatible with ACCESS 200x versions.

For an existing .MDB, you can specify DBMS=ACCESS on Microsoft Windows platforms and SAS identifies the version for you. Specify DBMS=ACCESS97 on Microsoft Windows platforms only when you want to export and create a new .MDB file with Microsoft Access Version 97 formats.

The following tables list the statements that are available to import data from an Microsoft Access database file using the LIBNAME statement on Microsoft Windows operating system platforms. See Examples of Importing and Exporting Microsoft Access Files.

On Linux, UNIX, and Microsoft Windows 64-bit operating platforms, you can use the client/server model to access data in your .MDB file. For more information, see PC Files Server.

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 Microsoft Access Version 97 formats.

Note:   If the client/server model is used, the SAS client cannot access a Microsoft 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 Microsoft Access database file from a SAS client.  

The following tables list the statements that are available to import data from a Microsoft Access database file using the client/server model on Linux, UNIX, and Microsoft Windows 64-bit operating platforms.

DATABASE="database";

specifies the complete path and filename of the Microsoft 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.

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.

You must surround the options in single quotation marks. Here is an example:

DBDSOPTS='FIRSTOBS=10 READBUFF=25';

If the option string that you are specifying contains single quotations marks, use double quotation marks around it in your statement. Here is an example:

DBDSOPTS="DBTYPE=(BilledTo='CHAR(8)')";

For a list of supported data set options, please refer to Data Set Options for PC Files on Microsoft Windows or Data Set Options for PC Files on Linux and UNIX.

Note:   

• For users who use the client/server model to access data in a Microsoft Access database, the default value for READBUFF= is 1. To improve performance for reading data, set READBUFF= to 25 or higher.

• For users who use the client/server model to access data in a Microsoft Access database, the default value for INSERTBUFF= is 1. To improve performance for writing data, set INSERTBUFF= to 25 or higher.

  

DBPASSWORD="database-file-password";

enables you to access your file if you have database-level security set in your .MDB file. A database password is case sensitive, and you can define itinstead of user-level security. Aliases: DBPWD, DBPW.

DBSASLABEL=(COMPAT/)YES | (NONE/)NO;

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

(COMPAT/)YES

specifies that the data source column names are saved as the corresponding SAS label names. YES is an alias of COMPAT.

(NONE/)NO

specifies that the data source column names are not saved as SAS label names and are left as nulls. NO is an alias of NONE.

MEMOSIZE=1 to 32767;

Specifies the maximum variable length in SAS that is allowed while importing data from memo columns of a Microsoft Access database table. Any memo data in Access database table whose length exceeds this value is truncated when it is imported into SAS. Alias: DBMAX_TEXT=.

PORT= port-number; (available only for Client Server Model)

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 is displayed on the PC Files Server control panel screen when it is started on the PC. The PORT= statement and the SERVICE= statement should not be used in the same procedure. The default value is 8621. Alias: PORT_NUMBER=.

SCANMEMO=YES | NO;

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

Note:   This option applies only to memo data type columns. It does not apply to text type columns.  

YES

scans the length of memo data for a data source column and uses the length of the longest memo text of data that it finds as the SAS variable width. However, if the maximum length that it finds is greater than what is specified in the MEMOSIZE= option, the smaller value that is specified in MEMOSIZE= 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 Jet provider is used as the SAS variable width. However, if the returned column width is greater than what is specified in the MEMOSIZE= option, the smaller value specified in MEMOSIZE= is applied as the SAS variable width.

Note:   To correct the truncated memo data problem for DBCS users (including Chinese, Japanese, and Korean), set the environment variable DBE_DBCS to YES. This doubles the scanned memo data length and therefore corrects the problem of truncated memo data. To set the DBE_DBCS environment variable, submit this statement: Options set=DBE_DBCS YES;   

SCANTIME=YES | NO;

specifies whether to scan the date/time data while importing data from a date/time column from the Microsoft Excel workbook.

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.

NO

specifies not to scan the date/time column. The DATETIME. format is assigned for a date/time column if USEDATE=NO. The DATE. format is assigned for a date/time column if USEDATE=YES.

SERVER= "PC-server-name"; (available only for Client Server Model)

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 configure the service name, port number, maximum number of connections allowed, and use of data encryption on your PC Files Server. This is a required statement.

Alias: SERVER_NAME=

SERVICE= "service-name"; (available only for Client Server Model)

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. This statement and the PORT= statement should not be used in the same procedure. Note that this service name needs to be defined on both your UNIX machine and your PC Files Server.

Alias: SERVICE_NAME=

USEDATE=YES | NO;

specifies whether to assign a DATE. or a DATETIME. format while importing a date/time column from a Microsoft Excel workbook. See the SCANTIME= statement 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.

YES

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

NO

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

VERSION='file-version'; (available only for Client Server Model)

specifies the version of the file that you want to create if the file does not exist on your PC Files Server yet. Valid values are '2003', '2002', '2000', and '97'. The default value is '2000'. Always surround the version value with single quotation marks. If the file already exists on the PC Files Server, the statement is ignored.

1. Import a SAS Data Set from an Access 97 Database Table

   The following example code imports a data set named CUSTOMER from the Customers table in a Microsoft Access database, demo97.mdb. The Access table was saved in Access Version 97 format.

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

2. Export a SAS Data Set and Create New Access Database File

   The following example 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 PROC EXPORT is submitted. SAS loads and names the table Employees, and then creates and saves it in the new file, test97.mdb, in Access 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;

3. Import a Data Set from a Microsoft Access Database File Using a Read Buffer

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

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

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

   The following example code exports a SAS data set named ORDERS to a new Microsoft Access database file named test.mdb, which is located on the PC Files Server. The code performs these tasks:

   	Drops the variable SPECINST
   	Sets the insert buffer to 20 rows
   	Writes SAS label names if column names exist in the loaded table named Orders
   	If test.mdb does not exist, a new test.mdb file is created with version 97 formats. Otherwise, the version statement is ignored.
   	Writes the table to the existing file without changing its version.

   LIBNAME SDF V9 "&sasdir";
   PROC EXPORT DATA=SDF.ORDERS (DROP=SPECINST)
               OUTTABLE="Orders"
               DBMS=ACCESSCS REPLACE LABEL;
      DATABASE="&cstmpdir.test97.mdb"; 
      SERVER=&server;
      PORT=&port;
      VERSION='97';
      DBDSOPTS="INSERTBUFF=20"; 
   RUN;

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