LIBNAME Statement and Pass-Through Facility for PC Files on Microsoft Windows

Microsoft Jet and Microsoft Ace Provider Supported Data Types

The following table lists the valid data types that are supported by Jet provider. 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		See Note 1
COUNTER	10			See Note 2
GUID	16
CURRENCY	19
DATETIME	8		#	See Note 3
VARCHAR	255	max length		See Note 4
LONGTEXT	536,870,910			See Note 5
VARBINARY	255
BIGBINARY	4000
LONGBINARY	1,073,741,823			See Note 5
1 When you use the data type DECIMAL, you can specify precision and scale. 2 When you use the DBTYPE= option, the COUNTER 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. 3 When 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#. 4 When you use the data types VARCHAR or VARBINARY, you must specify the maximum length. 5 All Excel columns can have a null value. Do not specify NOT NULL for a column when you create an Excel table. However, you can specify NULL or NULL attributes for a field when you create an Excel table.

Example 1

Connects to Excel, drops a table named DEMO, 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

Connects to Access, drops a table named DEMO, 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;

Top of Page