Contents

About This Book

Acknowledgements

About the Author

Part 1 Data Preparation

Chapter 1 Moving, Copying, Importing, and Exporting Data

1.1  LIBNAME Statement Engines

1.1.1  Using Data Access Engines to Read and Write Data

1.1.2  Using the Engine to View the Data

1.1.3  Options Associated with the Engine

1.1.4  Replacing EXCEL Sheets

1.1.5  Recovering the Names of EXCEL Sheets

1.2  PROC IMPORT and EXPORT

1.2.1  Using the Wizard to Build Sample Code

1.2.2  Control through the Use of Options

1.2.3  PROC IMPORT Data Source Statements

1.2.4  Importing and Exporting CSV Files

1.2.5  Preventing the Export of Blank Sheets

1.2.6  Working with Named Ranges

1.3  DATA Step INPUT Statement

1.3.1  Format Modifiers for Errors

1.3.2  Format Modifiers for the INPUT Statement

1.3.3  Controlling Delimited Input

1.3.4  Reading Variable-Length Records

1.4  Writing Delimited Files

1.4.1  Using the DATA Step with the DLM= Option

1.4.2  PROC EXPORT

1.4.3  Using the %DS2CSV Macro

1.4.4  Using ODS and the CSV Destination

1.4.5  Inserting the Separator Manually

1.5  SQL Pass-Through

1.5.1  Adding a Pass-Through to Your SQL Step

1.5.2  Pass-Through Efficiencies

1.6  Reading and Writing to XML

1.6.1  Using ODS

1.6.2  Using the XML Engine

Chapter 2 Working with Your Data

2.1 Data Set Options

2.1.1  REPLACE and REPEMPTY

2.1.2  Password Protection

2.1.3  KEEP, DROP, and RENAME Options

2.1.4  Observation Control Using FIRSTOBS and OBS Data Set Options

2.2  Evaluating Expressions

2.2.1  Operator Hierarchy

2.2.2  Using the Colon as a Comparison Modifier

2.2.3  Logical and Comparison Operators in Assignment Statements

2.2.4  Compound Inequalities

2.2.5  The MIN and MAX Operators

2.2.6  Numeric Expressions and Boolean Transformations

2.3  Data Validation and Exception Reporting

2.3.1  Date Validation

2.3.2  Writing to an Error Data Set

2.3.3  Controlling Exception Reporting with Macros

2.4  Normalizing - Transposing the Data

2.4.1  Using PROC TRANSPOSE

2.4.2  Transposing in the DATA Step

2.5  Filling Sparse Data

2.5.1  Known Template of Rows

2.5.2  Double Transpose

2.5.3  Using COMPLETYPES with PROC MEANS or PROC SUMMARY

2.5.4  Using CLASSDATA

2.5.5  Using Preloaded Formats

2.5.6  Using the SPARSE Option with PROC FREQ

2.6  Some General Concepts

2.6.1  Shorthand Variable Naming

2.6.2  Understanding the ORDER= Option

2.6.3  Quotes within Quotes within Quotes

2.6.4  Setting the Length of Numeric Variables

2.7  WHERE Specifics

2.7.1  Operators Just for the WHERE

2.7.2  Interaction with the BY Statement

2.8  Appending Data Sets

2.8.1  Appending Data Sets Using the DATA Step and SQL UNION

2.8.2  Using the DATASETS Procedure’s APPEND Statement

2.9  Finding and Eliminating Duplicates

2.9.1  Using PROC SORT

2.9.2  Using FIRST. and LAST. BY-Group Processing

2.9.3  Using PROC SQL

2.9.4  Using PROC FREQ

2.9.5  Using the Data Component Hash Object

2.10  Working with Missing Values

2.10.1  Special Missing Values

2.10.2  MISSING System Option

2.10.3  Using the CMISS, NMISS, and MISSING Functions

2.10.4  Using the CALL MISSING Routine

2.10.5  When Classification Variables Are Missing

2.10.6  Missing Values and Macro Variables

2.10.7  Imputing Missing Values

Chapter 3 Just In the DATA Step

3.1  Working across Observations

3.1.1  BY-Group Processing—Using FIRST. and LAST. Processing

3.1.2  Transposing to ARRAYs

3.1.3  Using the LAG Function

3.1.4  Look-Ahead Using a MERGE Statement

3.1.5  Look-Ahead Using a Double SET Statement

3.1.6  Look-Back Using a Double SET Statement

3.1.7  Building a FIFO Stack

3.1.8  A Bit on the SUM Statement

3.2  Calculating a Person’s Age

3.2.1  Simple Formula

3.2.2  Using Functions

3.2.3  The Way Society Measures Age

3.3  Using DATA Step Component Objects

3.3.1  Declaring (Instantiating) the Object

3.3.2  Using Methods with an Object

3.3.3  Simple Sort Using the HASH Object

3.3.4  Stepping through a Hash Table

3.3.5  Breaking Up a Data Set into Multiple Data Sets

3.3.6  Hash Tables That Reference Hash Tables

3.3.7  Using a Hash Table to Update a Master Data Set

3.4  Doing More with the INTNX and INTCK Functions

3.4.1  Interval Multipliers

3.4.2  Shift Operators

3.4.3  Alignment Options

3.4.4  Automatic Dates

3.5  Variable Conversions

3.5.1  Using the PUT and INPUT Functions

3.5.2  Decimal, Hexadecimal, and Binary Number Conversions

3.6  DATA Step Functions

3.6.1  The ANY and NOT Families of Functions

3.6.2  Comparison Functions

3.6.3  Concatenation Functions

3.6.4  Finding Maximum and Minimum Values

3.6.5  Variable Information Functions

3.6.6  New Alternatives and Functions That Do More

3.6.7  Functions That Put the Squeeze on Values

3.7  Joins and Merges

3.7.1  BY Variable Attribute Consistency

3.7.2  Variables in Common That Are Not in the BY List

3.7.3  Repeating BY Variables

3.7.4  Merging without a Clear Key (Fuzzy Merge)

3.8  More on the SET Statement

3.8.1 Using the NOBS= and POINT= Options

3.8.2  Using the INDSNAME= Option

3.8.3  A Comment on the END= Option

3.8.4  DATA Steps with Two SET Statements

3.9  Doing More with DO Loops

3.9.1  Using the DOW Loop

3.9.2  Compound Loop Specifications

3.9.3  Special Forms of Loop Specifications

3.10  More on Arrays

3.10.1  Array Syntax

3.10.2  Temporary Arrays

3.10.3  Functions Used with Arrays

3.10.4  Implicit Arrays

Chapter 4 Sorting the Data

4.1  PROC SORT Options

4.1.1  The NODUPREC Option

4.1.2  The DUPOUT= Option

4.1.3  The TAGSORT Option

4.1.4  Using the SORTSEQ Option

4.1.5  The FORCE Option

4.1.6  The EQUALS or NOEQUALS Options

4.2  Using Data Set Options with PROC SORT

4.3  Taking Advantage of Known or Knowable Sort Order

4.4  Metadata Sort Information

4.5  Using Threads

Chapter 5 Working with Data Sets

5.1  Automating the COMPARE Process

5.2  Reordering Variables on the PDV

5.3  Building and Maintaining Indexes

5.3.1  Introduction to Indexing

5.3.2  Creating Simple Indexes

5.3.3  Creating Composite Indexes

5.3.4  Using the IDXWHERE and IDXNAME Options

5.3.5  Index Caveats and Considerations

5.4  Protecting Passwords

5.4.1  Using PROC PWENCODE

5.4.2  Protecting Database Passwords

5.5  Deleting Data Sets

5.6  Renaming Data Sets

5.6.1  Using the RENAME Function

5.6.2  Using PROC DATASETS

Chapter 6 Table Lookup Techniques

6.1  A Series of IF Statements—The Logical Lookup

6.2  IF -THEN/ELSE Lookup Statements

6.3  DATA Step Merges and SQL Joins

6.4  Merge Using Double SET Statements

6.5  Using Formats

6.6  Using Indexes

6.6.1  Using the BY Statement

6.6.2  Using the KEY= Option

6.7  Key Indexing (Direct Addressing)—Using Arrays to Form a Simple Hash

6.7.1  Building a List of Unique Values

6.7.2  Performing a Key Index Lookup

6.7.3  Using a Non-Numeric Index

6.8  Using the HASH Object

Part 2 Data Summary, Analysis, and Reporting

Chapter 7 MEANS and SUMMARY Procedures

7.1  Using Multiple CLASS Statements and CLASS Statement Options

7.1.1  MISSING and DESCENDING Options

7.1.2  GROUPINTERNAL Option

7.1.3  Order= Option

7.2  Letting SAS Name the Output Variables

7.3  Statistic Specification on the OUTPUT Statement

7.4  Identifying the Extremes

7.4.1  Using the MAXID and MINID Options

7.4.2  Using the IDGROUP Option

7.4.3  Using Percentiles to Create Subsets

7.5  Understanding the _TYPE_ Variable

7.6  Using the CHARTYPE Option

7.7  Controlling Summary Subsets Using the WAYS Statement

7.8  Controlling Summary Subsets Using the TYPES Statement

7.9  Controlling Subsets Using the CLASSDATA= and EXCLUSIVE Options

7.10  Using the COMPLETETYPES Option

7.11  Identifying Summary Subsets Using the LEVELS and WAYS Options

7.12  CLASS Statement vs. BY Statement

Chapter 8 Other Reporting and Analysis Procedures

8.1  Expanding PROC TABULATE

8.1.1  What You Need to Know to Get Started

8.1.2  Calculating Percentages Using PROC TABULATE

8.1.3  Using the STYLE= Option with PROC TABULATE

8.1.4  Controlling Table Content with the CLASSDATA Option

8.1.5  Ordering Classification Level Headings

8.2  Expanding PROC UNIVARIATE

8.2.1  Generating Presentation-Quality Plots

8.2.2  Using the CLASS Statement

8.2.3  Probability and Quantile Plots

8.2.4  Using the OUTPUT Statement to Calculate Percentages

8.3  Doing More with PROC FREQ

8.3.1  OUTPUT Statement in PROC FREQ

8.3.2  Using the NLEVELS Option

8.4  Using PROC REPORT to Better Advantage

8.4.1  PROC REPORT vs. PROC TABULATE

8.4.2  Naming Report Items (Variables) in the Compute Block

8.4.3  Understanding Compute Block Execution

8.4.4  Using a Dummy Column to Consolidate Compute Blocks

8.4.5  Consolidating Columns

8.4.6  Using the STYLE= Option with LINES

8.4.7  Setting Style Attributes with the CALL DEFINE Routine

8.4.8  Dates within Dates

8.4.9  Aligning Decimal Points

8.4.10  Conditionally Executing the LINE Statement

8.5  Using PROC PRINT

8.5.1  Using the ID and BY Statements Together

8.5.2  Using the STYLE= Option with PROC PRINT

8.5.3  Using PROC PRINT to Generate a Table of Contents

Chapter 9 SAS/GRAPH Elements You Should Know—Even if You Don’t Use SAS/GRAPH

9.1  Using Title Options with ODS

9.2  Setting and Clearing Graphics Options and Settings

9.3  Using SAS/GRAPH Statements with Procedures That Are Not SAS/GRAPH Procedures

9.3.1  Changing Plot Symbols with the SYMBOL Statement

9.3.2  Controlling Axes and Legends

9.4  Using ANNOTATE to Augment Graphs

Chapter 10 Presentation Graphics—More than Just SAS/GRAPH

10.1  Generating Box Plots

10.1.1  Using PROC BOXPLOT

10.1.2  Using PROC GPLOT and the SYMBOL Statement

10.1.3  Using PROC SHEWHART

10.2  SAS/GRAPH Specialty Techniques and Procedures

10.2.1  Building Your Own Graphics Font

10.2.2  Splitting a Text Line Using JUSTIFY=

10.2.3  Using Windows Fonts

10.2.4  Using PROC GKPI

10.3  PROC FREQ Graphics

Chapter 11 Output Delivery System

11.1  Using the OUTPUT Destination

11.1.1  Determining Object Names

11.1.2  Creating a Data Set

11.1.3  Using the MATCH_ALL Option

11.1.4  Using the PERSIST= Option

11.1.5  Using MATCH_ALL= with the PERSIST= Option

11.2  Writing Reports to Excel

11.2.1  EXCELXP Tagset Documentation and Options

11.2.2  Generating Multisheet Workbooks

11.2.3  Checking Out the Styles

11.3  Inline Formatting Using Escape Character Sequences

11.3.1  Page X of Y

11.3.2  Superscripts, Subscripts, and a Dagger

11.3.3  Changing Attributes

11.3.4  Using Sequence Codes to Control Indentations, Spacing, and Line Breaks

11.3.5  Issuing Raw RTF Specific Commands

11.4  Creating Hyperlinks

11.4.1  Using Style Overrides to Create Links

11.4.2  Using the LINK= TITLE Statement Option

11.4.3  Linking Graphics Elements

11.4.4  Creating Internal Links

11.5  Traffic Lighting

11.5.1  User-Defined Format

11.5.2  PROC TABULATE

11.5.3  PROC REPORT

11.5.4  Traffic Lighting with PROC PRINT

11.6  The ODS LAYOUT Statement

11.7  A Few Other Useful ODS Tidbits

11.7.1  Using the ASIS Style Attribute

11.7.2 ODS RESULTS Statement

Part 3 Techniques, Tools, and Interfaces

Chapter 12 Taking Advantage of Formats

12.1  Using Preloaded Formats to Modify Report Contents

12.1.1  Using Preloaded Formats with PROC REPORT

12.1.2  Using Preloaded Formats with PROC TABULATE

12.1.3  Using Preloaded Formats with the MEANS and SUMMARY Procedures

12.2  Doing More with Picture Formats

12.2.1  Date Directives and the DATATYPE Option

12.2.2  Working with Fractional Values

12.2.3  Using the MULT and PREFIX Options

12.2.4  Display Granularity Based on Value Ranges – Limiting Significant Digits

12.3  Multilabel (MLF) Formats

12.3.1  A Simple MLF

12.3.2  Calculating Rolling Averages

12.4  Controlling Order Using the NOTSORTED Option

12.5  Extending the Use of Format Translations

12.5.1  Filtering Missing Values

12.5.2  Mapping Overlapping Ranges

12.5.3  Handling Text within Numeric Values

12.5.4  Using Perl Regular Expressions within Format Definitions

12.5.5  Passing Values to a Function as a Format Label

12.6  ANYDATE Informats

12.6.1  Reading in Mixed Dates

12.6.2  Converting Mixed DATETIME Values

12.7  Building Formats from Data Sets

12.8  Using the PVALUE Format

12.9  Format Libraries

12.9.1  Saving Formats Permanently

12.9.2  Searching for Formats

12.9.3  Concatenating Format Catalogs and Libraries

Chapter 13 Interfacing with the Macro Language

13.1 Avoiding Macro Variable Collisions—Make Your Macro Variables %Local

13.2 Using the SYMPUTX Routine

13.2.1 Compared to CALL SYMPUT

13.2.2 Using SYMPUTX to Save Values of Options

13.2.3 Using SYMPUTX to Build a List of Macro Variables

13.3 Generalized Programs—Variations on a Theme

13.3.1 Steps to the Generalization of a Program

13.3.2 Levels of Generalization and Levels of Macro Language Understanding

13.4 Utilizing Macro Libraries

13.4.1 Establishing an Autocall Library

13.4.2 Tracing Autocall Macro Locations

13.4.3 Using Stored Compiled Macro Libraries

13.4.4 Macro Library Search Order

13.5 Metadata-Driven Programs

13.5.1 Processing across Data Sets

13.5.2 Controlling Data Validations

13.6 Hard Coding—Just Don’t Do It

13.7 Writing Macro Functions

13.8 Macro Information Sources

13.8.1 Using SASHELP and Dictionary tables

13.8.2 Retrieving System Options and Settings

13.8.3 Accessing the Metadata of a SAS Data Set

13.9 Macro Security and Protection

13.9.1 Hiding Macro Code

13.9.2 Executing a Specific Macro Version

13.10 Using the Macro Language IN Operator

13.10.1 What Can Go Wrong

13.10.2 Using the MINOPERATOR Option

13.10.3 Using the MINDELIMITER= Option

13.10.4 Compilation vs. Execution for these Options

13.11 Making Use of the MFILE System Option

13.12 A Bit on Macro Quoting

Chapter 14 Operating System Interface and Environmental Control

14.1  System Options

14.1.1  Initialization Options

14.1.2  Data Processing Options

14.1.3  Saving SAS System Options

14.2  Using an AUTOEXEC Program

14.3  Using the Configuration File

14.3.1  Changing the SASAUTOS Location

14.3.2  Controlling DM Initialization

14.4  In the Display Manager

14.4.1  Showing Column Names in ViewTable

14.4.2  Using the DM Statement

14.4.3  Enhanced Editor Options and Shortcuts

14.4.4  Macro Abbreviations for the Enhanced Editor

14.4.5  Adding Tools to the Application Tool Bar

14.4.6  Adding Tools to Pull-Down and Pop-up Menus

14.4.7  Adding Tools to the KEYS List

14.5  Using SAS to Write and Send E-mails

14.6  Recovering Physical Location Information

14.6.1  Using the PATHNAME Function

14.6.2  SASHELP VIEWS and DICTIONARY Tables

14.6.3  Determining the Executing Program Name and Path

14.6.4  Retrieving the UNC (Universal Naming Convention) Path

Chapter 15 Miscellaneous Topics

15.1  A Few Miscellaneous Tips

15.1.1  Customizing Your NOTEs, WARNINGs, and ERRORs

15.1.2  Enhancing Titles and Footnotes with the #BYVAL and #BYVAR Options

15.1.3  Executing OS Commands

15.2  Creating User-defined Functions Using PROC FCMP

15.2.1  Building Your Own Functions

15.2.2  Storing and Accessing Your Functions

15.2.3  Interaction with the Macro Language

15.2.4  Viewing Function Definitions

15.2.5  Removing Functions

15.3  Reading RTF as Data

15.3.1  RTF Diagram Completion

15.3.2  Template Preparation

15.3.3  RTF as Data

Appendix A Topical Index

Appendix B Usage Index

Global Statements and Options

Statements, Global

Macro Language

GOPTIONS, Graphics

Options, System

Options, Data Set

Procedures: Steps, Statements, and Options

Procedures

DATA Step: Statements and Options

Statements, DATA Step

Format Modifiers

Functions

Hash Object

Output Delivery System, ODS

ODS Destinations and Tagsets

ODS Attributes

ODS Options

ODS Statements

SAS Display Manager

Display Manager Commands

References

User Publications

Generally Good Reading - Lots More to Learn

SAS Documentation

SAS Usage Notes

Discussion Forums

Newsletters, Corporate and Private Sites

User Communities

Publications

Learning SAS

Index

Accelerate Your SAS Knowledge with SAS Books