topic Re: If a table has multiple indices, can you call to only use one? (Like SQL WITH(INDEX())) in SAS Programming

If a table has multiple indices, can you call to only use one? (Like SQL WITH(INDEX()))

lawatkey — Fri, 22 May 2020 14:34:33 GMT

I am trying to run some programs against a large table with many indices (that I do not have control over). Is there a way to have SAS just choose 1 index, like you can in SQL? And is it possible that this makes a program more efficient, or is this just a waste of time?

Re: If a table has multiple indices, can you call to only use one? (Like SQL WITH(INDEX()))

yabwon — Fri, 22 May 2020 14:56:38 GMT

Hi,

read about: IDXNAME= Data Set Option and IDXWHERE= Data Set Option

https://documentation.sas.com/?docsetId=ledsoptsref&docsetTarget=p0y66t2zyidag8n1uivmrp3473it.htm&docsetVersion=9.4&locale=en

https://documentation.sas.com/?docsetId=ledsoptsref&docsetTarget=p1wqokbcewdl6vn1eaxukhvar9ic.htm&docsetVersion=9.4&locale=en

Hope it helps.

Bart

Re: If a table has multiple indices, can you call to only use one? (Like SQL WITH(INDEX()))

lawatkey — Fri, 22 May 2020 15:27:15 GMT

IDXNAME= didn't work on my table but I did a proc contents and realized the SQL indices were not translating over to the SAS table 🙂
Sort of a side question, but is it possible to create an index on a table you can't make any changes to? But in terms of the original question, your solution was spot on so thank you very much!

Re: If a table has multiple indices, can you call to only use one? (Like SQL WITH(INDEX()))

Reeza — Fri, 22 May 2020 15:37:03 GMT

This depends on how you're connecting and viewing the data. After you extract the data you won't have any indexes. But SAS should use the DB indexes if they exist. If you really want to make it efficient explicit SQL pass through is likely your best bet, as you can use the native language and the indexes as desired. Implicit works fairly well, you can have the log show the full information of the SQL code so that you can see if the indexes are used. There's ways to do all of this in SAS but I can't remember the exact code but maybe @yabwon does 😉

Re: If a table has multiple indices, can you call to only use one? (Like SQL WITH(INDEX()))

yabwon — Fri, 22 May 2020 20:36:08 GMT

As @Reeza suggested you could use pass through, syntax is more or less as follows (but do check documentation for the details):

proc sql;
  /* start connection to some database <engine> e.g. Teradata, Oracle, Postgers */
  connect to <ENGINE> as DB (user="user" password="password" database="database" server="123.123.123.123");


  create table WORK.LOCAL AS /* create local table */
      (
      select * from connection to DB /* with pass through to database with databale native SQL */
          (
              select A.id, A.x, B.y 
              from Exteranal1 A 
              join External2 B 
              on A.id = B.id
          )
      );

  disconnect from BD;
quit;

Bart