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!
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;
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.
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.