SAS
®
#ERTIFICATION0REP'UIDE
Advanced Programming for SAS
®
9
The correct bibliographic citation for this manual is as follows: SAS Institute Inc. 2007.
SAS
®
Certification Prep Guide: Advanced Programming for SAS
®
9. Cary, NC: SAS
Institute Inc.
SAS
®
Certification Prep Guide: Advanced Programming for SAS
®
9
Copyright © 2002–2007, SAS Institute Inc., Cary, NC, USA
978-1-59994-559-0
All rights reserved. Produced in the United States of America.
For a hard-copy book: 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.
For a Web download or e-book: Your use of this publication shall be governed by the
terms established by the vendor at the time you acquire this publication.
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, November 2007
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.
®
indicates USA
registration.
Other brand and product names are registered trademarks or trademarks of their
respective companies.
Contents
About This Book and CD xi
What’s New xi
Purpose xi
Audience xi
Prerequisites xi
How to Use This Book and CD
xii
Syntax Conventions for This Book xii
SAS Certification Practice Exam: Advanced Programming for SAS 9
xiii
SAS Advanced Programming Exam for SAS 9 xiii
Additional Resources xiii
PART1 SQL Processing With SAS 1
Chapter 1
4
Performing Queries Using PROC SQL 3
Overview 4
PROC SQL Basics 4
Writing a PROC SQL Step
6
Selecting Columns 8
Specifying the Table 10
Specifying Subsetting Criteria 10
Ordering Rows 10
Querying Multiple Tables 12
Summarizing Groups of Data 16
Creating Output Tables 17
Additional Features 18
Summary
19
Quiz 21
Chapter 2
4
Performing Advanced Queries Using PROC SQL 25
Overview 27
Viewing SELECT Statement Syntax 28
Displaying All Columns 29
Limiting the Number of Rows Displayed
30
Eliminating Duplicate Rows from Output 31
Subsetting Rows by Using Conditional Operators 32
Subsetting Rows by Using Calculated Values 40
Enhancing Query Output 42
Summarizing and Grouping Data 47
Subsetting Data by Using Subqueries 58
Subsetting Data by Using Noncorrelated Subqueries 60
Subsetting Data by Using Correlated Subqueries 65
iv
Validating Query Syntax 67
Additional Features
69
Summary
70
Quiz 73
Chapter 3
4
Combining Tables Horizontally Using PROC SQL 79
Overview 80
Understanding Joins 81
Generating a Cartesian Product 81
Using Inner Joins 83
Using Outer Joins 91
Creating an Inner Join with Outer Join-Style Syntax 97
Comparing SQL Joins and DATA Step Match-Merges 97
Using In-Line Views 102
Joining Multiple Tables and Views 106
Summary 113
Quiz 116
Chapter 4
4
Combining Tables Vertically Using PROC SQL 125
Overview 126
Understanding Set Operations 127
Using the EXCEPT Set Operator 132
Using the INTERSECT Set Operator 138
Using the UNION Set Operator 142
Using the OUTER UNION Set Operator 146
Comparing Outer Unions and Other SAS Techniques 149
Summary 151
Quiz 152
Chapter 5
4
Creating and Managing Tables Using PROC SQL 159
Overview 161
Understanding Methods of Creating Tables
162
Creating an Empty Table by Defining Columns 163
Displaying the Structure of a Table 168
Creating an Empty Table That Is Like Another Table 169
Creating a Table from a Query Result 172
Inserting Rows of Data into a Table 174
Creating a Table That Has Integrity Constraints 182
Handling Errors in Row Insertions
190
Displaying Integrity Constraints for a Table 193
Updating Values in Existing Table Rows 194
Deleting Rows in a Table 202
Altering Columns in a Table 204
Dropping Tables 210
Summary 211
Quiz 217
