DATA Step, Macro, Functions and more

Sashelp.vcolumn vs dictionary.columns in PROC SQL

Reply
Super Contributor
Posts: 271

Sashelp.vcolumn vs dictionary.columns in PROC SQL

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.

Super User
Posts: 19,782

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

Posted in reply to SAS_inquisitive

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. 

 

Super User
Posts: 7,771

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

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 3,252

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

Posted in reply to KurtBremser

I second @KurtBremser'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. 

PROC Star
Posts: 7,471

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

@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

 

Super User
Posts: 19,782

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

@art297 Yes, I was wrong here Smiley Happy

 

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. 

 

 

Respected Advisor
Posts: 3,156

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

Posted in reply to SAS_inquisitive

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.

 

 

Frequent Contributor
Posts: 83

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

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

Ask a Question
Discussion stats
  • 7 replies
  • 1298 views
  • 9 likes
  • 7 in conversation