Setting Up Query Managers

Defining and Using a Personal SAS Query Manager

Use a personal SAS Query Manager to query the SAS tables in your SAS session. By default, a personal SAS Query Manager is created the first time you enter the Query window. The default personal Query Manager uses the program in SASHELP.QASSIST.SASQM.SOURCE to generate the Query Manager tables for all available SAS data libraries in the current SAS session except SASHELP and MAPS.

Use a dynamic Query Manager instead of a static Query Manager if you want your queries to always reflect the most current structure of the SAS or DB2 tables to which you have access in your SAS session. If, for example, you add new tables (SAS tables) 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.

This section shows you how to edit the default static, personal SAS Query Manager. It also shows you how to create a new dynamic, personal Query Manager by using the default settings. The Query Manager tables for this Query Manager can be updated using Update Query Manager from the Tools menu of the Select window.

Editing the Default Personal SAS Query Manager

To edit the default personal SAS Query Manager, follow this path:

Tasks Data Management Query Query and Reporting Setup...

Click on Administration, then select Open... from the File menu.

The Open window appears as shown in the following display.

Open Window

[Open Window]

The Open window shows the default personal Query Manager that was created for you the first time you entered Query and Reporting. To select SASQM from the list, type S in the Cmd field next to it and press ENTER . The Query Manager Administration window appears, as shown in the following display.

Query Manager Administration Window

[Query Manager Administration Window]

The previous display shows the values for the default personal Query Manager Administration window fields. You can edit the default settings by typing new values where typing is permitted or by choosing a different selection where two alternatives are listed.

Database

specifies the type of database for which you want to set up a Query Manager. In the previous display, the database is SAS. Type the name of the database you want to use or type a ? in the field and press ENTER to select a database from a list of the database systems that you can access.

Program to generate Query Manager tables

specifies the name of the program that generates and updates the Query Manager tables.

If you select Yes, the options SAS entry and External file appear. If the program is stored in a catalog entry, select SAS entry and type the appropriate four-level SAS name. In the previous display, the program in SASHELP.QASSIST.SASQM.SOURCE generates the Query Manager tables. This is the default program. It generates Query Manager tables for all available SAS data libraries in the current SAS session, except SASHELP and MAPS.

If the program is stored in an external file, select External file and type the physical file name for the program. The external file must already exist.

After you supply a program name, you can update the Query Manager tables from Query and Reporting. See Updating a Personal SAS Query Manager for further information.

If you select No, the Query Manager tables cannot be updated using Update Query Manager from the Tools menu of the Select window. The tables must be updated by submitting the program to update the tables from the Program Editor window, or by batch submitting the program. You can also select No when you create a dynamic Query Manager because there is no need to update the Query Manager tables. Dynamic Query Manager tables always reflect the most current structure of the SAS or DB2 tables.

Location of Query Manager tables

You store the Query Manager tables in a SAS data library. Select SAS library name and type the libref or select Physical file name and type the external file name of the SAS library. By default, the personal Query Manager tables are located in the SASUSER library. Only one set of SAS Query Manager tables can reside in the same SAS data library.

Query Manager type

Select Static or Dynamic. A static Query Manager is based on SAS data files and provides faster access to the Query Manager tables. However, you must manually update the static Query Manager tables when the structure of the tables changes, for example when new tables (SAS data files) are added to your SAS data libraries or when new columns are added to the tables. See Updating a Personal SAS Query Manager for more information.

A dynamic Query Manager is based on PROC SQL views to SAS dictionary tables or, for DB2 on z/OS, system tables. The PROC SQL views supply current information to the dynamic Query Manager each time the Query Manager is referenced. See Creating Dynamic SAS Query Manager Views for further information.

Allow Update of relations

Select Yes to allow users to add and delete relations in the Query Manager tables by following this path from the Join Manager window:

View Relations Current Tables | All Tables

Note: Relations can be updated only when SAS library name is selected for Location of Query Manager tables. When Physical file name is selected for Location of Query Manager tables, the library is open for READ access only. [cautionend]

You can save any joins you make in the Join Manager window for future automatic joining of tables. See Chapter 10, "Query and Reporting," in Doing More with SAS/ASSIST for more information.

Select No if you want the user to only browse the Query Manager tables from the Join window.

To save any changes you made to the Query Manager definition, select Save from the File menu. By default, Query and Reporting looks for user-defined Query Managers in the SASUSER.MANAGER catalog. The default name of the personal SAS Query Manager is SASQM.

To return to the WorkPlace menu, select File, Close, and then Goback.

Creating Dynamic SAS Query Manager Views

To create a dynamic Query Manager, you create PROC SQL views instead of SAS data files for all of the Query Manager tables except the _SARELS table. The following steps create the views that describe the data to be queried:

Submit a LIBNAME statement from the Program Editor window that references the library to contain the dynamic views.
Copy the SASHELP.QASSIST.SASDYNA.SOURCE program into the Program Editor window. This program creates the dynamic views and the static _SARELS table.
Change the assignment of the macro variable QMLIB to the libref that references the SAS data library to contain the dynamic Query Manager views.

The following display shows the edited SASDYNA.SOURCE program. The libref DYNAMIC is assigned to the macro variable QMLIB.

Editing Source Statements to Create Dynamic Query Manager Views
Submit the program to generate the Query Manager views. The views for this Query Manager do not need to be updated because they always reflect the most current structure of the SAS or DB2 tables.

Creating a New Dynamic, Personal SAS Query Manager

To create a new personal SAS Query Manager using the default personal Query Manager, click on Administration in the Query and Reporting Setup window. Next, select Open... from the File menu.

In the Open window, change the catalog by typing SASHELP.QASSIST in the Catalog field and pressing ENTER. Type S in the Cmd field for SASQM and press ENTER to select it from the list. The Query Manager Administration window appears.

You can change the default values for the default personal Query Manager Administration window fields by typing new values where typing is permitted or by choosing a different selection where two alternatives are listed. See Defining and Using a Personal SAS Query Manager for descriptions of the fields in this window. For this example, select No in the Program to generate Query Manager tables field. Select Dynamic as the Query Manager type.

To save the Query Manager definition in a catalog for user-defined Query Managers, select Save As... from the File menu. The Save As window appears as shown in the following display.

Save As Window

[Save As Window]

Type a name, description, and catalog of your choice. Make sure you type a new catalog name over SASHELP.QASSIST. By default, Query and Reporting looks for user-defined Query Managers in the SASUSER.MANAGER catalog. Select OK to save the Query Manager definition.

To return to the WorkPlace main menu, select File, Close, and then Goback.

Selecting a Personal SAS Query Manager

To use your personal Query Manager, follow this path:

Tasks Data Management Query Query and Reporting SQL Editor...

Then select Select Query Manager... from the Tools menu.

You may get a message that the list of Query Managers is being updated. A list of Query Managers appears, including any Query Managers that you have created, as shown in the following display.

Query Manager Selection List

[Query Manager Selection List]

Select your Query Manager from the list. The list of Query Managers shows the Query Manager name, description, and type. The type of Query Manager can be M for Master, G for Group, or U for User depending on how the User, Master, and Group profiles are set up. See Setting Up and Customizing Master and Group Profiles for more information on Master, Group, and User profiles.

Updating a Personal SAS Query Manager

When you create a dynamic Query Manager, there is no need to update the dynamic Query Manager tables because they always reflect the most current structure of the SAS or DB2 tables.

When you create a static Query Manager, you must update the Query Manager periodically to show any changes made to the structure of your data tables. To update your personal SAS Query Manager, follow this path: Tasks Data Management Query Query and Reporting Query...

Click on Select. Then select Update Query Manager from the Tools menu.

Note: You can also select Query Manager from the Tools menu in the SQL Editor window. The Select DBMS window appears. Select Update Query Manger from the Tools menu. [cautionend]

The Create/Update Query Manager Tables window appears, as shown in the following display.

Create/Update Query Manager Tables Window

[Create/Update Query Manager Tables Window]

The window indicates which program is used to update the Query Manager tables. in the previous display, the SASHELP.QASSIST.SASQM.SOURCE program is used.

Select Create to update the tables. A message indicates that the tables have been created and that processing has ended successfully.

Select Goback to return to the previous window.

Top of Page