The metadata availble in SAS dictionary tables can be a valuable resource in developing applications. This data can be accessed using PROC SQL and the special dictionary libref or from the SASHELP.V* views. The below comparison reveals that PROC SQL is about 100X faster than the associated view.
For more information about SAS dictionary tables, view the following paper by Frank Dilorio and Jeff Abolafia titled Dictionary Tables and Views: Essential Tools for Serious Applications.
options fullstimer ;
%let viewstart = %sysfunc( datetime() ) ;
data columninfodatastep ;
set sashelp.vcolumn ;
where libname = 'SASHELP'
and memname = 'CLASS' ;
run ;
%put dictionary view extract time: %sysfunc(putn(%sysevalf(%sysfunc(datetime())-&viewstart.),time13.3)) ;
%let sqlstart = %sysfunc( datetime() ) ;
/* same thing using dictionary tables (vs sashelp.v* views) */
proc sql ;
create table columninfosql as
select *
from dictionary.columns
where libname = 'SASHELP'
and memname = 'CLASS' ;
quit ;
%put dictionary table extract time: %sysfunc(putn(%sysevalf(%sysfunc(datetime())-&sqlstart.),time13.3)) ;
You don't have to get everything, just what you want:
ReplyDeletedata columninfodatastep;
set sashelp.vcolumn (where= (libname eq 'SASHELP' and memname eq 'CLASS'));
run;
The difference is much less like this:
before:
/*
dictionary view extract time: 0:00:11.588
dictionary table extract time: 0:00:00.051
*/
after:
/*
dictionary view extract time: 0:00:00.416
dictionary table extract time: 0:00:00.106
*/
Interesting, but the answer is still the same. In your scenario, dictionary tables are still 4X faster. Bottom line, which approach is more efficient?
ReplyDelete