Docstoc

SAS-Performing-Interactive-SQL-Queries

Document Sample
SAS-Performing-Interactive-SQL-Queries Powered By Docstoc
					Course Notes

Instructor-based Training

Performing Interactive SQL Queries

SAS Institute Inc. SAS Campus Drive Cary, NC 27513

55447

Performing Interactive SQL Queries
Course Notes

SAS Institute Inc. SAS Campus Drive Cary, NC 27513

120495

120495

SAS INSTITUTE TRADEMARKS
The SAS System is an integrated system of software providing complete control over data access, management, analysis, and presentation. Base SAS software is the foundation of the SAS System. Products within the SAS System include SAS/ACCESS , SAS/AF , SAS/ASSIST , SAS/CALC , SAS/CONNECT , SAS/CPE , SAS/DMI , SAS/EIS , SAS/ENGLISH , SAS/ETS , SAS/FSP , SAS/GRAPH , SAS/IMAGE , SAS/IML , SAS/IMS-DL/I , SAS/INSIGHT , SAS/LAB , SAS/NVISION , SAS/OR , SAS/PH-Clinical , SAS/QC , SAS/REPLAY-CICS , SAS/SESSION , SAS/SHARE , SAS/SPECTRAVIEW , SAS/STAT , SAS/TOOLKIT , SAS/TRADER , SAS/TUTOR , SAS/BUDGET , SAS/DB2 , SAS/FINANCE , SAS/GEO , SAS/GIS , SAS/PH-Kinetics , SAS/SHARE*NET , and SAS/SQL-DS software. Other SAS Institute products are SYSTEM 2000 Data Management Software, with basic SYSTEM 2000, CREATE , Multi-User , QueX , Screen Writer , and CICS interface software; InfoTap software; NeoVisuals software; JMP , JMP IN , and JMP Serve software; SAS/RTERM software; the SAS/C Compiler and the SAS/CX Compiler; Video Reality software; VisualSpace software; and Emulus software. MultiVendor Architecture and MVA are trademarks of SAS Institute Inc. SAS Institute also offers SAS Consulting , SAS Video Productions , Ambassador Select , and On-Site Ambassador services. Authorline , Books by UsersSM, the Encore Series , JMPer Cable , Observations , SAS Communications , SAS Training , SAS Views , the SASware Ballot , and SelecText documentation are published by SAS Institute Inc. The SAS Video Productions logo and the Books By Users SAS Institute’s Author Service logo are registered service marks and the Helplus logo, the SelecText logo, the SAS Online Samples logo, the Video Reality logo, the Quality Partner logo, and The Encore Series logo are trademarks of SAS Institute Inc. All trademarks above are registered trademarks or trademarks of SAS Institute Inc. in the USA and other countries. indicates USA registration. The Institute is a private company devoted to the support and further development of its software and related services. OS/2 is a trademark or registered trademark of International Business Machines Corporation. ORACLE is a trademark or registered trademark of Oracle Corporation. Other brand and product names are registered trademarks or trademarks of their respective companies. Performing Interactive SQL Queries Course Notes Copyright 1995 by SAS Institute Inc., Cary, NC 27513, USA. All rights reserved. Printed in the United States of America. No part of this publication may be reproduced, stored in a retrieval system, or transmitted, in any form or by any means, electronic, mechanical, photocopying, or otherwise, without the prior written permission of the publisher, SAS Institute Inc.

Book code 55447, course code MSQL, prepared 02JAN96. ISBN 1-55544-593-4

120495

Table of Contents
Course Description . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Prerequisites . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . General Conventions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Course Objectives . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
v vi vii ix

1. SQL Query Window . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 1.1 1.2 1.3 1.4 1.5 Getting Started . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3 Building a Simple Query . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11 Summarizing and Joining Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 46 SQL Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 84 Solutions to Exercises . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 99

Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 107

iv

Table of Contents
120495

For Your Information
120495

v

Course Description
Performing Interactive SQL Queries Course Notes teaches you how to use the SQL Query window to perform ad hoc queries interactively, join data sets, and create SQL views. The course contains workshop exercises and is designed to be taught in approximately four hours. It is not offered publicly; it is available for on-site presentation only.

vi

For Your Information
120495

Prerequisites
Before selecting this course, you should understand the concept of a SAS data library understand interactive processing.

For Your Information
120495

vii

General Conventions
This section explains the various conventions used in presenting text, SAS language syntax, and examples in this book. Typographical Conventions You will see several type styles in this book. This list explains the meaning of each style: roman UPPERCASE ROMAN italic is the standard type style used for most text in this book. is used for SAS statements, variable names, and other SAS language elements when they appear in the text. identifies terms or concepts that are defined in text. Italic is also used for book titles when they are referenced in text, as well as for various syntax and mathematical elements. is used for emphasis within text. is used for examples of SAS programming statements and for SAS character strings. Monospace is also used to refer to field names in windows, information in fields, and user-supplied information. indicates push buttons and other items that you select.

bold
monospace

box

Mouse Conventions The number of buttons on mouse devices varies. On mouse devices with two or three buttons, one button makes selections and one displays pop-up menus. Because the locations of these buttons vary, this book references them as the mouse select button or the mouse menu button. If you use a mouse device, you can determine which button executes which action by trying them.

menu button

select button

Two-Button Mouse with Default Settings

viii

For Your Information
120495

General Conventions
Syntax Conventions The general forms of SAS statements and commands shown in this book include only that part of the syntax actually taught in the course. For complete syntax, see the appropriate SAS reference guide. This is an example of how SAS syntax is shown in text:

PROC CHART DATA=SAS-data-set; HBAR | VBAR chart-variables </ options>; RUN;

PROC and CHART are in uppercase bold because they are SAS keywords. DATA= is in uppercase to indicate that it must be spelled as shown. SAS-data-set is in italic because it represents a value that you supply. In this case, the value must be the name of a SAS data set. HBAR and VBAR are in uppercase bold because they are SAS keywords. They are separated by a vertical bar to indicate they are mutually exclusive; you can choose one or the other. chart-variables is in italic because it represents a value or values that you supply. </ options> represents optional syntax specific to the HBAR and VBAR statements. The angle brackets enclose the slash as well as options because if no options are specified you do not include the slash. RUN is in uppercase bold because it is a SAS keyword.

For Your Information
120495

ix

Course Objectives
After completing this course, you should be able to Chapter 1 SQL Query Window

access the SQL Query window select and build a column in any specified table subset your data create summary reports set SAS options and specify titles join tables save and include queries create and use SQL views.

x

For Your Information
120495

120495

Chapter 1 SQL Query Window
1.1 1.2 1.3 1.4 1.5 Getting Started . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3 Building a Simple Query . . . . . . . . . . . . . . . . . . . . . . . . 11 Summarizing and Joining Tables . . . . . . . . . . . . . . . . . . 46 SQL Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 84 Solutions to Exercises . . . . . . . . . . . . . . . . . . . . . . . . . . 99

2

Chapter 1

SQL Query Window
120495

Getting Started
120495

3

1.1

Getting Started

Objectives
After completing this section, you should be able to access the SQL Query window.

4

Chapter 1

SQL Query Window
120495

Introduction
The SQL Query window is an easy-to-use point-and-click interface to query data and produce reports. You can access data from native SAS data sets and some database management systems, and create SAS data sets for future use. The SQL Query window is accessed using SAS/ASSIST software the QUERY command the EXECCMD routine in Screen Control Language. The SQL Query window is part of SAS/ASSIST software starting with Release 6.08.

Getting Started
120495

5

Benefits
The SQL Query window is easy to use because you can query data without writing code or learning code syntax. The SQL Query window enables you to access data from multiple sources including SAS data sets DB2 DB2/2 (OS/2 Database Manager) ODBC ORACLE Rdb/VMS SQL/DS SYBASE INFORMIX INGRES save a query from one session to the next save a resulting data set as a view or data file create multiple styles of report perform automatic joins perform automatic table lookups.

6

Chapter 1

SQL Query Window
120495

Accessing the SQL Query Window
You can access the SQL Query window from the Report Writing Menu or Data Management Menu in SAS/ASSIST software. Select REPORT WRITING to access the Report Writing Menu from the Primary Menu in SAS/ASSIST.

SAS/ASSIST Primary Menu

Getting Started
120495

7

Accessing the SQL Query Window
Select Query to access the Query menu.

Report Writing Menu Select SQL Query . . . to open the SQL Query window.

Choosing the Query Type

8

Chapter 1

SQL Query Window
120495

Accessing the SQL Query Window
Assign a libref to point SAS to the location of your data. Select Setup to access the Setup Selections window.

SQL Query Window Using the QUERY command will not open this window.

Getting Started
120495

9

Accessing the SQL Query Window
Select SAS data libraries .

Setup Selections Window Select Assign a new libref . . . .

SAS Data Libraries Window

10

Chapter 1

SQL Query Window
120495

Accessing the SQL Query Window
Type company in the Libref: field and . in the Name of the SAS data library: field to indicate the directory from which SAS is invoked. Select OK to assign the libref COMPANY.

Assigning a New Libref A message indicating the libref was successfully assigned is displayed. Select Goback until the SQL Query window appears. Select Run . Using a . indicates the default directory on directory-based systems only. The name of the SAS data library will depend on your operating system.

Building a Simple Query
120495

11

1.2

Building a Simple Query

Objectives
After completing this section, you should be able to select and build a column in any specified table subset your data.

12

Chapter 1

SQL Query Window
120495

Selecting a Table
By default, only the tables in the SASUSER library are displayed in the Available Tables list box. To display tables stored in other libraries, select the appropriate libref from the Table Sources list box.

Initial SQL Query Window

Building a Simple Query
120495

13

Selecting a Table
Example: Create a query that selects the columns IDNUM, JOBCODE, SEX, and SALARY from the data set COMPANY.PAYROLL and displays the data in a report.

Select COMPANY from the Table Sources list box.

Selecting a Table Source After selecting COMPANY from the Table Sources list box, all of the tables stored in the COMPANY SAS data library appear in the Available Tables list box.

14

Chapter 1

SQL Query Window
120495

Selecting a Table
To move COMPANY.PAYROLL from the Available Tables list box to the Selected Tables list box, select
COMPANY.PAYROLL

or double-click on COMPANY.PAYROLL.

Selecting a Table Select OK to view the list of columns available in the COMPANY.PAYROLL table.

Building a Simple Query
120495

15

Selecting Columns
The SQL QUERY COLUMNS window displays all of the columns available for the selected tables.

SQL QUERY COLUMNS Window
<,COUNT(*) > is a summary function that counts the number of rows returned by

a query.
* PAYROLL * <all columns> selects all of the columns in the PAYROLL

table.

16

Chapter 1

SQL Query Window
120495

Selecting Columns
To move IDNUM, JOBCODE, SEX, and SALARY to the Selected Columns list box, select 1. IDNUM , JOBCODE , SEX , and SALARY 2. .

Selecting Columns The order in which the columns are selected determines their order in the report. To change the column order once the columns are in the Selected Columns list, select the desired column and then select Move Before or Move After .

Building a Simple Query
120495

17

Selecting Columns
Click on the mouse menu button anywhere in the SQL QUERY COLUMNS window to display a list of actions. Select Run Query to execute the query.

Selecting Run Query You can execute a query from the pull-down menus by selecting Actions Run Query .

18

Chapter 1

SQL Query Window
120495

Selecting Columns
Another pop-up menu appears.

Selecting a Run Action Select Run Immediate . Selecting Design a Report from the pop-up menu extracts the selected data and branches to the REPORT procedure, which offers more advanced report writing capabilities.

Building a Simple Query
120495

19

Selecting Columns
The report is displayed in the OUTPUT window.

Output Report

20

Chapter 1

SQL Query Window
120495

Selecting Columns
Select File
End to return to the SQL QUERY COLUMNS window.

Ending the OUTPUT Window

Building a Simple Query
120495

21

Building a Column
Example: Select the columns IDNUM , JOBCODE , SEX , and SALARY from the COMPANY.PAYROLL table. Label the SALARY column as Annual Salary. Compute the monthly salary by dividing the SALARY column by 12. Name the computed column MSALARY and format the column with the DOLLAR12. format.

To add a label to the SALARY column, select 1. SALARY from the Selected Columns list box 2. Column Alias/Label .

Labeling a Column

22

Chapter 1

SQL Query Window
120495

Building a Column
Type Annual Salary in the Label: field. Select OK to close the window.

Specifying a Label

Building a Simple Query
120495

23

Building a Column
Select Build a Column to create the monthly salary column.

Selected Column with Label

24

Chapter 1

SQL Query Window
120495

Building a Column
Select SALARY to begin the expression.

Building a Column Expression

Building a Simple Query
120495

25

Building a Column
A list of operators appears.

Selecting an Operator Select / from the list of operators.

26

Chapter 1

SQL Query Window
120495

Building a Column
Select <CONSTANT enter value> to divide salary by 12. Type 12 in the Numeric: field.

Specifying a Constant Value Select OK .

Building a Simple Query
120495

27

Building a Column
Select Column Attributes from the BUILD A COLUMN EXPRESSION window to assign an alias, label, and format.

BUILD A COLUMN EXPRESSION Window

28

Chapter 1

SQL Query Window
120495

Building a Column
Type 1. msalary in the Alias Name= field 2. Monthly Salary in the Label= field.

Specifying an Alias and a Label Select to specify a SAS format.

Building a Simple Query
120495

29

Building a Column
Select
dollar OK OK OK

to choose a format and return to the SQL QUERY COLUMNS window.

Choosing a Format The default format width is 12.

30

Chapter 1

SQL Query Window
120495

Building a Column
Select Run Query
Run Immediate from the pop-up menu to create the report.

Running the Query

Building a Simple Query
120495

31

Building a Column
The report is displayed in the OUTPUT window.

Partial Report Output Select File
End to return to the SQL QUERY COLUMNS window.

32

Chapter 1

SQL Query Window
120495

Formatting a Column
To add a format to the salary column, 1. select SALARY from the Selected Columns list box 2. select Column Formats 3. type DOLLAR7. in the Format= field 4. select OK .

Specifying a Format Use the horizontal scroll bar in the Selected Columns list box to see the FORMAT= option.

Building a Simple Query
120495

33

Subsetting Data
Example: Display only flight attendants (job codes FA1, FA2, and FA3) and mechanics (job codes ME1, ME2, and ME3) who make less than $50,000 annually.

Press the mouse menu button to display the pop-up menu. Select Where Conditions . . . to subset the data.

Selecting an Action You can subset data from the pull-down menu by selecting Locals Where Conditions . . . .

34

Chapter 1

SQL Query Window
120495

Subsetting Data
Select SALARY from the Available Columns list box to start building the expression SALARY LT 50000.

Building a WHERE Expression

Building a Simple Query
120495

35

Subsetting Data
A list of operators automatically appears.

Selecting an Operator Select LT from the list of operators.

36

Chapter 1

SQL Query Window
120495

Subsetting Data
To specify the constant value of 50000, 1. select <CONSTANT enter value> 2. type 50000 in the Numeric: field 3. select OK to close the window.

Specifying a Constant Value

Building a Simple Query
120495

37

Subsetting Data
Subset the data for flight attendants and mechanics. Select Operators
AND .

The AND operator is chosen because both expressions must be true to include the row in the report.

Selecting an Operator

38

Chapter 1

SQL Query Window
120495

Subsetting Data
To build the JOBCODE IN (’FA1’ ’FA2’ ’FA3’ ’ME1’ ’ME2’ ’ME3’) expression, select
JOBCODE IN <LOOKUP distinct values> .

Selecting Distinct Values Select FA1 , FA2 , FA3 , ME1 , ME2 , and ME3 . Select OK to save the selections and close the window.

Building a Simple Query
120495

39

Subsetting Data
The WHERE expression is now complete.

The Completed WHERE Expression Select OK to close the WHERE EXPRESSION window.

40

Chapter 1

SQL Query Window
120495

Subsetting Data
Select Run Query
Run Immediate from the pop-up menu.

The report is displayed in the OUTPUT window.

Subsetted Report Select File
End to close the OUTPUT window.

Building a Simple Query
120495

41

Ordering the Report
Example: Display the previous report in sorted order by the job code.

Selecting an Action Select Order By . . . from the pop-up menu to display the report in sorted order by JOBCODE. You can order the report from the pull-down menu by selecting Locals Order By . . . .

42

Chapter 1

SQL Query Window
120495

Ordering the Report
To order the report by JOBCODE, select 1. JOBCODE from the Available Columns list box 2. .

ORDER BY Columns Window

Building a Simple Query
120495

43

Ordering the Report
The default sort order is ascending (ASC).

Selecting an ORDER BY Column Select OK to return to the SQL QUERY COLUMNS window. To change the sort order to descending, select the column in the Selected Columns list box, then select Descending Order .

44

Chapter 1

SQL Query Window
120495

Ordering the Report
From the pop-up menu, select Run query The ordered report is displayed.
Run Immediate to execute the query.

Partial Ordered Report Select File
End to close the window.

Building a Simple Query
120495

45

Exercises
1.1 Accessing Your Data Invoke the SQL Query window and assign the libref COMPANY to the workshop SAS data library. 1.2 Creating a Simple Query Run a query that displays all columns and rows in the COMPANY.STAFF table. 1.3 Subsetting and Ordering a Query a. Reset the query by selecting 1. Actions
Reset from the SQL QUERY COLUMNS window

2. OK to verify that you want to clear everything. b. Create a report from the COMPANY.FREQ table that displays the NAME, FFID, and MILETRAV columns. Display only the rows where the passenger has traveled more than 55,000 miles. c. Re-execute the same report, ordering the rows alphabetically by NAME. d. Create a new column to display the points remaining for each passenger. Calculate the column by subtracting PTSUSED from PTSEARND. Name the new column PTSRMNG and label it Points Remaining. Execute the new query.

46

Chapter 1

SQL Query Window
120495

1.3

Summarizing and Joining Tables

Objectives
After completing this section, you should be able to create summary reports set SAS options and specify titles join tables save and include queries.

Summarizing and Joining Tables
120495

47

Summary Reports
Example: Create a report that displays the average and maximum salary for flight attendants and mechanics.

To remove IDNUM, SEX, SALARY, and MSALARY from the Selected Columns list box, select 1. IDNUM , SEX , SALARY , and msalary 2. .

Removing Columns Remove SALARY in order to remove the format and label options.

48

Chapter 1

SQL Query Window
120495

Summary Reports
To calculate the maximum and average salary select, 1. SALARY from the Available Columns list box 2. 3. SALARY from the Available Columns list box 4. .

Selecting Columns

Summarizing and Joining Tables
120495

49

Summary Reports
Select the first SALARY from the Selected Columns list. Then select Summary Functions AVG to request the average be generated for the first SALARY column.

Selecting a Summary Statistic Select the second SALARY from the Selected Columns list. Then select Summary Functions MAX to request the maximum be generated for the second SALARY column. The summary functions appear in the Selected Columns list box.

50

Chapter 1

SQL Query Window
120495

Summary Reports
Select Run Query
Run Immediate .

A window opens requesting information on how the summary functions AVG and MAX are to be interpreted in the query.

Requesting GROUP BY Information Select AUTOGROUP because JOBCODE is the only column that is not used in a summary function and summaries for each JOBCODE are requested.

Summarizing and Joining Tables
120495

51

Summary Reports
Select Yes to retain the GROUP BY criterion after the query is run.

Specifying a Permanent GROUP BY

52

Chapter 1

SQL Query Window
120495

Summary Reports
The summary report is displayed in the OUTPUT window.

Grouped Report Select File
End to close the OUTPUT window.

Summarizing and Joining Tables
120495

53

Setting SAS Options
Example: Turn off the page number and the date/time stamp in the upper-right corner of the report and add titles to the report.

You can eliminate the date and page number from the report by turning off the DATE and NUMBER SAS system options. To access SAS system options, select Globals from the menu bar. Then select Global options SAS options from the pull-down menus.

Changing SAS Options

54

Chapter 1

SQL Query Window
120495

Setting SAS Options
A filled box beside the option indicates the option is currently in effect. To toggle the option on or off, select the option. Select DATE to turn the DATE option off. Select NUMBER to turn the NUMBER option off.

Specifying SAS Options Close the OPTIONS window by selecting Edit menu.
End from the menu bar or pop-up

Options changed here are global and affect all other output generated in your SAS session.

Summarizing and Joining Tables
120495

55

Specifying Titles
To specify titles for the report, select
Globals Global options Titles .

Specifying Titles On some operating systems, you can specify titles by selecting Actions Report Options .

56

Chapter 1

SQL Query Window
120495

Specifying Titles
Type 1. Average and Maximum Salaries in the Title1 field 2. for Flight Attendants and Mechanics in the Title2 field.

Specifying Titles Select 1. Edit
End from the menu bar or pop-up menu to close the TITLES window Run Immediate to create the report.

2. Run Query

Summarizing and Joining Tables
120495

57

Specifying Titles
The report is displayed in the OUTPUT window.

Final Report Select Edit
End to close the OUTPUT window.

58

Chapter 1

SQL Query Window
120495

Resetting the QUERY Window
Example: Reset the QUERY window to begin a new query.
Reset .

Select Actions

Resetting the Window

Summarizing and Joining Tables
120495

59

Resetting the QUERY Window
Select OK to reset the entire query.

Resetting the Query When resetting the query, any SAS system or global option (such as titles or dates) remains in effect.

60

Chapter 1

SQL Query Window
120495

Joining Tables
Example: Create a report that contains the columns IDNUM, FNAME, LNAME, JOBCODE, and SALARY for flight attendants and pilots. Prompt the user whether to run the report for males or females.

When several columns from different tables need to be placed side by side in a report, an SQL join can be used. The data needed for this example can be found in COMPANY.STAFF and COMPANY.PAYROLL. Select COMPANY from the Table Sources list box.

Selecting a Table Source

Summarizing and Joining Tables
120495

61

Joining Tables
Select 1. COMPANY.PAYROLL and COMPANY.STAFF from the Available Tables list box 2. to move COMPANY.PAYROLL and COMPANY.STAFF to the Selected Tables list box.

Selecting Multiple Tables Select OK to access the SQL QUERY COLUMNS window.

62

Chapter 1

SQL Query Window
120495

Joining Tables
All the columns available from each table are listed in the Available Columns list box.

Column Listing

Summarizing and Joining Tables
120495

63

Joining Tables
Select 1. IDNUM , FNAME , LNAME , JOBCODE , and SALARY from the Available Columns list box 2. to move the columns to the Selected Columns list box.

Selecting Columns

64

Chapter 1

SQL Query Window
120495

Joining Tables
Select Run Query
Run Immediate to execute the query.

Running the Query

Summarizing and Joining Tables
120495

65

Joining Tables
A window is displayed warning that joining tables without a WHERE clause can produce a large amount of data. Typically, when two or more tables are joined, the tables will contain a common column or key to ensure that the corresponding data from both tables are matched correctly. In this example the column IDNUM is used as the key.

66

Chapter 1

SQL Query Window
120495

Joining Tables
If the WHERE clause is omitted, all combinations of rows from all tables are matched. For example, if tables ONE and TWO listed below are joined without a WHERE clause, the result is a Cartesian product (all possible combinations of rows).

Table ONE X 1 2 4 A a b d

Table TWO Y 2 3 5 B x y v

Result of a Join without a WHERE Clause X 1 1 1 2 2 2 4 4 4 A a a a b b b d d d Y 2 3 5 2 3 5 2 3 5 B x y v x y v x y v

Result of a Join with the WHERE Clause ONE.X=TWO.Y X 2 A b Y 2 B x

Select WHERE to specify a WHERE clause to join the COMPANY.STAFF and COMPANY.PAYROLL tables.

Summarizing and Joining Tables
120495

67

Joining Tables
The IDNUM column appears in both COMPANY.STAFF and COMPANY.PAYROLL and can be used to join the two tables. To build the appropriate WHERE clause, select 1. PAYROLL.IDNUM 2. Operators
AND IN OK LOOKUP distinct values FA1 , FA2 , FA3 , EQ STAFF.IDNUM

3. PAYROLL.JOBCODE PT1 , PT2 , and PT3 4. Operators 5. PAYROLL.SEX
AND EQ

<PROMPT at run-time>

Specifying Join Criteria
<PROMPT at run-time> implies that the user is prompted to supply a value

when the query is executed.

68

Chapter 1

SQL Query Window
120495

Joining Tables
The prompt string is a text string that appears when the query executes to help the user enter the correct information. Type Please Enter M or F in the Prompt String: field.

Specifying a Prompt String Select OK to close this window.

Summarizing and Joining Tables
120495

69

Joining Tables
Notice the &PROMPT1 variable in the WHERE clause. &PROMPT1 is replaced with the value supplied at run time (either M or F).

Final WHERE Clause Select OK to close the WHERE EXPRESSION window.

70

Chapter 1

SQL Query Window
120495

Joining Tables
Select Run Query
Run Immediate to execute the query.

The Prompt window appears and requests a value. Type F in the Prompt window.

Prompt Window Select OK .

Summarizing and Joining Tables
120495

71

Joining Tables
The result of the query is displayed in the OUTPUT window.

Final Report Select File
End to return to the SQL QUERY COLUMNS window.

72

Chapter 1

SQL Query Window
120495

Viewing Query Code
All the clauses that make up the query can be viewed at any time by selecting Show Query . . . .

Viewing the Query You can view the query from the pull-down menus by selecting Actions Show Query . . . .

Summarizing and Joining Tables
120495

73

Viewing Query Code
From the SQL QUERY window, selecting
Run Query Save Query Include Query Create View Create Table

executes the query. saves the query. includes a previously saved query. creates an SQL view. creates a table (SAS data file).

SQL QUERY Window Example: Save the current query in the COMPANY.PROFILE catalog, and specify the entry name COMBINED and an appropriate description.
Save as QUERY to Include later . . . to save the query

Select Save Query for later use.

74

Chapter 1

SQL Query Window
120495

Saving Your Query
Queries are stored as entries in a SAS catalog. A SAS catalog entry is referenced with a four-level naming convention, libref.catalog.entry-name.entry-type. When saving a query, you must specify the libref.catalog.entry-name. The entry-type of QUERY is added automatically. The default storage location is the SASUSER.PROFILE catalog. You can specify a different storage location using the Library , Catalog Name , and Entry Name pushbuttons.

Saving the Query

Summarizing and Joining Tables
120495

75

Saving Your Query
Select Library Type 1. combined in the Entry Name field 2. Combined Staff and Payroll Query in the Description field.
COMPANY OK .

Saving the Query Select OK to save the query. A saved query can only be executed from within the QUERY window. To access or execute a query from outside of the QUERY window, save the query as a SOURCE entry in a catalog or in an external file.

76

Chapter 1

SQL Query Window
120495

Saving Your Query
Example: Save the current query in the external file named /dept/edu/student/join.sas.

To save the current query in an external file, select
File Save Query Save as External File . . . .

Saving Query to an External File

Summarizing and Joining Tables
120495

77

Saving Your Query
The Save As External File . . . window opens. Type join.sas in the Selection field. Select OK to save the file.

Specifying an External File External filenames and the window in which you specify them vary among operating systems.

78

Chapter 1

SQL Query Window
120495

Including a Saved Query
Example: Include the saved query COMPANY.PROFILE.COMBINED.QUERY and display it in the SQL QUERY window.
List/Include Saved Queries . . . to include a saved query.

Select File

Including a Query

Summarizing and Joining Tables
120495

79

Including a Saved Query
Select 1. COMPANY from the Libraries list box 2. OK to close the window.

Selecting a Library

80

Chapter 1

SQL Query Window
120495

Including a Saved Query
Select 1. the saved query COMPANY.PROFILE.COMBINED 2. Include .

Including a Query You can also include the query by double-clicking on COMPANY.PROFILE.COMBINED.

Summarizing and Joining Tables
120495

81

Including a Saved Query
Select Clear Query in Progress before Including
OK .

Clearing the Previous Query

82

Chapter 1

SQL Query Window
120495

Including a Saved Query
Select Actions
Show Query . . . to view the query.

Showing the Query The query is displayed.

Query Code

Summarizing and Joining Tables
120495

83

Exercises
1.4 Joining Tables Reset the query by selecting 1. Actions
Reset from the SQL QUERY COLUMNS window

2. OK to verify that you want to clear everything. Create a report that displays the name and birthday of each employee. The report should include the FNAME and LNAME columns from the COMPANY.STAFF table and the BIRTH column from the COMPANY.PAYROLL table. The two tables should be joined where the values of IDNUM are equal. 1.5 Prompting for Information a. Alter the previous query so that the user can provide a last name at run time to subset the report for a particular employee. Alter the WHERE expression in your query to check where LNAME equals a run-time prompt. b. Execute the query and request a report for UPCHURCH. Then generate reports for other employees (such as BOYCE, WOOD, or DEAN).

84

Chapter 1

SQL Query Window
120495

1.4

SQL Views

Objectives
After completing this section, you should be able to create and use SQL views.

SQL Views
120495

85

Creating a View
An SQL view is a stored query. It contains no rows of data. can be used in SAS programs as a SAS data set. The only limitation is that the view is read-only. extracts underlying data when used, thus accessing the most current data. Example: Create a view named COMPANY.FA_PT using the current query. Retrieve only male flight attendants and pilots.

Select Create View from the SQL QUERY window. Select Library:
COMPANY OK . Type FA_PT in the View: field.

Naming the View Select OK to close the window.

86

Chapter 1

SQL Query Window
120495

Creating a View
Since the query had ‘‘Prompt at Run Time’’ as part of the WHERE expression, a prompt box appears. Type M in the prompt field. Select OK to close the window.

Specifying Subsetting Criteria

SQL Views
120495

87

Creating a View
A message displays in the SQL QUERY window confirming the creation of the view COMPANY.FA_PT.

Creation Confirmation The COMPANY.FA_PT data set is now the active data set.

88

Chapter 1

SQL Query Window
120495

Exiting the Query Window
Select File
Exit to begin the exit process.

Exiting the Query Window

SQL Views
120495

89

Exiting the Query Window
Select OK in the prompter box to confirm your exit choice.

Exiting the Query Window

90

Chapter 1

SQL Query Window
120495

Exiting the Query Window
Select Main menu to return to the SAS/ASSIST Primary Menu.

Returning to SAS/ASSIST

SQL Views
120495

91

Using an SQL View
Example: Use the COMPANY.FA_PT view to create a high resolution bar chart that displays the average salary for flight attendant and pilot job codes.

Select GRAPHICS from the Primary Menu.

SAS/ASSIST Main Menu

92

Chapter 1

SQL Query Window
120495

Using an SQL View
Select High resolution . . . if SAS/GRAPH software is installed and your device supports high resolution graphics. Otherwise, select Low resolution . . . .

Graphics Window

SQL Views
120495

93

Using an SQL View
Select BAR CHARTS .

SAS/ASSIST High Resolution Graphics Menu Select Horizontal bar charts . . . .

Bar Charts Window

94

Chapter 1

SQL Query Window
120495

Using an SQL View
Select Simple from the Horizontal Bar Charts window.

Selecting a Chart Type Select Chart variable: .

Creating a Bar Chart

SQL Views
120495

95

Using an SQL View
Select JOBCODE from the Variables window.

Selecting a Chart Variable

96

Chapter 1

SQL Query Window
120495

Using an SQL View
Select 1. Bar values: 2. Mean from the list of statistics 3. Analysis variable:
SALARY

4. OK to close the Bar/Slice Values window.

Specifying Statistics Select Run to generate the bar chart.

SQL Views
120495

97

Using an SQL View
The horizontal bar chart is displayed.

Final Bar Chart

98

Chapter 1

SQL Query Window
120495

Exercises
1.6 Saving a Query a. Edit the query created in the previous exercise to remove the run-time prompt. b. Save the new query as a QUERY catalog entry to include later. Store your query in SASUSER.PROFILE.BDAY.QUERY. 1.7 Creating a View Create a view named SASUSER.BDAYVIEW using the query created in the previous exercise.

Solutions to Exercises
120495

99

1.5

Solutions to Exercises

Solutions
1.1 Accessing Your Data To access the SQL Query window using SAS/ASSIST software, select 1. REPORT WRITING from the Primary Menu 2. QUERY from the Report Writing Menu 3. SQL Query . . . in the Query window. To assign the libref, 1. select SETUP in the SQL Query window 2. select SAS data libraries in the Setup Selections window 3. select Assign a new libref . . . in the SAS Data Libraries window 4. type company in the Libref field, and type the physical name (directory, file name, or minidisk) that corresponds to the SAS data library 5. select OK and then select Goback three times.

100

Chapter 1

SQL Query Window
120495

Solutions
1.2 Creating a Simple Query Specify the COMPANY.STAFF data set. Select 1. Active data set: in the SQL Query window. 2. COMPANY.STAFF in the Directory window. (You may have to scroll down using the scroll bar or use the FORWARD function key.) 3. Run . Specify the variables to appear in the query. Select 1. * STAFF * <all columns> from the Available Columns list box in the SQL QUERY COLUMNS window 2. to add these columns to the Selected Columns list box.

Execute the query. 1. Select Actions Run Query Run Immediate from the menu bar at the top of the SQL QUERY COLUMNS window. 2. Examine the OUTPUT window to see the report. 3. Select File End from the menu bar in the OUTPUT window to return to the SQL QUERY COLUMNS window. You can also press the END function key.

Solutions to Exercises
120495

101

Solutions
1.3 Subsetting and Ordering a Query a. Reset the query by selecting 1. Actions
Reset from the SQL QUERY COLUMNS window

2. OK to verify that you want to clear everything. b. Specify the COMPANY.FREQ data set. Select 1. COMPANY from the Table Sources list box in the SQL QUERY TABLES window 2. COMPANY.FREQ in the Available Tables list box 3. to add this table to the Selected Tables list box 4. OK . Specify the columns to appear in the report. Select 1. FREQ.NAME , FREQ.FFID , and FREQ.MILETRAV from the Available Columns list box in the SQL QUERY COLUMNS window 2. to add these columns to the Selected Columns list box.

102

Chapter 1

SQL Query Window
120495

Solutions
1.3 Subsetting and Ordering a Query (continued) b. (continued) Construct a WHERE clause to include only passengers who traveled more than 55,000 miles. Select 1. Locals
Where Conditions . . .

2. FREQ.MILETRAV from the Available Columns list box in the WHERE EXPRESSION window 3. GE in the pop-up window that opens 4. <CONSTANT enter value> from the Available Columns list box and type 55000 in the field 5. OK 6. OK to close the WHERE EXPRESSION window. Execute the query. Examine the output and then close the OUTPUT window. c. Specify that you want to sort the query. 1. Select Locals 3. Select OK . Execute the query. Examine the output generated and then close the OUTPUT window. d. Create a column that corresponds to the bonus points remaining for each passenger. 1. Select Build a Column in the SQL QUERY COLUMNS window. 2. Select FREQ.PTSEARND from the Available Columns list box. 3. Select in the pop-up window. 4. Select FREQ.PTSUSED from the Available Columns list box. 5. Select Column Attributes . 6. Type ptsrmng in the Alias Name field and Points Remaining in the Label field. 7. Select OK twice. Execute the query. Examine the output and close the OUTPUT window. Reset the query by following Exercise 1.3.a.
Order By . . . .

2. Select FREQ.NAME and

in the ORDER BY COLUMNS window.

Solutions to Exercises
120495

103

Solutions
1.4 Joining Tables Specify the two tables. 1. Select COMPANY from the Table Sources list box. 2. Double-click on COMPANY.STAFF in the Available Tables list box. (This should add this table to the Selected Tables list box). 3. Repeat step 2 for COMPANY.PAYROLL. 4. Select OK . Specify the columns to appear in the report. Select 1. LNAME , FNAME , BIRTH 2. (there should be three columns in the Selected Columns list box).

Construct a WHERE clause. To execute the query, select 1. Actions
Run Query Run Immediate .

2. WHERE in the warning window that opens. (If you do not specify the WHERE clause, all combinations of rows from the six tables would be matched.) To specify the WHERE clause, select 1. STAFF.IDNUM 2. EQ in the pop-up window 3. SCHEDULE.IDNUM 4. OK to close the WHERE EXPRESSION window. Execute the query by selecting
Actions Run Query Run Immediate .

Examine the generated output and close the OUTPUT window.

104

Chapter 1

SQL Query Window
120495

Solutions
1.5 Prompting for Information a. Edit the WHERE clause. Select 1. Locals
Where Conditions . . . .

2. Operators and AND . 3. LNAME , EQ , and PROMPT at <run-time > . Type employee last name and select OK in the Enter a String for this Prompt window. 4. OK to close the WHERE EXPRESSION window. b. Execute the query. 1. Select Actions
Run Query Run Immediate .

2. Type UPCHURCH for the last name in the window that opens and select OK . 3. Examine the report and select File QUERY COLUMNS window. 1.6 Saving a Query a. Edit the WHERE clause and remove the two prompts at run time. Select 1. Locals
Where Conditions . . . End to return to the SQL

2. UNDO four times to remove this part of the WHERE clause: AND
STAFF.LNAME EQ &PROMPT1

3. OK to specify the modified WHERE clause. b. Save the query. 1. Select File
Save Query Save as QUERY to Include later .

2. Type bday in the Entry Name field. 3. Type a description in the Enter a description for query field. 4. Select OK to save the query (PROC SQL statements) as the SASUSER.PROFILE.BDAY.QUERY SAS file.

Solutions to Exercises
120495

105

Solutions
1.7 Creating a View 1. Select File 2. Select 3. Select SASUSER
Create View of Query . . . .

next to the Library field.
OK to confirm your choice of library.

4. Type BDAYVIEW in the View field. 5. Select OK to create the view (a dynamic file containing the joined tables).

106

Chapter 1

SQL Query Window
120495

120495

Index
&
&PROMPT1 variable WHERE clause 69

I
including saved queries 78-82

J A
accessing data SQL Query window 5 adding titles 55-57 TITLES window 56 AVG function 49-50 joining tables 60-71 Cartesian product 66 SQL join 60 WHERE clause 65-70

L
labeling columns 21-22 libraries selecting 12

B
bar charts SQL views 92-97

M C
Cartesian product joining tables 66 columns creating 23-31 formatting 29, 32 labeling 21-22 moving 16 selecting 15-20 creating columns 23-31 SQL views 85-87 SQL views, WHERE expressions summary reports 47-52 WHERE expressions 33-40 MAX function 49-50 moving columns 16

N
NUMBER option 53-54

O
ordering reports 41-44 OUTPUT window 19 86

P
prompt string queries 68

D
data accessing 5 sorting 41-44 subsetting 33-40 DATE option 53-54

Q
queries executing 17 including 78-82 prompt string 68 resetting 58-59 saving 74-77 query code viewing 72-73 QUERY Window resetting 58-59

E
executing queries 17

F
formatting columns 29 FORMAT= option 32

R
reports ordering 41-44 resetting queries 58-59

G
graphics SQL views 91-97 GROUP BY clause 50

108 S

Index
120495

SAS options setting 53-54 saving queries 74-77 SOURCE entries 75 selecting columns 15-20 libraries 12 tables 12-14 setting SAS options 53-54 sorting data 41-44 SQL QUERY COLUMNS window SQL QUERY window 73 accessing 6-10 SQL views bar charts 92-97 creating 85-87 graphics 91-97 subsetting data 33-40 summary reports creating 47-52

15

T
tables joining 60-71 selecting 12-14 titles adding 55-57 TITLES window adding titles 56

V
viewing query code 72-73

W
WHERE clause &PROMPT1 variable 69 joining tables 65-70 WHERE expressions creating 33-40 creating SQL views 86


				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:39
posted:5/18/2009
language:English
pages:121