Chapter Contents |
Previous |
Next |
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.
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.
Customizing Your Query |
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. |
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.
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;
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.
(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.
File | Close |
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.
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.
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 (
&
).
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
window.
?
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
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 |
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
Select
Close from the File menu to return to the Query
window.
Run | Submit |
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.
Query Results
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
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
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
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
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 |
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 |
Performing a COUNT(*) on a Table |
Edit | Count(*) |
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.
Chapter Contents |
Previous |
Next |
Top of Page |
Copyright 1999 by SAS Institute Inc., Cary, NC, USA. All rights reserved.