02-26-2016 03:19 PM
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.
02-26-2016 03:33 PM
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.
02-26-2016 04:31 PM
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.
02-26-2016 07:04 PM
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.
08-17-2017 01:02 PM
@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
08-17-2017 01:05 PM
@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.
02-26-2016 03:34 PM
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.
02-27-2016 12:48 PM
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:
314 quit ;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.14 seconds
cpu time 0.00 seconds