Proc SQL User's Guide.pdf
(
1112 KB
)
Pobierz
SAS 9.1 SQL Procedure: User's Guide
SAS
®
9.1
SQL Procedure
User’s Guide
The correct bibliographic citation for this manual is as follows: SAS Institute Inc., 2004.
SAS
®
9.1 SQL Procedure User’s Guide
. Cary, NC: SAS Institute Inc.
SAS
®
9.1 SQL Procedure User’s Guide
Copyright © 2004, SAS Institute Inc., Cary, NC, USA.
ISBN 1-59047-334-5
All rights reserved. Produced 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.
U.S. Government Restricted Rights Notice.
Use, duplication, or disclosure of this
software and related documentation by the U.S. government is subject to the Agreement
with SAS Institute and the restrictions set forth in FAR 52.227–19 Commercial Computer
Software-Restricted Rights (June 1987).
SAS Institute Inc., SAS Campus Drive, Cary, North Carolina 27513.
1st printing, January 2004
SAS Publishing provides a complete selection of books and electronic products to help
customers use SAS software to its fullest potential. For more information about our
e-books, e-learning products, CDs, and hard-copy books, visit the SAS Publishing Web site
at
support.sas.com/publishing
or call 1-800-727-3228.
SAS
®
and all other SAS Institute Inc. product or service names are registered trademarks
or trademarks of SAS Institute Inc. in the USA and other countries.
registration.
Other brand and product names are registered trademarks or trademarks of their
respective companies.
®
indicates USA
Contents
Chapter 1
Introduction to the SQL Procedure
1
What Is SQL?
1
What Is the SQL Procedure?
1
Terminology
2
Comparing PROC SQL with the SAS DATA Step
3
Notes about the Example Tables
4
Chapter 2
Retrieving Data from a Single Table
11
Overview of the SELECT Statement
12
Selecting Columns in a Table
14
Creating New Columns
18
Sorting Data
25
Retrieving Rows That Satisfy a Condition
30
Summarizing Data
39
Grouping Data
45
Filtering Grouped Data
50
Validating a Query
52
Chapter 3
Retrieving Data from Multiple Tables
55
Introduction
56
Selecting Data from More Than One Table by Using Joins
56
Using Subqueries to Select Data
74
When to Use Joins and Subqueries
80
Combining Queries with Set Operators
81
Chapter 4
Creating and Updating Tables and Views
89
Introduction
90
Creating Tables
90
Inserting Rows into Tables
93
Updating Data Values in a Table
96
Deleting Rows
98
Altering Columns
99
Creating an Index
102
Deleting a Table
103
Using SQL Procedure Tables in SAS Software
103
Creating and Using Integrity Constraints in a Table
103
Creating and Using PROC SQL Views
105
Chapter 5
Programming with the SQL Procedure
111
Introduction
111
Using PROC SQL Options to Create and Debug Queries
112
Improving Query Performance
115
iv
Accessing SAS System Information Using DICTIONARY Tables
117
Using PROC SQL with the SAS Macro Facility
120
Formatting PROC SQL Output Using the REPORT Procedure
127
Accessing a DBMS with SAS/ACCESS Software
128
Using the Output Delivery System (ODS) with PROC SQL
132
Chapter 6
Practical Problem-Solving with PROC SQL
133
Overview
134
Computing a Weighted Average
134
Comparing Tables
136
Overlaying Missing Data Values
138
Computing Percentages within Subtotals
140
Counting Duplicate Rows in a Table
141
Expanding Hierarchical Data in a Table
143
Summarizing Data in Multiple Columns
144
Creating a Summary Report
146
Creating a Customized Sort Order
148
Conditionally Updating a Table
150
Updating a Table with Values from Another Table
153
Creating and Using Macro Variables
154
Using PROC SQL Tables in Other SAS Procedures
157
Appendix 1
Recommended Reading
161
Recommended Reading
161
Glossary
163
Index
167
1
CHAPTER
1
Introduction to the SQL
Procedure
What Is SQL?
1
What Is the SQL Procedure?
1
Terminology
2
Tables
2
Queries
2
Views
2
Null Values
3
Comparing PROC SQL with the SAS DATA Step
3
Notes about the Example Tables
4
What Is SQL?
Structured Query Language (SQL) is a standardized, widely used language that
retrieves and updates data in relational tables and databases.
A
relation
is a mathematical concept that is similar to the mathematical concept of a
set. Relations are represented physically as two-dimensional tables that are arranged
in rows and columns. Relational theory was developed by E. F. Codd, an IBM
researcher, and rst implemented at IBM in a prototype called System R. This
prototype evolved into commercial IBM products based on SQL. The Structured Query
Language is now in the public domain and is part of many vendors’ products.
What Is the SQL Procedure?
The SQL procedure is SAS’ implementation of Structured Query Language. PROC
SQL is part of Base SAS software, and you can use it with any SAS data set (table).
Often, PROC SQL can be an alternative to other SAS procedures or the DATA step. You
can use SAS language elements such as global statements, data set options, functions,
informats, and formats with PROC SQL just as you can with other SAS procedures.
PROC SQL can
generate reports
generate summary statistics
retrieve data from tables or views
combine data from tables or views
create tables, views, and indexes
update the data values in PROC SQL tables
update and retrieve data from database management system (DBMS) tables
Plik z chomika:
jjn
Inne pliki z tego folderu:
SAS Publishing Basic Statistics Using SAS Enterprise Guide A Primer Nov 2007.pdf
(2604 KB)
SAS data mining - Enterprise Miner.pdf
(789 KB)
wprowadzenie do Enterprise Miner.pdf
(1278 KB)
Using SAS for Data Management, Statistical Analysis, and Graphics Ken Kleinman and Nicholas J. Horton.rar
(12910 KB)
Macro Language Reference.pdf
(2232 KB)
Inne foldery tego chomika:
♦ Inne
♦ Legia Cudzoziemska
♦ SAS
♦ Specnaz
CZARNOBYL
Zgłoś jeśli
naruszono regulamin