topic Re: Join Sas Data Set with Oracle Table in New SAS User

Join Sas Data Set with Oracle Table

muratatik — Wed, 17 Oct 2018 08:39:04 GMT

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;

Re: Join Sas Data Set with Oracle Table

SASKiwi — Wed, 17 Oct 2018 08:44:29 GMT

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

Re: Join Sas Data Set with Oracle Table

RW9 — Wed, 17 Oct 2018 08:50:30 GMT

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.

Re: Join Sas Data Set with Oracle Table

muratatik — Wed, 17 Oct 2018 11:40:58 GMT

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 🙂

Re: Join Sas Data Set with Oracle Table

LinusH — Wed, 17 Oct 2018 12:16:14 GMT

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.

Re: Join Sas Data Set with Oracle Table

muratatik — Wed, 17 Oct 2018 12:51:32 GMT

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