Date:         Fri, 20 May 2011 14:09:16 -0500
Reply-To:     Joe Matise <snoopy369@GMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Joe Matise <snoopy369@GMAIL.COM>
Subject:      Re: nonexistent index, strange result
Comments: To: bbser 2009 <bbser2009@gmail.com>
In-Reply-To:  <000301cc1720$8968f090$9c3ad1b0$@com>
Content-Type: text/plain; charset=ISO-8859-1

According to "The Complete Guide to SAS Indexes", to use a compound index
you must include at least the first _two_ variables in that index in your
where query.  So unless I'm misreading MIKEEEEEE, you can't use daily in
that way.  Try putting a phony date reference in there, like
where flightnumber='182' and date > 0
and see if that helps?  Or date=date, maybe that would work (that should
always be true :)

-Joe

On Fri, May 20, 2011 at 2:02 PM, bbser 2009 <bbser2009@gmail.com> wrote:

> Greetings!
>
> As indicated below, I got error information between Line 883 and 884, which
> says the index daily does not exist.
> But the information preceding Line 888 clearly indicates that it exists.
>
> Any comments about this contradiction? Thanks a lot.
>
> Max
>
>
> 880  proc sql;
> 881    select *
> 882    from marchflights(idxname=daily)
> 883    where flightnumber="182";
> ERROR: The IDXNAME= dataset option specified a nonexistent index.
> 884  quit;
> NOTE: The SAS System stopped processing this step because of errors.
> NOTE: PROCEDURE SQL used (Total process time):
>      real time           0.00 seconds
>      cpu time            0.01 seconds
>
> 885
>
>
> 886  proc sql;
> 887    describe table marchflights;
> NOTE: SQL table WORK.MARCHFLIGHTS was created like:
>
> create table WORK.MARCHFLIGHTS( bufsize=8192 )
>  (
>   Date num format=DATE9. informat=DATE9.,
>   DepartureTime num format=TIME5. informat=TIME5.,
>   FlightNumber char(3),
>   Origin char(3),
>   Destination char(3),
>   Distance num,
>   Mail num,
>   Freight num,
>   Boarded num,
>   Transferred num,
>   NonRevenue num,
>   Deplaned num,
>   PassengerCapacity num
>  );
> create index Date on WORK.MARCHFLIGHTS(Date);
> create unique index daily on WORK.MARCHFLIGHTS(FlightNumber,Date);
>
> 888  quit;
> NOTE: PROCEDURE SQL used (Total process time):
>      real time           0.00 seconds
>      cpu time            0.01 seconds
>