Previous Page | Next Page

File Format-Specific Reference for the IMPORT and EXPORT Procedures

The IMPORT and EXPORT Procedure Statements for Access Files

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 file, you can specify DBMS=ACCESS on Windows platforms and SAS identifies the version.

See Example 1: Import a SAS Data Set from an Access 2007 Database Table and Example 2: Export a SAS Data Set to Create an Access97 Database File.

IMPORT and EXPORT Procedure Statements for Access Files on Windows
Data Source Syntax Valid Value Default Value PROC IMPORT PROC EXPORT?
ACCESS

ACCESS97

ACCESS2000

ACCESS2002

ACCESS2003

ACCESS2007

 DATABASE Complete path and filename for the Microsoft Access database file.
Yes Yes
DBDSOPTS Any valid data set options for Microsoft Access database engine.
Yes Yes
DBPASSWORD database password
Yes Yes
DBSASLABEL Yes|No Yes Yes No
MEMOSIZE 1 to 32767 1024 Yes No
PASSWORD user password
Yes Yes
SCANMEMO Yes|No Yes Yes No
SCANTIME Yes|No Yes Yes No
USEDATE Yes|No No Yes No
USER User ID
Yes Yes
DBSYSFILE Complete path and filename for the Workgroup Administration file.
Yes Yes

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.  [cautionend]

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

IMPORT and EXPORT Procedure Statements for Access Files on Linux, UNIX, and Windows 64-Bit Operating Platforms
Data Source Syntax Valid Value Default Value PROC IMPORT PROC EXPORT
ACCESSCS DATABASE Complete path and filename for the Microsoft Access database file.
Yes Yes
DBDSOPTS Any valid data set options for Microsoft Access database engine.
Yes Yes
DBPASSWORD database password
Yes Yes
DBSASLABEL Yes|No Yes Yes No
MEMOSIZE 1 to 32767 1024 Yes No
PASSWORD user password
Yes Yes
PORT 1 to 65535 8621 Yes Yes
SCANMEMO Yes|No Yes Yes No
SCANTIME Yes|No Yes Yes No
SERVER server name
Yes Yes
SERVERPASS server password
Yes Yes
SERVERUSER server User ID
Yes Yes
SERVICE service name'
Yes Yes
SSPI= Yes|No
Yes Yes
USEDATE Yes|No No Yes No
USER User ID
Yes Yes
VERSION 97|2000|2002|2003|2007 2000 Yes Yes
DBWSYSFILE Complete path and filename for the Workgroup Administration file.
Yes Yes

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, refer to Data Set Options 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|YES|NO

specifies the data source for column names.

COMPAT

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

Alias: YES
NONE

specifies that the data source column headings are not saved as SAS label names. The SAS label names are then left as blanks.

Alias: NO
Restriction: Due to a Microsoft Jet engine limitation, no more than 64 characters of column names are written to SAS variable labels.
Restriction: Due to a limitation in the Microsoft Ace engine and the Microsoft Jet Excel engine, using MIXED=YES could result in improper text variable lengths.
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
Note: 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 DATE9. 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 these items:

  • 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, this preserves 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 and time values between SAS and Microsoft Access, see Processing Date and 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 to Create an Access97 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";
   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 write buffer is set to 25 rows.

The code performs these tasks:

LIBNAME SDF   "&sasdir";
PROC EXPORT DATA=SDF.ORDERS (DROP=SPECINST)
            OUTTABLE='Orders'
            DBMS=ACCESSCS REPLACE LABEL;
   SERVER="&server";
   VERSION='97';
   DBDSOPTS='INSERTBUFF=25';
RUN;

Previous Page | Next Page | Top of Page

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