SAS DICTIONARY TABLES AND VIEWS

SAS DICTIONARY TABLES AND VIEWS

The untapped & underutilized marvels

Objective : SAS maintains valuable information about the tables, views, catalogs present in current session and this information can be utilized for a host of tasks right from maintaining or updating 100s of tables in a go or searching the metadata for any specific requirement .

The collection is so vast that it can’t be imagined upto what extent this info can be utilized to programmer’s advantage. This concept is relatively simple and very powerful but their potential has been underutilized and ignored by sas community.

Here I present very simple and basic steps to use a dictionary table or a view.

Theory : Dictionary tables are tables (technically they can’t be called tables as the information is generated and maintained at runtime, but for simplicity sake they can be used exactly as a table can be)  that store all the information about the tables, view, catalogs, options, macros etc in scope of current session.

A POINT TO NOTE HERE IS THAT DICTIONARY TABLES ARE ONLY AVAILAIBLE FOR USE WITHIN PROC SQL AND CANNOT BE USED WITHIN A DATASTEP, FOR THIS REASON THERE IS A CORRESPONDING VIEW WITH EXACTLY SAME INFORMATION IN SASHELP LIBRARY WHICH CAN BE USED IN DATASTEPS,

FOR E.G. DICTIONARY.COLUMNS HAS A CORRESPONDING VIEW SASHELP.VCOLUMN.

These tables and views are a lot in number but you will be using only a few in your work. Below I’ll try to present them in easy steps:

   1)      First point to note here is that there is a table which stores info about all the dictionary tables which is   

          called DICTIONARY.DICTIONARIES so you don’t need to remember which tables are present. 

          Below is a listing of all dictionary tables present taken from DICTIONARIES table :

            proc sql;

        select distinct memname from dictionary.dictionaries;

      quit;

                               Member Name

                               --------------------------------

                               CATALOGS

                               CHECK_CONSTRAINTS

                               COLUMNS

                               CONSTRAINT_COLUMN_USAGE

                               CONSTRAINT_TABLE_USAGE

                               DATAITEMS

                               DESTINATIONS

                               DICTIONARIES

                               ENGINES

                               EXTFILES

                               FILTERS

                               FORMATS

                               FUNCTIONS

                               GOPTIONS

                               INDEXES

                               INFOMAPS

                               LIBNAMES

                               MACROS

                               MEMBERS

                               OPTIONS

                               PROMPTS

                               PROMPTSXML

                               REFERENTIAL_CONSTRAINTS

                               REMEMBER

                               STYLES

                               TABLES

                               TABLE_CONSTRAINTS

                               TITLES

                               VIEWS

So you can use all these tables and the information present in it are at your service to be utilized!!

   2)      Lets take up one dictionary table DICTIONARY.COLUMNS and explore its contents.

      proc sql;

        describe table dictionary.columns;

      quit;

Following is printed in log :

   create table DICTIONARY.COLUMNS

  (

   libname char(8) label='Library Name',

   memname char(32) label='Member Name',

   memtype char(8) label='Member Type',

   name char(32) label='Column Name',

   type char(4) label='Column Type',

   length num label='Column Length',

   npos num label='Column Position',

   varnum num label='Column Number in Table',

   label char(256) label='Column Label',

   format char(49) label='Column Format',

   informat char(49) label='Column Informat',

   idxusage char(9) label='Column Index Type',

   sortedby num label='Order in Key Sequence',

   xtype char(12) label='Extended Type',

   notnull char(8) label='Not NULL?',

   precision num label='Precision',

   scale num label='Scale',

   transcode char(8) label='Transcoded?'

);

This table lists all columns in every table present in current SAS session. So you can use this dictionary table for uses like counting all numeric columns in a particular table, dynamically creating a list of all tables present in a particular library etc.

    3)      Some of the most frequently used tables are :  TABLES , CATALOGS, FORMATS, VIEWS etc.

    4)      Here I am mentioning only Dictionary tables and sashelp views. The point being that they are identical things and can be used interchangeably in proc sql code and views can be used ouside proc sql as well. SO we’ll go ahead with dictionary tables and you can assume the same usage for sashelp views. For your reference below is the list of some dictionary tables and their corresponding views and purposes :

Dictionary Table	Sashelp view	Purpose
CATALOGS	VCATALG	Provides information about SAS catalogs.
CHECK_CONSTRAINTS	VCHKCON	Provides check constraints information.
COLUMNS	VCOLUMN	Provides information about column in tables.
CONSTRAINT_COLUMN_USAGE	VCNCOLU	Provides column integrity constraints information.
CONSTRAINT_TABLE_USAGE	VCNTABU	Provides information related to tables with integrity constraints defined.
DICTIONARIES	VDCTNRY	Provides information about all the DICTIONARY tables.
EXTFILES	VEXTFL	Provides information related to external files.
FORMATS	VFORMAT	Provides information related to defined formats and informats.
GOPTIONS	VGOPT	Provides information about currently defined SAS/GRAPH software graphics options.
INDEXES	VINDEX	Provides information related to defined indexes.
LIBNAMES	VLIBNAM	Provides information related to defined SAS data libraries.
MACROS	VMACRO	Provides information related to any defined macros.
MEMBERS	VMEMBER	Provides information related to objects currently defined in SAS data libraries.
OPTIONS	VOPTION	Provides information related to SAS system options.
REFERENTIAL_CONSTRAINTS	VREFCON	Provides information related to tables with referential constraints.
STYLES	VSTYLE	Provides information related to select ODS styles.
TABLES	VTABLE	Provides information related to currently defined tables.
TABLE_CONSTRAINTS	VTABCON	Provides information related to tables containing integrity constraints.
TITLES	VTITLE	Provides information related to currently defined titles and footnotes.
VIEWS	VVIEW	Provides information related to currently defined data views.

Code:

Below I will give you a simple example which will demonstrate the use of these dictionary tables and will help to convince you on its use.

/*  Example to delete all datasets in specific library which start wil letter a */

proc sql;

select distinct memname into : list separated by ' '

from dictionary.columns

where libname="WORK" and lowcase(memname) like "a%";

quit;

%put &list;

proc datasets library=work;

delete &list;

quit;

The example above dynamically selects any number of dataset starting with letter ‘a’ from work library and passes it to proc datasets which deletes those.

A similar example can be searching a library for all datasets which contain a given variable :

/*  Example to search all datasets in specific library for existence of a specific variable */

proc sql;

  select distinct memname into : list separated by ' '

  from dictionary.columns

  where libname="WORK" and lowcase(compress(name)) eq "id";

quit;

%put &list;

Conclusion : Here I have given very small and easy to understand examples, but you can do wonders with metadata using these tables and view. This facility is very powerful and rest is left to your imagination how to make use of it.

Will be back with some SAS Magic again. Till then Goodbye..!!

Saurabh Singh Chauhan

Note : Comments and suggestions are always welcome.  
  

Disclaimer :
SAS® and all other SAS Institute Inc. product or service names are registered trademarks or trademarks of SAS Institute Inc.in the USA and other countries. ® Indicates USA registration.
Other brand and product names are registered trademarks or trademarks of their respective companies. The contents of this post are the works of the author(s)and do not necessarily represent the opinions,recommendations, or practices of any organization whatsoever.