Thursday, January 8, 2015

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


  1. You don't have to get everything, just what you want:

    data columninfodatastep;
    set sashelp.vcolumn (where= (libname eq 'SASHELP' and memname eq 'CLASS'));

    The difference is much less like this:
    dictionary view extract time: 0:00:11.588
    dictionary table extract time: 0:00:00.051

    dictionary view extract time: 0:00:00.416
    dictionary table extract time: 0:00:00.106

  2. Interesting, but the answer is still the same. In your scenario, dictionary tables are still 4X faster. Bottom line, which approach is more efficient?