topic Re: Sashelp.vcolumn vs dictionary.columns in PROC SQL in SAS Programming

Sashelp.vcolumn vs dictionary.columns in PROC SQL

SAS_inquisitive — Fri, 26 Feb 2016 20:19:09 GMT

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.

Re: Sashelp.vcolumn vs dictionary.columns in PROC SQL

Reeza — Fri, 26 Feb 2016 20:33:08 GMT

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.

Re: Sashelp.vcolumn vs dictionary.columns in PROC SQL

Haikuo — Fri, 26 Feb 2016 20:34:11 GMT

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.

Re: Sashelp.vcolumn vs dictionary.columns in PROC SQL

Kurt_Bremser — Fri, 26 Feb 2016 21:31:59 GMT

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.

Re: Sashelp.vcolumn vs dictionary.columns in PROC SQL

SASKiwi — Sat, 27 Feb 2016 00:04:06 GMT

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.

Re: Sashelp.vcolumn vs dictionary.columns in PROC SQL

KevinViel — Sat, 27 Feb 2016 17:48:35 GMT

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

Re: Sashelp.vcolumn vs dictionary.columns in PROC SQL

art297 — Thu, 17 Aug 2017 17:02:07 GMT

@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

Re: Sashelp.vcolumn vs dictionary.columns in PROC SQL

Reeza — Thu, 17 Aug 2017 17:05:08 GMT

@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.