BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
muratatik
Obsidian | Level 7

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;

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

 

View solution in original post

5 REPLIES 5
SASKiwi
PROC Star

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

muratatik
Obsidian | Level 7

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Tourmaline | Level 20
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.
Data never sleeps
muratatik
Obsidian | Level 7

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

 

 

 

 

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 5 replies
  • 2339 views
  • 2 likes
  • 4 in conversation