I had problem using SAS SQL Passthrough facilities for PCFILES with DSN (ODBC) connection to Oracle database. My SAS environment only have "SAS/ACCESS for PC Files" and the "SAS PC File Server". I'm manage to setup the oracle connection through SAS PC File Server --> DSN (ODBC) --> Oracle Database. It only works if the SQL statement is simple or do not contain *. I had tested the same sql statements in oracle database and it ran just a few seconds. Case 1: Simple Count = Works and SAS returned result in 1 seconds. proc sql; connect to PCFILES (SERVER=localhost DSN=dsnname USER=typeuserhere PWD=typepasswordhere); create table temp as select * from connection to PCFILES (select count(*) cnt from my_table where rownum<10); disconnect from PCFILES; quit; Case 2: With * = Not working, run indefinitely proc sql; connect to PCFILES (SERVER=localhost DSN=dsnname USER=typeuserhere PWD=typepasswordhere); create table temp as select * from connection to PCFILES (select * from my_table where rownum<10); disconnect from PCFILES; quit; Case 3: complex oracle sql - few table joins, oracle functions etc. = Not Working, run indefinitely proc sql; connect to PCFILES (SERVER=localhost DSN=dsnname USER=typeuserhere PWD=typepasswordhere); create table temp as select * from connection to PCFILES (with t as ( select '12345' cli from dual union all select '67890' cli from dual union all select '98765' cli from dual) select t.cli, max(pol.pol) keep (dense_rank last order by iss_dt) lst_pol, ca.addr_1, ca.addr_2, ca.addr_3, ca.addr_4, ca.zip_code, ca.country_cd from t, cpl, pol, ca where t.cli=cpl.cli and cpl.pol=pol.pol and in_force(pol.status)='Y' and cpl.cli=ca.cli group by t.cli,ca.addr_1, ca.addr_2, ca.addr_3, ca.addr_4, ca.zip_code, ca.country_cd); disconnect from PCFILES; quit; Font in blue color=Passthrough SQL and suppose to process by Oracle. if i amend the SQL and make some syntax error on purpose, i can get error returned by Oracle. It just doesnt work if everything is correct (i tested the same SQL by connect to Oracle directly). Example of syntax error returned by SQL Passthrough: ERROR: CLI describe error: [Oracle][ODBC][Ora]ORA-00904: "xxxxxx": invalid identifier the SQL seem to correctly processed by Oracle but it just hang somewhere. anyone facing this error or do you have any suggestion to solve this? I'm not sure if this problem occur in SAS/ACCESS for Oracle... if yes, it defeat the purpose for me to request for purchase on SAS/ACCESS for Oracle.
... View more