Chapter Contents

Previous

Next

The First Steps

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

Submit the following statement in the PROGRAM EDITOR window to assign the SAMPLE libname to the sample library:

libname sample 'sample library';

Consult your SAS Administrator 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.

Invoking the Query Window

See Invoking the SQL Query Window for instructions on the different ways in which you can invoke the SQL Query Window. In this example, invoke the SQL Query Window by typing

query

in the Program Editor window.

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

Changing Your Profile

Set your SQL Query Window profile to include the tables in the sample data library. From the PMENU, select

Profile

Set Preferences...

Select the [→] next to Data Restrictions to display the Data Restrictions for Profile window.

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

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

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

Select Save to save your new profile setting.

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 PMENU, select

Tools

Switch to New Profile

Select the [→] next to Profile Name: to display a list of profiles.

Select SASUSER.PROFILE.SAMPLE from the Preference Profiles in Catalog window.

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.

To practice with the SQL Query Window examples, you will need the following data sets:

• SAMPLE.EMPINFO

• SAMPLE.JOBCODES

• SAMPLE.LEAVE

• SAMPLE.SALARY

• SAMPLE.PROGRAM

See Setting Your Profile for more information on the SQL Query Window user profile.

Selecting a Table

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

Select [→] to add your selection to the Selected Tables list. You can also double-click on SAMPLE.SALARY to select it. Select OK to display the SQL QUERY COLUMNS window.

Selecting Columns

Select Salary, BEGDATE, and JOBCODE from the list of Available Columns. Select [→] 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 list of Selected Columns. 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; they can also 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 a 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 are displayed. You can enter a format, or select [→] 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 specify your own values in the Width: field.

Informat

specifies the form in which the column data are read by other SAS procedures if you create a table or view from the query. You can enter an informat, or select [→] 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 [→] next to Format to display a list of formats.

Select date from the list of Format Names. 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 meet conditions you specify. Create a WHERE expression that displays the range of job codes whose employees were hired after October 1991 and whose salaries are less than $18,000.00. Select Where Conditions for Subset... from the View PMENU. The WHERE EXPRESSION window appears.

Available Columns

The Available Columns list contains all the 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

Select Undo to remove 10000 from the WHERE statement. You can delete your last addition to the WHERE statement by selecting Undo.

Lookup Distinct Values

Select LOOKUP distinct values to view all of the values for the SALARY column. Distinct values remove duplicate rows from your output table.

Select $18,000 from the list of values. Because the LT comparison operator requires only one value, you are automatically returned to the WHERE EXPRESSION window.

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 list of Available Columns. Select GT from the list of comparison operators.

Run-Time Prompt

Select PROMPT at run-time to display the Prompt String window. 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 Beginning Date:

Select 13OCT1991 from the list of values; the Prompt at Runtime 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.

Chapter Contents

Previous

Next

Top of Page

Copyright 1999 by SAS Institute Inc., Cary, NC, USA. All rights reserved.