An Overview of the SQL Query Window

Query Window Menus

The SQL Query Window has File, Tools, View, and Profile items on the menu bar.

Some items in a menu might appear dimmed, which means that they cannot be selected until you have performed some other action.

Note: The items that are described here are specific to the SQL Query Window. Other items that are on the menus are related to general SAS functionality. See the SAS System Help for more information about these items. [cautionend]

File Menu

Save Query

This item displays a menu from which you can select these options:

Save as QUERY to include later: saves your query as a QUERY catalog entry. You can include the saved query during your current SQL Query Window session or during a later session. Other users who have access to the catalog in which the query is stored can also include the query in their sessions.
Save as SOURCE entry: saves your query as a SOURCE catalog entry. A query that is saved as a .SOURCE entry can be used in SAS/AF and SAS/EIS applications, and it can be included in the SAS Program Editor, but it cannot be included in the SQL Query Window.
Save as External file: saves your query as a PROC SQL statement in an external file.

For all of these ways to save a query, the query is stored on the local host even if you are connected to a remote session through SAS/CONNECT software.

List/Include Saved Queries

This item displays a list of the queries that you have previously saved in the Profile catalog with which the SQL Query Window was invoked. You can also display a list of queries that were saved in other catalogs. If the SQL Query Window was invoked without a profile, then the default Profile catalog is SASUSER.PROFILE.

Create Table from Query Results

This item enables you to create a PROC SQL table, which is a SAS data set, and to save the results of your query into it. If SAS/CONNECT software is licensed at your site and you select this item when you are connected to a remote session, then you can choose to download the results of your query into a local SAS data set, or create the table on the remote system.

Create View of Query

This item enables you to create a PROC SQL view that contains the SQL syntax of your query. The PROC SQL view can be read by any SAS procedure as if the view were a SAS data set. When you specify the view in a PROC or DATA step, the query is processed and returns current data from the queried table or tables to your report. If SAS/CONNECT software is licensed at your site and you select this item when you are connected to a remote session, then the view will be created on the remote system.

View Menu

Columns

This item enables you to

select the columns that you want to include in your query
set summary functions for columns
build new computed columns to include in your query.

Where Conditions for Subset

This item enables you to use a WHERE expression to read a subset of the data in a table or tables by specifying the conditions that the selected data must meet.

Distinct

This item removes duplicate rows from your query output.

Order By

This item enables you to select columns or column expressions to specify the order by which you want the output sorted.

Group(s) for Summary Functions

This item enables you to specify groups of column values to which a function is to be applied.

Having Condition for Group

This item enables you to build or modify a HAVING expression. A HAVING expression specifies a condition (or conditions) for each group that is included in the query. You specify the group in a Group By clause. If no Group By clause is specified, then the rows in a table or a subset of the table are evaluated as one group.

Tables

This item enables you to select the table or tables from which you want to retrieve data. This is the first step in the query-building process. If you have already started building your query, then use the Tables item to

select an additional table or tables for your query
remove a table or tables from the current query
select a table or tables for a new query.

Join Type

This item enables you to use inner joins or outer joins to join tables when you have selected two tables for the query.

Tools Menu

Run Query

This item displays a menu from which you can select these options:

Run Immediate

immediately submits the query to the SQL procedure for processing. The output appears in the Output window. If SAS/CONNECT software is licensed at your site and you select this item when you are connected to a remote session, then the query is submitted to the remote session for processing.

Design a Report

uses the REPORT procedure to design a report for your query output. Another menu appears with the following options:

Begin with default report: invokes PROC REPORT with the default settings for the query. You can then design a report within PROC REPORT.
Name a predefined report: lists any report definitions that have been stored in the catalog from which you invoked your SQL Query Window session or in other catalogs.
Use definition from last report: invokes PROC REPORT and uses the report definition that you designed when you selected Design a Report for your current query.

Show Query

This item displays the PROC SQL syntax for your query. You can choose this item at any time during the query-building process.

Preview Window

This item displays your query in a PREVIEW window. You can edit the query syntax in this window and save it to a file. Changes that you make in the PREVIEW window are not reflected in the current query in the SQL Query Window.

Switch Access Mode

This item enables you to specify whether you are going to query SAS data files (including SAS data sets and SAS data views) or tables from a database management system (DBMS). You can change the access mode at any time during an SQL Query Window session. Changing access modes resets the query and displays the tables that are available for that access mode.

Depending upon your operating environment and the SAS/ACCESS products that have been installed at your site, you can select one of the following access modes:

SAS
DB2
ODBC
ORACLE
Sybase
SQLDS
RDB
DB2/2
INGRES
INFORMIX
DB2/6000.

Switch to New Profile

This item resets the query and enables you to change to a profile that was previously created and stored.

Reset

This item deletes your current query from the SQL Query Window and returns you to the Tables window to begin a new query.

Report Options

This item enables you to specify the beginning page number, title, and subtitles for the report.

Profile Menu

Set Preferences

This item enables you to create a profile entry.

Show Current Preferences

This item displays the preference settings that are in effect for your current SQL Query Window session.

Update Preferences

This item enables you to update the preference settings for any SQL Query Window profile.

Pop-Up Menu

If your system supports the use of a mouse, you can also display the most frequently used Tools and View items from the pop-up menu. To invoke the pop-up menu, click the rightmost mouse button anywhere in the SQL Query Window.

Top of Page