BookmarkSubscribeRSS Feed
Calcite | Level 5
Does anyone know why during left join in roc SQL T1 (medium size table around 80k rows) and T2 (very large table with 3bln rows) Sas doesn't use index?
But during inner join proc SQL it uses index and performance is great.
I've used MSGLEVEL=I option to see what's happening in log.
Super User

It may help to answer such a question by providing the log with the code and all the notes.


And indicate which data set(s) have index on which variable(s).

Tourmaline | Level 20

You can also add the undocumented PROC SQL options _method and _tree to get more details on how PROC SQL is evaluating the query.

The optimal would a hash join, which requires that your left table fits into memory and uses index on the right table for the join criteria. You can affect the optimizer to chose this by adjusting the BUFFERSIZE option, but you cannnot force it.

Data never sleeps



Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

Register now!

How to Concatenate Values

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.

Get the $99 certification deal.jpg



Back in the Classroom!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 1 like
  • 3 in conversation