Talk:Use of Macro to generate data values instead of code

From sasCommunity
Jump to: navigation, search

This code, and variants, is often offered as a solution to getting a list of tables for assigned libraries. It may, thankfully, die out as users migrate to the SAS9 BI platform and make better use of metadata to reveal libraries, tables and their columns. Until then, be aware that there is a serious performance limitation where the library is not a native SAS table format.

proc sql;

create table created as
select memname, crdate
from dictionary.tables
where trim(libname)||'.'||memname in (select data from. . . . . 


The necessity to select by concatenating valid libraries and members means the SAS supervisor needs to interrogate all libraries for their assigned tables. If any libraries are SQLServer, Oracle, DB2 or similar foreign formats, the author has seen serious performance degradation while the remote server(s) respond and produce a list in the SAS required format.

It is far better to allow the SQL query optimiser to speed up the query by only interrogating the library list as one part, and then interrogate the table list as a second part of the query. Indeed, one must wonder why a UI would be designed with a large list, populated after a long delay, when it makes better sense to select one or more libraries, populate a table list from these libraries and then select from the tables as required.

--Deep cover 22:47, 30 December 2007 (EST)