Previous Page | Next Page

File-Specific Reference

Microsoft Access Files

Statements

The SAS/ACCESS LIBNAME statement connects to a Microsoft Access database MDB file in READ/WRITE mode by default. The SAS CONNECT statement in PROC SQL also connects in READ/WRITE mode by default. Set the LIBNAME option ACCESS=READONLY to connect to the file in READONLY mode.

The SAS engine for the Microsoft Access database uses the Microsoft Jet Provider to connect to Microsoft Access data in the MDB file. A connection requires an existing MDB file. The engine knows the version of the MDB file that the Microsoft Access database saves.

The engine supports some special queries. The queries return information such as available tables, primary keys, foreign keys, and indexes. For more information, see LIBNAME Statement and Pass-Through Facility for PC Files on Microsoft Windows.

Connection Options

These  connection options can be used in the LIBNAME statement or in the PROC SQL statement.

Note:   You can use USER, PASSWORD, DBPASSWORD, and DBSYSFILE to access your .mdb files, but it does not change your current security settings for those files.  [cautionend]

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 can be defined in addition to user-level security. Do not include ampersands, quotation marks, or invisible characters in your password.

Alias: DBPWD|DBPW
DBSYSFILE='workgroup-information-file'

contains information about the users in a workgroup based on information that defines your Microsoft Access database. Any user and group accounts or passwords that you create are saved in the workgroup information file.

Alias: DBSYS|WGDB
PASSWORD='user-password'

specifies a password for the user account. A password can be 1 to 14 characters long and can include any characters except ASCII character 0 (null). Passwords are case sensitive. Do not include ampersands, quotation marks, or invisible characters in your password.

Note:   If you have user-level security set in your .mdb file, you need to use this option and the USER option to be able to access your file.  [cautionend]

Alias: PWD|PW
USER= 'user-ID'

specifies a user account name. User names can be 1 to 20 characters long and can include alphabetic characters, accented characters, numbers, and spaces.

Alias: UID|USERID
Note: If you have user-level security set in your .mdb file, you need to use this option and the PASSWORD option to be able to access your file.
Data Types Conversion

The following table shows the default SAS variable formats that SAS/ACCESS assigns to .mdb data types. These formats are assigned when SAS reads data from Microsoft Access files using the LIBNAME engine.

Default SAS Formats Assigned for MDB Data Types
MDB Field Data Type SAS Variable Format SAS Variable Type
Yes|No 2. Numeric
Number (FieldSize=Byte) 4. Numeric
Number (FieldSize=Decimal) w.d 5 Numeric
Number (FieldSize=Integer) 6. Numeric
Number (FieldSize=Long Integer) 11. Numeric
Number (FieldSize=Single)
Numeric
Number (FieldSize=Double)
Numeric
AutoNumber (FieldSize=Long Integer) 11. Numeric
AutoNumber (FieldSize=Replication ID) $38. Character
CURRENCY DOLLAR21.2 Numeric
Date/Time DATETIME.

12

 Numeric
Text $w. 3 Character
Memo $w. 4 Character
OLE Object $w. 4 Character
Hyperlink $w. 4 Character
1 The default format is DATETIME. You can use USEDATE=YES to change format from DATETIME. to DATE. You can also use the SASDATEFMT option to change the format to other date or date and time formats.

2 The SAS date/time value uses 01Jan1960 as the cutoff date. The Jet provider date/ time value uses 30Dec1899 as the cutoff date.

3 The width of $w. is equal to the field size of the column defined in your Microsoft Access table.

4 When SCANMEMO=YES (default is NO), the width value of $w. is determined by the longest string of data that is scanned in the field. It can also be determined by the value specified in the DBMAX_TEXT= option, whichever is less. Otherwise, when the option SCAN_TEXT=NO, the width value of $w. is equal to the value specified in DBMAX_TEXT= option.

5 The w width value is equal to the precision value plus 1. The d decimal value is equal to the scale value, where precision and scale are defined for the column in the table.


The following table shows the default .mdb data types that SAS/ACCESS assigns to SAS variable formats. These data types are assigned when you write SAS data to an .mdb file using the LIBNAME engine. You can override these default conversions by using the DBTYPE data set option during processing.

Default DB Data Types Assigned for SAS Variable Formats
SAS Variable Format MDB Data Type
$BINARYw.
 Text (VarChar) or Memo (LongText) 1
$CHARw. Text (VarChar) or Memo (LongText)1
HEX w.
 Text (VarChar) or Memo (LongText) 1
$w. Text (VarChar) or Memo (LongText)1
w.d Number 2
BESTw.
 Number 2
BINARYw. Number 2
COMMA w.d
 Number 2
COMMAXw.d
 Number 2
Ew.
 Number 2
FRACTw.
 Number 2
HEXw.
 Number 2
NEGPARENw.d
 Number 2
PERCENTw.d
 Number 2
DOLLARw.d
 Currency
DOLLARXw.d
 Currency
DATEw.
 Date/Time
DATETIMEw.d
 Date/Time
DDMMYYw.
 Date/Time
HHMMw.d
 Date/Time
JULDAYw.
 Date/Time
JULIANw.
 Date/Time
MMDDYYw.
 Date/Time
MMYYw.d
 Date/Time
MONTHw.
 Date/Time
MOYYw.
 Date/Time
WEEKDATEw.
 Date/Time
WEEKDATXw.
 Date/Time
WEEKDAYw.
 Date/Time
WORDDATEw.
 Date/Time
WORDDATXw. Date/Time
1 If the character format length is greater than 255 characters, the loaded format is Memo. Otherwise, the loaded format is Text.

2 For Microsoft Access 2000, 2002, and 2003, a SAS numeric data type with no format specified is converted to a number data type with a double field size. If the format is specified as w. in SAS, the loaded data type is a number with a long integer field size. If the format is specified as w.d in SAS, the loaded data type is a number data type with a decimal field size. For Microsoft Access 97, if the format is specified as w. in SAS, the loaded data type is a number with a long integer field size. Otherwise, the SAS numeric data type is converted to a number data type with a double field size. However, you can set the SAS environment variable, LOAD_DBL=YES, to force a SAS numeric data type to be loaded into a numeric data type with a Double field size.

Example:

DATA test;
 FORMAT j 5. k 6.2;
 i=123.45; j=12345; k=123.45;
RUN;

/* The following PROC loads the Test1 table, which contains 
 Column i with a Double field size,
 Column j with a Long Integer field size,
 and Column k with a Decimal field size. */
PROC EXPORT DATA=test
       OUTTABLE= 'Test1'
            DBMS=ACCESS REPLACE;
     DATABASE='c:\temp\test.mdb';
RUN;

/* The following PROC loads the Test2 table, which contains
  Columns i, j, and k, all of which have a Double field size. */
OPTIONS SET=load_dbl yes;
PROC EXPORT DATA=test
            OUTTABLE= 'Test2'
            DBMS=ACCESS REPLACE;
     DATABASE='c:\temp\test.mdb';
RUN;

Processing Date and Time Values between SAS and Microsoft Access

To import date and time values from a Microsoft Access database, the SAS/ACCESS LIBNAME engine reads in the date using the DATE9. format. It reads in the time values using TIME8. However, you can set the LIBNAME option, USEDATE=YES, or the LIBNAME statement, USEDATE=YES. Using the IMPORT procedure, you can have date and time values read in using the DATE format.

To export SAS data values with DATE, TIME, or DATE TIME format to a Microsoft Access database, SAS values are written using the date and time data type. However, Microsoft Access can identify and display the values in the correct DATE, TIME, or DATETIME format.


Setting Environment Variables: BOOL_VAL

By default, the SAS/ACCESS LIBNAME engine imports YES (TRUE) into SAS as the numeric value 1. Set the environment variable with the following statement: 

/* To have the YES value imported into SAS as numeric value-1 */
OPTIONS SET=BOOL_VAL ASIS;

/* Reset to the default value */
OPTIONS SET=BOOL_VAL SAS;


Setting Environment Variables: TIME_VAL

By default, the SAS/ACCESS LIBNAME engine loads SAS time values into Microsoft Access databases with a time value less than or equal to 24 hours. Set the environment variable with this statement:

/* To have SAS time values exported to Microsoft Access
 database with SAS date/time base, 01Jan1960. */ 
OPTIONS SET=TIME_VAL SAS;

When importing data with the SCANTIME=YES option, SAS scans date/time values in the columns and assigns the TIME. format if all the values are in the year 1960. Otherwise SAS assigns the DATETIME. format to the columns.

/* Reset to default value */
OPTIONS SET=TIME_VAL ONEDAY;

By default, SAS loads the numeric value of format w.d with a decimal field size into Access database files, Version 2000 or later. The decimal type field takes more space than a double type field. To manage your storage space, set the environment variable with the following statement:

/* To have SAS numeric values loaded into an Access database
 with a double field type, to save storage space. */
OPTIONS SET=LOAD_DBL YES;

/* To reset to the default behavior */
OPTIONS SET=LOAD_DBL NO;

Previous Page | Next Page | Top of Page

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