Is there an efficient way to exploit an index (45k unique) using a list of ~1k values without entering them into the WHERE? Is there some method of loading the list into a hash and pulling it into the where instead of into a subsetting if?
I guess another way to state "Is there a way to exploit an index outside of a WHERE"?
I'm not finding anything.
Background is that we need 6 tables (and counting...) attached to a table with 1.9 billion rows on a persistent basis. A full table scan is not an option, and SQL gets messy really quickly. There is an indexed field in common with one of the attached tables that needs to be filtered on.
The best I can think of is what you said, to populate a macro variable and load that into the WHERE.
It would help if you could post your SQL so what you are trying to achieve is clearer to everyone.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.