topic Re: Converting SQL Server code to SAS DI 9.4 in SAS Programming

Converting SQL Server code to SAS DI 9.4

prasanthor — Thu, 15 Dec 2022 16:05:25 GMT

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;

Re: Converting SQL Server code to SAS DI 9.4

s_lassen — Thu, 15 Dec 2022 15:08:36 GMT

The code you are showing as SAS code is not valid SAS SQL code.

The square brackets "[]" are not applicable for delimiting names in SAS SQL. So I wonder how you got as far as to get the machine to spend half an hour calculating with that code, I would expect it to throw an error immediately, like this:

 69         proc sql;
 70           select * from [sashelp].[class];
                            _
                            22
                            200
 ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, (, CONNECTION, DICTIONARY.  
 
 ERROR 200-322: The symbol is not recognized and will be ignored.

Is the log message from some earlier step, or did you submit something completely different?

Actually, the easiest solution may be to embed the existing query in a SELECT * FROM CONNECTION, something like this:

proc sql;
  connect to SQLsvr (<connect options>);
  create table <Library>.<Table_name> as select * from connection to SQLsvr(
     select *
     from [Schema].[dbo].[Fact_TableA] dd <and all the rest of the SQL server query>
  );

You can look up SQL passthrough for SQL server here

Re: Converting SQL Server code to SAS DI 9.4

s_lassen — Fri, 16 Dec 2022 04:08:15 GMT

A note about my first answer: Originally, the supposed SAS SQL code in the question was actually SQL server code. @prasanthor has now replaced that with actual SAS SQL code.

@prasanthor : it is not a good idea to just edit the code like that, that makes the thread very confusing. Better to answer my post, admit that you made a mistake, and post the question again as it should have been.

The SQL server code you show works on a number of tables that are probably indexed on the variables used in the joins, which is probably why it works. The SAS SQL code works on a number of tables in the WORK library, which are (I assume) not indexed. Where did those tables come from?

Re: Converting SQL Server code to SAS DI 9.4

LinusH — Fri, 16 Dec 2022 07:23:05 GMT

I think the key element here is:

NOTE: The execution of this query involves performing one or more Cartesian product joins that can not be optimized.

Does the code suppose to do that?

Usually cartesian joins is not what you want. But on the other hand, the SQLS and SAS code looks "identical"...?

I see two ways forward here:

- Go back to the requirements to understand what output is to produce, and then building it with an ETL flow from scratch, instead of copy/paste code

- Look into your data, compare your SAS work tables with corresponding ones in SQLS