Microsoft Access MDB Files

LIBNAME Statement Data Conversions for MDB Files

The following table shows the default SAS variable formats that SAS/ACCESS assigns to MDB data types when you read or import MDB data with the LIBNAME statement.

Default SAS Variable Formats for MDB Data
MDB Field Data Type	SAS Variable Format	SAS Variable Type
YES\|NO	2.	numeric
Number (FieldSize=Byte)	4.	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	DATE9. See Notes 1 and 2	numeric
Text	$w. See Note 3	character
Memo	$w. See Note 4	character
OLE Object	$w. See Note 4	character
Hyperlink	$w. See Note 4	character
1 The default format is DATE9. However, you can use the SASDATEFMT option to change the format to other date or datetime formats. The engine automatically converts the internal date value for you. 2 If you have a time only field in your Microsoft Access range, you can use SASDATEFMT to assign it with the SAS TIME. format. Note that the SAS date/time value uses 01Jan1960 as the cutoff date, while 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 Access table. 4 When the option SCAN_TEXT=YES (which is the default value), the width value of $w. is determined by the longest string of data that is scanned in the field or 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.

The following table shows the default MDB data types that SAS/ACCESS assigns to SAS variable formats when you write SAS data to an MDB file with the LIBNAME statement.

Default MDB Data Types for SAS Variable Formats
SAS Variable Format	MDB Data Type
$BINARYw.	Text (VarChar> or Memo (LongText) See Note 2
$CHARw.
HEXw.
$w.
w.d	Number. See Notes 3 and 4.
BESTw.
BINARYw.
COMMAw.d
COMMAXw.d
Ew.
FRACTw.
HEXw.
NEGPARENw.d
PERCENTw.d
DOLLARw.d	Currency
DOLLARXw.d	Currency
DATEw.	Date/Time
DATETIMEw.d
DDMMYYw.
HHMMw.d
JULDAYw.
JULIANw.
MMDDYYw.
MMYYw.d
MONTHw.
MOYYw.
WEEKDATEw.
WEEKDATXw.
WEEKDAYw.
WORDDATEw.
WORDDATXw.
1 You can use the data set option DBTYPE= to override the default data types. For valid data types supported, please refer to the valid data types list. 2 If the character format length is greater than 255 characters, the loaded format is Memo; otherwise, the loaded format is Text. 3 For Access 2000 and 2002, 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 in Access is a number data type with an integer field size. If the format is specified as w.d in SAS, the loaded data type in Access is a number data type with a decimal field size. 4 For Access 97, if the format is specified as w. in SAS, the loaded data type in Access is a number data type with an integer field size. Otherwise, the SAS numeric data type is converted to a number data type with a double field size.

Top of Page