SAS 9.1 SQL Query Window: Users Guide

2017-07-07 02:10:07

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 OK 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 Column Alias/Label to assign a new label to the JOBCODE column.

Alias Name

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.

Label

associates a label with a column heading.

Type Job Code in the Label field. Select OK 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 Column Alias/Label. Type Beginning Date in the Label field. Select OK .

Column Format

To modify the format of the BEGDATE column, select BEGDATE from the Selected Columns list. Select Column Formats to specify the format in which the beginning dates are presented.

Format

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.

Informat

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 OK . Select OK 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.

Available Columns

The Available Columns list contains all columns from the selected tables, in addition to the following choices:

<CONSTANT enter value>	enables you to enter a constant value for the WHERE expression.
<PROMPT at run-time>	enables you to enter a value for the WHERE expression when you run the query or create a table or view.

Comparison Operators

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.

Constant Values

Select <CONSTANT enter value> . Enter 10000 in the Numeric field.

Select OK . The WHERE expression is built for you as you select new operators and values.

Undo

You can delete the last operator or operand that you added to the WHERE statement by selecting Undo . For this example, select Undo to remove 10000 from the WHERE statement.

Lookup Distinct Values

Select <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.

Logical Operators

Select Operators to display the list of operators. Note that the list of comparison operators has changed to a list of logical operators. Select AND from the list of operators.

Select BEGDATE from the Available Columns list. Select GT from the list of comparison operators.

Run-Time Prompt

Select <PROMPT at run-time> to display the Prompt String dialog box. Type Beginning Date: in the Prompt String field.

Select OK . &PROMPT1 in the WHERE expression indicates that you will supply a value for this variable when you run the query.

Select OK from the WHERE EXPRESSION window to return to the SQL QUERY COLUMNS window.

Running Your Query

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.

Select Lookup 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 OK to continue to run the query and to view your output in the Output window.