Here are the two SQL Statements: SAS EG generated code as seen in 'Preview' PROC SQL; CREATE TABLE tbl_output AS SELECT DISTINCT t1.keyID AS keyID, t2.EventID AS EventID, t2.Date_Begn, /* YY_QQ */ (DATEPART(t2.Date_Begn)) FORMAT=YYQD6. LABEL="YY_QQ" AS YY_QQ, t3.Field1 AS t3Field1, t5.Field1 AS t5Field1, t4.Field1, t4.Field2, t2.status_value FROM dbo.table1 t1, dbo.table2 t2, dbo.table3 t3, dbo.table4 t4, dbo.table5 t5 WHERE (t1.EventID = t2.case_id AND t2.ntype_id = t3.EventID AND t2.EventID = t4.key2 AND t4.t4key = t5.EventID) AND (t2.Date_Begn >= "&begdt:00:00"dt AND t2.Date_Begn < "&enddt:00:00"dt AND t5.Field1 IN ( 'Value 1', 'Value 2', ) AND t2.status_value = 'Complete' AND t3.Field1 = 'Distinct') ORDER BY t1.keyID, t2.EventID, t2.Date_Begn, t3.Field1, t5.Field1, t4.Field1, t4.Field2, t1.status_value; QUIT; The one below is another SQL code that runs 1/10th the time the above takes PROC SQL; CREATE TABLE tbl_output AS SELECT distinct t1.keyID AS keyID, t2.EventID AS EventID, t2.Date_Begn, (DATEPART(t2.Date_Begn)) FORMAT=YYQD6. LABEL="YY_QQ" AS YY_QQ, t3.Field1 AS t3Field1, t5.Field1 AS t5Field1, t4.Field1, t4.Field2, t2.status_value FROM dbo.table1 t1 join dbo.table2 t2 on t1.EventID = t2.case_id join dbo.table3 t3 on t2.ntype_id = t3.EventID join dbo.table4 t4 on t2.EventID = t4.key2 join dbo.table5 t5 on t4.t4key = t5.EventID WHERE t2.Date_Begn >= "&s_date:00:00"dt AND t2.Date_Begn < "&e_date:00:00"dt AND t5.Field1 IN ("Value 1","Value 2") AND t2.status_value = 'Complete' AND t3.Field1 = 'Distinct' ORDER BY t2.Date_Begn, t3.Field1, t5.Field1, t4.Field1, t4.Field2, t1.status_value; QUIT; As I mentioned in my first post, EG combines the table in a 'where' if the type of join is all 'inner' . How can I make it come up with the second so it runs faster? I can ofcourse stick the second in a program within EG but that defeats the whole purpose of using EG to begin with
... View more