Format column to no format when loading SAS table

[jim_toby]

I'm trying to load a SAS table to Netezza and one of the issues I get is a communication link failure. Based on this discussion:

https://communities.sas.com/t5/SAS-Data-Management/Communication-link-failure-when-upload-table-to-N...

it may be because of one of my column values. However, I don't understand how to format that value when I load to Netezza. Here is how I load to netezza:

libname myPathToTable "\path\to\SAS\Table";

libname sdf odbc noprompt="server=xxxx; DRIVER={NetezzaSQL}; port=xxxx; database=xxxxx; username=xxx; password=xxxx;";

proc sql;
create table sdf.table
(DBCREATE_TABLE_OPTS 'distribute on (col1)' bulkload = YES)
AS SELECT * FROM myPathToTable.mySASTable;
quit;

Now let's say I want to reformat col2 to no format. How would I do this? 

Thanks in advance!

[kiranv_]

formats are not transfered from SAS to DBMS or vice versa. Formats are only for display i.e. that how your data appears, what is the issue with col2 that you want to fix. Format the author takes about in link is not format but a data type.

I am not sure the link you have provided makes much sense to me as I have moved data from DBMS to SAS with various numeric data types. SAS is smart enough to intrepret those data types. Only known issue is with bigint datatype, because of known numeric restriction in SAS and can be handled in various ways

[jim_toby]

@kiranv_ would I be able to format the column before I load it to Netezza? 

My column is a numeric type but contains decimals, and in the link I provided above, that could be the issue related to my communication link failure. So I would like to format it to no format as suggesting in the forum.

[kiranv_]

I have updated my answer, it is not format but one of the data type in DBMS (netezza in this case). I am not sure this is the issue.

[jim_toby]

@kiranv_All of the data types in my SAS table are either characters or numerics. Are u saying when I create the table in SAS, Netezza could be assigning one of the column values as BIGINT which could cause the issue?

[kiranv_]

No. SAS is smart enough to understand various numeric datatypes in DBMS to convert into SAS number and same with character variable of various DBMS into SAS character variable. If you have bigint  datatypein Netteza, then you may certain issue with your precision but SAS does not create big int datatypes. My take home point is the link you have provided and logic you are saying is not correct. saying that decimal(8,2) is creating problems is very tough to agree.

[jim_toby]

@kiranv_ in my example, I am not moving a table from Netezza to SAS, but rather from SAS to Netezza. So in Netezza, I am not creating any of the datatypes manually, I just run the code to load the table to Netezza from SAS

[SuryaKiran]

Check this data types document for Netezza. This tells you the default formats translated to Netezza

https://documentation.sas.com/?docsetId=acreldb&docsetTarget=p07ge3f7fvfh5ln1msgdpqhbeh31.htm&docset...

[kiranv_]

You can do following.

1. create your sas table with format you want and then insert the data rather create table

2 in pass through code use cast(yourcolumn as integer). I would prefer cast as integer way and I know it works

SAS when seeing a datatype decimal(8,2) it tries to use format  w.d  in format of 8.2. 

As far as I know connectivity issues general happens when you have limitation at Netezza side, that is  netezza admin decides a particular user can stay connected for certain amount of time( this is done for number of reasons).  

Generally Connectivity issues does not have anything with datatypes conversion.

[jim_toby]

@Reeza @kiranv_

So I broke up the problem and I found which column is causing the communication link failure (let's call this column col1). When I load all columns besides it, I don't get the communication link failure, but when I try to load that one column alone, I get the error. I tried to reformat the column like this:

Proc datasets Lib=MyLib;
Modify mySASTable;
format col1;
Run;
Quit;

And than I tried to load it alone with proc sql but I get the same error. Is it because the modification is only temporary and doesn't affect the actual SAS table? If so, can someone show me how I can load the modified table?

[Reeza]

You're assuming that link was correct and that the format has to be the issue. I'm not 100%sure of that. What format was applied to col1 before you changed it?

[jim_toby]

@Reeza I'm thinking maybe it's worth a shot because when I try to load that column alone to a table in Netezza I get the communication link error, but when I load other columns together, It does it successfully. Col1 is of type character which contains names of individuals. 

[Reeza]

Check your entries, I'm going to guess a few have characters that are problematic to loading. To do this, split your data in half (or quarters or whatever) and try loading it in batches.

[jim_toby]

@Reeza thanks for the suggestion, but maybe I just won't load data from SAS straight to Netezza because some of these tables I'll need to load are over 300 million records so splitting them up will be a time kill. I'll just convert them to csv and load the csv to Netezza.

[jim_toby]

@Reeza also, I uploaded the table without the bulkload option (which took about 5 hours) and it did not error out...At this point I'm not sure what to think the issue is.