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.
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;
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.
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?
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
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 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.