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

 

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);

 

1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

@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?

 

 

 

 

View solution in original post

3 REPLIES 3
SGrisham
Fluorite | Level 6

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;
Patrick
Opal | Level 21

@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?

 

 

 

 

SGrisham
Fluorite | Level 6

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!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 719 views
  • 1 like
  • 2 in conversation