Hi, Basically I am converting SQLserver code to SAS in SAS 9.4 DI studio and I am getting below error. It will be great helpful if any one can suggest better approach to redesign the SAS code in Proc SQL/SAS Datastep. ERROR: NOTE: The execution of this query involves performing one or more Cartesian product joins that can not be optimized. ERROR: Sort execution failure. NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements. 1036 quit; NOTE: The SAS System stopped processing this step because of errors. NOTE: Due to ERROR(s) above, SAS set option OBS=0, enabling syntax check mode. This prevents execution of subsequent data modification statements. NOTE: PROCEDURE SQL used (Total process time): real time 33:46.96 cpu time 1:10:16.57 SQL Server code: create view [dbo].<view_name> as select * from [Schema].[dbo].[Fact_TableA] dd LEFT JOIN [Schema].[dbo].[Fact_TableB] gr on (dd.account_id = gr.account_id) LEFT JOIN [Schema].[dbo].[Fact_TableC] gh on (dd.account_id = gh.account_id) LEFT JOIN [Schema].[dbo].[Fact_TableD] h on (dd.account_id = h.account_id) INNER JOIN [Schema].[dbo].[Fact_TableE] qe on (dd.account_id = qe.account_id) WHERE qe.country='AU' AND qe.product in ('123','909') AND EXISTS(SELECT 1 FROM [Schema].[dbo].[Fact_TableB] fgh WHERE dd.[occurance_end_dt] >= fgh.occurance_end_dt AND dd.account_id = fgh.account_id HAVING MAX(fgh.occurance_st_dt) = gr.occurance_st_dt) AND EXISTS(SELECT 1 FROM [Schema].[dbo].[Fact_TableC] ggh WHERE dd.[occurance_end_dt] >= ggh.occurance_end_dt AND dd.account_id = ggh.account_id HAVING MIN(ggh.occurance_st_dt) = gh.occurance_st_dt) OR NOT EXISTS(SELECT 1 FROM [Schema].[dbo].[Fact_TableC] gggh WHERE gr.account_id = gggh.account_id) SAS Code: Proc sql; create table <Library>.<Table_name> as select * from Fact_TableA dd LEFT JOIN Fact_TableB gr on (dd.account_id = gr.account_id) LEFT JOIN Fact_TableC gh on (dd.account_id = gh.account_id) LEFT JOIN Fact_TableD h on (dd.account_id = h.account_id) INNER JOIN Fact_TableE qe on (dd.account_id = qe.account_id) WHERE qe.country='AU' AND qe.product in ('123','909') AND EXISTS(SELECT 1 FROM Fact_TableB fgh WHERE dd.occurance_end_dt >= fgh.occurance_end_dt AND dd.account_id = fgh.account_id HAVING MAX(fgh.occurance_st_dt) = gr.occurance_st_dt) AND EXISTS(SELECT 1 FROM Fact_TableC ggh WHERE dd.occurance_end_dt >= ggh.occurance_end_dt AND dd.account_id = ggh.account_id HAVING MIN(ggh.occurance_st_dt) = gh.occurance_st_dt) OR NOT EXISTS(SELECT 1 FROM Fact_TableC gggh WHERE gr.account_id = gggh.account_id) ;quit;
... View more