topic Re: WHERE IN Table Function not working in SAS Programming

WHERE IN Table Function not working

supersasnewbie — Tue, 23 Aug 2022 20:14:12 GMT

Hello,

I'm having some issues with running some code, and hoping someone might have insight into what's going on. It's a pretty simple code that I'm running off a corporate database (I believe it's Teradata-based). However, when I run my code using (SELECT xxx from LIST) in the WHERE function, SAS runs for an extremely long time before timing out. However, when I use the same 85 values from LIST (example: 100, 200, 300) in my WHERE function, the query returns my results pretty quickly. I'm trying to troubleshoot this problem, but not seeing any reason for the query to not work. Here's an example for how this query is put together:

proc sql;

CREATE TABLE MKTLISTING AS

SELECT MKTSTATE, MKTNBR

FROM MKT_TBL

WHERE MKTSTATE in ('NY') /*This results in numeric MKTNBRs 100,200,300*/

;

RUN

proc sql;

CREATE TABLE products AS

SELECT C.PRODCD

,C.PRODTYP

,C.PRODMAN

,S.SALESAREA

,S.MKTNBR

FROM PRODTBL C

, SALESTBL S

WHERE C.PRODCD = S.PROD CD

AND S.MKTNBR in (SELECT MKTNBR FROM MKTLISTING) /*Using this causes the query to timeout, but works fine when (100,200,300) is used in place*/

;

QUIT;

Any clues to why this occurs would be greatly appreciated!

Re: WHERE IN Table Function not working

ballardw — Tue, 23 Aug 2022 20:01:03 GMT

Your second query is sort of missing a FROM, such as what table source is getting the alias C, so I am surprised that it runs at all.

Also, Proc SQL requires a QUIT. When you use Run; SAS is still waiting for a Quit; to finish the procedure.

Second query might run faster as a JOIN but too much stuff missing to make a suggestion.

Re: WHERE IN Table Function not working

supersasnewbie — Tue, 23 Aug 2022 20:14:51 GMT

Thanks for the reply. I made a couple of mistakes when moving the query over to the forum. I've adjusted the post to reflect this.

Re: WHERE IN Table Function not working

PGStats — Tue, 23 Aug 2022 20:29:26 GMT

My guess is that the list is shipped to the database server but the table being local requires the server data to be downloaded to perform the query. Using a macro variable to contain the list might work better:

proc sql;
SELECT MKTNBR into :MKTLISTING separated by ","
FROM MKT_TBL
WHERE MKTSTATE in ('NY') ;
QUIT;

proc sql;
CREATE TABLE products AS
SELECT C.PRODCD
,C.PRODTYP
,C.PRODMAN
,S.SALESAREA
,S.MKTNBR
FROM PRODTBL C
, SALESTBL S
WHERE C.PRODCD = S.PRODCD
AND S.MKTNBR in (&MKTLISTING.) 
;
QUIT;

Re: WHERE IN Table Function not working

supersasnewbie — Tue, 23 Aug 2022 20:44:49 GMT

I think that makes sense, but let me give it a shot. Thanks!

Re: WHERE IN Table Function not working

utrocketeng — Tue, 23 Aug 2022 22:55:54 GMT

if i understand your code correctly, this might be a bit cleaner for the query optimizer.

PROC SQL;
CREATE TABLE products AS
	SELECT 
		C.PRODCD
		,C.PRODTYP
		,C.PRODMAN
		,S.SALESAREA
		,S.MKTNBR
	FROM 
		PRODTBL C
		INNER JOIN 
		(
		SELECT s.ProdCD
		FROM 
			SALESTBL a
			INNER JOIN MKTLISTING b on a.MKTNBR = b.MKTNBR
		) s ON C.PRODCD = S.PROD CD
;
QUIT;