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?
Hi,
read about: IDXNAME= Data Set Option and IDXWHERE= Data Set Option
Hope it helps.
Bart
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!
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.