Sashelp.vcolumn vs dictionary.columns in PROC SQL

[SAS_inquisitive]

When retrieveing metadata from sas data set I tend to use dictionary.columns and others. Are there any differences between sashelp.vcolumn and dictionary.columns in PROC SQL. I am not aware if sashelp.column has additional functionality than PROC SQL counter part.

[Reeza]

SASHELP table are views of the Dictionary Tables.

There used to be the argument that hitting the Dictionary tables was faster, but I don't think that applies anymore. 

[Kurt_Bremser]

Reeza, sometimes I have the impression that somehow the access to the dictionary tables from SQL is faster when a lot of libraries with a lot of datasets are assigned and SQL profits from optimization of a where clause.

[SASKiwi]

I second @Kurt_Bremser's comments. This is particularly true if you work in a SAS environment where all your libraries are defined in SAS metadata and so are available in every SAS job. Also if your libraries reference external data sources it can be even slower.

I've found that if you want just the columns for a single table then PROC DATASETS is far quicker. 

[art297]

@Reeza: I think it is safe to say that dictionary.columns is still more efficient!

I just ran PROC SQL using the _method option and discovered that when one uses sashelp.vcolumn with a where clause, the SQL optimizer replaces it with dictionary.columns

 62         proc sql _method _tree;
 63           create table test as
 64             select *
 65               from sashelp.vcolumn
 66                 where libname="SASHELP"
 67           ;
 
 NOTE: SQL execution methods chosen are:
 
       sqxcrta
           sqxsob( DICTIONARY.COLUMNS )

Art, CEO, AnalystFinder.com

[Reeza]

@art297 Yes, I was wrong here :). 

There are definitely uses where using PROC CONTENTS/DATASETS is more appropriate and where dictionary tables are more helpful.

I'll still likely reference the SASHELP tables though, mostly because beginners (and myself) can navigate to it and view it as a table in the library which makes it easier to use. 

[Haikuo]

Not I am aware of, except sashelp.vcolumn can be used anywhere (data step, proc sql and other procs), while dictionary.columns is only valid in Proc SQL.

[KevinViel]

Besides the speed issue, one can and should determine the content:

312 proc sql ;
313 describe view sashelp.vcolumn ;
NOTE: SQL view SASHELP.VCOLUMN is defined as:

select *
from DICTIONARY.COLUMNS;

314 quit ;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.14 seconds
cpu time 0.00 seconds

HTH,

Kevin