Wednesday, January 18, 2012

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.