Load Sas to Netezza with specifying data types

[jim_toby]

When I load SAS files to Netezza, SAS chooses it's own data types which will affect the performance in Netezza when querying through a really large table because sometimes I can store a column as a BYTEINT in Netezza but when importing it from SAS, it'll store it as Character Varying (1). So instead I tried to create the table in Aginity and than insert the SAS file into the table through SAS.

This is my code to import SAS to Netezza:

LIBNAME ECLIB000 "\\Path\To\SASTables";
libname sdf odbc noprompt="server=xxxx; DRIVER={NetezzaSQL}; port=xxxx; database=xxx; username=xxxx; password=xxxx;";


proc sql;
INSERT INTO sdf.NetezzaTable
(bulkload=YES)
SELECT * FROM ECLIB000.SASTable;
quit;

With doing this, I get errors like: ERROR: Value 2 on the SELECT clause does not match the data type of the corresponding column listed after the INSERT table name

And I'm guessing it's because it's trying to insert a value that has a different data type than the one I created. How can I insert into the Netezza table using the data types I created in Aginity? Or is there a better way to doing this?

Thanks!

[kiranv_]

if you have SAS dataset with number and you can map it to byteint.

As said previously post SAS is pretty smart in mapping most of time.

SAS numeric can be mapped to byteint int decimal easily and it takes care of its own

SAS charcter to char varchar etc.

I am not sure, what exactly issue your having.

try using dbsastype as shown in below example . info on dbsastype in the link

http://support.sas.com/documentation/cdl/en/acreldb/63647/HTML/default/viewer.htm#a001371572.htm

proc append base=dblib.hrdata (dbsastype=(empid='CHAR(20)'))
            data=saslib.personnel;
run;

[jim_toby]

@kiranv_ does this append to a table in SAS or in Netezza. I want to insert data into an existing table in netezza. In the link you sent it seems like the proc append is used to insert in a SAS table.

[kiranv_]

this is appending to netezza table. your base table is netezza table

[jim_toby]

@kiranv_ thanks, it seems like it's appending but I'm still having issues with the data type.

I'm doing what you suggested but when I run:

select * from _v_relation_column where name = 'hrdata'

I still see that the data types under the FORMAT_TYPE column are either double precision or character varying. I defined them as either integer, char(1) or DEC(18, 10). Here is my SAS code:

LIBNAME SASLIB "\Path\To\SASTable";
LIBNAME ntza odbc noprompt "server=xxxx; port=xxxx, ....";

proc append base= ntza.hrdata (dbsastype= (col1= 'INTEGER' col2= 'CHAR(1)' col3= 'DEC(18, 10) col4= 'INTEGER'))
data= SASLIB.SasTable
run;

 Also, when appending to Netezza, can you use the bulkload option?

[kiranv_]

is your Netezza table,  newly created table or you are appending the data to already created netezza table(Just for information, Proc append will create table, if same name table is not there). if it is already created table, you cannot change datatype. for changing datatypes will have multiple restrictions, most often you have to recreate netezza table.

[jim_toby]

@kiranv_ no, my Netezza table isn't created before hand. I let the proc append create the table but for example it still changes all of the integers to be double precision.

[kiranv_ · Accepted solution]

ok if that is the case, please use dbtype as shown. if it is possible  make a SAS dataset with 5 or ten records and then try something like shown below. if it works then apply it to large dataset.

data nztable.cust_table (dbtype=(cust_value='integer'
 cust_n='char(1)'));
set work.cust_table;
run;