Date:         Thu, 6 Apr 2000 11:39:29 -0400
Reply-To:     WHITLOI1 <WHITLOI1@WESTAT.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         WHITLOI1 <WHITLOI1@WESTAT.COM>
Subject:      Re: getoption function can not be used in proc sql?
Comments: To: Ya Huang <ya.huang@AGOURON.COM>
Content-Type: text/plain; charset=US-ASCII

Subject: getoption function can not be used in proc sql?
Summary: Logic and timing discussed
Respondent: Ian Whitlock <whitloi1@westat.com>

Ya Huang <ya.huang@AGOURON.COM> tried (presumably 6.12)

>   proc sql;
>     select distinct libname, getoption('fmtsearch') as fmtpath
>     from dictionary.catalogs
>   ;

and got an error message.

First the logic of the situation is wrong.  The value of FMTSEARCH
will not change during the execution of the SQl.  Hence it would
be far better to use

31   proc sql;
32     select distinct libname,
33                     "%sysfunc(getoption(fmtsearch))" as fmtpath
34       from dictionary.catalogs
35     ;
36   quit ;
NOTE: The PROCEDURE SQL used 49.71 seconds.

This solves the function problem because SQL does not even see the
function call.

Now why does it take almost 50 seconds to execute?  SASHELP is the
culprit.

14   proc sql;
15     select distinct libname,
16                     "%sysfunc(getoption(fmtsearch))" as fmtpath
17       from dictionary.catalogs
18       where libname not in ( "SASHELP" )
19     ;
20   quit ;
NOTE: The PROCEDURE SQL used 0.66 seconds.

Incidentally the original code does execute in Version 8, where it
took 42 seconds.  Using %SYSFUNC cut it to 34 seconds.  Adding a

    where objtype contains "FORMAT"

brought it down to 30 seconds, and changing it to

    where libname not in ( "SASHELP" )

cut the time to 0.22 seconds!

Why was SASHELP such a problem?  Creating a table and removing the
DISTINCT resulted in 34,417 rows.  It takes a lot of time to open
each member of this library.

Ian Whitlock