Date:         Thu, 14 Aug 2008 13:47:58 -0400
Reply-To:     Dave Scocca <dave@SCOCCA.ORG>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Dave Scocca <dave@SCOCCA.ORG>
Subject:      Re: how to check if index exist?
In-Reply-To:  <ce1fb7450808140831o32a06053yd7b93ccafb910a46@mail.gmail.com>
Content-Type: text/plain; charset=us-ascii; format=flowed

Interesting question.

On 8/14/08, Jeff <zhujp98@gmail.com> wrote:

> Error
> 2695 create unique index inx_dz_ind on Mdata.PM_FLAGS_MULTIPLE (elig_id,
> model_time_ind);
>
> ERROR: An index named indx with the same definition already exists for
file
> MDATA.PM_FLAGS_MULTIPLE.DATA.

The error occurs when you create a new index with the same definition as an
existing index.  That is, if you execute the following two commands in
order:

create index indexA on MyLib.MyData (varA varB) ;
create index indexB on MyLib.MyData (varA varB) ;

the second one will fail because indexB is defined identically to indexA.

Here's where it gets complicated.... there are a bunch of SCL functions to
ask questions about indexes.

isIndex() returns blank if there is no index on the column, REG if the
column has a simple index, COMP if the column is part of a composite index,
and BOTH if it is part of both indexes.

iVarList() returns the list of variables involved in the composite index
with the given name


IF you can assume that the only composite index on your variables is the
one that you want to create, then the solution is pretty simple...

INIT:
   declare Num dsid indexExists ;
   dsid = open('MyLib.MyData', 'i') ;
   if isIndex(dsid, 'VarA') in ('COMP', 'BOTH') and
      isIndex(dsid, 'VarB') in ('COMP', 'BOTH') then indexExists=1 ;
   else indexExists=0 ;
   dsid = close(dsid) ;

   if indexExists then do ;
      [... create the index...]
     end ;
  return ;

Similarly, IF you can assume that any existing index will have the same
name as the one you'll be creating--not the case in the original code,
where the existing index was "INDX" but the created index was named
"INX_DZ_IND"--then it is similarly pretty simple...

INIT:
   declare Num dsid indexExists ;
   declare Char indexVarList ;
   dsid = open('MyLib.MyData', 'i') ;
   indexVarList = upcase(iVarList(dsid, 'IndexA')) ;
   dsid = close(dsid) ;

   if indexW(indexVarList, 'VARA') and
      indexW(indexVarList, 'VARB') and
      (scan(indexVarList, 3, ' ')='') then indexExists = 1 ;
   else indexExists = 0 ;

   if indexExists then do ;
      [... create the index...]
     end ;
  return ;


HOWEVER, if there is a possibility that both variables are part of
composite indexes other than the one you want, and that an index matching
the one you want may have an arbitrary name, then I think you have to
resort to the dictionary tables.  While you can determine that a variable
is involved in a composite index, and you can get the list of variables for
a named composite index, there's no way through the built-in index
functions to get the NAME of the composite index (or indexes) from the
variable name.


Dave Scocca