Help using Base SAS procedures

Why doesn't SAS use index on nested query, or is it another problem?

Reply
New Contributor
Posts: 2

Why doesn't SAS use index on nested query, or is it another problem?

I have used nested query to query on a variable that is already indexed, but SAS returns the following message:

INFO: Index BaseCardNumber not used.  Sorting into index order may help.

The following is the code:

proc sql;

    create table CCF_GDN as

    select *

    from fad.CCF_GDN

    where BaseCardNumber not in (select distinct ACCT_NBR

             from FALT2)

            and EffectiveDate between '02oct2012'd and '02jan2013'd

;quit;

So I'm wondering how should I write this in another way to optimise the search by using the index on the variable BaseCardNumber.

Thanks in advance guys.

Super Contributor
Posts: 578

Re: Why doesn't SAS use index on nested query, or is it another problem?

Posted in reply to kooliskool

might try...

create table ccf_gdn as

select f.*

from

     fad.ccf_gdn f

     left outer join falt2 f2

          on f.basecardnumber = f2.acct_nbr

where

     f.effecdtivedate between '02oct2012'd and '02jan2013'd

     and f2.acct_nbr is null;

New Contributor
Posts: 2

Re: Why doesn't SAS use index on nested query, or is it another problem?

Hi,

Thanks on the reply, I guess it is a possible solution, because I tried and the joining does use the index. However I'm interested in querying BaseCardNumber NOT in acct_nbrs, so can't really do that with joins right.....?

And the answer I was really after is how to still do the nested query, but using the index to make it faster, because the error message says order of the observation is not the same as in the index, how can I fix that problem?

Thanks in advance.

Super User
Super User
Posts: 7,039

Re: Why doesn't SAS use index on nested query, or is it another problem?

Posted in reply to kooliskool

How would it use an index for NOT in query?  I can see if you have a list of IDs you can look them up in the index.  But how could you use the index to look up a the ones that aren't in your list?

Super Contributor
Posts: 578

Re: Why doesn't SAS use index on nested query, or is it another problem?

Posted in reply to kooliskool

that's the purpose of doing a left join and then requiring the value to be null.  It actually accomplishes the same things as a "not in"...

select

     *

from t1 left outer join t2 on t1.col1 = t2.col1

where t2.col1 is null

is functionally equivalent to

select * from t1 where col1 not in (select col1 from t2);

I'm a bit surprised that the not exists doesn't use the index on t2.

select * from t1 where not exists (select * from t2 where col1=t1.col1)

I would have thought that it did.

select * from

Ask a Question
Discussion stats
  • 4 replies
  • 159 views
  • 0 likes
  • 3 in conversation