Date:         Tue, 7 May 2002 11:26:21 -0400
Reply-To:     Howard_Schreier@ITA.DOC.GOV
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Howard_Schreier@ITA.DOC.GOV
Subject:      Re: Index usage - Where with upcase

The observed behavior makes sense to me.

In order to use the index in the second situation (invoking the UPCASE
function), SAS would have to invert the expression in the WHERE statement
and use the index to retrieve all values of NAME which meet the test:

   smith
   Smith
   sMith
   ...
   SMITH

That's barely feasible in this particular case and, in general, with
different and more complex expressions, not at all feasible.

On Tue, 7 May 2002 07:20:49 EDT, OlivHiller@AOL.COM wrote:

>Hi,
>Does anyone know why it is not possible to use an index with an
>"Where"-expression, if I use the function upcase? For example:
>----------------------------------
>Options Msglevel=I;       *Show usage of indexes;
>Data Temp (Index=(Name)); *Create dataset and Index;
>Input Name $ Letter $ @@;
>Datalines;
>Smith R Johnes X Porter L
>Smith U Alan U   Smith N
>Run;
>
>*Show part of dataset - Index used;
>Proc Print Data=Temp (Idxwhere=Yes);
> Where Name="Smith";
>Run;
>*Show part of dataset - Index not used;
>Proc Print Data=Temp (Idxwhere=Yes);
> Where Upcase(Name)="SMITH";
>Run;
>------------------------------------
>The "Log"-window shows that the first "Proc Print" uses the index and the
>second "Proc Print" does not.
>Greetings Oliver
>