Date:         Sat, 9 Aug 2003 18:16:03 +0300
Reply-To:     Arto Raiskio <arto.raiskio@SUOMENPOSTI.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Arto Raiskio <arto.raiskio@SUOMENPOSTI.COM>
Subject:      Re: Get MAX index value efficiently

"Chakravarthy, Venky" wrote
> SQL is optimized to evaluate the benefits of using the INDEX before
deciding
> whether to use it or not. See
> http://support.sas.com/techsup/unotes/SN/001/001126.html

at least on my system (laptop running XP and SAS 8.2) the original code by
Dave was faster (ran both separately in by starting and stopping SAS for
each test run)

17   *TEST SCENARIO 2;
18   data _null_ ;
19      call symput('nobs',put(nobs,best.-l)) ;
20      stop ;
21      set test nobs=nobs ;
22   run ;

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


23
24   data _null_ ;
25      set test(firstobs=&nobs.) ;
26      by key ;
27      call symput('max_key',key);
28   run ;

NOTE: Numeric values have been converted to character values at the places
given by:
      (Line):(Column).
      27:26
INFO: Index key selected for BY clause processing.
NOTE: There were 1 observations read from the data set WORK.TEST.
NOTE: DATA statement used:
      real time           3.26 seconds
      cpu time            2.99 seconds




*****************

16
17   *TEST SCENARIO 1;
18   data _null_;
19     /* Uses index, but only works for MIN_key */
20     set test (obs=1);
21     by key; /* No, DESCENDING does not work with index */
22     call symput('min_key',key);
23   run;

NOTE: Numeric values have been converted to character values at the places
given by:
      (Line):(Column).
      22:25
INFO: Index key selected for BY clause processing.
NOTE: There were 1 observations read from the data set WORK.TEST.
NOTE: DATA statement used:
      real time           0.74 seconds
      cpu time            0.02 seconds


24
25   proc sql noprint;
26     /* Works for MAX_key, but does NOT use index */
27     select max(key) into: max_key from test;
28   quit;
NOTE: PROCEDURE SQL used:
      real time           1.73 seconds
      cpu time            1.01 seconds