Lesson 8, Part 3: Dictionary Tables¶

First, check multiple system libraries¶
In [ ]:
%put SASHELP = %sysfunc(pathname(sashelp));
%put SASUSER = %sysfunc(pathname(sasuser));
%put WORK    = %sysfunc(pathname(work));
In [ ]:
Dictionary tables are Read-Only metadata views that contain session metadata, such as¶

SAS libraries

  • data sets
  • external files in use or available in the current session

Dictionary tables are:

  • created at SAS session
  • updated automatically
  • limited to Read-Only access
In [ ]:
Goal Dictionary Table
SAS datasets (CARS, CLASS, etc.) DICTIONARY.TABLES
Columns in datasets DICTIONARY.COLUMNS
SAS catalogs (formats, source, etc.) DICTIONARY.CATALOGS
Macro variables DICTIONARY.MACROS

For datasets, you must use a Dictionary.Tables (correct for datasets). You can query dictionary tables with PROC SQL.

What DICTIONARY.TABLES contains¶

It contains metadata about all tables and views currently available in your SAS session.

That includes:

  • SAS datasets (e.g., SASHELP.CARS)
  • SAS views
  • Work datasets (WORK.*)
  • Database tables (if connected via ODBC/Oracle/etc.)
To see the actual exhaustive list in your system:¶
In [ ]:
proc sql;
    select *
    from dictionary.tables;
quit;
Most useful practical subset (what people actually use)¶
In [ ]:
proc sql;
    select libname, memname, memtype, nobs, crdate, modate
    from dictionary.tables
    order by libname, memname;
quit;
Sashelp views¶
  • select all views from dictionary.members
  • dictionary.members can be used to select all views in sashelp.
In [9]:
ods html close;
options nodate nonotes nonumber nosource;
proc sql noprint;
    select lowcase(memname)
    into :view_list separated by ' '
    from dictionary.members
    where libname = 'SASHELP'
      and memtype = 'VIEW'
    order by memname;
quit;
%put &=view_list;

                                                           The SAS System

VIEW_LIST=mlview vallopt vcatalg vcformat vchkcon vcncolu vcntabu vcolumn vdatait vdctnry vdest vengine vextfl vfilter vformat 
vfunc vgopt vindex vinfomp vlibnam vlocale vmacro vmember voption vprmxml vprompt vrefcon vrememb vsacces vscatlg vslib vstable 
vstabvw vstyle vsview vtabcon vtable vtitle vview vxattr
                                                           The SAS System

E3969440A681A2408885998500000011

Explanation of the Code Below¶

Sashelp library dictionary view (Source: SAS(R) Institute's Slides)¶
  • SAS provides views based on the dictionary tables in the Sashelp.library
  • View names are similar to dictionary table names (shortened to 8 characters or less
  • They begin with the letter v and do not end in s. For example:
    • dictionary.tables = sashelp.vtables
In [6]:
title 'Tables (dictionary view names) in the DATAFLDR libary';
libname DATAFLDR 'C:\Data';
proc print data=sashelp.vtable label;
 var memname nobs nvar;
 where libname='DATAFLDR';
run;
Out[6]:
SAS Output

Tables (dictionary view names) in the DATAFLDR libary

Obs Member Name Number of Physical Observations Number of Variables
1 ANALYTIC_DATA2020 3194 786
2 FCLASS 9 5
3 H183 15898 3489
4 H192 34655 1941
5 H193 17017 3591
6 H197A 310487 63
7 H197G 170491 49
8 H201 31880 1561
9 H202 15617 3324
10 H204 30461 970
11 H206A 319666 66
12 H209 30461 1501
Querying Dictionary Information¶

Displaying information about the following in the DATAFLDR library

  • SAS data set names
  • number of observations
  • number of variables
  • creation date/time of the SAS data set
In [7]:
title 'Dictionary tablesin the DATAFLDR libary';
libname DATAFLDR 'C:\Data';
proc sql;
select memname,
        nobs format =comma9.
       ,nvar format =comma9.
       ,DATEPART(crdate) format date9. as Date_created label='Creation Date'
       ,TIMEPART(crdate) format timeampm. as Time_created label='Creation Time'
from dictionary.tables
 where libname='DATAFLDR' and memname like "H%";
 quit;
Out[7]:
SAS Output

Dictionary tablesin the DATAFLDR libary

Member Name Number of Physical Observations Number of Variables Creation Date Creation Time
H183 15,898 3,489 09APR2020 6:08:07 PM
H192 34,655 1,941 09APR2020 6:07:53 PM
H193 17,017 3,591 09APR2020 6:08:02 PM
H197A 310,487 63 09APR2020 6:08:11 PM
H197G 170,491 49 02OCT2020 1:57:05 PM
H201 31,880 1,561 09APR2020 6:07:48 PM
H202 15,617 3,324 09APR2020 6:07:58 PM
H204 30,461 970 04SEP2020 7:03:17 PM
H206A 319,666 66 02OCT2020 2:00:57 PM
H209 30,461 1,501 02OCT2020 1:57:33 PM
Querying Dictionary Information¶

Dispalying information about the names of all SAS data sets in the SASHELP librarry.

In [ ]:
*Ex9b_SASHELP_Dict_Tables;
*ods excel file = 'C:\Data\SASHELP_Datasets_10_14_2020.xlsx'
   *options (embedded_titles='on'  sheet_name='List'); 
title "Listing of SASHELP Data Sets";
proc sql number;
  select memname,nobs format=comma10.
         ,nvar format=comma7.
         ,DATEPART(crdate) format date9. as Date_created label='Creation Date'
         ,TIMEPART(crdate) format timeampm. as Time_created label='Creation Time'
       from dictionary.tables
       where libname = 'SASHELP' 
        and memtype = 'DATA';
     quit;

  *ods excel close;
  *ods listing;
Querying Dictionary Information¶

Dispalying information about the columns in SASHELP.HEART

In [2]:
*Ex9_Dictionary_column.sas (Part 1);
OPTIONS nocenter nodate nonumber ;
Title1 'Get column names and column type from a particular SAS Table';
proc sql;
 SELECT NAME, TYPE, LABEL  
  FROM DICTIONARY.COLUMNS  
   WHERE LIBNAME="SASHELP" AND  
        MEMNAME="HEART";  
QUIT;
Out[2]:
SAS Output

Get column names and column type from a particular SAS Table

Column Name Column Type Column Label
Status char  
DeathCause char Cause of Death
AgeCHDdiag num Age CHD Diagnosed
Sex char  
AgeAtStart num Age at Start
Height num  
Weight num  
Diastolic num  
Systolic num  
MRW num Metropolitan Relative Weight
Smoking num  
AgeAtDeath num Age at Death
Cholesterol num  
Chol_Status char Cholesterol Status
BP_Status char Blood Pressure Status
Weight_Status char Weight Status
Smoking_Status char Smoking Status

Comparison between DICTIONARY.TABLES and SASHELP.VTABLES¶

Both DICTIONARY.TABLES and SASHELP.VTABLE give you metadata about SAS tables, but they come from different mechanisms and behave slightly differently.

Feature DICTIONARY.TABLES SASHELP.VTABLE
Type SQL dictionary view SAS view (SASHELP library)
Engine Internal SQL metadata engine Traditional SASHELP views
Scope Most complete, current session metadata Similar but slightly older/limited
Performance Faster, more direct Slightly slower (view-based)
Coverage Includes full metadata for tables, views, DB tables Primarily SAS libraries and datasets
Recommendation Preferred (modern standard) Legacy-compatible

image.png

When querying using PROC SQL,¶
  • You must put LIBNAME and MEMTYPE values in uppercase.
The PROC SQL code¶
  • retrieves dataset names, types, creation dates, and observation counts.
In [ ]:
proc sql;
    select memname, nobs 
        from dictionary.tables
        where libname = 'SASHELP' 
          and memtype='DATA';
quit;
In [ ]:
proc sql;
    select memname, nobs
    from sashelp.vtable
    where libname = 'SASHELP' and memtype = 'DATA';
quit;

SASHELP.VTABLE¶

  • has the information on the number of numeric variables and the number of character variables for every SAS dataset: num_character, num_numeric.
In [ ]:
proc print data=sashelp.vtable;
    var memname nobs;
    where libname = 'SASHELP' and memtype = 'DATA';
quit;

How to access identical variable names from multiple SAS data sets?¶

In [3]:
*Ex9_Dictionary_column.sas;
OPTIONS nocenter nodate nonumber ;
proc sql;
 SELECT MEMNAME 'Table Names', NAME
  FROM DICTIONARY.COLUMNS  
   WHERE LIBNAME="SASHELP" AND  
         NAME = "Weight";  
QUIT;
SAS Output

The SAS System

Table Names Column Name
BWEIGHT Weight
CARS Weight
CLASS Weight
CLASSFIT Weight
FISH Weight
GRIDDED Weight
HEART Weight
In [34]:
*Ex9_Dictionary_column.sas (Part 4);
OPTIONS nocenter nodate nonumber nonotes nosource ;
ods html close;
%let lref=SASHELP;
%let dsn=CLASS;

proc sql noprint;
select num_character, num_numeric into :n_char_x, :n_num_x
from sashelp.vtable
where libname = upcase("&lref.") and upcase(memname) = upcase("&dsn.");
quit;

%put Number of character variables = &n_char_x;
%put Number of numeric variables = &n_num_x;
Out[34]:

The SAS System

Number of character variables =        2
Number of numeric variables =        3
E3969440A681A2408885998500000037
In [36]:
*Ex9_Dictionary_column.sas (Part 6);
 OPTIONS nocenter nodate nonumber nonotes nosource ;
ods html close;

* Data Step Approach;
Data test;
  set sashelp.cars;
  array nums(*) _numeric_;
  array chrs(*) _character_;
     call symputx('nb1',dim(nums),'G');
     call symputx('nb2',dim(chrs),'G');
run;

%put Number of numeric variables = &nb1;
%put Number of numeric variables = &nb2;
Out[36]:

The SAS System

Number of numeric variables = 10
Number of numeric variables = 5
E3969440A681A2408885998500000039

In the sample program below, we want to create a macro variable whose values have the suffix “_Status” from the dataset “HEART” saved in the “SASHELP” library.

A Simple Macro to Select Various Variables Lists

In [38]:
*Ex9_Dictionary_column.sas (Part 7);
OPTIONS nocenter nodate nonumber nonotes nosource ;
ods html close;
proc sql noprint; 
 select name into :varlist_status separated by ' '
 from dictionary.columns
 where libname='SASHELP' and memname='HEART'
 and name like "%#_Status" escape '#';
 quit;
%put &varlist_status;
Out[38]:

The SAS System

Chol_Status BP_Status Weight_Status Smoking_Status
E3969440A681A2408885998500000041
In [46]:
*Ex9_Dictionary_column.sas (Part 7);
OPTIONS nocenter nodate nonumber nonotes nosource ;
ods html close;
proc sql noprint; 
 select name into :varlist separated by ' '
 from dictionary.columns
 where libname='SASHELP' and memname='HEART'
 and name like '%_Status%';
 quit;
%put &varlist;
Out[46]:

The SAS System

Chol_Status BP_Status Weight_Status Smoking_Status
E3969440A681A2408885998500000049
proc sql;
select memname,
        nobs format =comma9.
       ,nvar format =comma9.
       ,DATEPART(crdate) format date9. as Date_created label='Creation Date'
       ,TIMEPART(crdate) format timeampm. as Time_created label='Creation Time'
from dictionary.tables
 where libname='SDS' and memname like "H%";
 quit;

PROC CONTENTS can be used to:

  • display metadata about a dataset.
  • show variable names, types, lengths, labels, formats, and dataset-level attributes.
  • learn about a single dataset at a time.

SAS Programmer Internship

In [ ]:
How to get the SAS data set's metadata using PROC SQL?¶

The SAS System generates the information at runtime about SAS libraries, data sets, and catalogs, indexes, macros, system options, titles, and views in a collection of read-only tables called dictionary tables. When you work in a SAS environment where all your libraries are defined in SAS metadata, and they are available in every SAS job. The following code extracts the information about "column" from a SQL table (i.e., SAS data set). DICTIONARY.COLUMNS (as an alternative to PROC CONTENTS) is especially useful when writing macros or metadata-driven SAS programs.

In [ ]:
proc sql; 
 select varnum, name, type 
	from dictionary.columns 
	where libname = 'SASHELP' and memname = 'HEART'; 
quit;
How to print a particular SAS data set's metadata from the Dictionary View (Method 1)?¶
In [ ]:
proc print data= sashelp.vcolumn;
 var name type format length label;
where libname = 'SASHELP'
   and memname='HEART';
run;
How to print a particular SAS data set's metadata from the Dictionary View (Method 2)?¶
In [ ]:
proc sql;
select name
, type
, label
, length
, informat
, format
from sashelp.vcolumn
where libname = 'SASHELP'
   and memname='HEART';
quit;
How to count the number of SAS datasets and their names from a library?¶
In [ ]:
options nocenter nodate nonumber;
proc sql noprint ;
  select distinct memname
   into :memlist separated by ' '
   from dictionary.columns
       where libname = 'SASHELP' 
        and memtype = 'DATA'
   group by memname
   having sum(length) <=32767;
   %let df_cnt=&sqlobs;
quit;

title "There are &df_cnt data files in the SASHELP library";
proc sql;
select memname, nobs format =comma9. ,
  nvar format=comma9.
from dictionary.tables
where libname='SASHELP' and memtype = 'DATA';
quit;
  • Remember: SASHELP.vcolumn is a data view, not a data set, maintained by SAS.
  • Also, remember: Dictionary.Columns is sashelp.vcolumn.
In [ ]:
proc sql;
         select name, type, length, label
         from dictionary.columns
         where libname="SASHELP" and
             memname = "HEART";
      quit;
In [ ]:
options label;
      proc sql;
       describe table sashelp.heart;
      quit;