Chapter 2: Examples

Setting up the Environment

To practice with the examples in this chapter, you will need to use the sample data library that is provided with the SQL Query Window.

Submit the following statement in the Program Editor to assign the SAMPLE libref to the sample library:

libname sample

'sample library';

Consult your site's SAS Support Consultant for the location of the sample library. Some of the examples require that you save files to the sample library. If you do not have write access to the sample library, you can save the files to another library of your choice, such as the SASUSER library.

Invoking the Query Window

For these examples, invoke the SQL Query Window by selecting
Tools – Query
or by entering query in the command window or at the Command ===> prompt.

The SQL QUERY TABLES window appears. By default, the SASUSER libref is selected and the tables from that libref appear in the Available Tables list.

Changing Your Profile

In order to include the tables that are in the sample library in the Available Tables list, you must set your SQL Query Window profile to include the tables in the SAMPLE library. Select
Profile – Set Preferences

Select the right arrow next to Data Restrictions to display the Data Restrictions for Profile window.

click to expand

Select SAMPLE from the Table Source list. Select Add entire Table Source to preferences from the pop-up menu that appears.

click to expand

Select WORK from the Table Source list. Select Add entire Table Source to preferences from the pop-up menu.

Note	If you do not have write access to the SAMPLE library, then repeat the previous step for the SASUSER library.

Select OK to return to the Preference Settings for Profile window.

Select Save to save your new profile setting.

click to expand

Type SAMPLE in the Entry Name field of the Name Catalog Entry for Profile window. Select OK .

Select Close in the Preference Settings for Profile window.

From the SQL QUERY TABLES window, select
Tools – Switch to New Profile

Select the right arrow next to the Profile Name field to display a list of profiles.

click to expand

In the Preference Profiles in Catalog window, select SASUSER from the Libraries list. Next, select PROFILE from the Catalogs list, and then select SAMPLE from the Profiles list. Select OK .

Select OK to return to the SQL QUERY TABLES window and to complete the switch to the new profile. The new profile displays only the tables that are in the sample library.

See "Setting Your Profile" on page 73 for more information about the SQL Query Window user profile.

Performing Simple Queries

Selecting a Table

First, you will analyze the relation between salary level, position, and hire date. Select SAMPLE.SALARY from the Available Tables list.

click to expand

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.

click to expand

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.

click to expand

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.

click to expand

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.

click to expand

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.

click to expand

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.

click to expand

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.

click to expand

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.

click to expand

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.

click to expand

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.

click to expand

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.

click to expand

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.

click to expand

Select Lookup to display a list of values for the BEGDATE column.

click to expand

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.

click to expand