An error might occur if you use the ODBC engine in a LIBNAME statement and you run a DATA step to write a SAS data set to the Netezza database


The following error might occur if you use the ODBC engine in a LIBNAME statement and you submit a DATA step to write a SAS data set to the Netezza database:

libname tstntz odbc dsn=nzsql user=production password=password;

data tstntz.new_table; 
set mydata.test;
run;

ERROR: Error attempting to CREATE a DBMS table.
ERROR: CLI execute error: ERROR: 'CREATE TABLE NEW_TABLE (rcrd_type varchar(11),indv_id numeric(16,0),pre_seg varchar(1),segment varchar(25),ran_num double(13))' error

When you create a Netezza table, you cannot specify the length for a double data type.  Instead, you should specify either DOUBLE or FLOAT(13), as shown in the following example.

As a workaround, create the Netezza table using the SQL procedure's Pass-Through facility and then write the SAS data set to that empty table, as illustrated in this example:

libname foo odbc dsn=netezza-data-set-name netza user=user-name pwd=netezza-password;
proc sql;
connect to odbc(dsn=netezza-data-set-name user=user-name pwd=netezza-password);
execute(create table classtest (Name varchar(8), sex varchar(1), Age double, height double, weight double)) by odbc;
quit;
proc sql;
insert into foo.classtest select * from sashelp.class;
quit;