SQL Procedure

Example 6: Reporting from DICTIONARY Tables

Features:

DESCRIBE TABLE statement

DICTIONARY.table-name component

Table name:

DICTIONARY.MEMBERS

This example uses DICTIONARY tables to show a list of the SAS files in a SAS library. If you do not know the names of the columns in the DICTIONARY table that you are querying, then use a DESCRIBE TABLE statement with the table.

Program

libname proclib 'SAS-library';

proc sql;
   describe table dictionary.members;

title 'SAS Files in the PROCLIB Library';

select memname, memtype
      from dictionary.members
      where libname='PROCLIB';

Program Description

Declare the PROCLIB library.The PROCLIB library is used in these examples to store created tables.

libname proclib 'SAS-library';

List the column names from the DICTIONARY.MEMBERS table.DESCRIBE TABLE writes the column names from DICTIONARY.MEMBERS to the SAS log.

proc sql;
   describe table dictionary.members;

Specify the title.

title 'SAS Files in the PROCLIB Library';

Display a list of files in the PROCLIB library.The SELECT clause selects the MEMNAME and MEMTYPE columns. The FROM clause specifies DICTIONARY.MEMBERS as the table to select from. The WHERE clause subsets the output to include only those rows that have a libref of PROCLIB in the LIBNAME column.

select memname, memtype
      from dictionary.members
      where libname='PROCLIB';

Log

277  options nodate pageno=1 source linesize=80 pagesize=60;
278
279  proc sql;
280     describe table dictionary.members;
NOTE: SQL table DICTIONARY.MEMBERS was created like:

create table DICTIONARY.MEMBERS
  (
   libname char(8) label='Library Name',
   memname char(32) label='Member Name',
   memtype char(8) label='Member Type',
   engine char(8) label='Engine Name',
   index char(32) label='Indexes',
   path char(1024) label='Path Name'
  );

281     title 'SAS Files in the PROCLIB Library';
282
283     select memname, memtype
284        from dictionary.members
285        where libname='PROCLIB';

HTML Output

SAS Files in the PROCLIB Library