OK, to break this into its parts, I want to get this working first up,   connect to oracle as ora (path="(DESCRIPTION=(ADDRESS = (PROTOCOL = TCP) 26 (HOST = xxxxxxxxxxxxxxxxxx.com)(PORT = 1234) 27 (CONNECT_DATA = (SERVICE_NAME=xxxxxx)))" 28 user=xxxxxxx pw=XXXXXXXXXX); 29 30 create table sales as select * from connection to ora 31 ( 32 select distinct 33 ID 34 35 from Table_1 36 37 WHERE rownum < 50 38 ) by ora; So this should create a table that I can query later,   As shown before, there's an error of some syntax missing, can you help me get this part working first? ... View more

Ok, here's where I'm at now;   The error is this - I can't see an error in the syntax or why this would error, any suggestions/ideas? ERROR 22-322: Syntax error, expecting one of the following: ',', GROUP, HAVING, ORDER, WHERE.   proc sql; 25 connect to oracle as ora (path="(DESCRIPTION=(ADDRESS = (PROTOCOL = TCP) 26 (HOST = xxxxxxxxxxxxxxxxxx.com)(PORT = 1234) 27 (CONNECT_DATA = (SERVICE_NAME=xxxxxx)))" 28 user=xxxxxxx pw=XXXXXXXXXX); 29 30 create table sales as select * from connection to ora 31 ( 32 select distinct 33 ID 34 35 from Table_1 36 37 WHERE rownum < 50 38 ) by ora; ___ 22 76 ERROR 22-322: Syntax error, expecting one of the following: ',', GROUP, HAVING, ORDER, WHERE. ERROR 76-322: Syntax error, statement will be ignored. NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements. 39 40 41 create table TABLE_4 as select * from connection to ora 42 ( 43 select DISTINCT 44 ID 45 from Table_1 46 ); 2 The SAS System 09:40 Sunday, March 11, 2018 NOTE: Statement not executed due to NOEXEC option. 47 48 Disconnect from ora ; ... View more

Here's the error msg from a basic select statement using your example;   ERROR: ORACLE execute error: ORA-01031: insufficient privileges. ... View more

Hi SASKiwi, the error I'm getting is 'Invalid Username/Password' but I know this isn't the error. It only errors where I'm trying to set up yjis libname statement. ... View more

I've had a look at some examples & this is what I've come up with, it still isn't right so I wonder if anyone can advise where I'm going wrong?   libname mylib oracle path="(DESCRIPTION=(ADDRESS = (PROTOCOL = TCP) (HOST = xxxxxxxxxxx.com)(PORT = 1234)) (CONNECT_DATA = (SERVICE_NAME=xxxxxxxx)))" ; user=xxxxxxx pw=xxxxxxxx) connection=global; ... View more

Thanks Tom,   I'm getting an error with the initial connection statement, can you adapt your first libname line to mine?   proc sql; connect to oracle as ora (path="(DESCRIPTION=(ADDRESS = (PROTOCOL = TCP) (HOST = xxxxxxxxxxxxxxxxxx.com)(PORT = 1234) (CONNECT_DATA = (SERVICE_NAME=xxxxxxx)))" user=xxxxxxx pw=xxxxxxx); Thanks ... View more

Thanks SASKiwi,   I'm getting an 'Insufficient priveleges' error when running the first part which I think is due to the difference between my connection syntax & yours.   How can I adapt mine to suit the 'execute ... statement?   proc sql; connect to oracle as ora (path="(DESCRIPTION=(ADDRESS = (PROTOCOL = TCP) (HOST = xxxxxxxxxxxxxxxxxx.com)(PORT = 1234) (CONNECT_DATA = (SERVICE_NAME=xxxxxxx)))" user=xxxxxxx pw=xxxxxxx); Thanks ... View more

Thanks SA SKiwi,   How can I access that table in another Pass Through query? ... View more

Another way to describe the issue is to ask "If I create a table with credentials as a "select * from connection to ora"   Can I retrieve the table by a "select from ora.tablename" statement?   Or how would I go about it?   The table data I've created must go somewhere, so how do I access it?   Thanks       ... View more

Thanks Patrick,   The reason I believe that the subquery is not an option for me is purely due to many tests, include it & it takes ages & crashes, exclude it & it takes a short while & works.   I'm also using Oracle TOAD for testing & I've found that I don't have create table access in TOAD but I can create tables in EG. Otherwise If I had create table access in TOAD, I'd just use TOAD for the whole task.   The best idea I've heard so far is to use a temp table that the Pass Through will recognize, I will just need to get the code adapted to SAS EG.   I did have a look at your article on the link, thank you, it seems to find the largest table of a group of tables, but I must be missing something as to how it will it help here.   Appreciate your patience & assistance anyway.   Cheers Peter  ... View more

Hi Patrick,   I've found that I don't have access to create temp tables but I have read that I can create a SAS Macro that will create the 60K recordset I want to use in a Pass Through.   Does a Macro work with your solution? ... View more

I changed the server & it now gives me a log error;   ERROR: ORACLE connection error: ORA-12162: TNS:net service name is incorrectly specified.   I can fix this if you think the rest of the script is correct.   I'm wondering how the script populates the table as I would think it would need a select statement of some table.  ... View more

I've run the code & there is no error or Log info. ... View more

Thanks Patrick,   Would you be able to apply this to my current script?   Thanks ... View more