Date:         Wed, 10 Jul 2002 13:31:59 -0600
Reply-To:     Jack Hamilton <JackHamilton@FIRSTHEALTH.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Jack Hamilton <JackHamilton@FIRSTHEALTH.COM>
Subject:      Re: Getting a view to use an index
Comments: To: gsnell@datasavantconsulting.com
Content-Type: text/plain; charset=us-ascii

Have you tried using an SQL view instead of a data step view?




--
JackHamilton@FirstHealth.com
Manager, Technical Development
METRICS Department, First Health
West Sacramento, California USA


>>> "Gregg Snell" <gsnell@DATASAVANTCONSULTING.COM> 07/09/2002 11:00 PM
>>>
Hi SAS-L,

Is it possible to create a view that is simply a set of several other
datasets
(i.e. jansales, febsales, ...) such that a where clause would use the
indexes
on those datasets?  My testing seems to indicate this doesn't work:

27   options msglevel=i;
28
29   data mylib.testv/view=mylib.testv;
30     set mylib.test1
31         mylib.test2;
32   run;

NOTE: DATA STEP view saved on file MYLIB.TESTV.
NOTE: A stored DATA STEP view cannot run under a different operating
system.

NOTE: DATA statement used:
      real time           0.08 seconds
      cpu time            0.01 seconds


33   data test;
34     set mylib.testv;
35     where month = '01feb2002'd;
36   run;

NOTE: View MYLIB.TESTV.VIEW used:
      real time           1.35 seconds
      cpu time            0.74 seconds

NOTE: There were 20000 observations read from the data set
MYLIB.TEST1.
NOTE: There were 20000 observations read from the data set
MYLIB.TEST2.
NOTE: There were 10000 observations read from the data set
MYLIB.TESTV.
      WHERE month='01FEB2002'D;
NOTE: The data set WORK.TEST has 10000 observations and 120 variables.
NOTE: DATA statement used:
      real time           1.38 seconds
      cpu time            0.22 seconds


37   data test;
38     set mylib.test1;
39     where month = '01feb2002'd;
INFO: Index Month selected for WHERE clause optimization.
40   run;

NOTE: There were 10000 observations read from the data set
MYLIB.TEST1.
      WHERE month='01FEB2002'D;
NOTE: The data set WORK.TEST has 10000 observations and 120 variables.
NOTE: DATA statement used:
      real time           0.45 seconds
      cpu time            0.29 seconds


Thanks in advance for any/all suggestions...

Gregg P. Snell
Data Savant Consulting
http://www.datasavantconsulting.com