You are previewing Combining and Modifying SAS® Data Sets: Examples Second Edition.
O'Reilly logo
Combining and Modifying SAS® Data Sets: Examples Second Edition

Book Description

Building on the popularity of the first edition, Michele Burlew has revised this popular examples book to include expanded content and new features of SAS software. Completely updated for SAS 9.2, Combining and Modifying SAS Data Sets: Examples, Second Edition, presents examples that show solutions to common programming tasks that involve combining, modifying, and reshaping data sets. Expanded examples demonstrate how to combine data sets vertically and horizontally; retrieve data from lookup tables; modify and update data sets; combine summary and detail data sets; reshape and transpose observations in a data set; and manipulate data in a data set with utilities and functions. The tools used to combine and modify data sets include the SET, MERGE, MODIFY, and UPDATE statements in the DATA step; joins and set operators in PROC SQL; BY-group processing; indexes; hash objects in the DATA step; the use of PROC FORMAT and hash tables as table lookups; and generation data sets. Unique features of this book include the following: Examples are grouped by task, not by code, so you can easily find a solution to a particular task; alternative solutions are presented in addition to the main examples; most examples that combine and modify data sets include both a DATA step and a PROC SQL solution; many examples include a "Closer Look" section that describes in-depth how the example helps you complete the task; and each example stands on its own so you do not need to read the book from beginning to end. Designed for SAS programmers at all levels, this examples book will help simplify the challenging task of combining and modifying data sets.

Table of Contents

  1. Copyright
  2. Acknowledgments
  3. About This Book
    1. Purpose
    2. Is This Book for You?
    3. What's New in This Edition
    4. Scope of This Book
    5. Programs and Data Sets Used in This Book
    6. Author Pages
    7. Additional Resources
    8. Comments or Questions?
    9. SAS Publishing News
  4. Introducing Data Relationships, Techniques for Data Manipulation, and Access Methods
    1. Overview
    2. Determining Data Relationships
    3. Understanding the Methods for Combining SAS Data Sets
    4. Understanding Access Methods: Sequential versus Direct
    5. Understanding the Tools for Combining SAS Data Sets
    6. Understanding the Tools for Processing Information in Groups
    7. Choosing between the DATA Step and PROC SQL
    8. Choosing between MODIFY and UPDATE
  5. Combining Data Sets Vertically: Concatenating, Interleaving, and Appending Data Sets
    1. Example 2.1 Concatenating Data Sets
    2. Example 2.2 Interleaving Observations from Two or More Data Sets Based on a Common Variable
    3. Example 2.3 Appending One Data Set to the End of Another Data Set
    4. Example 2.4 Selecting Unique Rows When Concatenating Tables
    5. Example 2.5 Selecting Rows in Common When Concatenating Tables
    6. Example 2.6 Selecting Observations Unique to Each Data Set When Concatenating Data Sets
  6. Combining Data Sets Horizontally: Match-Merging Data Sets by Value
    1. Example 3.1 Merging Data Sets by a Common Variable
    2. Example 3.2 Merging Observations from Multiple Data Sets by a Common Variable
    3. Example 3.3 Combining Observations When Variable Values Do Not Match Exactly
    4. Example 3.4 Combining Observations by the Formatted Value of a Variable
    5. Example 3.5 Combining Multiple Tables When the Matching Column Has Different Attributes
    6. Example 3.6 Combining Rows When There Is No Common Column
    7. Example 3.7 Matching Observations Randomly
    8. Example 3.8 Combining Multiple Data Sets without a Variable Common to All the Data Sets
    9. Example 3.9 Generating Every Combination of Rows (Cartesian Product) between Tables
    10. Example 3.10 Generating Every Combination of Rows between Tables Based on a Common Column
    11. Example 3.11 Generating Every Combination of Observations between Data Sets Based on a Common Variable When an Index Is Available
    12. Example 3.12 Combining and Collapsing Observations Based on a Common Variable
    13. Example 3.13 Combining and Collapsing Observations Based on a Common Variable When the Transaction Data Set Is Indexed
  7. Using Lookup Tables to Match Data
    1. Example 4.1 Performing a Simple Table Lookup
    2. Example 4.2 Performing a Table Lookup in a Small Lookup Data Set
    3. Example 4.3 Performing a Table Lookup in a Large, Non-Indexed Lookup Data Set
    4. Example 4.4 Performing Multiple Lookups for Each Observation in a Data Set
    5. Example 4.5 Performing a Table Lookup When the Lookup Data Set Is Indexed
    6. Example 4.6 Performing a "Chained" Lookup
  8. Combining Summary and Detail Data
    1. Example 5.1 Adding Values to All Observations in a Data Set
    2. Example 5.2 Adding Values from the Last Observation in a Data Set to All Observations in a Data Set
    3. Example 5.3 Computing Summary Data and Combining Them with Detail Data
    4. Example 5.4 Subsetting a Table Based on the Calculated Average of a Group
    5. Example 5.5 Calculating Totals across a BY Group to Produce Cumulative and Grand Totals
    6. Example 5.6 Calculating Percentages and Statistics That One Row Contributes to a BY Group
  9. Updating Data Sets by Match-Merging by Value
    1. Example 6.1 Updating a Data Set and Controlling Whether Common Variables Are Overwritten with Missing Values
    2. Example 6.2 Updating a Data Set and Allowing Some Values to Be Updated with Missing Values
    3. Example 6.3 Merging Data Sets and Conditionally Overwriting Common Variables
    4. Example 6.4 Adding Observations and Variables to the Master Data Set When Duplicate Matching Variable Values Exist in the Transaction Data Set
    5. Example 6.5 Saving Observations from Only the Master Data Set When the Transaction Data Set Contains Duplicates
  10. Modifying Data Sets in Place
    1. Example 7.1 Modifying All Observations in a Data Set in Place
    2. Example 7.2 Modifying a Non-Indexed Data Set in Place by Matching by a Common Variable
    3. Example 7.3 Modifying an Indexed Master Data Set in Place
    4. Example 7.4 Modifying an Indexed Master Data Set in Place When Both the Master and Transaction Data Sets Contain Duplicate Key Values
  11. Manipulating Data from a Single Source
    1. Example 8.1 Performing a Simple Subset
    2. Example 8.2 Separating Unique Observations from Duplicate Observations Based on BY Values
    3. Example 8.3 Separating Completely Duplicate Observations from Unique Observations
    4. Example 8.4 Separating the First Observation in a BY Group from the Other Observations in the BY Group
    5. Example 8.5 Accessing a Specific Number of Observations from the Beginning and End of a Data Set
    6. Example 8.6 Creating a Customized Sort Order without Adding a New Column to the Table
    7. Example 8.7 Adding New Observations to the End of a Data Set
    8. Example 8.8 Adding Observations to a Data Set Based on the Value of a Variable
    9. Example 8.9 Adding Observations to a SAS Data Set So the Values of a Variable Are Consecutive throughout the BY Group
    10. Example 8.10 Adding Rows to a Table So That All Possible Values of Specific Columns Are Present in Each BY Group
    11. Example 8.11 Expanding Single Observations into Multiple Observations
    12. Example 8.12 Collapsing Observations within a BY Group into a Single Observation
    13. Example 8.13 Obtaining the Previous Value of a Variable within a BY Group
    14. Example 8.14 Comparing the Value of a Variable to Its Value in the Next Observation
    15. Example 8.15 Applying the Same Operation to a Group of Variables
    16. Example 8.16 Obtaining Hierarchical Data from a Table and Matching Them to the Rows in the Same Table
    17. Example 8.17 Combining Generation Data Sets
  12. Manipulating Data with Utilities and Functions
    1. Example 9.1 Converting Variable Types from Character to Numeric and Vice Versa
    2. Example 9.2 Determining the Type of a Variable's Content
    3. Example 9.3 Determining Whether a Variable Is Character or Numeric
    4. Example 9.4 Specifying a Numeric or Character Format at Run Time
    5. Example 9.5 Creating Columns That Contain the Attributes of Other Columns
    6. Example 9.6 Sorting Variable Values within an Observation
    7. Example 9.7 Shifting Nonmissing Values Left in an Observation
    8. Example 9.8 Generating Random Numbers within a Range of Values
    9. Example 9.9 Selecting Observations at Random from a Data Set without Replacement
    10. Example 9.10 Selecting Equal-Sized Samples from Different Groups
    11. Example 9.11 Creating SAS Datetime Values and Computing the Difference between Two Datetime Values
    12. Example 9.12 Creating a SAS Time Value from a Character Value
    13. Example 9.13 Calculating a Person's Age
    14. Example 9.14 Incrementing a Date by an Interval
    15. Example 9.15 Determining the Number of U.S. Business Days between Two Dates
    16. Example 9.16 Counting the Occurrences of a String
    17. Example 9.17 Extracting a Character String without Breaking the Text in the Middle of a Word
    18. Example 9.18 Cleaning Character Data Values
    19. Example 9.19 Validating and Standardizing Complex Character Data