Index

Special Characters

_ATDATETIME_ audit trail variable

_ATMESSAGE_ audit trail variable

_ATOBSNO_ audit trail variable

_ATOPCODE_ audit trail variable

Audit Trail Variables
Values of the _ATOPCODE_ Variable

_ATRETURNCODE_ audit trail variable

_ATUSERID_ audit trail variable

_DSEMTR mnemonic

_DSENMR mnemonic

_DSENOM mnemonic

_IORC_ automatic variable

The _IORC_ Variable
Monitoring I/O Error Conditions
Using _IORC_ with %SYSRC

_SOK mnemonic

_TEMPORARY_ keyword

_TYPE_ variable

Understanding Types
Using the TYPES Statement in PROC MEANS to Combine Class Variables

; (semicolon)

? conditional operator

Using Operators in PROC SQL
Using the CONTAINS or Question Mark (?) Operator to Select a String

. (period)

& (ampersand)

% (percent sign)

macro programs and

specifying directives

tokens and

%a directive

%A directive

%b directive

%B directive

%BQUOTE function

%CMPRES statement

%COPY statement

%d directive

%DATATYP statement

%DO-%END statement

%IF-%THEN Compared to IF-THEN
Overview
Generating Complete Steps

%EVAL function

%GLOBAL statement

%H directive

%I directive

%IF-%THEN/%ELSE macro statement

Conditional Execution
%IF-%THEN Compared to IF-THEN
Macro Execution with Conditional Processing
Conditional Processing of Parts of Statements
Case Sensitivity in Macro Comparisons

%INCLUDE statement

%INDEX function

%j directive

%LEFT statement

%LENGTH function

%LET statement

macro parameter support

processing

user-defined macro variables and

%LOCAL statement

%LOWCASE statement

%m directive

%M directive

%MACRO statement

creating stored compiled macros

general form

macro parameter support

Macros That Include Positional Parameters
Macros That Include Keyword Parameters
Macros That Include Mixed Parameter Lists

PARMBUFF option

SOURCE option

Using the SOURCE Option
Accessing Stored Macro Code

STORE option

%MEND statement

%NRSTR function

%p directive

%PUT statement

%QLOWCASE statement

%QSCAN function

%QSUBSTR function

%QSYSFUNC function

%QUPCASE function

%S directive

%SCAN function

%STR function

%SUBSTR function

%SYSEVALF function

%SYSFUNC function

%SYSRC autocall macro

%TRIM statement

%U directive

%UPCASE function

%w directive

%y directive

%Y directive

=* conditional operator

description

general form

WHERE conditions and

A

access methods, selecting

accumulator variables

ADD clause, ALTER TABLE statement (SQL)

ADD method

aliases

column

Creating New Columns
Using the Keyword CALCULATED
Renaming a Column By Using a Column Alias

in-line views

table

ALL conditional operator

Using Operators in PROC SQL
Using the ALL Operator

ALL keyword

DELETE statement (DATASETS)

EXCEPT set operator and

Using the Keyword ALL with the EXCEPT Operator
Using the Keywords ALL and CORR with the EXCEPT Operator

INTERSECT set operator and

Using the Keyword ALL with the INTERSECT Operator
Using the Keywords ALL and CORR with the INTERSECT Operator

set operations and

UNION set operator and

Using the Keyword ALL with the UNION Operator
Using the Keywords ALL and CORR with the UNION Operator

ALTER TABLE statement, SQL procedure

ADD clause

DROP clause

functionality

general form

MODIFY clause

ALTER= data set option

ampersand (&)

ANY conditional operator

comparison operator and

description

APPEND procedure

BASE= data set option

DATA= data set option

FORCE option

Using the FORCE Option
Appending Variables with Different Lengths
Appending Variables with Different Types

general form

arguments, summary functions and

Number of Arguments and Summary Function Processing
Using a Summary Function with a Single Argument (Column)
Using a Summary Function with Multiple Arguments (Columns)

arithmetic expressions in macro programs

The %EVAL Function
Automatic Evaluation

ARRAY statement

combining data with

creating arrays

general form

loading array elements

lookup values and

stored array values and

arrays

combining data in

creating

defined

loading elements

multidimensional

reading values

stored values

Overview
Creating an Array
Loading the Array Elements
Reading the Actual Values

AS keyword, CREATE TABLE statement (SQL)

ATTRIB statement

attributes

DATA Step Component Objects
Using the DATASETS Procedure to Modify Variable Attributes

AUDIT statement, DATASETS procedure

Overview
Overview [2]

audit trails

controlling

controlling data in

Overview
Data Set Variables
Audit Trail Variables
Values of the _ATOPCODE_ Variable
Using the LOG Statement to Control the Data in the Audit Trail

initiating

overview

reading files

USER_VAR statement

variables

Audit Trail Variables
Values of the _ATOPCODE_ Variable

autocall libraries

accessing macros

creating

default

defined

automatic macro variables

defined

functionality

global symbol table and

Macro Variables
The Global Symbol Table

AVG function

B

base table

BASE= data set option

BEFORE_IMAGE option, LOG statement

benchmark guidelines

best practices

conditional logic

DO groups

eliminating data passes

Eliminating Unnecessary Passes through the Data
Using the SORT Procedure with a WHERE Statement to Create Sorted Subsets
Using the DATASETS Procedure to Modify Variable Attributes

executing only necessary statements

reading/writing essential data

Overview
Selecting Observations Using Subsetting IF versus WHERE Statement
Other Differences between the IF and WHERE Statements
Using the WHERE Statement with the OBS= and FIRSTOBS= Options
Selecting Observations When Reading Data from External Files
Subsetting Variables with the KEEP and DROP Statements and Options

subsetting variables

Subsetting Variables with the KEEP and DROP Statements and Options
Subsetting Variables with the KEEP and DROP Statements and Options [2]

BETWEEN-AND conditional operator

description

general form

identifying conditions to optimize

binary search

buffers

controlling number of

Measuring I/O
Using the BUFSIZE= Option
Using the BUFNO= Option

controlling page size

Measuring I/O
Page Size

BUFNO= data set option

BUFNO= system option

BUFSIZE= system option

BY statement, DATA step

handling duplicate values

MODIFY statement and

Overview
Monitoring I/O Error Conditions

TRANSPOSE procedure

BY variable

DATA step match-merge

The DATA Step Match-Merge
Execution of a DATA Step Match-Merge

joining tables

Overview
When All of the Values Match
When Only Some of the Values Match

C

CALCULATED keyword

CALL MISSING routine

Cartesian product

Generating a Cartesian Product
PROC SQL Join

CASE expression

general form

INSERT statement (SQL)

SELECT statement (SQL)

UPDATE statement (SQL)

Updating Rows By Using Different Expressions
How PROC SQL Updates Rows Based on a CASE Expression
Updating Rows By Using the CASE Expression without a Case Operand
Updating Rows By Using the CASE Expression with a Case Operand

case operand

CASE expression, UPDATE statement (SQL)

Updating Rows By Using the CASE Expression without a Case Operand
Updating Rows By Using the CASE Expression with a Case Operand

defined

case sensitivity in macro comparisons

CATALOG access method, FILENAME statement

CATALOG procedure

general form

The CATALOG Procedure
Using PROC CATALOG to Manage Formats

managing formats

catalogs, storing macro definitions

Overview
The CATALOG Procedure
The CATALOG Access Method

CENTILES option, CONTENTS procedure

CHANGE statement, DATASETS procedure

Renaming Data Sets
How Generation Numbers Change

character constants

character data types

column widths and

defined

flowing characters in columns

character strings

%INDEX function

%LENGTH function

%QSCAN function

%QSUBSTR function

%QUPCASE function

%SCAN function

%SUBSTR function

%UPCASE function

macro character functions

character variables, storing

CHART procedure

Overview
Delimiters in Macro Variable Names

CHECK constraint type

functionality

Overview
General Integrity Constraints
Overview

in column specification

CLASS statement

TABULATE procedure

class variables

_TYPE_ values

Understanding Types
Using the TYPES Statement in PROC MEANS to Combine Class Variables

combining

summary statistics and

Using PROC MEANS to Display Summary Statistics for Combinations of Class Variables
Using a Basic PROC MEANS Step to Combine All Class Variables

CNTLIN= option, FORMAT procedure

Overview
Rules for Control Data Sets

CNTLOUT= option, FORMAT procedure

COALESCE function

When Only Some of the Values Match
When Only Some of the Values Match: Using the COALESCE Function

column alias

CALCULATED keyword and

defined

renaming columns with

column constraints

column modifiers

column widths

columns

adding to tables

altering in tables

as multiple arguments for summary functions

combining

counting non-missing values

counting unique values

creating constraints

creating new

creating tables by defining

deleting from tables

eliminating duplicate

flowing characters within

key

Introduction
PROC SQL Queries That Can Be Optimized by an Index

modifying in tables

ordering by multiple

outside summary functions

overlaying

processing calculated

renaming with column aliases

rows numbers in output

selecting

specifying formats and labels

specifying in multiple tables

specifying subsets in tables

summary functions and

Using a Summary Function with a Single Argument (Column)
Using a Summary Function with Multiple Arguments (Columns)

viewing all

comments in macro programs

COMPARE procedure

comparison operators

ALL conditional operator and

ANY conditional operator and

identifying conditions to optimize

subqueries and

compiling macro programs

Compiling a Macro
The MCOMPILENOTE= Option

composite indexes

creating

Creating Multiple Indexes
Example: Creating a Composite, Unique Index

defined

Simple and Composite Indexes
Introduction
Types of Indexes

COMPRESS function

COMPRESS= data set option

COMPRESS= system option

compressing data files

costs of

storage considerations

Compressing Data Files
Review of Uncompressed Data File Structure
Compressed Data File Structure
Deciding Whether to Compress a Data File
The COMPRESS= System Option and the COMPRESS= Data Set Option
Accessing Observations Directly in a Compressed Data Set
The POINTOBS= Data Set Option
The REUSE= System Option and the REUSE= Data Set Option
How SAS Compresses Data

concatenating data

appending data sets

Overview
Using the FORCE Option
Appending Variables with Different Lengths
Appending Variables with Different Types

creating delimiting list of values

defined

FILENAME statement

INFILE statement

Overview
Assigning the Names of the Files to Read
Using the COMPRESS Function
Using the END= Option
Using Date Functions
Using the INTNX Function

conditional operators

conditional processing

best practices

case sensitivity in macro programs

for macro programs

Conditional Execution
%IF-%THEN Compared to IF-THEN
Macro Execution with Conditional Processing
Conditional Processing of Parts of Statements
Case Sensitivity in Macro Comparisons

constants

Adding a Character Constant to Output
Macro Quoting Functions

CONTAINS conditional operator

description

general form

identifying conditions to optimize

CONTENTS procedure

CENTILES option

displaying index specifications

maintaining indexes

reporting buffer information

CONTENTS statement

CATALOG procedure

DATASETS procedure

Overview
Overview [2]
Page Size
Printing Centile Information

conventional join

COPY procedure

COPY statement, DATASETS procedure

copying

data sets

Copying Data Sets
Copying a Data Set and Preserving Integrity Constraints

tables

CORR keyword

EXCEPT set operator and

Using the Keyword CORR with the EXCEPT Operator
Using the Keywords ALL and CORR with the EXCEPT Operator

INTERSECT operator and

Using the Keyword CORR with the INTERSECT Operator
Using the Keywords ALL and CORR with the INTERSECT Operator

OUTER UNION set operator and

Using the Keyword CORR with the OUTER UNION Operator
Comparing Outer Unions and Other SAS Techniques

set operations and

UNION set operator and

Using the Keyword CORR with the UNION Operator
Using the Keywords ALL and CORR with the UNION Operator

correlated subqueries

defined

EXISTS conditional operator

indexes and

NOT EXISTS conditional operator

subsetting data

COUNT function

CREATE INDEX statement, SQL procedure

creating composite indexes

Creating Multiple Indexes
Example: Creating a Composite, Unique Index

creating multiple indexes

creating simple indexes

Example: Creating a Simple Index
Example: Creating a Composite, Unique Index

displaying index specifications

functionality

general form

Overview
Overview [2]

CREATE statement, SQL procedure

CREATE TABLE statement, SQL procedure

AS keyword

copying tables

creating constraints

creating constraints outside column specifications

creating empty tables

creating like other tables

creating output tables

creating tables from query results

displaying table structure

FORMAT= option

FROM clause

general form for creating output tables

general form with column specifications

general form with constrain specification

general form with constraint specification

general form with LIKE clause

general form with query clauses

INFORMAT= option

LABEL= option

LIKE clause

MESSAGE= option

Creating a Constraint in a Column Specification
Creating a Constraint By Using a Constraint Specification

MSGTYPE= option

Creating a Constraint in a Column Specification
Creating a Constraint By Using a Constraint Specification

SELECT clause

specifying column modifiers

specifying column widths

specifying data types

specifying empty tables like other tables

specifying subsets of columns

CREATE VIEW statement, SQL procedure

FROM clause

general form

USING clause

D

data files

accessing observations directly

COMPRESS= data set option

COMPRESS= system option

compressed structure

compression process

Compressing Data Files
How SAS Compresses Data

number of pages

POINTOBS= data set option

reasons for compressing

REUSE= data set option

REUSE= system option

uncompressed structure

data sets

accessing observations directly

appending

Overview
Using the FORCE Option
Appending Variables with Different Lengths
Appending Variables with Different Types

audit trail overview

audit trails, controlling

audit trails, controlling data in

Overview
Data Set Variables
Audit Trail Variables
Values of the _ATOPCODE_ Variable
Using the LOG Statement to Control the Data in the Audit Trail

audit trails, initiating

audit trails, reading files

controlling update process

Overview
Monitoring I/O Error Conditions
Using _IORC_ with %SYSRC

copying

Copying Data Sets
Copying a Data Set and Preserving Integrity Constraints

creating formats from

Overview
Rules for Control Data Sets
Apply the Format

creating from formats

duplicate values in

Handling Duplicate Values
Handling Duplicate Values [2]

generation

Understanding Generation Data Sets
Overview
Creating Generation Data Sets
Overview
How Generation Numbers Change

hash objects and

integrity constraint overview

Overview
General Integrity Constraints
Referential Integrity Constraints
How Constraints Are Enforced
Copying a Data Set and Preserving Integrity Constraints

integrity constraints, documenting

integrity constraints, placing in

Overview
How Constraints Are Enforced
Copying a Data Set and Preserving Integrity Constraints

integrity constraints, removing

listing variables

lookup values outside

Overview
The IF-THEN/ELSE Statement
SAS Arrays
User-Defined SAS Formats

missing values in

modifying

Using the MODIFY Statement
Overview
Overview [2]

modifying observations located by indexes

renaming

storing data

storing raw data file names

transaction

Overview
Using the UPDATE Statement
Overview

transposed

Structuring the Data for a Merge
Using a BY Statement with PROC TRANSPOSE
Sorting the Work.Ctarget2 Data Set
Reorganizing the Sasuser.Monthsum Data Set
Sorting the Work.Mnthsum2 Data Set
Completing the Merge

DATA step

ATTRIB statement

best practices

BY statement

Overview
Handling Duplicate Values
Monitoring I/O Error Conditions

comparing joins and

Overview
When All of the Values Match
When Only Some of the Values Match

COMPRESS= data set option

conserving storage space

Overview
DATA Step Views
The DESCRIBE Statement
Creating and Referencing a SAS DATA Step View
Referencing a Data View Multiple Times in One Program
Making Multiple Passes through Data in a Data View
Creating Data Views on Unstable Data

creating indexes in

creating macro variables in

creating multiple macro variables in

DESCRIBE statement

DROP statement

Subsetting Variables with the KEEP and DROP Statements and Options
Subsetting Variables with the KEEP and DROP Statements and Options [2]

FORMAT procedure

hash objects and

IF-THEN/ELSE statement

INDEX= option

KEEP statement

Step 2: Restructure the Data
Subsetting Variables with the KEEP and DROP Statements and Options
Subsetting Variables with the KEEP and DROP Statements and Options [2]

LENGTH statement

Reducing the Length of Character Data with the LENGTH Statement
Assigning Lengths to Numeric Variables

match-merges

Overview
When All of the Values Match
When Only Some of the Values Match
The DATA Step Match-Merge
Working with Multiple Lookup Tables
Overview
DATA Step Match-Merge
Execution of a DATA Step Match-Merge
Execution of a PROC SQL Join
Using Multiple SET Statements

MERGE statement

The DATA Step Match-Merge
Working with Multiple Lookup Tables

obtaining macro variable values

OUTPUT statement

PUT function

The %SYSFUNC Function
PUT Function

REMOVE statement

RENAME statement

REPLACE statement

RETAIN statement

REUSE= data set option

SET statement

subsetting IF statement

sum statement

SYMGET function

SYMPUT routine and

The SYMPUT Routine
Using SYMPUT with a Literal
Using SYMPUT with a DATA Step Variable
Using CALL SYMPUT with DATA Step Expressions
Creating Multiple Macro Variables with CALL SYMPUT

SYMPUTX routine and

table lookups and

tools for summarizing data

UNIQUE option

UPDATE statement

VIEW= option

WHERE statement

Selecting Observations Using Subsetting IF versus WHERE Statement
Other Differences between the IF and WHERE Statements
Overview
Accessing Data Directly
Example
Overview
Compound Optimization

data types

appending variables with different

character

Specifying Data Types
Specifying Column Widths
Flowing Characters within a Column

creating tables by defining columns

numeric

Specifying Data Types
Specifying Column Widths

DATA_IMAGE option, LOG statement

DATA= data set option

DATALINES statement

Macro Variables
Calling a Macro

DATASETS procedure

AUDIT statement

Overview
Overview [2]

best practices

Using the DATASETS Procedure to Modify Variable Attributes
Executing the DATASETS Procedure
Using Different Types of RUN Groups with PROC DATASETS

CHANGE statement

Renaming Data Sets
How Generation Numbers Change

CONTENTS statement

Overview
Overview [2]
Page Size
Printing Centile Information

COPY statement

creating tables with integrity constraints

DELETE statement

How Generation Numbers Change
Examples

displaying index specifications

general form

IC CREATE statement

IC DELETE statement

INDEX CREATE statement

INDEX DELETE statement

INITIATE statement

LIBRARY= option

managing indexes

MODIFY statement

Overview
Overview [2]

permanently assigning formats

RENAME statement

DATATYPE= option, PICTURE statement (FORMAT)

DATE function

debugging

FEEDBACK option, SQL procedure

macro programs

Monitoring Execution with System Options
The MLOGIC Option
Comments in Macro Programs

DECLARE statement

DEFAULT= option, LENGTH statement (DATA)

DEFINEDATA method

Defining Keys and Data
Working with Multiple Data Variables

DEFINEDONE method

DEFINEKEY method

Defining Keys and Data
Working with Multiple Data Variables

DELETE statement, DATASETS procedure

How Generation Numbers Change
Examples

DELETE statement, SQL procedure

audit trails

functionality

general form

updating views

deleting

columns from tables

indexes

rows in tables

tables

views

delimiters

in macro programs

in macro variable names

dense match

DESCRIBE statement

DATA step

SQL procedure

DESCRIBE TABLE CONSTRAINTS statement, SQL procedure

DESCRIBE TABLE statement, SQL procedure

displaying Dictionary table definitions

displaying index specifications

displaying indexes

displaying table structure

general form

Overview
Overview [2]

DESCRIBE VIEW statement, SQL procedure

detail reports

Dictionary tables

functionality

Introduction
Overview

querying

digit selectors

direct access

directives, specifying pictures

Ways to Specify Pictures
Example

DISTINCT keyword

DO loops

Loading the Array Elements
Using DO Groups Efficiently

DO UNTIL loops

dot notation method

DOUBLE option, SQL procedure

DROP clause, ALTER TABLE statement (SQL)

DROP INDEX statement, SQL procedure

Overview
Overview [2]

DROP statement

DATA step

Subsetting Variables with the KEEP and DROP Statements and Options
Subsetting Variables with the KEEP and DROP Statements and Options [2]

SQL procedure

DROP statement, SQL procedure

DROP TABLE statement, SQL procedure

DROP VIEW statement, SQL procedure

DROP= data set option

general form

subsetting variables

Subsetting Variables with the KEEP and DROP Statements and Options
Subsetting Variables with the KEEP and DROP Statements and Options [2]

duplicate columns, eliminating

duplicate rows

eliminating from output

processing unique vs.

duplicate values in data sets

Handling Duplicate Values
Handling Duplicate Values [2]

E

efficiency

assessing needs

Assessing Efficiency Needs at Your Site
Assessing Your Technical Environment
Assessing Your Programs
Assessing Your Data

benchmark guidelines

comparing resource usage

Overview
How SAS Compares Resource Usage
Factors That Affect I/O
Subset Size Relative to Data Set Size
Number of Pages in the Data File
Order of the Data
Cost to Uncompress a Compressed File for a Sequential Read
Other Factors That Affect Resource Usage

computer resources

detail reports

estimating observations

identifying available indexes

Overview
Compound Optimization

identifying conditions to optimize

Identifying Conditions That Can Be Optimized
WHERE Conditions That Cannot Be Optimized
Requirements for Compound Optimization

indexes and WHERE processing

Overview
Accessing Data Directly
Example

tools for summarizing data

Overview
Comparing Resource Usage across Summarization Tools
Understanding Types

tracking resources

trade-offs in

empty tables

Overview
Overview [2]

END= option, INFILE statement

error handling

for row insertions

monitoring I/O error conditions

stopping SQL procedure

ERROR_IMAGE option, LOG statement

ERRORSTOP option, SQL procedure

evaluating performance

EXCEPT set operator

ALL keyword and

Using the Keyword ALL with the EXCEPT Operator
Using the Keywords ALL and CORR with the EXCEPT Operator

CORR keyword and

Using the Keyword CORR with the EXCEPT Operator
Using the Keywords ALL and CORR with the EXCEPT Operator

functionality

Introducing Set Operators
Overview

EXCLUDE statement, FORMAT procedure

Using FMTLIB with PROC FORMAT to Document Formats
Overview

EXEC option, SQL procedure

EXISTS conditional operator

correlated queries and

description

external files

selecting observations from

storing macro definitions in

storing raw data filenames in

F

FEEDBACK option, SQL procedure

FILENAME statement

The CATALOG Access Method
Overview

FILEVAR= option, INFILE statement

FIND method

Retrieving Matching Data
Retrieving Multiple Data Values
Using Return Codes with the FIND Method

FIRSTOBS= option, WHERE statement (PRINT)

FLOW option, SQL procedure

FMTERR system option

FMTLIB keyword, FORMAT procedure

FMTSEARCH= system option

FOOTNOTE statement, SQL procedure

FORCE option, APPEND procedure

Using the FORCE Option
Appending Variables with Different Lengths
Appending Variables with Different Types

FOREIGN KEY constraint type

constraint specification

functionality

Overview
General Integrity Constraints
Referential Integrity Constraints
Overview

in column specification

FORMAT procedure

binary search

CNTLIN= option

Overview
Rules for Control Data Sets

CNTLOUT= option

EXCLUDE statement

Using FMTLIB with PROC FORMAT to Document Formats
Overview

FMTLIB keyword

general form

LIB= option

PICTURE statement

Overview
Guidelines for Specifying Directives

SELECT statement

Using FMTLIB with PROC FORMAT to Document Formats
Overview

VALUE statement

User-Defined SAS Formats
Example: Using the FORMAT Procedure to Combine Data
Review of Creating Non-Overlapping Formats
Creating a Format with Overlapping Ranges
Example

FORMAT statement, TABULATE procedure

FORMAT= option

CREATE TABLE statement (SQL)

SELECT statement, SQL procedure

TABULATE procedure

formats

associating with variables

creating data sets from

creating from data sets

Overview
Rules for Control Data Sets
Apply the Format

creating with PICTURE statement

Overview
Guidelines for Specifying Directives

creating with VALUE statement

Review of Creating Non-Overlapping Formats
Creating a Format with Overlapping Ranges

FMTERR system option

FMTSEARCH= system option

managing

Using FMTLIB with PROC FORMAT to Document Formats
Using PROC CATALOG to Manage Formats

multilabel

NOFMTERR system option

permanently assigning

Overview
Using a Permanent Storage Location for Formats
Avoiding Format Errors

specifying for columns

substituting to avoid errors

with overlapping ranges

Forward Re-Scan rule

FREQ procedure

Using Conditional Logic Efficiently
Overview

FROM clause

CREATE TABLES (SQL)

CREATE VIEW statement (SQL)

INSERT statement (SQL)

SELECT statement (SQL)

Specifying the Table
Specifying Multiple Table Names
Generating a Cartesian Product
Introducing In-Line Views

FSEDIT procedure

FSLIST procedure

full outer join

Introducing Types of Outer Joins
Using a Full Outer Join

FULLSTIMER system option

functions

%QSYSFUNC function

%SYSFUNC function

macro variable support

G

GCHART procedure

Delimiters in Macro Variable Names
Using Conditional Logic Efficiently

general integrity constraints

General Integrity Constraints
General Integrity Constraints [2]

generation data sets

creating

defined

initiating

processing

Overview
How Generation Numbers Change

GENMAX= option, MODIFY statement (DATASETS)

GENNUM= data set option

Overview
How Generation Numbers Change
Examples

global symbol table

%GLOBAL statement

defined

macro variables and

Macro Variables
The Global Symbol Table

GPLOT procedure

Delimiters in Macro Variable Names
Using Conditional Logic Efficiently

GROUP BY clause, SELECT statement (SQL)

selecting groups

summarizing groups of data

Summarizing Groups of Data
Overview
Groups and Summary Function Processing

summary functions and

Using a Summary Function without a GROUP BY Clause
Using a Summary Function with a GROUP BY Clause

groups, selecting

H

hash objects

CALL MISSING routine

creating from data sets

DATA step component objects

declaring

defined

defining keys and data

loading key/data values

multiple data variables

processing

retrieving matching data

retrieving multiple data values

return codes with FIND method

SET statement and

structure

HAVING clause, SELECT statement (SQL)

Additional Features
Selecting Groups By Using the HAVING Clause

HIST keyword

I

I/O processing

comparing resource usage

Overview
How SAS Compares Resource Usage
Factors That Affect I/O
Subset Size Relative to Data Set Size
Number of Pages in the Data File
Order of the Data
Cost to Uncompress a Compressed File for a Sequential Read
Other Factors That Affect Resource Usage

measuring

IC CREATE statement, DATASETS procedure

IC DELETE statement, DATASETS procedure

IDXNAME= data set option

controlling index usage

functionality

general form

IDXWHERE= data set option

controlling index usage

functionality

general form

IF-THEN/ELSE statement

%IF-%THEN comparison

best practices

Using Conditional Logic Efficiently
Using DO Groups Efficiently

lookup values outside data sets

Overview
The IF-THEN/ELSE Statement
SAS Arrays

IN conditional operator

description

general form

identifying conditions to optimize

in-line views

assigning aliases

functionality

multiple tables and

Referencing an In-Line View with Other Views or Tables
Referencing Multiple Tables in an In-Line View

INDEX CREATE statement, DATASETS procedure

INDEX DELETE statement, DATASETS procedure

INDEX= option, DATA step

indexes

accessing rows in tables

benefits

Overview
Benefits of Using an Index
Benefits and Costs of Using an Index

combining data with

Overview
The KEY= Option
The _IORC_ Variable

composite

Simple and Composite Indexes
Creating Multiple Indexes
Example: Creating a Composite, Unique Index
Introduction
Types of Indexes

controlling usage

copying data sets

costs of using

Understanding the Costs of Using an Index
Benefits and Costs of Using an Index

creating

creating in DATA step

creating multiple

creation guidelines

Guidelines for Creating Indexes
Deciding Whether to Create an Index

defined

Introduction
Introduction [2]
Overview

displaying specifications

documenting

dropping

identifying available

Overview
Compound Optimization

maintaining

managing usage

Overview
Controlling Index Usage

managing with DATASETS procedure

managing with SQL procedure

modifying observations located by

querying

PROC SQL Queries That Can Be Optimized by an Index
Overview
Accessing Data Sequentially
Accessing Data Directly
Benefits and Costs of Using an Index
How SAS Selects an Access Method

reasons for not using

renaming data sets

renaming variables

simple

Simple and Composite Indexes
Example: Creating a Simple Index
Introduction
Types of Indexes

subsetting data

types of

Simple and Composite Indexes
Types of Indexes

unique

Unique Indexes
Example: Creating a Composite, Unique Index

INFILE statement

assigning names of files to be read

COMPRESS function

date functions

END= option

FILEVAR= option

general form

INTNX function

INFORMAT= option, CREATE TABLE statement (SQL)

INITIATE statement, DATASETS procedure

inner joins

combining data horizontally

Overview
DATA Step Match-Merge
Execution of a DATA Step Match-Merge
Execution of a PROC SQL Join
Using Multiple SET Statements

defined

Overview
Introducing Inner Join Syntax

general form with outer join

INOBS= option, SQL procedure

input data sources

INPUT function

The %SYSFUNC Function
Reading the Actual Values

INPUTC function

INPUTN function

INSERT statement, SQL procedure

audit trails

CASE expression

controlling UNDO processing

FROM clause

functionality

general form

Inserting Rows By Using the SET Clause
Inserting Rows By Using the VALUES Clause
Inserting Rows from a Query Result

handling errors in row insertions

inserting rows from query results

inserting rows of data in tables

Overview
Overview [2]
Inserting Rows By Using the SET Clause
Inserting Rows By Using the VALUES Clause

SELECT clause

SET clause

Overview
Inserting Rows By Using the SET Clause

updating views

VALUES clause

Overview
Inserting Rows By Using the VALUES Clause
Example

integrity constraints

creating outside of column specifications

creating tables with

displaying for tables

documenting

enforcing

functionality

Overview
General Integrity Constraints
Referential Integrity Constraints

general

General Integrity Constraints
General Integrity Constraints [2]

general form with specifications

in column specifications

placing in data sets

Overview
How Constraints Are Enforced
Copying a Data Set and Preserving Integrity Constraints

referential

Referential Integrity Constraints
Referential Integrity Constraints [2]

removing

INTERSECT set operator

ALL keyword and

Using the Keyword ALL with the INTERSECT Operator
Using the Keywords ALL and CORR with the INTERSECT Operator

CORR keyword and

Using the Keyword CORR with the INTERSECT Operator
Using the Keywords ALL and CORR with the INTERSECT Operator

functionality

Introducing Set Operators
Overview

INTNX function

INTO clause, SELECT statement (SQL)

The INTO Clause and the NOPRINT Option
Creating Variables with the INTO Clause
Creating a Delimited List of Values

IS MISSING conditional operator

Using Operators in PROC SQL
Using the IS MISSING or IS NULL Operator to Select Missing Values
Requirements for Optimizing a Single WHERE Condition

IS NULL conditional operator

Using Operators in PROC SQL
Using the IS MISSING or IS NULL Operator to Select Missing Values
Requirements for Optimizing a Single WHERE Condition

J

joining data sets

combining summary/detail data

Overview
The MEANS Procedure
The Sum Statement

DATA step match-merge

The DATA Step Match-Merge
Working with Multiple Lookup Tables

defined

indexes and

Overview
The KEY= Option
The _IORC_ Variable

lookup values and

Overview
The IF-THEN/ELSE Statement
SAS Arrays
User-Defined SAS Formats

multiple SET statements

Using Multiple SET Statements
Example: Using Multiple SET Statements with a Many-to-Many Match

relationships between input data sources

SQL procedure support

The SQL Procedure
Overview
DATA Step Match-Merge
Execution of a DATA Step Match-Merge
Execution of a PROC SQL Join
Using Multiple SET Statements

terminology

transactional data sets

Overview
Using the UPDATE Statement

transposed data sets

Structuring the Data for a Merge
Using a BY Statement with PROC TRANSPOSE
Sorting the Work.Ctarget2 Data Set
Reorganizing the Sasuser.Monthsum Data Set
Sorting the Work.Mnthsum2 Data Set
Completing the Merge

joining tables

advantages

COALESCE function

When Only Some of the Values Match
When Only Some of the Values Match: Using the COALESCE Function

comparing with DATA step match-merges

Overview
When All of the Values Match
When Only Some of the Values Match

defined

Overview
Understanding Joins

EXCEPT set operator

for rows with matching values

in-line views and

Introducing In-Line Views
Referencing an In-Line View with Other Views or Tables

indexes and

inner joins

Overview
Introducing Inner Join Syntax

inner joins with outer-join general form

INTERSECT set operator

outer joins

OUTER UNION set operator

Overview
Comparing Outer Unions and Other SAS Techniques

processes defined

set operations

UNION set operator

with views

K

KEEP statement, DATA step

Step 2: Restructure the Data
Subsetting Variables with the KEEP and DROP Statements and Options
Subsetting Variables with the KEEP and DROP Statements and Options [2]

KEEP= data set option

general form

subsetting variables

Subsetting Variables with the KEEP and DROP Statements and Options
Subsetting Variables with the KEEP and DROP Statements and Options [2]

key columns

Introduction
PROC SQL Queries That Can Be Optimized by an Index

key values

defined

Overview
Introduction

return codes with FIND method

key variables

KEY= option

MODIFY statement (DATA)

Quiz
Overview
Handling Duplicate Values
Monitoring I/O Error Conditions

SET statement (DATA)

keyword parameters

Macros That Include Keyword Parameters
Macros That Include Mixed Parameter Lists

keywords, modifying set operations

L

LABEL= option

CREATE TABLE statement (SQL)

SELECT statement, SQL procedure

labels, column

LEFT function

left outer join

Introducing Types of Outer Joins
Using a Left Outer Join

LENGTH statement, DATA step

Reducing the Length of Character Data with the LENGTH Statement
Assigning Lengths to Numeric Variables

LIB= option, FORMAT procedure

LIBNAME statement

LIBRARY= option, DATASETS procedure

librefs, views and

LIKE clause, CREATE TABLE statement (SQL)

LIKE conditional operator

description

general form

identifying conditions to optimize

specifying patterns

literal tokens

local symbol table

The Local Symbol Table
The %LOCAL Statement
Multiple Local Symbol Tables

LOG statement

BEFORE_IMAGE option

controlling data in audit trails

DATA_IMAGE option

ERROR_IMAGE option

logical expressions in macro programs

The %EVAL Function
Automatic Evaluation

lookup tables

defined

Overview
Introduction

hash objects as

multidimensional arrays and

multiple

stored array values

Overview
Creating an Array
Loading the Array Elements
Reading the Actual Values

TRANSPOSE function

Overview
Adding Descriptive Variable Names

transposed data sets

Structuring the Data for a Merge
Using a BY Statement with PROC TRANSPOSE
Sorting the Work.Ctarget2 Data Set
Reorganizing the Sasuser.Monthsum Data Set
Sorting the Work.Mnthsum2 Data Set
Completing the Merge

LOOPS= option, SQL procedure

M

macro character functions

%INDEX function

%LENGTH function

%QSCAN function

%QSUBSTR function

%QUPCASE function

%SCAN function

%SUBSTR function

%UPCASE function

manipulating character strings

macro definitions

macro facility

Macro Variables
Macro Triggers

macro language

macro processor

functionality

macro facility and

macro variables rules

referencing macro variables

macro programs

%DO-%END statement

%EVAL function

%GLOBAL statement

%IF-%THEN/%ELSE macro statement

Conditional Execution
%IF-%THEN Compared to IF-THEN
Macro Execution with Conditional Processing
Conditional Processing of Parts of Statements
Case Sensitivity in Macro Comparisons

%LOCAL statement

%SYSEVALF function

arithmetic/logical expressions

The %EVAL Function
Automatic Evaluation

automatic evaluation

calling

case sensitivity and

comments in

compiling

Compiling a Macro
The MCOMPILENOTE= Option

conditionally processing statements

Conditional Execution
%IF-%THEN Compared to IF-THEN
Macro Execution with Conditional Processing
Conditional Processing of Parts of Statements
Case Sensitivity in Macro Comparisons

debugging

Monitoring Execution with System Options
The MLOGIC Option
Comments in Macro Programs

defined

developing

Monitoring Execution with System Options
The MLOGIC Option
Comments in Macro Programs

executing

functionality

iterative processing for

Overview
Generating Complete Steps

monitoring execution

Monitoring Execution with System Options
The MLOGIC Option
Comments in Macro Programs

nested

Multiple Local Symbol Tables
The MPRINTNEST Option

parameters and

Using Macro Parameters
Macros That Include Positional Parameters
Macros That Include Keyword Parameters
Macros That Include Mixed Parameter Lists
Macros That Include the PARMBUFF Option

Stored Compiled Macro Facility

The Stored Compiled Macro Facility
Creating a Stored Compiled Macro
Using the SOURCE Option
Accessing Stored Compiled Macros
Accessing Stored Macro Code

storing definitions in catalog SOURCE entries

Overview
The CATALOG Procedure
The CATALOG Access Method

storing in autocall libraries

The CATALOG Procedure
Overview
Creating an Autocall Library
Default Autocall Library
Accessing Autocall Macros

storing macro in external files

storing session-compiled macros

symbol tables

The Global Symbol Table
The %GLOBAL Statement
The Local Symbol Table
The %LOCAL Statement
Rules for Creating and Updating Variables
Multiple Local Symbol Tables
The MPRINTNEST Option
The MLOGICNEST Option

macro quoting functions

%BQUOTE function

%NRSTR function

%STR function

masking special characters

macro triggers

macro variables

%PUT statement

automatic

Macro Variables
Macro Variables [2]
Overview
The Global Symbol Table

combining references with text

creating delimited list of values

creating during DATA step

creating during PROC SQL execution

creating multiple during DATA step

creating with INTO clause

DATALINES statement and

delimiters in names

displaying values in SAS log

The SYMBOLGEN Option
The %PUT Statement

Forward Re-Scan rule

functionality

Introduction
Overview

in symbol tables

The Global Symbol Table
The %GLOBAL Statement
The Local Symbol Table
The %LOCAL Statement
Rules for Creating and Updating Variables
Multiple Local Symbol Tables
The MPRINTNEST Option
The MLOGICNEST Option

macro facility and

macro parameters and

Using Macro Parameters
Macros That Include Positional Parameters
Macros That Include Keyword Parameters
Macros That Include Mixed Parameter Lists
Macros That Include the PARMBUFF Option

macro processor rules

obtaining values during DATA step

processing

PUT function

The %SYSFUNC Function
PUT Function

referencing

Referencing Macro Variables
Overview

referencing indirectly

SAS function support

SCL program support

Overview
The SYMPUTN Routine
The SYMGETN Function

SYMBOLGEN system option

SYMGET function

SYMGETN function

SYMPUT routine

The SYMPUT Routine
Using SYMPUT with a Literal
Using SYMPUT with a DATA Step Variable
Using CALL SYMPUT with DATA Step Expressions
Creating Multiple Macro Variables with CALL SYMPUT

SYMPUTN routine

SYMPUTX routine

tokenization

triggers for

user-defined

Macro Variables
The %LET Statement
The Global Symbol Table

many-to-many match

Relationships between Input Data Sources
Examples

masking special characters

%BQUOTE function

%NRSTR function

%STR function

macro function support

MAUTOLOCDISPLAY system option

MAUTOSOURCE system option

MCOMPILENOTE= system option

MEANS procedure

general form

multilabel formats

NOPRINT option

NWAY option

OUTPUT statement

The MEANS Procedure
Using the WHERE= Output Data Set Option in PROC MEANS to Select Desired Types

tools for summarizing data

Overview
Using PROC MEANS to Display Summary Statistics for Combinations of Class Variables
Comparing Resource Usage across Three Techniques for Using PROC MEANS

TYPES statement

WAYS statement

WHERE= data set option

memory

controlling number of buffers

Measuring I/O
Using the BUFSIZE= Option
Using the BUFNO= Option

controlling page size

Measuring I/O
Page Size
Reporting Page Size

MEMRPT system option

MERGE statement, DATA step

The DATA Step Match-Merge
Working with Multiple Lookup Tables

message characters

MESSAGE= option, CREATE TABLE statement (SQL)

Creating a Constraint in a Column Specification
Creating a Constraint By Using a Constraint Specification

metadata

methods

missing values in data sets

MLF option, TABULATE procedure

MLOGIC system option

MLOGICNEST system option

MODIFY clause, ALTER TABLE statement (SQL)

MODIFY statement, DATA step

audit trails and

BY statement

Overview
Handling Duplicate Values
Monitoring I/O Error Conditions

functionality

Introduction
Using the MODIFY Statement

general form

KEY= option

Quiz
Overview
Handling Duplicate Values
Monitoring I/O Error Conditions

POINT= option

UNIQUE option

UPDATEMODE= option

MODIFY statement, DATASETS procedure

Overview
Overview [2]

MONTH function

MPRINT system option

MPRINTNEST system option

MSGLEVEL= system option

Determining Whether SAS Is Using an Index
Examples
Specifying MSGLEVEL=I to Determine Whether SAS Used an Index

MSGTYPE= option, CREATE TABLE statement (SQL)

Creating a Constraint in a Column Specification
Creating a Constraint By Using a Constraint Specification

MSTORED system option

The Stored Compiled Macro Facility
Accessing Stored Compiled Macros

multidimensional arrays

multilabel formats

MULTILABEL option, VALUE statement (FORMAT)

multiple columns

Ordering by Multiple Columns
Using a Summary Function with Multiple Arguments (Columns)

multiple indexes

multiple tables

querying

specifying columns in

specifying names

views and

Referencing an In-Line View with Other Views or Tables
Referencing Multiple Tables in an In-Line View
Joining Multiple Tables and Views

N

name tokens

Tokenization
Macro Triggers

NAME= option, TRANSPOSE procedure

nested macros

Multiple Local Symbol Tables
The MPRINTNEST Option

NMISS function

NODOUBLE option, SQL procedure

NOERRORSTOP option, SQL procedure

NOEXEC option, SQL procedure

Using the NOEXEC Option
Stopping Execution in PROC SQL after an Error

NOFLOW option, SQL procedure

NOFMTERR system option

NOFULLSTIMER system option

NOMEMRPT system option

noncorrelated subqueries

defined

multiple-value

single-value

subsetting data

nonmatching data

Relationships between Input Data Sources
Examples

NONUMBER option, SQL procedure

NOPRINT option

MEANS procedure

SELECT statement (SQL)

NOPROMPT option, SQL procedure

Example
Restricting the Number of Loops

NOSTATS system option

NOSTIMER option, SQL procedure

NOSTIMER system option

NOT EXISTS conditional operator

NOT NULL constraint type

constraint specification

functionality

Overview
General Integrity Constraints
Overview

in column specification

NOTSORTED option

TRANSPOSE procedure

NUMBER option, SQL procedure

number tokens

numeric data types

Specifying Data Types
Specifying Column Widths

numeric variables, storing

Reducing Data Storage Space for Numeric Variables
How SAS Stores Numeric Variables
Assigning Lengths to Numeric Variables
Maintaining Precision in Reduced-Length Numeric Variables
Using PROC COMPARE

NWAY option, MEANS procedure

O

OBS= option, WHERE statement (PRINT)

OBSBUF= data set option

one-to-many match

Relationships between Input Data Sources
Examples

one-to-one match

Relationships between Input Data Sources
Examples

OPTIONS statement, SQL procedure

BUFNO= option

BUFSIZE= option

COMPRESS= option

FMTERR option

FMTSEARCH= option

FULLSTIMER option

MAUTOLOCDISPLAY option

MAUTOSOURCE option

Accessing Autocall Macros
Accessing Autocall Macros [2]

MCOMPILENOTE= option

MEMRPT option

MLOGIC option

MLOGIC system option

MLOGICNEST option

MLOGICNEST system option

MPRINT option

MPRINT system option

MPRINTNEST option

MPRINTNEST system option

MSGLEVEL= option

Determining Whether SAS Is Using an Index
Examples

MSGLEVEL= system option

MSTORED option

The Stored Compiled Macro Facility
Accessing Stored Compiled Macros

NOFMTERR option

NOFULLSTIMER option

NOMEMRPT option

NOSTATS option

NOSTIMER option

REUSE= option

SASAUTOS option

SASAUTOS= option

SASMSTORE option

SASMSTORE= option

STATS option

STIMER option

Writing Timing Information for Each Statement
Using SAS System Options to Track Resources

SYMBOLGEN option

ORDER BY clause, SELECT statement (SQL)

ordering by multiple columns

ordering rows

Overview
Ordering Rows

outer joins

functionality

general form

inner joins and

OUTER UNION set operator

CORR keyword and

Using the Keyword CORR with the OUTER UNION Operator
Comparing Outer Unions and Other SAS Techniques

functionality

Introducing Set Operators
Overview
Using the OUTER UNION Operator Alone

OUTOBS= option, SQL procedure

general form

restricting row processing

summary functions and

output

adding character constants

controlling

detail reports

double-spacing

eliminating duplicate rows from

enhancing for queries

flowing characters in columns

including row numbers

SYMPUT routine support

OUTPUT statement

DATA step

MEANS procedure

The MEANS Procedure
Using the WHERE= Output Data Set Option in PROC MEANS to Select Desired Types

output tables, creating

P

parameters

in macro programs

Using Macro Parameters
Macros That Include Positional Parameters
Macros That Include Keyword Parameters
Macros That Include Mixed Parameter Lists
Macros That Include the PARMBUFF Option

keyword

Macros That Include Keyword Parameters
Macros That Include Mixed Parameter Lists

PARMBUFF option

positional

Macros That Include Positional Parameters
Macros That Include Mixed Parameter Lists

PARMBUFF option, %MACRO statement

patterns

Using the LIKE Operator to Select a Pattern
Specifying a Pattern

percent sign (%)

macro programs and

specifying directives

tokens and

performance

testing and evaluating

period (.)

PICTURE statement, FORMAT procedure

DATATYPE= option

general form

specifying directives

specifying pictures

PLOT procedure

POINT= option

MODIFY statement (DATA)

SET statement (DATA)

POINTOBS= data set option

positional parameters

Macros That Include Positional Parameters
Macros That Include Mixed Parameter Lists

PREFIX= option, TRANSPOSE procedure

PRIMARY KEY constraint type

constraint specification

functionality

Overview
General Integrity Constraints
Overview

in column specification

PRINT procedure

detail reports

UNIFORM option

WHERE statement

PROC FCMP

process management

best practices

Overview
Executing the DATASETS Procedure
RUN-Group Processing
Using Different Types of RUN Groups with PROC DATASETS

conditional processing for macro programs

Conditional Execution
%IF-%THEN Compared to IF-THEN
Macro Execution with Conditional Processing
Conditional Processing of Parts of Statements
Case Sensitivity in Macro Comparisons

controlling execution

controlling output

controlling updates

Overview
Monitoring I/O Error Conditions

creating macro variables in DATA step

creating macro variables in PROC SQL

The INTO Clause and the NOPRINT Option
Creating Variables with the INTO Clause
Creating a Delimited List of Values

creating multiple macro variables in DATA step

Dictionary tables

Introduction
Overview

error handling

for macro programs

Monitoring Execution with System Options
The MLOGIC Option
Comments in Macro Programs

Forward Re-Scan rule

hash objects

iterative processing for macro programs

Overview
Generating Complete Steps

macro variables

SAS Processing
Rules for Creating and Updating Variables

macro variables in SCL programs

Overview
The SYMPUTN Routine
The SYMGETN Function

obtaining macro variable values in DATA step

PUT function

The %SYSFUNC Function
PUT Function

referencing macro variables indirectly

resetting options

restricting number of loops

specifying SQL options

SYMGET function

SYMGETN function

SYMPUT routine

The SYMPUT Routine
Using SYMPUT with a Literal
Using SYMPUT with a DATA Step Variable
Using CALL SYMPUT with DATA Step Expressions
Creating Multiple Macro Variables with CALL SYMPUT

SYMPUTN routine

SYMPUTX routine

testing and evaluating performance

working with views

PROMPT option, SQL procedure

Example
Restricting the Number of Loops

PUT function

The %SYSFUNC Function
PUT Function
User-Defined SAS Formats

PUTC function

PUTN function

Q

queries

comparing resource usage

Overview
How SAS Compares Resource Usage
Factors That Affect I/O
Subset Size Relative to Data Set Size
Number of Pages in the Data File
Order of the Data
Cost to Uncompress a Compressed File for a Sequential Read
Other Factors That Affect Resource Usage

creating output tables

creating tables from

detail reports

Dictionary tables and

displaying all columns

eliminating duplicate rows from output

enhancing output

estimating observations

identifying available indexes

Overview
Compound Optimization

identifying conditions to optimize

Identifying Conditions That Can Be Optimized
WHERE Conditions That Cannot Be Optimized
Requirements for Compound Optimization

in-line views

indexes and

PROC SQL Queries That Can Be Optimized by an Index
Overview
Accessing Data Sequentially
Accessing Data Directly
Benefits and Costs of Using an Index
How SAS Selects an Access Method

inserting rows from

joining multiple tables and views

limiting number of rows displayed

ordering rows

Overview
Ordering Rows

querying multiple tables

selecting columns

specifying subsetting criteria

specifying tables

subqueries for subsetting data

subsetting data using correlated subqueries

subsetting data using noncorrelated subqueries

subsetting rows using calculated values

subsetting rows with conditional operators

summarizing groups of data

Summarizing Groups of Data
Overview

tools for summarizing data

Overview
Comparing Resource Usage across Summarization Tools
Understanding Types

validating general form

viewing all columns

viewing SELECT statement general form

views in

Introduction
PROC SQL Views

writing SQL procedure steps

quotation marks

literal tokens

macro quoting functions and

macro triggers and

referencing macro variables

R

raw data filenames, storing

Storing Raw Data Filenames in a SAS Data Set
Storing Raw Data Filenames in an External File

referential integrity constraints

Referential Integrity Constraints
Referential Integrity Constraints [2]

REMOVE statement, DATA step

RENAME statement

DATA step

DATASETS procedure

RENAME= data set option

REPLACE statement, DATA step

REPORT procedure

RESET statement, SQL procedure

Additional Features
Overview

resource management

assessing needs

Assessing Efficiency Needs at Your Site
Assessing Your Technical Environment
Assessing Your Programs
Assessing Your Data

benchmark guidelines

comparing probable usage

Overview
How SAS Compares Resource Usage
Factors That Affect I/O
Subset Size Relative to Data Set Size
Number of Pages in the Data File
Order of the Data
Cost to Uncompress a Compressed File for a Sequential Read
Other Factors That Affect Resource Usage

comparing summarization tools

Comparing Resource Usage across Summarization Tools
Comparing Resource Usage across Three Techniques for Using PROC MEANS

computer resources

efficiency trade-offs

system options for

RETAIN statement, DATA step

return codes (FIND method)

REUSE= data set option

REUSE= system option

right outer join

Introducing Types of Outer Joins
Using a Right Outer Join

rows

accessing in tables

counting all

counting number of

deleting in tables

duplicate

Eliminating Duplicate Rows from Output
Processing Unique versus Duplicate Rows

eliminating duplicates from output

estimating number of

handling insertion errors

inserting in tables

Overview
Overview [2]
Inserting Rows By Using the SET Clause
Inserting Rows By Using the VALUES Clause

joining tables with matching values

limiting number displayed

ordering

Overview
Ordering Rows

ordering by multiple columns

processing unique vs. duplicate

restricting processing

row numbers in output

updating based on CASE expression

How PROC SQL Updates Rows Based on a CASE Expression
Updating Rows By Using the CASE Expression without a Case Operand
Updating Rows By Using the CASE Expression with a Case Operand
Using the CASE Expression in the SELECT Statement

updating values

updating with different expressions

updating with same expression

RUN statement

Executing the DATASETS Procedure
RUN-Group Processing
Using Different Types of RUN Groups with PROC DATASETS

RUN-group processing

Executing the DATASETS Procedure
RUN-Group Processing
Using Different Types of RUN Groups with PROC DATASETS

S

SAS log

%PUT statement

displaying macro variable values

The SYMBOLGEN Option
The %PUT Statement

MCOMPILENOTE= option

SYMBOLGEN system option

SASAUTOS= system option

SASMSTORE system option

SASMSTORE= system option

SCL programs

macro variables in

Overview
The SYMPUTN Routine
The SYMGETN Function

SYMGETN function

SYMPUTN routine

security, table views

SELECT clause

CREATE TABLE statement (SQL)

INSERT statement (SQL)

SELECT statement (SQL)

Overview
Specifying Columns That Appear in Multiple Tables
Using SELECT *
Using the FEEDBACK Option
Eliminating Duplicate Rows from Output
Overview
SELECT Clause Columns and Summary Function Processing

SELECT statement, FORMAT procedure

best practices

managing formats

Using FMTLIB with PROC FORMAT to Document Formats
Overview

SELECT statement, SQL procedure

best practices

CASE expression

creating new columns

DISTINCT keyword

FEEDBACK option

FROM clause

Specifying the Table
Specifying Multiple Table Names
Generating a Cartesian Product
Introducing In-Line Views

functionality

The SELECT Statement
Introduction

general form

general form for inner join

Introducing Inner Join Syntax
Creating an Inner Join with Outer Join-Style Syntax

general form for outer join

general form for set operations

GROUP BY clause

Summarizing Groups of Data
Overview
Groups and Summary Function Processing
Using a Summary Function without a GROUP BY Clause
Using a Summary Function with a GROUP BY Clause
Selecting Groups By Using the HAVING Clause

HAVING clause

Additional Features
Selecting Groups By Using the HAVING Clause

INTO clause

The INTO Clause and the NOPRINT Option
Creating Variables with the INTO Clause
Creating a Delimited List of Values

LABEL= option

NOPRINT option

ORDER BY clause

Overview
Ordering by Multiple Columns
Ordering Rows

ordering by multiple columns

ordering rows

querying multiple tables

SELECT clause

Overview
Specifying Columns That Appear in Multiple Tables
Using SELECT *
Using the FEEDBACK Option
Eliminating Duplicate Rows from Output
Overview
SELECT Clause Columns and Summary Function Processing

selecting columns

specifying columns in multiple tables

specifying multiple table names

specifying subset criteria

specifying tables

summarizing groups of data

Overview
SELECT Clause Columns and Summary Function Processing

VALIDATE keyword

viewing all columns

Displaying All Columns
Using SELECT *

WHERE clause

Specifying Subsetting Criteria
Specifying a Join Condition
Overview
Understanding How PROC SQL Processes Calculated Columns
Using the Keyword CALCULATED

SELECT/WHEN statement

semicolon (;) in macro programs

sequential access

session-compiled macros

SET clause

INSERT statement (SQL)

Overview
Inserting Rows By Using the SET Clause

UPDATE statement (SQL)

set operations

combining and overlaying columns

defined

EXCEPT set operator

Introducing Set Operators
Overview
Using the Keyword ALL with the EXCEPT Operator
Using the Keyword CORR with the EXCEPT Operator
Using the Keywords ALL and CORR with the EXCEPT Operator

INTERSECT set operator

Introducing Set Operators
Overview
Using the Keyword ALL with the INTERSECT Operator
Using the Keyword CORR with the INTERSECT Operator
Using the Keywords ALL and CORR with the INTERSECT Operator

modifying results via keywords

OUTER UNION set operator

Introducing Set Operators
Overview
Comparing Outer Unions and Other SAS Techniques

processing multiple operations

Using Multiple Set Operators
Processing Multiple Set Operations

processing single operations

processing unique vs. duplicate rows

UNION set operator

Introducing Set Operators
Overview
Using the Keyword ALL with the UNION Operator
Using the Keyword CORR with the UNION Operator
Using the Keywords ALL and CORR with the UNION Operator

SET statement, DATA step

hash objects and

KEY= option

multiple

Using Multiple SET Statements
Example: Using Multiple SET Statements with a Many-to-Many Match

POINT= option

simple indexes

creating

defined

Simple and Composite Indexes
Introduction
Types of Indexes

SORT procedure

WHERE statement

sorting

ordering by multiple columns

ordering rows

sounds-like conditional operator

description

general form

WHERE conditions and

SOURCE entries, storing

Overview
The CATALOG Procedure
The CATALOG Access Method

SOURCE option, %MACRO statement

Using the SOURCE Option
Accessing Stored Macro Code

sparse match

special characters, masking

%BQUOTE function

%NRSTR function

%STR function

macro function support

special tokens

spelling variations

SQL procedure

accessing metadata

combining data horizontally

Overview
DATA Step Match-Merge
Execution of a DATA Step Match-Merge
Execution of a PROC SQL Join
Using Multiple SET Statements

creating macro variables

The INTO Clause and the NOPRINT Option
Creating Variables with the INTO Clause
Creating a Delimited List of Values

detail reports

DOUBLE option

ERRORSTOP option

EXEC option

FEEDBACK option

FLOW option

functionality

general form

Overview
When Only Some of the Values Match

INOBS= option

INSERT statement

joining data

LOOPS= option

managing indexes

NODOUBLE option

NOERRORSTOP option

NOEXEC option

Using the NOEXEC Option
Stopping Execution in PROC SQL after an Error

NOFLOW option

NONUMBER option

NOPROMPT option

Example
Restricting the Number of Loops

NOSTIMER option

NUMBER option

OUTOBS= option

Overview
Restricting Row Processing

PROMPT option

Example
Restricting the Number of Loops

specifying options

STIMER option

tools for summarizing data

UNDO_POLICY option

unique features

writing steps

SQLOOPS macro variable

STATS system option

STIMER option, SQL procedure

STIMER system option

Writing Timing Information for Each Statement
Using SAS System Options to Track Resources

storage

array values

Overview
Creating an Array
Loading the Array Elements
Reading the Actual Values

best practices

compressing data files

Compressing Data Files
Review of Uncompressed Data File Structure
Compressed Data File Structure
Deciding Whether to Compress a Data File
The COMPRESS= System Option and the COMPRESS= Data Set Option
Accessing Observations Directly in a Compressed Data Set
The POINTOBS= Data Set Option
The REUSE= System Option and the REUSE= Data Set Option
How SAS Compresses Data

conserving with DATA step views

Overview
DATA Step Views
The DESCRIBE Statement
Creating and Referencing a SAS DATA Step View
Referencing a Data View Multiple Times in One Program
Making Multiple Passes through Data in a Data View
Creating Data Views on Unstable Data

in autocall libraries

The CATALOG Procedure
Overview
Creating an Autocall Library
Default Autocall Library
Accessing Autocall Macros

macro definitions in catalog SOURCE entries

Overview
The CATALOG Procedure
The CATALOG Access Method

macro definitions in external files

permanent locations for formats

raw data filenames in data sets

raw data filenames in external files

reducing for character variables

reducing for numeric variables

Reducing Data Storage Space for Numeric Variables
How SAS Stores Numeric Variables
Assigning Lengths to Numeric Variables
Maintaining Precision in Reduced-Length Numeric Variables
Using PROC COMPARE

session-compiled macros

Stored Compiled Macro Facility

The Stored Compiled Macro Facility
Creating a Stored Compiled Macro
Using the SOURCE Option
Accessing Stored Compiled Macros
Accessing Stored Macro Code

STORE option, %MACRO statement

Stored Compiled Macro Facility

The Stored Compiled Macro Facility
Creating a Stored Compiled Macro
Using the SOURCE Option
Accessing Stored Compiled Macros
Accessing Stored Macro Code

SUBMIT block

subqueries

comparison operator in

correlated

Types of Subqueries
PROC SQL Queries That Can Be Optimized by an Index

noncorrelated

subsetting data

subsetting columns in tables

subsetting data

correlated subqueries and

indexes and

noncorrelated subqueries and

subqueries and

subsetting IF statement

Positioning of the Subsetting IF Statement
Selecting Observations Using Subsetting IF versus WHERE Statement
Other Differences between the IF and WHERE Statements

subsetting rows

functionality

specifying criteria

using calculated values

using conditional operators

subsetting variables

Subsetting Variables with the KEEP and DROP Statements and Options
Subsetting Variables with the KEEP and DROP Statements and Options [2]

SUBSTR function

Reading the Actual Values
Requirements for Optimizing a Single WHERE Condition
WHERE Conditions That Cannot Be Optimized

sum statement

summarizing data

comparing tools

Overview
Comparing Resource Usage across Summarization Tools
Comparing Resource Usage across Three Techniques for Using PROC MEANS
Understanding Types

GROUP BY clause, SELECT statement (SQL)

Summarizing Groups of Data
Overview
Groups and Summary Function Processing

SELECT clause and

summary functions

functionality

Summary Functions
Overview

GROUP BY clause and

Using a Summary Function without a GROUP BY Clause
Using a Summary Function with a GROUP BY Clause

number of arguments

on groups of data

SELECT clause and

with columns outside function

with multiple arguments

with single arguments

SUMMARY procedure

multilabel formats

tools for summarizing data

summary statistics

class variables and

Using PROC MEANS to Display Summary Statistics for Combinations of Class Variables
Using a Basic PROC MEANS Step to Combine All Class Variables

combining data and

Overview
The MEANS Procedure
The Sum Statement

MEANS procedure

The MEANS Procedure
Using PROC MEANS to Display Summary Statistics for Combinations of Class Variables

sum statement

symbol tables

%GLOBAL statement

%LOCAL statement

defined

global

Macro Variables
The Global Symbol Table

local

The Local Symbol Table
The %LOCAL Statement
Multiple Local Symbol Tables

SYMBOLGEN system option

SYMGET function

The SYMGET Function
Overview

SYMGETN function

SYMPUT routine

creating multiple macro variables

DATA step expressions and

DATA step variables and

general form

The SYMPUT Routine
Creating Multiple Macro Variables with CALL SYMPUT

SCL program support

SYMPUTN routine

SYMPUTX routine

SYSDATE automatic macro variable

SYSDATE9 automatic macro variable

Overview
Example
The %SYSFUNC Function

SYSDAY automatic macro variable

SYSERR automatic macro variable

SYSJOBID automatic macro variable

SYSLAST automatic macro variable

Overview
Example

SYSPARM automatic macro variable

SYSSCP automatic macro variable

system options, tracking resources

SYSTENV automatic macro variable

SYSTIME automatic macro variable

Overview
The %SYSFUNC Function

SYSVER automatic macro variable

T

table aliases, specifying

TABLE statement, TABULATE procedure

tables

base

copying

creating from query results

creating like others

creating output tables

creating with integrity constraints

Overview
General Integrity Constraints

displaying integrity constraints

displaying structure

dropping

empty

Overview
Overview [2]

generating Cartesian products

joining with SQL procedure

methods of creating

querying multiple

specifying

specifying data types

specifying multiple table names

symbol

updating values

views and

Referencing an In-Line View with Other Views or Tables
Referencing Multiple Tables in an In-Line View
Joining Multiple Tables and Views
Introduction
Creating a View to Enhance Table Security

virtual

TABULATE procedure

CLASS statement

FORMAT statement

FORMAT= option

MLF option

multilabel formats

TABLE statement

tools for summarizing data

testing performance

text, macro variable references and

TITLE statement, SQL procedure

titles, specifying

tokens

literal

macro triggers and

macro variables and

name

Tokenization
Macro Triggers

number

percent sign and

special

transaction data sets

Overview
Using the UPDATE Statement
Overview

TRANSPOSE procedure

adding variable names

BY statement

general form

NAME= option

NOTSORTED option

PREFIX= option

RENAME= data set option

VAR statement

transposed data sets

Structuring the Data for a Merge
Using a BY Statement with PROC TRANSPOSE
Sorting the Work.Ctarget2 Data Set
Reorganizing the Sasuser.Monthsum Data Set
Sorting the Work.Mnthsum2 Data Set
Completing the Merge

TRIM function

TYPE= data set option

TYPES statement, MEANS procedure

U

UNDO_POLICY option, SQL procedure

UNIFORM option, PRINT procedure

UNION set operator

ALL keyword and

Using the Keyword ALL with the UNION Operator
Using the Keywords ALL and CORR with the UNION Operator

CORR keyword and

Using the Keyword CORR with the UNION Operator
Using the Keywords ALL and CORR with the UNION Operator

functionality

Introducing Set Operators
Overview

UNIQUE constraint type

constraint specification

functionality

Overview
General Integrity Constraints
Overview

in column specification

unique indexes

Unique Indexes
Example: Creating a Composite, Unique Index

UNIQUE option

DATA step

MODIFY statement (DATA)

UNIVARIATE procedure

Using Conditional Logic Efficiently
Overview

update process

controlling

Overview
Monitoring I/O Error Conditions
Using _IORC_ with %SYSRC

for views

UPDATE statement, DATA step

UPDATE statement, SQL procedure

audit trails and

CASE expression

Updating Rows By Using Different Expressions
How PROC SQL Updates Rows Based on a CASE Expression
How the Case Operand Works

controlling UNDO processing

functionality

general form

SET clause

updating table row values

updating views

WHERE clause

Updating Rows By Using the Same Expression
Updating Rows By Using Different Expressions
Overview

UPDATEMODE= option, MODIFY statement (DATA)

user variables

user-defined macro variables

%LET statement and

defined

global symbol table and

USER_VAR statement

USING clause, CREATE VIEW statement (SQL)

V

VALIDATE keyword

VALUE statement, FORMAT procedure

combining data

creating formats

User-Defined SAS Formats
Review of Creating Non-Overlapping Formats

creating formats with overlapping ranges

MULTILABEL option

Creating a Format with Overlapping Ranges
Example

value/identifier pairs

VALUES clause, INSERT statement (SQL)

functionality

Overview
Inserting Rows By Using the VALUES Clause

handling errors for row insertions

VAR statement, TRANSPOSE function

variables

accumulator

adding descriptive names

appending with different lengths

appending with different types

associating formats with

audit trail

Audit Trail Variables
Values of the _ATOPCODE_ Variable

best practices

character

class

Using PROC MEANS to Display Summary Statistics for Combinations of Class Variables
Using a Basic PROC MEANS Step to Combine All Class Variables
Understanding Types
Using the TYPES Statement in PROC MEANS to Combine Class Variables
Using the NWAY Option in PROC MEANS to Combine Class Variables

hash objects and

key

listing in data sets

numeric

Reducing Data Storage Space for Numeric Variables
How SAS Stores Numeric Variables
Assigning Lengths to Numeric Variables
Maintaining Precision in Reduced-Length Numeric Variables
Using PROC COMPARE

renaming

subsetting

Subsetting Variables with the KEEP and DROP Statements and Options
Subsetting Variables with the KEEP and DROP Statements and Options [2]

summary statistics and

Using PROC MEANS to Display Summary Statistics for Combinations of Class Variables
Using a Basic PROC MEANS Step to Combine All Class Variables

user

WHERE conditions and

VIEW= option, DATA step

views

benefits

conserving storage space

Overview
DATA Step Views
The DESCRIBE Statement
Creating and Referencing a SAS DATA Step View
Referencing a Data View Multiple Times in One Program
Making Multiple Passes through Data in a Data View
Creating Data Views on Unstable Data

creating

deleting

displaying definitions

dropping

enhancing table security

functionality

Using PROC SQL Views
Using PROC SQL Views [2]
Working with PROC SQL Views

in queries

in-line

Introducing In-Line Views
Referencing an In-Line View with Other Views or Tables
Referencing Multiple Tables in an In-Line View
Assigning an Alias to an In-Line View

librefs and

managing

tables and

Referencing an In-Line View with Other Views or Tables
Referencing Multiple Tables in an In-Line View
Joining Multiple Tables and Views
Introduction
Creating a View to Enhance Table Security

updating

VIEWTABLE window

virtual tables

W

WAYS statement, MEANS procedure

WHERE clause

SELECT statement (SQL)

Specifying Subsetting Criteria
Specifying a Join Condition
Overview
Using the Keyword CALCULATED

UPDATE statement (SQL)

Updating Rows By Using the Same Expression
Updating Rows By Using Different Expressions
Overview

WHERE condition

compound optimization and

controlling index usage

not optimized

optimizing

printing centile information

WHERE statement

DATA step

Selecting Observations Using Subsetting IF versus WHERE Statement
Other Differences between the IF and WHERE Statements
Overview
Accessing Data Directly
Example
Overview
Compound Optimization

PRINT procedure

SORT procedure

WHERE= data set option

word scanner

macro triggers and

tokenization and