Microsoft Jet and Microsoft Ace Provider Supported Data Types

Valid data types that are supported by Jet provider.

Valid in:

CREATE statements.

Example 1: Connect to Excel and Drop and Create a Table

Example 2: Connect to Access and Drop and Create a Table

Details

You can use these data types when you use the CREATE statement using the SQL pass-through facility to create a table. You can also use the data types when you use data set option DBTYPE to change the data type for a loaded column.

Microsoft Jet Provider Supported Data Types

Data Type	Column Size	Create Parameters	Prefix and Suffix	Comments
BIT	2
BYTE	3
SHORT	5
LONG	10
SINGLE	7
DOUBLE	15
DECIMAL	28	precision,scale		1
COUNTER	10			2
GUID	16
CURRENCY	19
DATETIME	8		#	3
VARCHAR	255	max length		4
LONGTEXT	536,870,910			5
VARBINARY	255
BIGBINARY	4000
LONGBINARY	1,073,741,823			5
1When you use the data type DECIMAL, you can specify precision and scale.
2When you use the DBTYPE option, theCOUNTER data type is valid only when you set INSERT SQL=YES. The COUNTER data type is supported only in the pass-through facility when you create a table.
3When you use the pass-through facility to set a date/time value, you must add the prefix and suffix, #. For example, #01/01/2001# and #03/12/1999 12:12:12#.
4When you use the data types VARCHAR or VARBINARY, you must specify the maximum length.
5All Excel columns can have a null value. Do not specify NOTNULL for a column when you create an Excel table. You can specifyNULL or NULL attributes for a field when you create a Microsoft Excel table.

Examples

Example 1: Connect to Excel and Drop and Create a Table

Connects to Microsoft Excel, drops the DEMO table, and creates a new table.

PROC SQL;
   CONNECT TO EXCEL AS db (PATH='c:\temp\demo.xls'filelock=yes);
   EXECUTE(DROP table demo) BY db;
   EXECUTE(CREATE TABLE demo(EmpID long, FirstName char(10),
       Salary decimal(10,2), hiredate datetime)) BY db;
   EXECUTE(INSERT INTO demo values(12345678, 'Michael',
       123456.78, #07/01/2001#)) BY db;
   EXECUTE(INSERT INTO demo values(23456789, 'Howard', 234567.89,
       #04/01/1983#)) BY db;
   EXECUTE(INSERT INTO demo values(34567890, 'Nancy', null,
       #02/01/1982#)) BY db;
   EXECUTE(INSERT INTO demo values(34567890, 'Andy', 456789.01,
       null)) BY DB;
   SELECT * FROM CONNECTION TO DB(SELECT * FROM demo);
   DISCONNECT FROM DB;
   QUIT;

Example 2: Connect to Access and Drop and Create a Table

Connects to Microsoft Access, drops the DEMO table, and creates a new table.

PROC SQL;
   CONNECT TO Access AS db (PATH='c:\temp\demo.mdb');
   EXECUTE(DROP table demo) BY db;
   EXECUTE(CREATE table demo(EmpID long not null, 
       FirstName char(10) not null,
       Salary decimal(10,2), hiredate datetime)) BY db;
   EXECUTE(INSERT INTO demo values(12345678, 'Michael',
       123456.78, #07/01/2001#)) BY db;
   EXECUTE(INSERT INTO demo values(23456789, 'Howard', 234567.89,
       #04/01/1983#)) BY db;
   EXECUTE(INSERT INTO demo values(34567890, 'Nancy', null,
       #02/01/1982#)) BY db;
   EXECUTE(INSERT INTO demo values(34567890, 'Andy', 456789.01,
       null)) BY db;
   SELECT * FROM CONNECTION TO db(SELECT * FROM demo);
   DISCONNECT FROM db;
   QUIT;