Hello, I have been given some code from SQL developer to put into SAS and the majority of it runs fine. I have one step which takes 10 minutes to run and i was hoping to improve its performance. Does anything stand out from the code below which could be improved performance wise? The exists and not exists pieces seem to make the code take longer to run (it runs in seconds without them). These parts reference back to the original base datasets (which are only going to increase in size over time). I am not sure if there is enough information here but any advice would be appreciated! PROC SQL; CREATE TABLE DATASET1 AS SELECT A.CUSTOMER, A.CUSTOMER_REC, A.APPNO, A.TYPE, A.DATE1, A.STATUS, A.STATUS1, A.STATUS2, A.DATE, A.TDAY FROM DATASET AS A WHERE (A.STATUS = 'A' OR (A.STATUS = 'D' AND NOT EXISTS (SELECT 1 FROM BASE.BASE1 B, BASE.BASE2 C WHERE B.CUSTOMER_REC = A.CUSTOMER_REC AND B.UNIQUEKEY = C.UNIQUEKEY AND C.VAR = 2 AND C.VAR2 IN('41','42','43')) AND EXISTS (SELECT 1 FROM BASE.BASE1 B, BASE.BASE2 C WHERE B.CUSTOMER_REC = A.CUSTOMER_REC AND B.UNIQUEKEY = C.UNIQUEKEY AND C.VAR = 2 AND C.VAR2 IN('41','44')) ) ) AND (A.STATUS IN ('A','D') AND A.STATUS1 = 'C' AND A.STATUS2 NOT IN ('A','B')); QUIT;
... View more
