Examples |
Selecting a Table |
First, you will analyze the relation between salary level, position, and hire date. Select SAMPLE.SALARY from the Available Tables list.
Select the right arrow to add your selection to the Selected Tables list. For mouse-enabled operating environments, you can also double-click on SAMPLE.SALARY to move it to the Selected Tables list. Select to display the SQL QUERY COLUMNS window.
Selecting Columns |
Select Salary, BEGDATE, and JOBCODE from the Available Columns list. Select the right arrow to add your selections to the Selected Columns list.
Alias Names and Labels |
To create more descriptive labels for JOBCODE and BEGDATE, select JOBCODE from the Selected Columns list. Select to assign a new label to the JOBCODE column.
specifies an alias for the column. The alias is used in place of the column name both in the query and in any table or view that is created from the query. Aliases make a result table clearer or easier to read. You can also use an alias to name a column expression.
associates a label with a column heading.
Type Job Code in the Label field. Select to return to the SQL QUERY COLUMNS window. The assigned label is displayed next to JOBCODE in the Selected Columns List.
Select BEGDATE from the Selected Columns list. Select . Type Beginning Date in the Label field. Select .
Column Format |
To modify the format of the BEGDATE column, select BEGDATE from the Selected Columns list. Select to specify the format in which the beginning dates are presented.
specifies the form in which the column data is displayed. You can enter a format, or select the right arrow to see a list of valid formats. When you select a format, a formatted example appears, along with its width range, default width, default decimal, and name. You can either accept the default width and decimal values, or you can specify your own values in the Width field.
specifies the form in which the column data is read by other SAS procedures if you create a table or view from the query. You can enter an informat, or you can select the right arrow to see a list of valid informats. When you select an informat, a formatted example appears, along with its width range, default width, default decimal, and name. You can either accept the default width and decimal values, or specify your own values.
Select the right arrow next to the Format field to display a list of formats.
Select date from the Format Names list. Type 9 in the Width field. Select .
Select
to return to the SQL QUERY COLUMNS window.Creating a WHERE Expression |
A WHERE expression returns a subset of data that meets conditions that you specify. In this example, you create a WHERE expression that displays the range of job codes for employees who were hired after October 1991 and whose salaries are less than $18,000.00.
Select View Where Conditions for Subset. The WHERE EXPRESSION window appears.
The Available Columns list contains all columns from the selected tables, in addition to the following choices:
Select Salary from the Available Columns list. A list of numeric comparison operators appears.
The list of operators is specific to the data type.
EQ |
is equal to |
NE |
is not equal to |
GT |
is greater than |
LT |
is less than |
GE |
is greater than or equal to |
LE |
is less than or equal to |
* |
multiplies by |
/ |
divides by |
+ |
adds |
- |
subtracts |
** |
raises to a power |
The OTHER Operators are
Is Missing |
selects rows in which a column value is missing or null. |
Is Not Missing |
selects rows in which a column value is not missing or is not null. |
Between |
searches for values that lie within the specified parameters. |
Not Between |
searches for values that lie outside the specified parameters. |
In |
tests if the column value is a member of a set. |
Not In |
tests if the column value is not a member of a set. |
Select LT from the list of comparison operators.
Select <CONSTANT enter value>. Enter 10000 in the Numeric field.
Select
. The WHERE expression is built for you as you select new operators and values.You can delete the last operator or operand that you added to the WHERE statement by selecting 10000 from the WHERE statement.
. For this example, select to removeSelect <LOOKUP distinct values> to view all the unique values that exist in the SALARY column.
Select $18,000 from the list of values. Because the LT comparison operator requires only one value, the WHERE EXPRESSION window automatically reappears.
Select AND from the list of operators.
to display the list of operators. Note that the list of comparison operators has changed to a list of logical operators. SelectSelect BEGDATE from the Available Columns list. Select GT from the list of comparison operators.
Select <PROMPT at run-time> to display the Prompt String dialog box. Type Beginning Date: in the Prompt String field.
Select &PROMPT1 in the WHERE expression indicates that you will supply a value for this variable when you run the query.
.Select
from the WHERE EXPRESSION window to return to the SQL QUERY COLUMNS window.To run your query, select Tools Run Query Run Immediate.
The Prompt at Run Time window appears, with the Beginning Date: prompt that you specified in the WHERE expression.
to display a list of values for the BEGDATE column.Select 13OCT1991 from the list of values; the Prompt at Run Time window is displayed with the value that you selected. Select to continue to run the query and to view your output in the Output window.
Copyright © 2008 by SAS Institute Inc., Cary, NC, USA. All rights reserved.