BookmarkSubscribeRSS Feed
kooliskool
Calcite | Level 5

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.

4 REPLIES 4
DBailey
Lapis Lazuli | Level 10

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;

kooliskool
Calcite | Level 5

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.

Tom
Super User Tom
Super User

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?

DBailey
Lapis Lazuli | Level 10

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

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

View all other training opportunities.

Discussion stats
  • 4 replies
  • 726 views
  • 0 likes
  • 3 in conversation