Chapter Contents
Previous
Next
The SQL Procedure

DICTIONARY tables

Retrieve information about elements associated with the current SAS session.

Restriction: You cannot use SAS data set options with DICTIONARY tables.
Restriction: DICTIONARY tables are read-only objects.
Featured in: Reporting from DICTIONARY Tables

DICTIONARY. table-name

Querying DICTIONARY Tables
The DICTIONARY tables component is specified in the FROM clause of a SELECT statement. DICTIONARY is a reserved libref for use only in PROC SQL. Data from DICTIONARY tables are generated at run time.

You can use a PROC SQL query to retrieve or subset data from a DICTIONARY table. You can save that query as a PROC SQL view for use later. Or, you can use the existing SASHELP views that are created from the DICTIONARY tables.

To see how each DICTIONARY table is defined, submit a DESCRIBE TABLE statement. After you know how a table is defined, you can use its column names in a subsetting WHERE clause to get more specific information. For example: 

proc sql;
   describe table dictionary.indexes;

The results are written to the SAS log: 

1   proc sql;
2      describe table dictionary.indexes;
NOTE: SQL table DICTIONARY.INDEXES was created like:

create table DICTIONARY.INDEXES
  (
   libname char(8) label='Library Name',
   memname char(32) label='Member Name',
   memtype char(8) label='Member Type',
   name char(32) label='Column Name',
   idxusage char(9) label='Column Index Type',
   indxname char(32) label='Index Name',
   indxpos num label='Position of Column in Concatenated Key',
   nomiss char(3) label='Nomiss Option',
   unique char(3) label='Unique Option'
  );

You specify a DICTIONARY table in a PROC SQL query or view to retrieve information about its objects. For example, the following query returns a row for each index in the INDEXES DICTIONARY table: 

proc sql;
   title 'DICTIONARY.INDEXES Table';
   select * from dictionary.indexes;

Subsetting Data from DICTIONARY Tables
DICTIONARY tables are often large. Therefore, if you are looking for specific information, use a WHERE clause to retrieve a subset of the rows in a DICTIONARY table. In the following example, only the rows with the member name ADBDBI are displayed from the DICTIONARY.CATALOGS table: 

proc sql ;
title 'Subset of the DICTIONARY.CATALOGS Table';
title2 'Rows with Member Name ADBDBI ';
   select * from dictionary.catalogs
      where memname ='ADBDBI';

Creating PROC SQL Views from DICTIONARY Tables
To use DICTIONARY tables in other SAS procedures or in the DATA step, use PROC SQL views that are based on the DICTIONARY tables.

You can either create a PROC SQL view on a DICTIONARY table or you can use the SASHELP views, as described in Accessing DICTIONARY Tables with SASHELP Views . You can then use the view in a DATA or PROC step. The following example creates a PROC SQL view on the DICTIONARY.OPTIONS table. DICTIONARY.OPTIONS Table (partial output) displays the view with PROC PRINT: 

options linesize=120 nodate pageno=1;

proc sql;
   create view work.options as
      select * from dictionary.options;


proc print data=work.options(obs=10) noobs;
   title 'Listing of the View WORK.OPTIONS';
   title2 'First 10 Rows Only';
run;

DICTIONARY.OPTIONS Table (partial output)
[HTML Output]  [Listing Output]

Accessing DICTIONARY Tables with SASHELP Views
You can use the permanent PROC SQL views that are available in the SASHELP data library to access DICTIONARY tables. Views in DICTIONARY Tables lists all of the permanent PROC SQL views in the SASHELP library as well as the CREATE VIEW statement that defines each view. You can reference these views and display their results using a PROC SQL query, other SAS procedure, or the DATA step.

Views in DICTIONARY Tables
PROC SQL Views in the SASHELP LIBRARY PROC SQL Statements to Create the Views
SASHELP.VCATALG create view sashelp.vcatalg as select * from dictionary.catalogs;
SASHELP.VCOLUMN create view sashelp.vcolumn as select * from dictionary.columns;
SASHELP.VEXTFL create view sashelp.vextfl as select * from dictionary.extfiles;
SASHELP.VINDEX create view sashelp.vindex as select * from dictionary.indexes;
SASHELP.VMACRO create view sashelp.vmacro as select * from dictionary.macros;
SASHELP.VMEMBER create view sashelp.vmember as select * from dictionary.members;
SASHELP.VOPTION create view sashelp.voption as select * from dictionary.options;
SASHELP.VTABLE create view sashelp.vtable as select * from dictionary.tables;
SASHELP.VTITLE create view sashelp.vtitle as select * from dictionary.titles;
SASHELP.VVIEW create view sashelp.vview as select * from dictionary.views;
SASHELP.VSACCES create view sashelp.vsacces as select libname, memname from dictionary.members where memtype='ACCESS' order by libname, memname;
SASHELP.VSCATLG create view sashelp.vscatlg as select libname, memname from dictionary.members where memtype='CATALOG' order by libname, memname;
SASHELP.VSLIB create view sashelp.vslib as select distinct libname, path from dictionary.members order by libname;
SASHELP.VSTABLE create view sashelp.vstable as select libname, memname from dictionary.members where memtype='DATA' order by libname, memname;
SASHELP.VSTABVW create view sashelp.vstabvw as select libname, memname, memtype from dictionary.members where memtype='VIEW' or memtype='DATA' order by libname, memname;
SASHELP.VSVIEW create view sashelp.vsview as select libname, memname from dictionary.members where memtype='VIEW' order by libname, memname;


Chapter Contents
Previous
Next
Top of Page

Copyright 1999 by SAS Institute Inc., Cary, NC, USA. All rights reserved.