The following code does not work, giving me the message ERROR: DWH.TEMP2.DATA does not exist. This is the only error message.
OPTIONS REMOTE=unix_name COMAMID=TCP; FILENAME RLINK 'H:\mysas\unix_log_in.txt'; signon; rsubmit; filename mysas "~/mysas" ; libname DW NETEZZA uid=userID pwd=my_password server=netezza_name database=database1 PRESERVE_TAB_NAMES=YES CONNECTION = GLOBAL sqlgeneration=DBMS BULKUNLOAD = YES AUTOCOMMIT = YES ; libname DWH NETEZZA uid=userID pwd=my_password server=netezza_name database=database2 PRESERVE_TAB_NAMES=YES CONNECTION = GLOBAL sqlgeneration=DBMS BULKUNLOAD = YES AUTOCOMMIT = YES ; proc sql; connect to netezza (user = userID pwd = my_password server = netezza_name database = database1 CONNECTION = GLOBAL BULKUNLOAD = YES AUTOCOMMIT = YES ) ; execute ( create temporary table TEMP1 as select distinct Year from TABLE_NAME; ) by netezza; create table temp1 as select * from DW.TEMP1; disconnect from netezza; connect to netezza (user = userID pwd = my_password server = netezza_name database = database2 CONNECTION = GLOBAL BULKUNLOAD = YES AUTOCOMMIT = YES ) ; execute ( create temporary table TEMP2 as select distinct Year) from TABLE_NAME; ) by netezza; create table temp2 as select * from DWH.TEMP2; quit; endrsubmit; signoff;
The following code does work:
OPTIONS REMOTE=unix_name COMAMID=TCP; FILENAME RLINK 'H:\mysas\unix_log_in.txt'; signon; rsubmit; filename mysas "~/mysas" ; libname DW NETEZZA uid=userID pwd=my_password server=netezza_name database=database1 PRESERVE_TAB_NAMES=YES CONNECTION = GLOBAL sqlgeneration=DBMS BULKUNLOAD = YES AUTOCOMMIT = YES ; proc sql; connect to netezza (user = userID pwd = my_password server = netezza_name database = database1 CONNECTION = GLOBAL BULKUNLOAD = YES AUTOCOMMIT = YES ) ; execute ( create temporary table TEMP1 as select distinct Year from TABLE_NAME; ) by netezza; create table temp1 as select * from DW.TEMP1; disconnect from netezza; libname DW clear; libname DWH NETEZZA uid=userID pwd=my_password server=netezza_name database=database2 PRESERVE_TAB_NAMES=YES CONNECTION = GLOBAL sqlgeneration=DBMS BULKUNLOAD = YES AUTOCOMMIT = YES ; connect to netezza (user = userID pwd = my_password server = netezza_name database = database2 CONNECTION = GLOBAL BULKUNLOAD = YES AUTOCOMMIT = YES ) ; execute ( create temporary table TEMP2 as select distinct Year) from TABLE_NAME; ) by netezza; create table temp2 as select * from DWH.TEMP2; quit; endrsubmit; signoff;
The second is just moving around the code from the first, and the only new line is the green libname statement after the disconnect statement. So some kind of name conflict is happening, but I have no idea what it is or why. The libraries DW and DWH should be pointing to different databases, so why is only the first connection working?
It seems as SAS can connect to NETEZA, only one database at a time.
Did your second code, with the
libname DW clear;
work? If yes - it may fit my theory.
It seems as SAS can connect to NETEZA, only one database at a time.
Did your second code, with the
libname DW clear;
work? If yes - it may fit my theory.
Yes, the second block of code works. But does the the libname statement actually establish a connection to the database, or is the connection only established when the connect to statement is invoked?
To complicate matters, in the second block of code, if I remove the "libname DW clear" statement, then it fails to work unless the second libname also uses "DW" instead of "DWH". I'm not certain whether that supports your theory or not.
It is interesting and chalenging.
LIBNAME syntax is documented as:
LIBNAME libref <engine> (library-specification-1 <. . . library-specification-n>)
where libref is a symbolic reference name.
Would you try run first block with libref as: DWH or LIB1 or any other valid name ? Does it work ?
Is it working only for first sas session connection to NETEZZA ?
Is it working if you define first conncetion to database2 and second to database1 ? (swap order of databases)
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.