Date:         Tue, 2 Dec 2003 15:08:09 -0500
Reply-To:     Ian Whitlock <WHITLOI1@WESTAT.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Ian Whitlock <WHITLOI1@WESTAT.COM>
Subject:      Re: Can loading SASHELP views be turned off?
Comments: To: "Patrick.F.O'Neill@KP.ORG" <Patrick.F.O'Neill@KP.ORG>
Content-Type: text/plain

Patrick,

By UPPER I assume you mean UPCASE.  The function calls (according to
previous SAS-L messages) force creating the complete DICTIONARY file before
subsetting.  They are not needed for the LIBNAME and MEMNAME conditions
since these are stored in upper case anyway.  I don't know about the third
condition, but you could use a second SQL statement to subset data created
by the first one.

IanWhitlock@westat.com

-----Original Message-----
From: Patrick F. O'Neill [mailto:Patrick.F.O'Neill@KP.ORG]
Sent: Tuesday, December 02, 2003 2:27 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Can loading SASHELP views be turned off?


Dear SAS-L._all_,

Can we specify on a libname statement that we don't want the metadata found
in that library to be included in the DICTIONARY tables or SASHELP views?

This is, schematically, the situation:

/*Step 1*/
libname ps odbc user=&user. password=&pwd. datasrc=&database. schema=sysadm
                dbindex=yes;

/*Step 2*/
data       work.stuff;
     set   somelib.stuff;
     where something = 'value';
run;

/*Step 3*/
proc SQL NOPRINT;
         select       trim(Name)
         into         :List
           separated by ' '
         from         DICTIONARY.COLUMNS
         where        upper(LibName) = "WORK"
                  and upper(MemName) = "STUFF"
                      and upper(format)  = "DATETIME20."
;quit;


/*Step 1*/ points to a very large remote database. /*Step 2*/ can be
independent of the /*Step 1*/ libname.  When it comes time to run /*Step
3*/, I think that SAS tries to load the metadata from libname ps into
DICTIONARY, and it takes a really long time.

Solution 1 would be to never run /*Step 1*/, and instead always use SQL
pass-through.

Solution 2 would be to never do /*Step 3*/ and rewrite all uses of
DICTIONARY tables to use PROC CONTENTS.

Solution 3, which I am looking for,  would be to alter the libname in /*Step
1*/ to tell it that we don't want its metadata in the DICTIONARY tables.  Is
this possible?

Yours,
Pat