The SQL Procedure

Example 6: Reporting from DICTIONARY Tables

Procedure features:

DESCRIBE TABLE statement

DICTIONARY.table-name component

Table:

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';

options nodate pageno=1 source linesize=80 pagesize=60;

proc sql;
   describe table dictionary.members;

   title 'SAS Files in the PROCLIB Library';

   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';

Output: Listing

                        SAS Files in the PROCLIB Library

                                                     Member
                   Member Name                       Type
                   ------------------------------------------
                   ALL                               DATA    
                   BONUS                             DATA    
                   BONUS95                           DATA    
                   DELAY                             DATA    
                   HOUSES                            DATA    
                   INTERNAT                          DATA    
                   MARCH                             DATA    
                   NEWPAY                            DATA    
                   PAYLIST                           DATA    
                   PAYLIST2                          DATA    
                   PAYROLL                           DATA    
                   PAYROLL2                          DATA    
                   SCHEDULE                          DATA    
                   SCHEDULE2                         DATA    
                   STAFF                             DATA    
                   STAFF2                            DATA    
                   SUPERV                            DATA    
                   SUPERV2                           DATA

Top of Page