Max

This does seem to be a LE4.1 issue. I'm running it on an XP Pro machine and I just got your answer. It is supposed to be a copy of 9.1.3 but there must be a hot fix or something like that missing on our copies. You might want to send a note to Tech Support since afaik, there is still supposed to be some support for the next seven months.

Nat

-----Original Message----- From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of bbser 2009 Sent: Saturday, May 21, 2011 11:50 PM To: SAS-L@LISTSERV.UGA.EDU Subject: Re: strange problem with using IDXNAME =

Jack

Thanks a lot. But I ran your code on my LE4.1 on virtual Win XP and still got the error (see in-between lines 186 and 187). This problem seemly only haunt me. :( This is really funny.

Any ideas?

Max

173 proc sql; 174 create table student as 175 select * 176 from sashelp.class 177 order by name; NOTE: Table WORK.STUDENT created, with 19 rows and 5 columns.

178 179 create unique index agename 180 on student (age, name); NOTE: Composite index agename has been defined. 181 quit; NOTE: PROCEDURE SQL used (Total process time): real time 0.00 seconds cpu time 0.01 seconds

182 183 proc sql; 184 select* 185 from student (idxname=agename) 186 where age=12; ERROR: The IDXNAME= dataset option specified a nonexistent index. 187 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.00 seconds

======================================================================

From: Jack Hamilton [mailto:jfh@alumni.stanford.org] Sent: May-21-11 11:28 PM To: bbser 2009 Cc: SAS-L@LISTSERV.UGA.EDU Subject: Re: [SAS-L] strange problem with using IDXNAME =

You didn't provide a data set we could use to reproduce the problem, but look at this:

===== 245 proc sql; 246 create table students as 247 select * 248 from sashelp.class 249 order by name; NOTE: Table WORK.STUDENTS created, with 19 rows and 5 columns.

250 251 create unique index agename 252 on students (age, name); NOTE: Composite index agename has been defined. 253 quit; NOTE: PROCEDURE SQL used (Total process time): real time 0.04 seconds cpu time 0.04 seconds

254 255 proc sql; 256 select * 257 from students (idxname=agename) 258 where age=12; INFO: Index agename selected for WHERE clause optimization. 259 quit; NOTE: PROCEDURE SQL used (Total process time): real time 0.01 seconds cpu time 0.01 seconds

260 261 proc sql; 262 select * 263 from students (idxname=agename) 264 where age=12 and name='James'; INFO: Index agename selected for WHERE clause optimization. 265 quit; NOTE: PROCEDURE SQL used (Total process time): real time 0.01 seconds cpu time 0.01 seconds =====

Seems to work as expected.

On May 21, 2011, at 6:41 PM, bbser 2009 wrote:

4 proc sql; 35 create table marchflights as 36 select * 37 from sasuser.marchflights; NOTE: Table WORK.MARCHFLIGHTS created, with 635 rows and 13 columns.

38 39 create unique index daily 40 on marchflights (flightnumber, date); NOTE: Composite index daily has been defined. 41 quit; NOTE: PROCEDURE SQL used (Total process time): real time 0.04 seconds cpu time 0.05 seconds

42 43 proc sql; 44 select * 45 from marchflights (idxname=daily) 46 where flightnumber="182"; ERROR: The IDXNAME= dataset option specified a nonexistent index. 47 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

50 proc sql; 51 select * 52 from marchflights (idxname=daily) 53 where flightnumber="182" and date="01MAR2000"d; ERROR: The IDXNAME= dataset option specified a nonexistent index. 54 quit; NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE SQL used (Total process time): real time 0.04 seconds cpu time 0.01 seconds

---