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?
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 <email@example.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
36 quit ;
NOTE: The PROCEDURE SQL used 49.71 seconds.
This solves the function problem because SQL does not even see the
Now why does it take almost 50 seconds to execute? SASHELP is the
14 proc sql;
15 select distinct libname,
16 "%sysfunc(getoption(fmtsearch))" as fmtpath
17 from dictionary.catalogs
18 where libname not in ( "SASHELP" )
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.