SAS Dictionary Tables vs Views

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)) ;

<