Setting Up Query Managers

Creating SAS Query Manager Tables

One sample program is provided to create SAS Query Manager tables. This program can be run from Query and Reporting or from outside Query and Reporting, either from the Program Editor window or in batch. The program is located in SASHELP.QASSIST.SASQM.SOURCE. By default, the program generates Query Manager tables for all available SAS data libraries except SASHELP and MAPS. The Query Manager tables are stored in a library defined by the Query Manager definition. The program can be edited to exclude more libraries and to store the Query Manager tables at a different location. See Creating Public SAS Query Manager Tables for an example of customizing and using this program.

The Query Manager tables are described in the next sections.

_SACOLS Table

The _SACOLS table contains one row for each column in the available SAS tables.

_SACOLS Table
Column Number	Column Name	Column Label	Column Type	Column Length	Index Type
1	LIBNAME	Library Name	CHAR	8	SIMPLE
2	MEMNAME	Member Name	CHAR	8	SIMPLE
3	MEMTYPE	Member Type	CHAR	8
4	NAME	Column Name	CHAR	8	SIMPLE
5	TYPE	Column Type	CHAR	4
6	LENGTH	Column Length	NUM	8
7	NPOS	Column Position	NUM	8
8	VARNUM	Column Number in Table	NUM	8
9	LABEL	Column Label	CHAR	40
10	FORMAT	Column Format	CHAR	16
11	INFORMAT	Column Informat	CHAR	16
12	IDXUSAGE	Column Index Type	CHAR	9
13	INDEX	Index	CHAR	1
14	F_LIB	First library	CHAR	1	SIMPLE
15	F_MEM	First member	CHAR	1	SIMPLE

The following list describes the columns in the _SACOLS table:

LIBNAME: contains the name of the SAS data library for the SAS table containing the column.
MEMNAME: contains the name of the SAS table containing the column.
MEMTYPE: contains the type of table (DATA/VIEW).
NAME: contains the name of the column.
TYPE: contains the type of column (CHAR/NUM).
LENGTH: contains the length of the column.
NPOS: contains the column starting position in the table.
VARNUM: contains the column number.
LABEL: contains the column label.
FORMAT: contains the format associated with the column.
INFORMAT: contains the informat associated with the column.
IDXUSAGE: indicates whether the column is part of an index (simple/composite/both).
INDEX: indicates whether the column is part of an index or is blank.
F_LIB: contains a quoted flag 1 for the first column for a given library; otherwise, it contains 0.
F_MEM: contains a quoted flag 1 for the first column for a given table; otherwise, it contains 0.

_SAINDX Table

The _SAINDX table contains information about SAS indexes that are defined on columns in tables in the _SACOLS table. The _SAINDX table contains one row for every column that is part of an index. The _SAINDX table is not required by the Query Manager, but users often find this information helpful.

_SAINDX Table
Column Number	Column Name	Column Label	Column Type	Column Length	Index Type
1	LIBNAME	Library Name	CHAR	8	SIMPLE
2	MEMNAME	Member Name	CHAR	8	SIMPLE
3	MEMTYPE	Member Type	CHAR	8
4	NAME	Column Name	CHAR	8
5	IDXUSAGE	Column Index Type	CHAR	9
6	INDXNAME	Index Name	CHAR	8
7	INDXPOS	Position of Column in Concatenated Key	NUM	8
8	NOMISS	Nomiss Option	CHAR	3
9	UNIQUE	Unique Option	CHAR	3
10	IXFIELDS	Column name(s) in index	CHAR	200

The following list describes the columns in the _SAINDX table:

LIBNAME: contains the name of the SAS data library for the SAS table containing the column.
MEMNAME: contains the name of the SAS table containing the column.
MEMTYPE: contains the type of table (DATA/VIEW).
NAME: contains the name of the column.
IDXUSAGE: contains the type of index (SIMPLE/COMPOSITE).
INDXNAME: contains the name of the index.
INDXPOS: contains the column position in the index.
NOMISS: indicates whether no missing values are allowed (yes/blank).
UNIQUE: indicates only unique values (yes/blank).
IXFIELDS: contains the names of all columns that are part of the index. They are separated by commas and listed in column order.

_SARELS Table

The _SARELS table contains relations between SAS tables. This information is used to join tables automatically. The relation information either must be entered manually or saved from the Join Manager window within Query and Reporting. The _SARELS table has one row for every column that is part of a relation.

_SARELS table
Column Number	Column Name	Column Label	Column Type	Column Length
1	RELNAME	Relation name	CHAR	8
2	RELDESC	Relation description	CHAR	30
3	TABLE	Table name	CHAR	27
4	RTABLE	Referenced table	CHAR	27
5	NAME	Foreign key column	CHAR	18
6	RNAME	Primary key column	CHAR	18
7	COLSEQ	Position within foreign key	NUM	8
8	COLCOUNT	Number of columns in foreign key	NUM	8
9	ORIGIN	Keep track of relation-originator	CHAR	3

The following list describes the columns in the _SARELS table:

RELNAME: Contains the name of the relation.
RELDESC: Contains the description of the relation.
TABLE: Contains libname.name of one of the tables in the relation.
RTABLE: Contains libname.name of the other table in the relation.
NAME: Contains the name of the column in the table from the TABLE column in the relation.
RNAME: Contains the name of the column from the table in the RTABLE column in the relation.
COLSEQ: Contains the number of the current column in the relation.
COLCOUNT: Contains the total number of columns in the relation.
ORIGIN: Describes where the relation was created (from the Join Manager window in Query and Reporting or manually).

_SATAB Table

The _SATAB table contains one row for each SAS table that can be queried with the Query Manager.

_SATAB Table
Column Number	Column Name	Column Label	Column Type	Column Length	Index Type
1	LIBNAME	Library Name	CHAR	8	SIMPLE
2	MEMNAME	Member Name	CHAR	8	SIMPLE
3	MEMTYPE	Member Type	CHAR	8
4	MEMLABEL	Dataset Label	CHAR	40
5	TYPEMEM	Dataset Type	CHAR	8
6	CRDATE	Date Created	NUM	8
7	MODATE	Date Modified	NUM	8
8	NOBS	Number of Observations	NUM	8
9	OBSLEN	Observation Length	NUM	8
10	NVAR	Number of Variables	NUM	8
11	PROTECT	Type of Password Protection	CHAR	3
12	COMPRESS	Compression Routine	CHAR	8
13	REUSE	Reuse Space	CHAR	3
14	BUFSIZE	Bufsize	NUM	8
15	DELOBS	Number of Deleted Observations	NUM	8
16	INDXTYPE	Type of Indexes	CHAR	9
17*	SCREEN	FSEDIT screen name	CHAR	35
* Optional.

The following list describes the columns in the _SATAB table:

LIBNAME: contains the name of the SAS data library containing the table.
MEMNAME: contains the name of the SAS table.
MEMTYPE: contains the type of table (DATA/VIEW).
MEMLABEL: contains the label of the table.
TYPEMEM: contains the table type.
CRDATE: contains the date the table was created.
MODATE: contains the date the table was last modified.
NOBS: contains the number of rows in the table.
OBSLEN: contains the length of one row.
NVAR: contains the number of columns in the table.
PROTECT: if password protected, contains the type of protection.
COMPRESS: YES if table is compressed.
REUSE: YES if deleted space should be reused.
BUFSIZE: contains the buffer size.
DELOBS: contains the number of deleted rows.
INDXTYPE: contains the types of indexes being used.
SCREEN: contains the name of a PROC FSEDIT screen for browsing and editing SAS tables (optional column).

Top of Page