book

Carpenter's Guide to Innovative SAS Techniques

by Art Carpenter

March 2012

Intermediate to advanced

570 pages

19h 20m

English

SAS Institute

Read now

Unlock full access

1.1 LIBNAME Statement Engines1.1.1 Using Data Access Engines to Read and Write Data1.1.2 Using the Engine to View the Data1.1.3 Options Associated with the Engine1.1.4 Replacing EXCEL Sheets1.1.5 Recovering the Names of EXCEL Sheets1.2 PROC IMPORT and EXPORT1.2.1 Using the Wizard to Build Sample Code1.2.2 Control through the Use of Options1.2.3 PROC IMPORT Data Source Statements1.2.4 Importing and Exporting CSV Files1.2.5 Preventing the Export of Blank Sheets1.2.6 Working with Named Ranges1.3 DATA Step INPUT Statement1.3.1 Format Modifiers for Errors1.3.2 Format Modifiers for the INPUT Statement1.3.3 Controlling Delimited Input1.3.4 Reading Variable-Length Records1.4 Writing Delimited Files1.4.1 Using the DATA Step with the DLM= Option1.4.2 PROC EXPORT1.4.3 Using the %DS2CSV Macro1.4.4 Using ODS and the CSV Destination1.4.5 Inserting the Separator Manually1.5 SQL Pass-Through1.5.1 Adding a Pass-Through to Your SQL Step1.5.2 Pass-Through Efficiencies1.6 Reading and Writing to XML1.6.1 Using ODS 1.6.2 Using the XML Engine
2.1 Data Set Options2.1.1 REPLACE and REPEMPTY2.1.2 Password Protection2.1.3 KEEP, DROP, and RENAME Options2.1.4 Observation Control Using FIRSTOBS and OBS Data Set Options2.2 Evaluating Expressions2.2.1 Operator Hierarchy2.2.2 Using the Colon as a Comparison Modifier2.2.3 Logical and Comparison Operators in Assignment Statements2.2.4 Compound Inequalities2.2.5 The MIN and MAX Operators2.2.6 Numeric Expressions and Boolean Transformations2.3 Data Validation and Exception Reporting2.3.1 Date Validation2.3.2 Writing to an Error Data Set2.3.3 Controlling Exception Reporting with Macros2.4 Normalizing - Transposing the Data2.4.1 Using PROC TRANSPOSE2.4.2 Transposing in the DATA Step2.5 Filling Sparse Data2.5.1 Known Template of Rows2.5.2 Double Transpose2.5.3 Using COMPLETYPES with PROC MEANS or PROC SUMMARY2.5.4 Using CLASSDATA2.5.5 Using Preloaded Formats2.5.6 Using the SPARSE Option with PROC FREQ2.6 Some General Concepts2.6.1 Shorthand Variable Naming2.6.2 Understanding the ORDER= Option2.6.3 Quotes within Quotes within Quotes2.6.4 Setting the Length of Numeric Variables2.7 WHERE Specifics2.7.1 Operators Just for the WHERE 2.7.2 Interaction with the BY Statement2.8 Appending Data Sets2.8.1 Appending Data Sets Using the DATA Step and SQL UNION2.8.2 Using the DATASETS Procedure’s APPEND Statement 2.9 Finding and Eliminating Duplicates2.9.1 Using PROC SORT2.9.2 Using FIRST. and LAST. BY-Group Processing2.9.3 Using PROC SQL2.9.4 Using PROC FREQ2.9.5 Using the Data Component Hash Object2.10 Working with Missing Values2.10.1 Special Missing Values2.10.2 MISSING System Option2.10.3 Using the CMISS, NMISS, and MISSING Functions2.10.4 Using the CALL MISSING Routine2.10.5 When Classification Variables Are Missing2.10.6 Missing Values and Macro Variables2.10.7 Imputing Missing Values
3.1 Working across Observations3.1.1 BY-Group Processing—Using FIRST. and LAST. Processing 3.1.2 Transposing to ARRAYs3.1.3 Using the LAG Function3.1.4 Look-Ahead Using a MERGE Statement3.1.5 Look-Ahead Using a Double SET Statement3.1.6 Look-Back Using a Double SET Statement3.1.7 Building a FIFO Stack3.1.8 A Bit on the SUM Statement3.2 Calculating a Person’s Age3.2.1 Simple Formula3.2.2 Using Functions3.2.3 The Way Society Measures Age3.3 Using DATA Step Component Objects3.3.1 Declaring (Instantiating) the Object3.3.2 Using Methods with an Object3.3.3 Simple Sort Using the HASH Object3.3.4 Stepping through a Hash Table3.3.5 Breaking Up a Data Set into Multiple Data Sets3.3.6 Hash Tables That Reference Hash Tables3.3.7 Using a Hash Table to Update a Master Data Set3.4 Doing More with the INTNX and INTCK Functions3.4.1 Interval Multipliers3.4.2 Shift Operators3.4.3 Alignment Options3.4.4 Automatic Dates3.5 Variable Conversions 3.5.1 Using the PUT and INPUT Functions3.5.2 Decimal, Hexadecimal, and Binary Number Conversions3.6 DATA Step Functions3.6.1 The ANY and NOT Families of Functions3.6.2 Comparison Functions3.6.3 Concatenation Functions3.6.4 Finding Maximum and Minimum Values3.6.5 Variable Information Functions3.6.6 New Alternatives and Functions That Do More3.6.7 Functions That Put the Squeeze on Values3.7 Joins and Merges3.7.1 BY Variable Attribute Consistency3.7.2 Variables in Common That Are Not in the BY List3.7.3 Repeating BY Variables3.7.4 Merging without a Clear Key (Fuzzy Merge)3.8 More on the SET Statement3.8.1 Using the NOBS= and POINT= Options3.8.2 Using the INDSNAME= Option3.8.3 A Comment on the END= Option3.8.4 DATA Steps with Two SET Statements3.9 Doing More with DO Loops3.9.1 Using the DOW Loop3.9.2 Compound Loop Specifications3.9.3 Special Forms of Loop Specifications3.10 More on Arrays3.10.1 Array Syntax3.10.2 Temporary Arrays3.10.3 Functions Used with Arrays3.10.4 Implicit Arrays
4.1 PROC SORT Options4.1.1 The NODUPREC Option4.1.2 The DUPOUT= Option4.1.3 The TAGSORT Option4.1.4 Using the SORTSEQ Option4.1.5 The FORCE Option4.1.6 The EQUALS or NOEQUALS Options4.2 Using Data Set Options with PROC SORT4.3 Taking Advantage of Known or Knowable Sort Order4.4 Metadata Sort Information4.5 Using Threads

5.1 Automating the COMPARE Process5.2 Reordering Variables on the PDV5.3 Building and Maintaining Indexes5.3.1 Introduction to Indexing5.3.2 Creating Simple Indexes5.3.3 Creating Composite Indexes5.3.4 Using the IDXWHERE and IDXNAME Options5.3.5 Index Caveats and Considerations5.4 Protecting Passwords5.4.1 Using PROC PWENCODE5.4.2 Protecting Database Passwords5.5 Deleting Data Sets5.6 Renaming Data Sets5.6.1 Using the RENAME Function5.6.2 Using PROC DATASETS
6.1 A Series of IF Statements—The Logical Lookup6.2 IF -THEN/ELSE Lookup Statements6.3 DATA Step Merges and SQL Joins6.4 Merge Using Double SET Statements6.5 Using Formats6.6 Using Indexes6.6.1 Using the BY Statement6.6.2 Using the KEY= Option6.7 Key Indexing (Direct Addressing)—Using Arrays to Form a Simple Hash6.7.1 Building a List of Unique Values6.7.2 Performing a Key Index Lookup6.7.3 Using a Non-Numeric Index6.8 Using the HASH Object
7.1.1 MISSING and DESCENDING Options7.1.2 GROUPINTERNAL Option7.1.3 Order= Option7.2 Letting SAS Name the Output Variables7.3 Statistic Specification on the OUTPUT Statement 7.4 Identifying the Extremes7.4.1 Using the MAXID and MINID Options7.4.2 Using the IDGROUP Option7.4.3 Using Percentiles to Create Subsets7.5 Understanding the _TYPE_ Variable7.6 Using the CHARTYPE Option7.7 Controlling Summary Subsets Using the WAYS Statement7.8 Controlling Summary Subsets Using the TYPES   Statement7.9 Controlling Subsets Using the CLASSDATA= and EXCLUSIVE Options7.10 Using the COMPLETETYPES Option7.11 Identifying Summary Subsets Using the LEVELS and WAYS Options7.12 CLASS Statement vs. BY Statement
8.1 Expanding PROC TABULATE8.1.1 What You Need to Know to Get Started8.1.2 Calculating Percentages Using PROC TABULATE8.1.3 Using the STYLE= Option with PROC TABULATE8.1.4 Controlling Table Content with the CLASSDATA Option8.1.5 Ordering Classification Level Headings8.2 Expanding PROC UNIVARIATE8.2.1 Generating Presentation-Quality Plots8.2.2 Using the CLASS Statement8.2.3 Probability and Quantile Plots8.2.4 Using the OUTPUT Statement to Calculate Percentages8.3 Doing More with PROC FREQ8.3.1 OUTPUT Statement in PROC FREQ8.3.2 Using the NLEVELS Option8.4 Using PROC REPORT to Better Advantage8.4.1 PROC REPORT vs. PROC TABULATE8.4.2 Naming Report Items (Variables) in the Compute Block8.4.3 Understanding Compute Block Execution8.4.4 Using a Dummy Column to Consolidate Compute Blocks8.4.5 Consolidating Columns8.4.6 Using the STYLE= Option with LINES8.4.7 Setting Style Attributes with the CALL DEFINE Routine8.4.8 Dates within Dates8.4.9 Aligning Decimal Points8.4.10 Conditionally Executing the LINE Statement8.5 Using PROC PRINT8.5.1 Using the ID and BY Statements Together8.5.2 Using the STYLE= Option with PROC PRINT8.5.3 Using PROC PRINT to Generate a Table of Contents
9.1 Using Title Options with ODS9.2 Setting and Clearing Graphics Options and Settings9.3 Using SAS/GRAPH Statements with Procedures That Are Not SAS/GRAPH Procedures9.3.1 Changing Plot Symbols with the SYMBOL Statement9.3.2 Controlling Axes and Legends9.4 Using ANNOTATE to Augment Graphs
10.1 Generating Box Plots10.1.1 Using PROC BOXPLOT10.1.2 Using PROC GPLOT and the SYMBOL Statement10.1.3 Using PROC SHEWHART10.2 SAS/GRAPH Specialty Techniques and Procedures10.2.1 Building Your Own Graphics Font10.2.2 Splitting a Text Line Using JUSTIFY=10.2.3 Using Windows Fonts10.2.4 Using PROC GKPI10.3 PROC FREQ Graphics
11.1 Using the OUTPUT Destination11.1.1 Determining Object Names11.1.2 Creating a Data Set11.1.3 Using the MATCH_ALL Option11.1.4 Using the PERSIST= Option11.1.5 Using MATCH_ALL= with the PERSIST= Option11.2 Writing Reports to Excel11.2.1 EXCELXP Tagset Documentation and Options11.2.2 Generating Multisheet Workbooks11.2.3 Checking Out the Styles11.3 Inline Formatting Using Escape Character Sequences11.3.1 Page X of Y11.3.2 Superscripts, Subscripts, and a Dagger11.3.3 Changing Attributes11.3.4 Using Sequence Codes to Control Indentations, Spacing, and Line Breaks11.3.5 Issuing Raw RTF Specific Commands11.4 Creating Hyperlinks11.4.1 Using Style Overrides to Create Links11.4.2 Using the LINK= TITLE Statement Option11.4.3 Linking Graphics Elements11.4.4 Creating Internal Links11.5 Traffic Lighting11.5.1 User-Defined Format11.5.2 PROC TABULATE11.5.3 PROC REPORT11.5.4 Traffic Lighting with PROC PRINT11.6 The ODS LAYOUT Statement11.7 A Few Other Useful ODS Tidbits11.7.1 Using the ASIS Style Attribute11.7.2 ODS RESULTS Statement
12.1 Using Preloaded Formats to Modify Report Contents12.1.1 Using Preloaded Formats with PROC REPORT12.1.2 Using Preloaded Formats with PROC TABULATE12.1.3 Using Preloaded Formats with the MEANS and SUMMARY Procedures12.2 Doing More with Picture Formats12.2.1 Date Directives and the DATATYPE Option12.2.2 Working with Fractional Values12.2.3 Using the MULT and PREFIX Options 12.2.4 Display Granularity Based on Value Ranges – Limiting Significant Digits12.3 Multilabel (MLF) Formats12.3.1 A Simple MLF12.3.2 Calculating Rolling Averages12.4 Controlling Order Using the NOTSORTED Option12.5 Extending the Use of Format Translations12.5.1 Filtering Missing Values12.5.2 Mapping Overlapping Ranges12.5.3 Handling Text within Numeric Values12.5.4 Using Perl Regular Expressions within Format Definitions12.5.5 Passing Values to a Function as a Format Label12.6 ANYDATE Informats12.6.1 Reading in Mixed Dates12.6.2 Converting Mixed DATETIME Values 12.7 Building Formats from Data Sets12.8 Using the PVALUE Format12.9 Format Libraries12.9.1 Saving Formats Permanently12.9.2 Searching for Formats12.9.3 Concatenating Format Catalogs and Libraries
13.1 Avoiding Macro Variable Collisions—Make Your Macro Variables %Local13.2 Using the SYMPUTX Routine13.2.1 Compared to CALL SYMPUT13.2.2 Using SYMPUTX to Save Values of Options13.2.3 Using SYMPUTX to Build a List of Macro Variables13.3 Generalized Programs—Variations on a Theme13.3.1 Steps to the Generalization of a Program13.3.2 Levels of Generalization and Levels of Macro Language Understanding13.4 Utilizing Macro Libraries13.4.1 Establishing an Autocall Library13.4.2 Tracing Autocall Macro Locations13.4.3 Using Stored Compiled Macro Libraries13.4.4 Macro Library Search Order13.5 Metadata-Driven Programs13.5.1 Processing across Data Sets13.5.2 Controlling Data Validations13.6 Hard Coding—Just Don’t Do It13.7 Writing Macro Functions13.8 Macro Information Sources13.8.1 Using SASHELP and Dictionary tables13.8.2 Retrieving System Options and Settings13.8.3 Accessing the Metadata of a SAS Data Set13.9 Macro Security and Protection13.9.1 Hiding Macro Code13.9.2 Executing a Specific Macro Version13.10 Using the Macro Language IN Operator 13.10.1 What Can Go Wrong13.10.2 Using the MINOPERATOR Option13.10.3 Using the MINDELIMITER= Option13.10.4 Compilation vs. Execution for these Options13.11 Making Use of the MFILE System Option13.12 A Bit on Macro Quoting
14.1 System Options14.1.1 Initialization Options14.1.2 Data Processing Options14.1.3 Saving SAS System Options14.2 Using an AUTOEXEC Program14.3 Using the Configuration File14.3.1 Changing the SASAUTOS Location14.3.2 Controlling DM Initialization14.4 In the Display Manager14.4.1 Showing Column Names in ViewTable 14.4.2 Using the DM Statement14.4.3 Enhanced Editor Options and Shortcuts 14.4.4 Macro Abbreviations for the Enhanced Editor14.4.5 Adding Tools to the Application Tool Bar14.4.6 Adding Tools to Pull-Down and Pop-up Menus14.4.7 Adding Tools to the KEYS List14.5 Using SAS to Write and Send E-mails14.6 Recovering Physical Location Information14.6.1 Using the PATHNAME Function14.6.2 SASHELP VIEWS and DICTIONARY Tables14.6.3 Determining the Executing Program Name and Path14.6.4 Retrieving the UNC (Universal Naming Convention) Path
15.1 A Few Miscellaneous Tips15.1.1 Customizing Your NOTEs, WARNINGs, and ERRORs15.1.2 Enhancing Titles and Footnotes with the #BYVAL and #BYVAR Options15.1.3 Executing OS Commands15.2 Creating User-defined Functions Using PROC FCMP15.2.1 Building Your Own Functions15.2.2 Storing and Accessing Your Functions15.2.3 Interaction with the Macro Language15.2.4 Viewing Function Definitions15.2.5 Removing Functions15.3 Reading RTF as Data15.3.1 RTF Diagram Completion15.3.2 Template Preparation15.3.3 RTF as Data
Global Statements and OptionsStatements, GlobalMacro LanguageGOPTIONS, GraphicsOptions, SystemOptions, Data SetProcedures: Steps, Statements, and OptionsProcedures DATA Step: Statements and OptionsStatements, DATA StepFormat ModifiersFunctionsHash Object Output Delivery System, ODSODS Destinations and TagsetsODS AttributesODS OptionsODS StatementsSAS Display ManagerDisplay Manager Commands
User PublicationsGenerally Good Reading – Lots More to Learn

Content preview from Carpenter's Guide to Innovative SAS Techniques

Chapter `4` Sorting the Data

`4.1 PROC SORT Options`

`4.1.4 Using the SORTSEQ 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`

Sorting data is always a resource-intensive operation; therefore, using PROC SORT wisely can save you both time and computing effort. Fortunately, there are both options and strategies to assist you in selecting more efficient, if not optimal methodologies.