|Doing More with SAS/ASSIST Software|
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
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:
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
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.
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
|Customizing Your Query|
You can customize your query by re-ordering the
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.
The Cmd field accepts these commands:
||Displays information about the column, as described in Expanding a List to Get More Information.|
||Permanently deselects (excludes) one or more columns from your query.|
||Repeats the row, adding the duplicate after the row to be repeated.|
||Displays a list of available commands.|
Although the No field (abbreviated from "Number") does not accept commands, you can use it to perform these actions:
1in 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.
The Order field accepts these commands:
||Organizes the data within each column in ascending order, that is, in numeric order or alphabetic order from A to Z.|
||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
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 (
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
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;
The Usage field accepts several commands that apply summary (aggregate) functions on the values in the designated column as shown in the following table.
||Groups data by the values in the column|
||Computes the sum of values in the column|
||Lists the lowest value (minimum)|
||Lists the highest value (maximum)|
||Averages or finds the mean of values in the column; equivalent to AVG|
||Averages or finds the mean of values in the column|
||Counts the number of distinct, nonmissing values|
||Counts the number of distinct, nonmissing values|
||Reports the corrected sum of squares|
||Returns the coefficient of variation (percent)|
||Reports the number of missing values|
||Reports the probability of a greater absolute value of Student's t|
||Reports a range of values|
||Shows the standard deviation|
||Returns the standard error of the mean|
||Returns the weighted sum|
||Returns the Student's t|
||Returns the uncorrected sum of squares|
||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.
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.
(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.
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.
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.
andis 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
&promptnis the name of the prompt. The prompt name must be seven characters or fewer and must be preceded by an ampersand (
Adding a Prompt to the Subset Rows Window
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.
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
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.
?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.
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
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
|Viewing the SQL Code, Running the Query, and Refining the Output|
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
Close from the File menu to return to the Query
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.
To enhance the appearance of your report, make the following changes:
Information on Early Morning Flights. Select Close from the File menu; when prompted, select Yes to save the changes to the Titles window.
YES, set it to
NOto 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
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
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
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
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:
To perform a COUNT DISTINCT(*) on a table:
*as your expression. Give the expression a name and select Close from the File menu to return to the Query window.
To perform a COUNT(*) within a group:
*as your expression. Give the expression a name and select Close form 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
Copyright 1999 by SAS Institute Inc., Cary, NC, USA. All rights reserved.