topic Re: Format column to no format when loading SAS table in SAS Data Management

Format column to no format when loading SAS table

jim_toby — Tue, 13 Nov 2018 16:25:24 GMT

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-Netezza-database/td-p/486989

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!

Re: Format column to no format when loading SAS table

kiranv_ — Tue, 13 Nov 2018 16:54:28 GMT

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

Re: Format column to no format when loading SAS table

jim_toby — Tue, 13 Nov 2018 16:48:30 GMT

@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.

Re: Format column to no format when loading SAS table

Reeza — Tue, 13 Nov 2018 16:50:47 GMT

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;

Re: Format column to no format when loading SAS table

kiranv_ — Tue, 13 Nov 2018 16:52:57 GMT

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.

Re: Format column to no format when loading SAS table

SuryaKiran — Tue, 13 Nov 2018 16:54:59 GMT

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&docsetVersion=9.4&locale=en

Re: Format column to no format when loading SAS table

jim_toby — Tue, 13 Nov 2018 16:57:21 GMT

@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?

Re: Format column to no format when loading SAS table

kiranv_ — Tue, 13 Nov 2018 17:15:00 GMT

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.

Re: Format column to no format when loading SAS table

jim_toby — Tue, 13 Nov 2018 18:57:43 GMT

@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

Re: Format column to no format when loading SAS table

jim_toby — Tue, 13 Nov 2018 19:18:14 GMT

@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.

Re: Format column to no format when loading SAS table

kiranv_ — Tue, 13 Nov 2018 19:27:54 GMT

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.

Re: Format column to no format when loading SAS table

Reeza — Tue, 13 Nov 2018 22:26:50 GMT

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?

Re: Format column to no format when loading SAS table

jim_toby — Wed, 14 Nov 2018 12:08:14 GMT

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

Re: Format column to no format when loading SAS table

jim_toby — Wed, 14 Nov 2018 13:49:52 GMT

@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?

Re: Format column to no format when loading SAS table

Reeza — Wed, 14 Nov 2018 16:01:27 GMT

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?

Re: Format column to no format when loading SAS table

jim_toby — Wed, 14 Nov 2018 16:13:22 GMT

@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.

Re: Format column to no format when loading SAS table

Reeza — Wed, 14 Nov 2018 16:17:13 GMT

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.

Re: Format column to no format when loading SAS table

jim_toby — Wed, 14 Nov 2018 16:40:55 GMT

@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.

Re: Format column to no format when loading SAS table

jim_toby — Wed, 14 Nov 2018 16:49:43 GMT

@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.

Re: Format column to no format when loading SAS table

Reeza — Wed, 14 Nov 2018 17:30:01 GMT

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).