Query and Reporting

Building a Query

To build a query on the table that you have selected, you must first return to the Query window; from the Select window, choose Close from the File menu. Any tables that you have selected in the Select window are displayed in theQuery window, as shown in the following display. <UNTITLED> appears in the Query window name because you have not given your query a name yet (you do so later in Saving a Query).

Query Window With AIRLINE.MARCH Table

[Query Window With AIRLINE.MARCH Table]

The Query window assigns aliases to the tables and columns. An alias is a temporary, alternate name for a table. If you select two tables that have columns with the same name, the aliases are used so that you can distinguish them. In the previous display, the AIRLINE.MARCH table is assigned an alias of A.

If you chose one table in the Select window, Subset and Expression are displayed. If you select multiple tables, Join is also displayed in the window.

If your query has many columns or several tables so that the list of columns extends beyond one display of this window, you can see the other columns in one of two ways:

Scroll forward (down) or backward (up) by using the View menu items.
Change the numbers in the ranges above the listed columns and tables. For example, the AIRLINE.MARCH table in the previous display shows this range:
```
1 to 7 of 7      1 to 1 of 1
```
The range on the left refers to the columns in the table. This example shows selected columns for the query on AIRLINE.MARCH. However, if you had many columns in your table, you could specify the first number of the range in the first field. For example, if you have 27 columns in the table, and you change the first number in the left range to 5 and press ENTER, the range changes to 5 to 20 of 27. Columns 5 to 20 are displayed in the window. The total number of columns in the table does not change.

The range on the right refers to the number of tables in the query. Again, you can specify the first number of the range that you want to see. The total number of tables does not change.

To see the SQL that has been created by selecting columns from the AIRLINE.MARCH table, select Show SQL from the View menu. The first page of the SQL code is shown in the following display.

Show SQL Window

[Show SQL Window]

This window also has a range so that you can scroll to a particular line of code. Select Close from the File menu to return to the Query window.

To run this query, within the Query window, select Submit from the Run menu. The following display shows the results.

Query Results

[Query Results]

When you return to the Query window, a message indicates how many rows were retrieved.

Note: You can use the Report Engine window to enhance the report, as described in Doing More with the Report Engine. Select Report Engine from the View menu to go directly to the Report Engine window. [cautionend]

Customizing Your Query

You can customize your query by re-ordering the columns, applying aggregate (summary) functions to the columns, retrieving a subset of the data, setting up a prompt for queries, or adding fields that include arithmetic expressions to the selection list.

Using the Cmd Field

The Cmd field accepts these commands:

I	Displays information about the column, as described in Expanding a List to Get More Information.
D	Permanently deselects (excludes) one or more columns from your query.
R	Repeats the row, adding the duplicate after the row to be repeated.
?	Displays a list of available commands.

Using the No Field

Although the No field (abbreviated from "Number") does not accept commands, you can use it to perform these actions:

Blanking out or "erasing" a number in this field omits the column temporarily from the query. The omitted column moves to the bottom of the column list. You can retype the number to select the column again.
Typing a new number over a current number reorganizes the column order accordingly. For example, if you want A.NONREV to be the first column in Query Window With AIRLINE.MARCH Table, type 1 in the No field next to A.NONREV and a different number for A.FLIGHT (the previous Column 1). When you press ENTER, the columns are reordered.

Using the Order Field

The Order field accepts these commands:

A	Organizes the data within each column in ascending order, that is, in numeric order or alphabetic order from A to Z.
D	Organizes the data within each column in descending (reverse) order, such as the highest miles traveled to the lowest miles traveled.
?	Displays a list of available commands.

You can designate one column as A and another as D within the same query. If you want the sorting order (ascending or descending) to be different from the column order, you can combine a letter (A or D ) with a number in the Order field. This affects only the query's output and not the ordering of any columns in the Query window.

For example, to list the dates and departure times in the AIRLINE.MARCH table in ascending order, you specify A1 next to the A.DATE and A2 next to A.DEPART. This would have the same effect as if you wrote the following SQL query:

select a.depart, a.date
   from airline.march a
   order by a.date asc, a.depart asc;

Using the Usage Field

The Usage field accepts several commands that apply summary (aggregate) functions on the values in the designated column as shown in the following table.

Command	Function
GROUP	Groups data by the values in the column
SUM	Computes the sum of values in the column
MIN	Lists the lowest value (minimum)
MAX	Lists the highest value (maximum)
MEAN	Averages or finds the mean of values in the column; equivalent to AVG
AVG	Averages or finds the mean of values in the column
COUNT	Counts the number of distinct, nonmissing values
N	Counts the number of distinct, nonmissing values
CSS	Reports the corrected sum of squares
CV	Returns the coefficient of variation (percent)
NMISS	Reports the number of missing values
PRT	Reports the probability of a greater absolute value of Student's t
RANGE	Reports a range of values
STD	Shows the standard deviation
STDERR	Returns the standard error of the mean
SUMWGT	Returns the weighted sum
T	Returns the Student's t
USS	Returns the uncorrected sum of squares
VAR	Returns the variance

When you use summary functions in queries, columns with no usage are treated as if they have GROUP usage. With SAS data, use the Grouping in summary User Profile option to specify which action to take when using summary functions in queries. If you set this option to Pop-up, you are prompted whether or not to apply the GROUP usage to all columns. If you set this option to Yes, the GROUP usage is automatically applied to all columns. If you set this option to No, the GROUP usage is not applied to any columns. See Doing More with Results and Customizing SAS/ASSIST Software for additional information. Note that in DB2 queries, the GROUP usage is always applied to all columns.

Retrieving a Subset of Data

To retrieve some but not all rows of data in a query, apply subsetting criteria to the data. In this example, you apply a subsetting criterion that retrieves information about flights that were filled to at least three-quarters capacity.

Select Subset from the Query window to open the Subset Rows window. In this window, you specify your subsetting criteria (also called WHERE predicates). Do not include the word WHERE.
On the first line, type the following:
```
(boarded+transfer+nonrev)>=(0.75*capacity)
```
The following display shows the edited Subset Rows window.

Subset Rows Window

Note that if only one table is selected for the query, the alias is not required for identifying columns in the Subset Rows window.
Follow this selection path to save your subset criteria and return to the Query window:File CloseThe word Specified appears under Subset, so you know that selection criteria have been applied to this query.

If you do not remember the name of a column, you can choose it from a selection list. Position your cursor in your subsetting criteria where you want the column name. Select Add Column Name from the Edit menu. A list of columns in the current tables appears. First select a table from the list, and then select a column name. The column name is added to your subsetting criteria.

Using the Prompt Facility

You can use prompts to run the same query several times on different subsets of data. When you run a query with a prompt, a window opens where you type a subset value. The rest of the query is then run with this value. Prompts give you more flexibility in customizing your program.

The following steps show you how to create a prompt window. In this example, you create a prompt that asks for a time value. The prompt is used to return only rows with a departure time of or earlier than the user-supplied time value.

Re-open the Subset Rows window.
On the second line, type the following:
```
and depart<=&promptn
```
- and is a logical operator that specifies that both of the criteria must be met for the row to be included in the output. Other logical operators are or and not .
- &promptn is the name of the prompt. The prompt name must be seven characters or fewer and must be preceded by an ampersand (& ).
The following display shows the edited Subset Rows window.
Adding a Prompt to the Subset Rows Window
Select Prompt from the Run menu. The Query Prompt Design window appears.
Fill in the fields as shown in the following display.

Query Prompt Design Window

The Default value field is optional; if used, this value appears in the prompt window for the user to either accept or overwrite.
Select OK to return to the Subset Rows window.
Select Close from the File menu to return to the Query window.

You can create an unlimited number of prompts for a query. You can use a prompt to substitute any part of the subsetting criteria, such as a logical operator (AND, OR, NOT), and you can use SAS macro functions.

Note: The prompt facility is also available from the SQL Editor window and from the Query Expression window. [cautionend]

Defining an Expression

An expression uses column names, functions, or constants to compute a result. The expression creates a new column in the query. In this example, you create a column that contains the number of paying customers on each flight.

Select Expression from the Query window. The Query Expression window appears.
The expression is called EXP_nnn by default. If desired, change it by typing over the name in the Expression field. You can change the data type in the Column type field by typing over the default value or by typing a ? in the field to display a selection list of data types. You can also supply values for the Format and Label fields.

Add your expression below the Label field as shown in the following display.

Query Expression Window

Note that if only one table is selected for the query, the alias is not required when identifying columns in the Query Expression window.
The previous display shows the Query Expression window filled in, with the name changed to PAY_CUST, the column type as NUMERIC, a LENGTH of 8, and the label "PAYING CUSTOMERS" assigned. An expression to calculate the number of paying customers is added after the label.

If you do not remember the name of a column, you can choose it from a selection list. Position your cursor in your expression where you want the column name. Select Add Column Name from the Edit menu. A list of columns in the current tables appears. First select a table from the list and then select a column name. The column name is added to the expression.
Select Close from the File menu to return to the Query window. The word Specified appears under Expression to indicate that an expression has been applied to this query, and the new column, PAY_CUST, appears in the list of columns.

The next time you select Expression from the Query window, a list of expressions appears; select an expression to modify or select <NEW> to create a new expression. Alternatively, you can edit an expression by typing I in the Cmd field next to the expression name.

You can add unlimited expressions to a query. To delete an expression, select Expression from the Query window and select the expression to be selected. From the Query Expression window, select Clear from the Edit menu. Alternatively, delete the column in the Query window by typing D in the Cmd field next to the expression name.

Viewing the SQL Code, Running the Query, and Refining the Output

Viewing the SQL Code

As you make changes to a query, the SQL code changes. You can browse the code by selecting Show SQL from the View menu in the Query window, the Subset Rows window, the Query Expression window, or the Join Manager window.

The SQL code in the following display is based on the query on the AIRLINE.MARCH table constructed in the previous sections. Scroll down to see the changes.

Show SQL Window

[Show SQL Window]

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

Running the Query

To run the query, follow this selection path from the Query window: Run SubmitThe Prompt window appears.

Prompt Window

For this report, specify '9:00't , Make sure to use single quotation marks (') around the date value, and include the t after the closing quote to convert the time to a SAS time value.
Press ENTER. The query returns your output.

Query Results

Refining the Output

To enhance the appearance of your report, make the following changes:

From the Report Output window, select Titles from the Edit menu. Add the title Information on Early Morning Flights . Select Close from the File menu; when prompted, select Yes to save the changes to the Titles window.
Select Page Layout from the Edit menu. If the Date value is YES , set it to NO to remove the date and time listing in your report. Set the Center value to YES , which centers the title. Select OK.

Select Close from the File menu to return to the Query window, then re-run the query by selecting Submit from the Run menu. The following display shows the modified report output.

Query Results With Refinements

[Query Results With Refinements]

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

To save your output as a SAS or DB2 table, see Creating New Tables

Limiting the Report's Output

If you anticipate that your report may have many rows of output and you need to see only some of them, you can limit the number of rows processed. Doing so might also shorten the time the query runs, unless you have used summary (aggregate) functions that must be computed on every row in the query.

To limit the number of rows in your output before you run the query, select Limit Output Rows from the Edit menu of the Query window. The Limit Number of Output Rows window appears.

Limit Number of Output Rows Window

[Limit Number of Output Rows Window]

The default value is MAX. Type the number of rows that you want to display and select OK.

When you exit the window, be aware that the specified limit always limits the number of rows displayed in the Report Output window. It also limits SAS or DB2 processing unless the query contains summary functions that force processing of all rows of the table(s) before returning the output to SAS.

If a limit has been placed on the number of output rows in your User Profile, you might be prompted when a query is run. A prompt window appears, verifying that you want to limit the number of rows to be output when this option has been selected in your profile. Type S to stop at the specified number of rows, or C to ignore the limit, and press ENTER. This feature is controlled by the User Profile options Limit output rows and Limit Rows Pop-up Window (see Customizing SAS/ASSIST Software).

Saving a Query

The Query window remains untitled until you save a query by using Save or Save As from the File menu. When you save a query, you give it a name and, optionally, a description. After the query is named, its name appears as part of the Query window title. To save and name your query, select Save As from the File menu in the Query window. The Save As window opens. Type the name of the query and a short description. Change the default catalog if you want and select OK. When you return to the Query window, the name of your query is displayed after the window title.

Saved queries can be used to create reports with the Report Engine. See Doing More with the Report Engine for more information.

Resetting the Query Window

To reset the Query window in order to build another query, select New from the File menu. Doing so clears all existing data and specifications from the Query window. To start another query, choose Select to open the Select window.

Performing a COUNT(*) on a Table

In order to display the number of rows returned by a query (that is, to perform a COUNT(*) on the table), follow these instructions:

Use the Select window to select tables and columns for the query.
Modify the query with subsets, expressions, repeated and deleted columns, and usages as desired.
Follow this selection path:Edit Count(*)Query and Reporting creates an expression called COUNT(*) with a usage of *. This expression appears in the Query window.
Select Submit from the Run menu. If you have not applied a usage to all columns, a prompt appears asking you if you want to apply a GROUP usage to all columns. Select Yes or No. The output includes a count of the returned rows.
Select Close from the File menu to return to the Query window.

To perform a COUNT DISTINCT(*) on a table:

Use the Select window to select table for the query.
From the Query window, select Expression and specify * as your expression. Give the expression a name and select Close from the File menu to return to the Query window.
Delete the numbers in the No column for all columns, except the new expression, to remove them from the output.
Select Submit from the Run menu. The output shows the number of distinct nonmissing rows for the entire table.
Select Close from the File menu to return to the Query window.

To perform a COUNT(*) within a group:

Use the Select window to select tables and columns for the query.
From the Query window, select Expression and specify * as your expression. Give the expression a name and select Close form the File menu to return to the Query window.
Specify the GROUP usage for the column of interest. Delete the numbers in the No column for all columns except the new expression and the column of interest.
Select Submit from the Run menu to run your query. The output shows the number of distinct values (groups) in the column of interest.
Select Close from the File menu to return to the Query window.

See Doing More with the Report Engine for more information on the Usage field.

Top of Page