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

[lawatkey]

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?

[yabwon]

Hi,

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

https://documentation.sas.com/?docsetId=ledsoptsref&docsetTarget=p0y66t2zyidag8n1uivmrp3473it.htm&do...

https://documentation.sas.com/?docsetId=ledsoptsref&docsetTarget=p1wqokbcewdl6vn1eaxukhvar9ic.htm&do...

Hope it helps.

Bart

[lawatkey]

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!

[Reeza]

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 😉

[yabwon]

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