BookmarkSubscribeRSS Feed
SAS_inquisitive
Lapis Lazuli | Level 10

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.

7 REPLIES 7
Reeza
Super User

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
Super User

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
PROC Star

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
Opal | Level 21

@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
Super User

@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
Onyx | Level 15

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
Pyrite | Level 9

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 8995 views
  • 15 likes
  • 7 in conversation