Query and Reporting

Opening and Using the Select Window

The Select window is part of theQuery window. In this window, you can select the tables and columns that you want to use to build queries. You can get more information about the tables and columns in this window, including

table and column attributes (column labels, lengths, and so on)
indexes and key columns
relations between database tables.

This information is gathered from database systems and additional sources (for example, third-party vendor data dictionaries) and stored in SAS files for fast access. This information is used to generate selection lists and to provide additional information about table and column descriptions, indexes, relations, and so on, between database tables. These relations are used to join tables in the Query window. Furthermore, you can browse or edit data interactively.

To open the Select window from the Query window, choose Select. The following display shows the Select window. In this window, you specify the SAS data libraries or DB2 authorization identifiers (called creators), tables, and columns that you use to create your queries.

Select Window

[Select Window]

The data presented in the Select window is based on which Query Manager has been chosen and how that Query Manager is set up, as described in the previous section.

You can list your libraries (in DB2, creators), tables, and columns in this window using one of the following methods:

Type names or use pattern-matching characters in fields at the top of the window.
Manipulate a vertical listing of libraries, tables, and columns with commands.

Typing Names into the Fields

The Select window has three fields where you can type names or pattern-matching characters: Library, Table, and Column.

The pattern-matching character (%) appears in Library by default and causes the library names to be listed below the fields. You can use the following pattern-matching characters as shown in the following display:

_ (underscore): matches any single character
% (percent): matches any sequence of zero or more characters.

You can use pattern-matching characters before or after the characters that you want to match. You can use one or more pattern-matching characters in a field.

Pattern-Matching Examples
Field Entry	Displays
%	all the libraries, tables, or columns, depending on the field where it is typed
AIRLINE	one specific library
AI%	one or more libraries that start with AI
%NE	one or more libraries that end with NE
%IR%	one or more libraries that contain IR
AIR_INE	a seven-letter library name that starts with AIR and ends with INE and can have any value in the fourth position.

Searches for the names of the libraries, tables, and columns are fast because this information is stored in the Select window.

In the following display, AIRLINE is typed in the Library field and a % is typed in the Table field, so that all the tables for the AIRLINE library are shown.

Select Window With Pattern-Matching Character

[Select Window With Pattern-Matching Character]

The following table shows other ways to combine the pattern-matching characters and object names.

Examples of Ways to Use the Fields
Action	Results
Type the name of a library	The library name is listed.
Type the names of a library and table	Both the library and table names are listed.
Type the name of a library and % in Table	The library name and all the tables in that library are listed.
Type the name of a library and % in Table and Column	The library name, all the tables, and all of the columns in those tables are listed.
Type % in all three fields	All the library, table, and column names are listed.
Type % in Library and a table name in Table	The library name(s) for the table are listed, and duplicates of the tables are listed.
Type % in Library and a column name in Column	All library and table names for that column are listed.

Using Commands to Manipulate the Listings

Specifying commands in the Cmd field next to the listed libraries, tables, and columns enables you to

expand or collapse the list of libraries, tables, and columns
display more information about the tables and columns
browse or edit the data
select or deselect tables and columns for your queries.

The following table summarizes the Select window commands that give you more information about your objects. Examples and more information follow this table.

Select Window Commands
Cmd	Action	Description
?	List commands	Lists all possible commands.
+	Expand	Shows the tables of a specific library or the columns of a specific table. When columns are listed, any indexes defined on those columns are indicated in the Index field.
-	Collapse	Returns to the previous level; for example, returns from listing the columns to listing just the table names.
S	Select	For a table or column, selects it for inclusion in a query. Specify S to select individual columns in a table (rather than selecting the entire table and then deselecting columns that you do not want).
D	Deselect	Removes a table or column from the query; it has no effect on the table itself.
I	Info	Provides detailed information about a table or column, including its indexes, relations, and so on.
EL	Edit List	Edits multiple rows in a table by using the SAS FSVIEW procedure in edit mode. EL requires UPDATE privileges and SAS/FSP software.
E	Edit Rows	Edits a table one row at a time by using the SAS FSEDIT procedure. E requires UPDATE privileges and SAS/FSP software.
BL	Browse List	Browses multiple rows of data in a table by using the SAS FSVIEW procedure. BL requires SAS/FSP software.
B	Browse Rows	Browses the data in a table one row at a time by using the SAS FSBROWSE procedure. B requires SAS/FSP software.

Note: You can type as many commands as you want before you press ENTER. For example, you can type ten S commands to select ten columns and then submit all of them by pressing ENTER once. [cautionend]

Expanding a List to Get More Information

To expand the AIRLINE.MARCH table shown in Select Window With Pattern-Matching Character to list the columns, type + in the Cmd field next to MARCH and press ENTER. The following display shows the expanded table. Notice that the fields at the top of the display change, and that columns with composite indexes are indicated.

Select Window With Expanded List

[Select Window With Expanded List]

Type I next to the FLIGHT column to open the Column information window, as shown in the following display. I can also be used with tables or columns listed in the Query window and its subwindows.

Column Information Window

[Column Information Window]

In this example, the Column Information window displays the full column name, its label, and other column attributes.

The following table gives more information about each field in the Column information window:

Column Information Fields

Field

Description

Column

Fully qualified name for the column.

Label

Label for the column.

Type

Data type for the column.

Length

Width of the column.

Format

(SAS only.) Column format.

Nulls

(DB2 only.) N means that the column cannot contain NULL values. Y means that the column can contain NULL values.

Description

Additional information about the column.

Index

Name(s) of the index(es).

Usage

(SAS only.) Simple means an index based on a single column. Composite means an index based on two or more columns.

Cluster

(DB2 only.) Y means that the index is a cluster index. N means that the index is not a cluster index.

Unique

Whether the index is unique.

	Yes (SAS only) means that the index is unique.
	D (DB2 only) means that duplicates are allowed.
	U (DB2 only) means that the index is unique.
	P means that the index is primary key and unique.

Order

(DB2 only.) A means ascending, D means descending.

Columns

Columns that contribute to the index.

If information does not apply to a field, the field is left blank. Some fields do not apply to views. If a > sign appears in a field, select it to see additional information. Select Close from the File menu to return to the Select window.

From the Select window, type I next to a table name to open the Table information window. This opens a window that is similar to Column Information Window. The following table lists information about each field in the Table information window.

Table Information Fields
Field	Description
Table	Name of the table or view.
Label	Description of the contents of the table.
Created	(SAS only.) Date when the table was created.
Modified	(SAS only.) Date when the table was last modified.
Primary Key	(DB2 only.) Column(s) that make up the table's primary key, if the key is defined.
Description	Additional information about the table.
Database	(DB2 only.) Name of the DB2 database where the table resides.
Tablespace	(DB2 only.) Name of the DB2 table space within the database where the table resides.
Columns	(SAS only.) Number of columns in the table.
Pages	(DB2 only.) Number of pages (typically of 4Kb) that the table uses. This value is -1 if no statistics have been gathered or if it is a DB2 view.
Rows	Number of rows in the table. For DB2 only, Rows is -1 if no statistics have been gathered using the DB2 RUNSTATS utility.
Type	For SAS, DATA or VIEW . For DB2, T means table, V means view.
Relation	Names of referential constraint(s) in the table.
References	Column(s) in the current table that reference the columns of the referenced table.
Foreign key(s)	(DB2 only.) Column(s) in the current table that reference the primary key of the referenced table.

If information does not apply to a field, the field is left blank. Some fields do not apply to views. If a > sign appears in a field, select it to see additional information.

Note: The Information windows are data specific and may vary depending on the data being queried. [cautionend]

Depending on the setup at your site, you may be able to access additional information about a column or table by selecting Additional Information from the View menu. Select Goback to return to the Column Information or Table Informationwindow from the Additional Information window. The ability to retrieve additional information is controlled by the Additional Information User Profile option. See Customizing SAS/ASSIST Software for details.

Select Close from the File menu to return to the Select window.

Selecting Tables and Columns for Queries

To select tables and columns for inclusion in your queries, type S next to the table or column name or names in the Select window. You can type as many commands as you want before you press ENTER.

When you select a table name, you automatically select all of the columns in that table. Asterisks (*) appear next to the table name and all of the columns.

If you want only a subset of the columns, type D (for deselect) next to the previously selected columns that you want to omit. The asterisk next to the name is removed. You can also use D to deselect a table. Or you can specify S to select individual columns in a table (rather than selecting the entire table and then deselecting columns that you do not want).

The following display shows the AIRLINE.MARCH table after it has been selected and some of its columns have been deselected.

Columns Selected for the Query

[Columns Selected for the Query]

Using the View Menu to Manipulate Columns

You can use the items on the View menu in the Select window to display all the columns or just selected columns, and to deselect columns. You can also use this menu to display the column names or column labels. You can use these menu items instead of the commands.

Show All: Displays all the columns in the table(s).
Show Selected: Displays only selected columns in the table(s).
Deselect All Columns: deselects the table(s) and all the columns.
Show Column Labels: Displays the columns' labels instead of the names.
Show Column Names: Displays the columns' names instead of the labels.

The Show item has two choices that act as a toggle. If you choose Selected, only the columns you have selected are displayed. The search fields are shown in another color or are "grayed out." Choose All to show all columns and to be able to type names in the Library and other fields at the top of the window.

Browsing or Editing the Selected Table

If you have SAS/FSP software installed at your site, the Select window also enables you to display the data that you have selected. To browse the data, in the Cmd field next to a table name, type B (to browse one row at a time) or BL (to browse multiple rows). The following table shows the AIRLINE.MARCH table that is displayed by using B . All of the table's columns are displayed, not just the columns selected for the query.

Browsing the AIRLINE.MARCH Table

[Browsing the AIRLINE.MARCH Table]

Note: For z/OS only, settings of the Table Browse and Table Edit options in your User Profile can affect your ability to browse or edit DB2 tables in the Select window (see Customizing SAS/ASSIST Software) . [cautionend]

To display a subset of the data, from the Browse window, select Where from the Search menu, specify a WHERE clause, and select OK. Select Where again and OK without a where clause in the Where window to clear the clause. To edit or update the data in a table that you have selected, type E (to edit one row) or EL (to edit multiple rows) beside the table name in the Select window. You must have UPDATE access to the table to open it with these commands. Select Close from the File menu to exit the EDIT or BROWSE window.

After you have selected the tables and columns for queries, you can build your queries, customize them, and run the queries.

Updating a Query Manager

A Query Manager can be static or dynamic. A static Query Manager gives you quick access to information about the structure of your SAS or DB2 tables. However, a static Query Manager requires that you update the Query Manager tables when the structure of the tables changes, for example, when new tables are added to your SAS data libraries or when new columns are added to the tables.

A dynamic Query Manager always reflects the most current structure of the SAS or DB2 tables to which you have access in your SAS session. For example, if you add new tables (SAS data sets) to your SAS data libraries or add new columns to your tables, these changes are automatically reflected by the dynamic Query Manager. However, information about the structure of the tables is displayed more slowly when you use a dynamic Query Manager.

If you use a static Query Manager, you should update your Query Manager periodically to reflect the most current structure of your tables. The time stamp in the title line of the Select window shows when the static Query Manager tables were last updated.

To update the Query Manager, select Update Query Manager from the Tools menu of the Select window. The Create/Update Query Manager Tables window appears.

Create/Update Query Manager Tables Window

[Create/Update Query Manager Tables Window]

If a Query Manager creation program has been defined, you can select Create to execute this program. The program updates your Query Manager to reflect the current structure of your tables. The name of the program that is used to update the Query Manager tables is shown in the Create/Update Query Manager Tables window.

The decision to make a Query Manager static or dynamic is normally made by the designer of the Query Manager, usually a system administrator. Contact your SAS Support Consultant for details.

Top of Page