Join Sas Data Set with Oracle Table

[muratatik]

Hi,

I've unique ID table include 90k rows. I want to filter ID's in oracle table. This table is quite huge approximately 100 Mio. I don't know exact observations because i guess proc contents can not show oracle table observation or index column.

By the way, i can't use libname oracle because i dont know password or username so i cant use pass through sql.

I tried below code but its running so slow. What should I do that make it faster?  

DATA _NULL_;
SET UNIQUE_ID_TABLE END=EOF;
IF _N_=1 THEN DO;
CALL EXECUTE("PROC SQL _METHOD;");
CALL EXECUTE("CREATE TABLE WANT AS");
CALL EXECUTE("SELECT * FROM ORACLE_TABLE");
CALL EXECUTE("WHERE ID IN ");
CALL EXECUTE("(");
END;
CALL EXECUTE(QUOTE(STRIP(ID)));
IF EOF THEN DO;
CALL EXECUTE(");");
CALL EXECUTE("QUIT;");
END;
RUN;

[SASKiwi]

So how do you access Oracle if you don't have a user name and a password?

[muratatik]

Sorry for late response,

There is a generic user that created by SAS system admin and this is a  oracle library when i connect SAS Server.

I use SAS EG 7.1  I can use proc sql with basic queries with these oracle tables. I try to figure out password information with  SASTRACE system option but it didn't work.

So simply, when i use these oracle tables, sas know this password but i don't 🙂

[RW9 · Accepted solution]

Its very hard to debug two systems and their interactions with no information.  A tip which might help:

Load the small table from SAS into a temporary area on the database.  Then execute the query fully on the database.

You will have a temporary area if you have an account on the DB presumably, but you will need to contact your system admin to help.  Much the same as pass-through, if you can't do this, then you are rate limited by the speed of transferring data back and forth across networks, which will always be slow.  And a 100million records is quite a lot.

[LinusH]

Do I understunderstand you correctly that you are issuing one query for each id? Unless you have a unique index in Oracle that could/should not be optimal (which you are experiencing).

The simplest is to actually join those tables directly. Then you could use the DBKEY= option, which will behind the scenes issue a dynamic where in().
You can use
options msglevel = I sastraceloc = saslog sastrace = ',,,d' nostsuffix;
to monitor what the libname engine sends to Oracle.

If this path is not feasible, revert to @RW9 suggestion of uploading your id data set.

[muratatik]

I just want to do filter huge table with only getting my ID's. Firstly, i tried macro variable but it's exceed macro variables capacities. So i discovered the call execute method thanks to sas communities.

Add your  system option's code and then log likes below,

ORACLE_4: Prepared: on connection 3

SELECT * FROM ORACLE_TABLE but different first name*.(Names with generic username for oracle connection)

ORACLE_5: Prepared: on connection 3

SELECT  VAR1,VAR2 ... VAR15 FROM  ORACLE_TABLE  WHERE  ( ("ID  IN  ( ID1 , ID2 ,ID3 ) ) ) because i tried only three ID.

ORACLE_6: Executed: on connection 3

SELECT statement  ORACLE_5