Format column to no format when loading SAS table

[kiranv_]

This makes sense. SAS is pretty powerful in converting datatypes and it is issue of bulkload. I have loaded and moved more than 200 million, without any issues. Please try bl_options and see whether it works. 

libname sasflt 'SAS-library';
libname net_air netezza user=myuser pwd=mypwd
        server=air2 database=flights;

proc sql;
create table net_air.flights98
       (bulkload=YES bl_options="logdir 'c:\temp\netlogs'")
        as select * from sasflt.flt98;
quit;

Also try using a very SAS small dataset  and do a bulkload and see whether it works or not. otherwise there is issue with your bulkload itself, which is netezza component.

[jim_toby]

@kiranv_ with smaller datasets the bulkload option is working.

My workaround was insead of using bulkload, to use insertbuff and dbcommit. 

[Reeza]

I was suggesting splitting to find the issue, assuming you do find the issue, it should be relatively easy to transfer it up. I hate working with CSVs because you have to ensure the right types and formats manually again (though I guest Netezza doesn't use formats).

[Reeza]

You would have to list the variables for starter - use the feedback option to cheat here.
Then you would add format=8. or best12. to set it to a format that may not be problematic. Or you can strip the format within PROC DATASETS ahead of time.

proc sql feedback;
create table want as select * from sashelp.class;
quit;

Copy code from log and then add in format portion:

proc sql;
select CLASS.Name, CLASS.Sex, CLASS.Age, CLASS.Height format=best12., CLASS.Weight
from SASHELP.CLASS;
quit;

[jim_toby]

@Reeza thanks I'll look into this. The problem is that some tables have a large amount of columns...so maybe it's not ideal even if it does solve my issue.

[Reeza]

You can drop formats using proc datasets or in a data step. You can refer to all numeric or character or lists of variables within those procedures, but you cannot with PROC SQL. That SQL limitation is painful. Have you tried loading your data with a data step or PROC APPEND instead of PROC SQL?

[jim_toby]

@Reeza I have not but I think maybe itll be worth a shot to do it like that.