Limit on Proc Sql table joins?

Reply
Super Contributor
Posts: 418

Limit on Proc Sql table joins?

Hello all. Does anyone know if there is a limit on the number of table joins within Proc SQL when run in a portion of larger code?

I have some code that runs within 1 second in sql server,  however when I run it in SAS it takes > 1 minute. In addition, if I run it as part of a large section of CODE (have other datasets created before and after this section) it actually crashes SAS when it gets to this step.  I have never seen this happen before.

ALl I am trying to do is join the following tables, and the joins are correct (from a sql standpoint).

LIBNAME CRMFR odbc dsn='odbc_mydatabasename' schema=dbo bulkload=yes DBMAX_TEXT=32000;

select distinct VARIABLESMAN

FROM CRMFR.FR_TABLE_LQ lq 

                JOIN CRMFR.FR_Loan_L l

                    ON l.keyID = lq.keyID

                JOIN CRMFR.FR_TABLE_M m

                                ON l.Mkey1ID = m.Mkey1ID

                JOIN CRMFR.FR_TABLE_C c

                                ON l.ckeyidID = c.ckeyidID

                                AND m.ckeyidD = c.ckeyidID

                                AND c.clientName = myclientname

    JOIN CRMFR.FR_TABLE_Q q

       ON lq.qkey3ID = q.qkey3ID

      AND m.Mkey1ID = q.Mkey1ID

Super User
Posts: 3,115

Re: Limit on Proc Sql table joins?

There are many reasons why an SQL query submitted from SAS to SQL Server might perform differently to one within SQL Server itself.

As a first step try running your query using SQL Passthru. This will avoid SAS doing some unexpected translations on your code.

Also try setting the READBUFF= option to ensure SAS brings back data from the database in large chunks:

proc sql;

connect to odbc (dsn='odbc_mydatabasename' schema=dbo bulkload=yes DBMAX_TEXT=32000 readbuff = 32767);

  create table test as

  select * from connection to odbc

  ( select *

     From databasetable

     Where selection = ?????

  ) ;

  disconnect from odbc;

quit;

Super Contributor
Posts: 418

Re: Limit on Proc Sql table joins?

Hello Kiwi. I actually did re-write the code to run as a pass through, and it ran in under 1 second. So that is a "solution" to my problem, however my problem isn't actually getting the code to work, I am more interesting the fundamental programming reasons behind this causing SAS to crash. That seems extremely unstable and I would like to know what I did "incorrectly" to avoid it in the future.

Thanks so much!

Ask a Question
Discussion stats
  • 2 replies
  • 171 views
  • 0 likes
  • 2 in conversation