03-29-2018 09:37 PM
I want to use the results of a query in my EG Project, (Version 7.1) which goes to the WORK library, in an Oracle Pass Through but the Pass through cannot 'see' the WORK Dataset.
How can I get both together in one query?
03-29-2018 10:04 PM
You can't, you'll need to move them to the same location.
Would SQL, not pass through work?
Depending on the type of join, you could also create the rules as macro variables and pass them to the query that way. This useful in a case where you want to select a subset and the subset list is local for example.
03-29-2018 10:16 PM
Ok, I'm creating a table of ID Numbers in the WORK Library.
The Pass Through query connects to an Oracle Data Warehouse with large tables.
I want to use the WORK.IDs as a Subquery in the Pass Through, something like below;
Connect to Oracle .....etc as connection string; Select * from Oracle.Table Where ID in ( Select distinct ID form WORK.ID ) Disconnect from Oracle; ;quit;
Hope this is clearer. Thanks
03-29-2018 11:08 PM
I would have thought that this problem had come up before.
So how do people overcome this?
I'd really like to know what is possible to do this OR is it impossible?
03-29-2018 11:38 PM
It is absolutely doable. You need to upload your SAS WORK table to Oracle as a temporary table. Then you join to it in a pass through query. Something like this should work:
libname Oraload Oracle << put Oracle connection details here >> connection = global dbmstemp = yes insertbuff = 10000; data Oraload.MyTempTable; set WORK.MySASTable; run; proc sql; connect using Oraload; create table MyMatchedTable as select * from connection to Oraload (select A.* FROM MyOracleTable A inner join MyTempTable B on A.ID = B.ID ); quit;
03-31-2018 11:46 PM
I've run this code:
Libname line was successful;
NOTE: Libref ORALOAD was successfully assigned as follows:
LIBNAME oraload ORACLE user=XXXXXXXX pw=XXXXXXX path="(DESCRIPTION=(ADDRESS = (PROTOCOL = TCP) (HOST = XXXXXXXXXXXXXX.com)(PORT = 1234)) (CONNECT_DATA = (SERVICE_NAME=XXXXXXXX)))"; connection = global dbmstemp = yes insertbuff = 10000; data oraload.mytemptable; set WORK.CUST_IDS; RUN;
I got the below error at the connection line;
29 connection = global dbmstemp = yes insertbuff = 10000; __________ 180 ERROR 180-322: Statement is not valid or it is used out of proper order.
Is there something needed in the CONNECTION statement?
04-01-2018 06:31 PM
The LIBNAME statement ends with a semicolon like all SAS statements. You have an extra semicolon where there shouldn't be:
LIBNAME oraload ORACLE user=XXXXXXXX pw=XXXXXXX path="(DESCRIPTION=(ADDRESS = (PROTOCOL = TCP) (HOST = XXXXXXXXXXXXXX.com)(PORT = 1234)) (CONNECT_DATA = (SERVICE_NAME=XXXXXXXX)))" connection = global dbmstemp = yes insertbuff = 10000;
04-01-2018 11:32 PM
Thanks, it ran but I then got the below privileges error.
I'll see about getting my access changed, but in the meantime, is there another way to do this?
NOTE: SAS variable labels, formats, and lengths are not written to DBMS tables. ERROR: Error attempting to CREATE a DBMS table. ERROR: ORACLE execute error: ORA-01031: insufficient privileges..
04-02-2018 05:11 PM
Unfortunately not. If your Oracle account does not allow the creation of temporary tables then the only option is to get the account permissions changed.
03-30-2018 09:44 PM
It's possible, and you'll get better answers if you include more details in your question to start off with.
If you're using SQL pass through use the macro option I mentioned.
So from your work table create a macro variable like this:
proc sql noprint; select quote(name, "'") Into :name_list separated by ", " from sashelp.class where age in (12, 13); quit;
Then you can add that into your query, assuming you know how your SQL query is written already, just add to the WHERE statement.
options mprint symbolgen; proc sql; create table want as select * from sashelp.class where name in (&ame_list); quit;
Obviously this isn't a SQL pass through, but an example that you can run to see how it works.
03-29-2018 11:26 PM
Either move SAS table to Oracle and then do joins or subquery. below is example of join. you can apply this logic to your query
as shown in next two steps
proc sql; create table oratable.tablename as select * from sastable.tablename; quit;
then use the above table in your query
proc sql; connect to oracle (user=&myid orapw=&mypwd path="&mydb"); execute ( use your logic here) by oracle; disconnect from oracle; quit;
if above thing is not possible you have to do something like this.
libname oratable oracle user=user password=password path=path; proc sql; create table oratable.tablename as select * from sastable.tablename left join oratable.tablename on yourcolumns ; quit;