Date:         Mon, 28 May 2007 09:23:29 -0400
Reply-To:     Michael Raithel <michaelraithel@WESTAT.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Michael Raithel <michaelraithel@WESTAT.COM>
Subject:      Re: SAS indexes:ERROR: Could not find value/rid to delete on
              index x for         file y
In-Reply-To:  <200705281008.l4RAmS9j025966@malibu.cc.uga.edu>
Content-Type: text/plain; charset="us-ascii"

Dear SAS-L-ers,

Shanks posted the following interesting situation:

> Got this error from a colleague and could only find this on SAS NOTES
>
> http://support.sas.com/techsup/unotes/V6/C/C016.html
>
> From what I've gathered, it's got 1.6 million records on SAS
> 9.x on windows 2000 server.  This is the 3rd job in a
> sequence of creating FACT tables(just in case this is
> something to do with memory/space issues which cause this
> corruption).  This first 2 go through fine, which are of
> similar file sizes He is modifying the records a lot.
>
> code in question....
>
> proc sql;
> create table lib_fact.fact_nbsm(compress=yes)
> (
>   date_key num,
>   app_id num format = 8.,
>   contract_id char(9) format =$char9.,
> ...
>  constraint fact_perf_cont_pk primary key(app_id,contract_id)
> ); quit; proc datasets library=lib_fact; modify fact_nbsm;
> index create app_id; run; quit;
>
> ERROR: Could not find value/rid to delete on index APP_ID for
> file FACT_NBSM
>
> Any ideas on what he should be looking for, please?
>
Shanks, hmmm... I have never encountered that problem before in my
travels throughout the index territories.  However, I think that your
colleague may be better off than s/he thinks.

In the SQL Procedure, your colleague creates a primary key constraint on
APP_ID.  Behind the scenes, SAS realizes the primary key constraint by
creating an index on APP_ID.  Not only will SAS use that index to
fulfill primary key constraint duties, but it will use that index as...
AN INDEX to fulfill WHERE statement and BY statement processing if/when
necessary.  So, the DATASETS Procedure is not really needed.  I suspect
that SAS is confused by the fact that there is already an index created
for APP_ID.

Have your colleague run a simple test of extracting a single observation
using a WHERE statement that specifies an APP_ID in the
LIB_FACT.FACT_NBSM table.  Make sure that MSGLEVEL=I; is specified
before the DATA step or PROC SQL, so that you can see if the APP_ID
index is used.  Then, please write back to the list and let us know if
everything is hunky-dory.

Shanks, best of luck to you and your colleague as you traverse the index
territories!

I hope that this suggestion proves helpful now, and in the future!

Of course, all of these opinions and insights are my own, and do not
reflect those of my organization or my associates. All SAS code and/or
methodologies specified in this posting are for illustrative purposes
only and no warranty is stated or implied as to their accuracy or
applicability. People deciding to use information in this posting do so
at their own risk.

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Michael A. Raithel
"The man who wrote the book on performance"
E-mail: MichaelRaithel@westat.com

Author: Tuning SAS Applications in the MVS Environment

Author: Tuning SAS Applications in the OS/390 and z/OS Environments,
Second Edition
http://www.sas.com/apps/pubscat/bookdetails.jsp?catid=1&pc=58172

Author: The Complete Guide to SAS Indexes

http://www.sas.com/apps/pubscat/bookdetails.jsp?catid=1&pc=60409

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
You can only be young once. But you can always be immature. - Dave Barry
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++