topic Re: Sub Query on External Data with Indexing in SAS Enterprise Guide

Sub Query on External Data with Indexing

SGrisham — Wed, 20 Jun 2018 16:08:40 GMT

I have an external Oracle db that has records of historical customer comments. I am attempting to pull data for a specific set of call records. In the table, each record number can have multiple comment_types, but only one row of each comment_type for each record number. My index on this table is as such, (CALL_NO, COMMENT_TYPE). If I run this query below, and substitute the "(SELECT b.CALL_NO FROM LIB.STAGE2 b WHERE b.Row between 10001 and 20000) with just a hard coded list of 10000 comma delimited call numbers the query runs perfectly. I have to limit this query to run 10,000 calls at a time, it returns results within seconds, however if I go even 1 call number over it locks up. I believe it is a limit on the external database. I have verified that this select statement results in a list of unique CALL_NO's, identical to the one I generate to hard code. With the above SELECT statement in place as seen below, the query will time out and fail.

PROC SQL;

libname LIB_O meta library="ORACLE LIB" METAOUT=DATA;

INSERT INTO LIB.COMMENT_TEMP
SELECT a.CALL_NO, a.COMMENTS, a.CREATE_DATE, a.CREATE_USERID, a.UPDATE_DATE, a.UPDATE_USERID, a.COMMENT_TYPE
FROM LIB_O.COMMENT a
WHERE a.CALL_NO IN (SELECT b.CALL_NO FROM LIB.STAGE2 b
				WHERE b.Row between 10001 and 20000)
And
a.COMMENT_TYPE IN ("PROB", "REM" , "DIR1", "DIR2", "DIR3", "DIR4", "DIR5", "DIR6", "DIR7", "DIR8");

QUIT;

Why does this query act so differently, if the results of the SELECT statement are the same as hard coding the CALL_NO's?? Judging by the way the log reads, it doesn't seem to be observing the external database using the index, but as two separate observations.

NOTE: There were 990500 observations read from the data set LIB_O.COMMENT.
      WHERE COMMENT_TYPE in ('DIR1', 'DIR2', 'DIR3', 'DIR4', 'DIR5', 'DIR6', 'DIR7', 'DIR8', 'PROB', 'REM');
NOTE: There were 10000 observations read from the data set LIB.STAGE2.
      WHERE (Row>=10001 and Row<=20000);

Re: Sub Query on External Data with Indexing

SGrisham — Wed, 20 Jun 2018 19:00:12 GMT

I slightly re-worked it to reference a macro variable. This actually works, but it only references the first line in the variable. How would I get this to use the full list of 10000 calls?

PROC SQL;

SELECT CALL_NO
	INTO :CALL_LIST
FROM LIB.STAGE2
WHERE Row between 1 and 10000;

libname LIB_O meta library="ORACLE LIB" METAOUT=DATA;

INSERT INTO LIB.COMMENT_TEMP
SELECT a.CALL_NO, a.COMMENTS, a.CREATE_DATE, a.CREATE_USERID, a.UPDATE_DATE, a.UPDATE_USERID, a.COMMENT_TYPE
FROM LIB.COMMENT a
WHERE a.CALL_NO IN (&CALL_LIST)
And
a.COMMENT_TYPE IN ("PROB", "REM" , "DIR1", "DIR2", "DIR3", "DIR4", "DIR5", "DIR6", "DIR7", "DIR8");
QUIT;

Re: Sub Query on External Data with Indexing

Patrick — Wed, 20 Jun 2018 21:58:49 GMT

@SGrisham

First of all: Move the LIBNAME statement before PROC SQL. That's where it belongs.

Also: The libref in the statement is not what you're using in the SQL. Is that just a typo?

Looking at your librefs it appears that all tables are in Oracle - correct?

How would I get this to use the full list of 10000 calls?

....INTO :CALL_LIST separated by ','

Why exactly do you have to limit your list to 100000 rows at a time?

Re: Sub Query on External Data with Indexing

SGrisham — Thu, 21 Jun 2018 13:33:35 GMT

That worked Patrick, thank you! My only issue now is the character limit on macro variables. Now I can only run 7000 call increments! Ran perfectly though, so should be able to put this in a macro and loop through it until all the call_no's have been processed. Thank you!