I want to query through a table stored on the Netezza server and generate a SAS table out of it. Here is my query: proc sql;
connect to odbc as odbcCon
(noprompt='Driver={NetezzaSQL}; server=xxxxxx; port=xxxxx; database=xxx; username=xxxx; password=xxxxx;');
select * from connection to odbcCon
(SELECT *
FROM SCHEMA.table1
LIMIT 100;);
quit; It's an sql pass through. How can I generate a SAS table from the result of the query? Thanks in advance!
... View more
Hello, I've read a lot about implicit and explicit pass-through facility in SAS. I'm wondering if implicit pass through only applies for proc sql? Or does it also apply when writing data steps? All the examples I saw so far regarding implicit pass-through are proc sqls. Can you clear this up for me? Thanks
... View more
I have an SQL pass through written in SAS that basically returns a limit of 100 records from a table stored in Netezza. I would like to also return 100 records from another table in Netezza without having to re-connect. Here's what I got: proc sql;
connect to odbc as odbcCon
(noprompt='Driver={NetezzaSQL}; server=xxxxxx; port=xxxxx; database=xxx; username=xxxx; password=xxxxx;');
select * from connection to odbcCon
(SELECT *
FROM SCHEMA.table1
LIMIT 100;);
quit; How can I select from table2 within the same connection? I tried doing this: proc sql;
connect to odbc as odbcCon
(noprompt='Driver={NetezzaSQL}; server=xxxxxx; port=xxxxx; database=xxx; username=xxxx; password=xxxxx;');
select * from connection to odbcCon
(SELECT *
FROM SCHEMA.table1
LIMIT 100;),
(SELECT *
FROM SCHEMA.table2
LIMIT 100;);
quit; But I get a syntax error. Any help would be great!
... View more
@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.
... View more
@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?
... View more
@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.
... View more
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!
... View more
@kiranv_ with smaller datasets the bulkload option is working. My workaround was insead of using bulkload, to use insertbuff and dbcommit.
... View more
@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.
... View more
@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.
... View more
@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.
... View more
@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?
... View more
@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.
... View more
@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
... View more
