Understanding SAS Dictionary Tables and Views

Dictionary Tables and Views, obtain information about SAS files 1

Dictionary Tables: Overview Dictionary tables are read-only SAS views that contain session metadata, such as information about SAS libraries, data sets, and external files in use or available in the current SAS session. Created at SAS session initialization Updated automatically by SAS Limited to read-only access. You can query dictionary tables with PROC SQL. 2

Dictionary Tables: Overview The metadata available in dictionary tables includes information about : SAS data sets and other SAS files available in SAS libraries Any allocated external files SAS session metadata: System option names and settings Macro variable names and values Title text Footnote text 3

proc proc sql sql; describe table dictionary.dictionaries; reset outobs=1 1; select * from dictionary.dictionaries; reset outobs=; select * from dictionary.dictionaries; describe table dictionary.libnames; select libname from dictionary.libnames; describe table dictionary.tables; quit quit; select memname from dictionary.tables where libname="NH9";

Metadata about SAS Libraries DICTIONARY.LIBNAMES general information about SAS libraries DICTIONARY.MEMBERS general information about SAS library members DICTIONARY.TABLES detailed information about tables DICTIONARY.VIEWS detailed information about all data views DICTIONARY.CATALOGS information about catalog entries DICTIONARY.COLUMNS detailed information about all columns in all tables continued... 5

Metadata about Indexes and Constraints DICTIONARY.INDEXES indexes defined for tables DICTIONARY.TABLE_CONSTRAINTS integrity constraints in all tables DICTIONARY.CHECK_CONSTRAINTS check constraints in all tables DICTIONARY.REFERENTIAL_CONSTRAINTS referential constraints in all tables DICTIONARY.CONSTRAINT_COLUMN_USAGE columns that are referenced by integrity constraints DICTIONARY.CONSTRAINT_TABLE_USAGE tables that use integrity constraints continued... 6

Metadata about the SAS Session DICTIONARY.MACROS macro variables names and values DICTIONARY.OPTIONS current settings of SAS system options DICTIONARY.TITLES text currently assigned to titles and footnotes DICTIONARY.EXTFILES currently assigned filerefs 7

proc proc sql select * from dictionary.libnames; quit quit; sql;

Exploring Dictionary Information proc proc sql select memname,nobs,nvar,crdate from dictionary.tables where libname='ORION'; quit quit; sql; Library names are stored in uppercase in dictionary tables. 9

Exploring Dictionary Information proc proc sql select Name,Type,Length from dictionary.columns where libname='ORION' and memname='EMPLOYEE_ADDRESSES' ; quit quit; sql; Table names (memnames) are also stored in uppercase in dictionary tables. 10

Using Dictionary Information, Which tables contain the Employee_ID column? proc proc sql select memname, name from dictionary.columns where libname='ORION' and upcase(name)='EMPLOYEE_ID'; quit quit; sql; Because different tables might use different cases for same-named columns, you can use the UPCASE function for comparisons, but this significantly degrades the performance of the query. 11

Dictionary Information in Other SAS Processes To use dictionary table metadata in other procedures or in a DATA step: use the SAS-provided views based on the dictionary tables in the Sashelp library create a PROC SQL view based on a dictionary table Most of the Sashelp library metadata view names are similar to dictionary table names, but are shortened to eight characters or less. They begin with the letter v and do not end in s. For example: dictionary.tables = sashelp.vtable 12

proc proc contents contents data=sashelp.vtable;run proc proc print print data=sashelp.vtable(obs=5 5); where libname="ORION"; run run; run;

proc proc sql select memname, name from dictionary.columns where libname="FRAM" and upcase(name) contains upcase("chol"); quit quit; sql;

proc proc sql select memname, name from dictionary.columns where libname="FRAM" and upcase(label) contains upcase("chol"); quit quit; sql;