Browse Prior Art Database

PROC SQL Beyond the Basics using SAS Disclosure Number: IPCOM000243397D
Original Publication Date: 2004-Dec-31
Included in the Prior Art Database: 2015-Sep-19
Document File: 384 page(s) / 1M

Publishing Venue

SAS Institute Inc.

This text was extracted from a PDF file.
This is the abbreviated version, containing approximately 3% of the total text.

Page 01 of 384


Beyond the Basics Using SAS®

Kirk Paul Lafler

Page 02 of 384

The correct bibliographic citation for this manual is as follows: Lafler, Kirk Paul. 2004. PROC SQL: Beyond the Basics Using SAS®. Cary, NC: SAS Institute Inc.

PROC SQL: Beyond the Basics Using SAS®

Copyright © 2004, SAS Institute Inc., Cary, NC, USA

ISBN: 978-1-59047-534-8

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, September 2004

2nd printing, March 2006

3rd printing, December 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 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.

Page 03 of 384


Preface vii

Dedication xi
Acknowledgments xiii

Chapter 1 Designing Database Tables 1

1.1 Introduction 2
1.2 Database Design 2
1.3 Column Names and Reserved Words 8
1.4 Data Integrity 9
1.5 Database Tables Used in This Book 10
1.6 Table Contents 13
1.7 Summary 21

Chapter 2 Working with Data in PROC SQL

2.1 Introduction 24
2.2 Data Types Overview 24
2.3 SQL Operators and Functions 32
2.4 Dictionary Tables 70
2.5 Summary 83

Chapter 3 Formatting Output

3.1 Introduction 86
3.2 Formatting Output 86
3.3 Formatting Output with the Output Delivery

System 101
3.4 Summary 107

Page 04 of 384

iv Contents

Chapter 4 Coding PROC SQL Logic

4.1 Introduction 110
4.2 Conditional Logic 110
4.3 CASE Expressions 112
4.4 Interfacing PROC SQL with the Macro Language 121
4.5 Summary 135

Chapter 5 Creating, Populating, and Deleting

5.1 Introduction 138
5.2 Creating Tables 138
5.3 Populating Tables 147
5.4 Integrity Constraints 156
5.5 Deleting Rows in a Table 171
5.6 Deleting Tables 174
5.7 Summary 178

Chapter 6 Modifying and Updating Tables and

6.1 Introduction 180
6.2 Modifying Tables...