Index

Special Characters

; (semicolon)

? conditional operator

Using Operators in PROC SQL
Using the CONTAINS Operator to Select a String

% (percent sign)

macro programs and

specifying directives

tokens and

%a directive

%A directive

%b directive

%B directive

%CMPRES statement

%d directive

%DATATYP statement

%DO-%END statement

A Brief Overview
%DO-%END Statement with %IF-%THEN Statement Syntax
A Brief Overview
Example: Generating Complete Steps

%EVAL function

A Brief Overview
%EVAL Syntax
Example: Using the %EVAL Function

%GLOBAL statement

%H directive

%I directive

%IF-%THEN/%ELSE macro statement

A Brief Overview
The %IF-%THEN and %ELSE Statement Syntax

%INCLUDE statement

%INDEX function

%INDEX Syntax
Example: Using the %INDEX Function

%j directive

%LEFT statement

%LENGTH function

%LET statement

macro parameter support

user-defined macro variables and

%LOCAL statement

A Brief Overview
%LOCAL Statement Syntax

%LOWCASE statement

%m directive

%M directive

%MACRO statement

macro parameter support

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

syntax

%MACRO and %MEND Statements Syntax
Example: Defining a Macro

%MEND statement

%MACRO and %MEND Statements Syntax
Example: Defining a Macro

%NRSTR function

%p directive

%PUT statement

%QLOWCASE statement

%S directive

%SCAN function

A Brief Overview
%SCAN Syntax

%STR function

%SUBSTR function

A Brief Overview
%SUBSTR Syntax

%SYMDEL statement

A Brief Overview
%SYMDEL Syntax
Example: Using the %SYMDEL Statement

%SYSEVALF function

A Brief Overview
%SYSEVALF Syntax
Example: Using the %SYSEVALF Function

%SYSFUNC function

A Brief Overview
%SYSFUNC Syntax

%TRIM statement

%U directive

%UPCASE function

The %UPCASE Function
%UPCASE Syntax
Example: Using the %UPCASE Function
Example: Using the %SUBSTR Function

%w directive

%y directive

%Y directive

=* conditional operator

description

syntax

A

ADD method

The ADD Method
Example: Adding Key and Data Values

adding key and data values

ADD method

The ADD Method
Example: Adding Key and Data Values

aliases

column

Example: Creating a New Column
Example: Using Calculated Values in a WHERE Clause
Example: Renaming a Column by Using a Column Alias

table

ALL conditional operator

Using Operators in PROC SQL
Using the ALL Operator

ALL keyword

EXCEPT set operator and

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

INTERSECT set operator and

Example: Using the Keyword ALL with the INTERSECT Set Operator
Example: Using the Keywords ALL and CORR with the INTERSECT Set Operator

set operations and

UNION set operator and

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

ANY conditional operator

comparison operator and

description

arguments, summary functions and

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

array processing

compilation phase

execution phase

ARRAY statement

syntax

arrays

assigning initial values

column lists

creating character columns

defining elements

DIM function

Using the DIM Function
Example: Using the DIM Function in an Iterative DO Statement

one-dimensional

Defining and Referencing One-Dimensional Arrays
ARRAY Statement Syntax
Defining the Number of Elements
Specifying the Array Elements
Using Column Lists as Array Elements
Referencing a One-Dimensional Array
DO Statement Syntax
Example: Processing Repetitive Code
Handling an Unknown Number of Array Elements
Using the DIM Function
Example: Using the DIM Function in an Iterative DO Statement
Compilation and Execution Phases for Array Processing
Creating Character Columns with an Array
Specifying Lower and Upper Bounds
Assigning Initial Values to Arrays
Specifying Temporary Array Elements
Example: Rotating Data

processing

referencing one-dimensional array

rotating data

specifying elements

specifying lower and upper bounds

table lookup

temporary elements

two-dimensional

A Brief Overview
Example: Creating a Two-Dimensional Array with Initial Values
Example: Creating a Two-Dimensional Array to Perform Table Lookup

unknown number of array elements

AS keyword, CREATE TABLE statement (SQL)

autocall libraries

accessing macros

creating

default

defined

automatic macro variables

functionality

global symbol table and

AVG function

B

BETWEEN-AND conditional operator

description

syntax

BY variable

joining tables

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

C

CALCULATED keyword

CALL MISSING routine

CALL SYMPUTX routine

syntax

Cartesian product

character data types

column widths and

defined

character strings

%INDEX function

%INDEX Syntax
Example: Using the %INDEX Function

%LENGTH function

%SCAN function

A Brief Overview
%SCAN Syntax

%SUBSTR function

A Brief Overview
%SUBSTR Syntax

macro character functions

CHART procedure

CMPLIB= option

syntax

COALESCE function

When Only Some of the Values Match
COALESCE Function Syntax

column alias

CALCULATED keyword and

defined

renaming columns with

column modifiers

column widths

columns

as multiple arguments for summary functions

combining

counting nonmissing values

counting unique values

creating new

creating tables by defining

CREATE TABLE Statement Syntax
Example: Creating an Empty Table by Defining Column Structure

eliminating duplicate

ordering by multiple

ordering by position

outside summary functions

overlaying

processing calculated

renaming with column aliases

rows numbers in output

selecting

summary functions and

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

viewing all

comments in macro programs

A Brief Overview
Comment Statement Syntax
Example: Use Macro Comments

comparison operators

ALL conditional operator and

ANY conditional operator and

subqueries and

compiling macro programs

A Brief Overview
The MCOMPILENOTE= Option

conditional operators

conditional processing

for macro programs

A Brief Overview
The %IF-%THEN and %ELSE Statement Syntax

constants

CONTAINS conditional operator

description

syntax

controlling output

OUTPUT method

conventional join

CORR keyword

EXCEPT set operator and

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

INTERSECT operator and

Example: Using the Keyword CORR with the INTERSECT Set Operator
Example: Using the Keywords ALL and CORR with the INTERSECT Set Operator

OUTER UNION set operator and

set operations and

UNION set operator and

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

correlated subqueries

defined

EXISTS conditional operator

NOT EXISTS conditional operator

subsetting data

COUNT function

COUNT/COUNTC/COUNTW function

The COUNT/COUNTC/COUNTW Function
COUNT/COUNTC/COUNTW Syntax
Example: Counting the Number of Words

counting words

syntax

The COUNT/COUNTC/COUNTW Function
COUNT/COUNTC/COUNTW Syntax

CREATE TABLE statement, SQL procedure

CREATE TABLE Statement Syntax
Example: Creating an Empty Table by Defining Column Structure

AS keyword

creating empty tables

CREATE TABLE Statement Syntax
Example: Creating an Empty Table by Defining Column Structure

creating like other tables

creating tables from query results

defining column structures

displaying table structure

FORMAT= option

INFORMAT= option

LABEL= option

LIKE clause

specifying column modifiers

specifying column widths

specifying data types

specifying empty tables like other tables

syntax with LIKE clause

syntax with query clauses

CREATE VIEW statement, SQL procedure

FROM clause

syntax

USING clause

D

data

grouping

GROUP BY Clause Syntax
HAVING Clause Syntax

DATA step

CALL SYMPUTX routine and

CALL SYMPUTX Routine Syntax
Example: Using the CALL SYMPUTX Routine with Literal Strings

comparing joins and

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

IF-THEN/ELSE statement

match-merges

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

PUT function

A Brief Overview
A Brief Overview
PUT Function Syntax

SYMPUT routine and

SYMPUTX routine and

A Brief Overview
Using SYMPUTX with a Literal

data types

character

Specifying Data Types for Columns
Specifying Column Widths

creating tables by defining columns

numeric

Specifying Data Types for Columns
Specifying Column Widths

data-driven macro calls

DOSUBL function

DATALINES statement

DATATYPE= option, PICTURE statement (FORMAT)

debugging

FEEDBACK option, SQL procedure

macro programs

A Brief Overview
The MPRINT Option Syntax
Example: Using the MPRINT Option
Comment Statement Syntax
Example: Use Macro Comments
A Brief Overview
MLOGIC System Option Syntax

DECLARE statement

DEFINEDATA method

DEFINEKEY method

defining a hash object

DEFINEDATA

DEFINEDONE

DEFINEKEY

DELETE statement, SQL procedure

updating views

deleting

views

delimiters

in macro programs

in macro variable names

DESCRIBE TABLE statement, SQL procedure

displaying Dictionary table definitions

displaying table structure

syntax

DESCRIBE VIEW statement, SQL procedure

Dictionary tables

functionality

querying

DIM function

syntax

directives, specifying pictures

DISTINCT keyword

DO statement

processing repetitive code

DOSUBL function

DROP VIEW statement, SQL procedure

duplicate columns, eliminating

duplicate rows

eliminating from output

processing unique vs.

E

empty tables

EXCEPT set operator

ALL keyword and

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

CORR keyword and

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

functionality

Introducing Set Operators
A Brief Overview

EXISTS conditional operator

correlated queries and

description

external files

storing macro definitions in

F

FCMP procedure

CMPLIB= option

syntax

FedSQL procedure

LIBNAME statement

LIMIT clause

PUT function

syntax

system options

FEEDBACK option, SQL procedure

FIND method

The FIND Method
Retrieving Matching Data

FIND/FINDC/FINDW function

The FIND/FINDC/FINDW Function
FIND/FINDC/FINDW Function Syntax
Example: Finding the Word Number

finding the word number

syntax

The FIND/FINDC/FINDW Function
FIND/FINDC/FINDW Function Syntax

finding key values

FIND method

FORMAT procedure

PICTURE statement

PICTURE Statement Syntax
Example: Using the PICTURE Statement
Creating Custom Date, Time, and Datetime Formats Using Directives
Creating Custom Date, Time, and Datetime Formats Using Directives [2]
Specifying Date and Time Directives
PICTURE Statement Syntax with Digit Selectors
Digit Selectors with Non-Numeric Characters
The MULTIPLIER= Option
The PREFIX= Option
The ROUND Option

VALUE statement

FORMAT= option

CREATE TABLE statement (SQL)

formats

creating with PICTURE statement

FROM clause

CREATE VIEW statement (SQL)

INSERT statement (SQL)

The INSERT Statement with SELECT and FROM Clauses
A Brief Overview
INSERT Statement with SELECT and FROM Clause Syntax

SELECT statement (SQL)

FROM Clause Syntax
Generating a Cartesian Product

full outer join

A Brief Overview
Example: Using a Full Outer Join

functions

%EVAL function

A Brief Overview
%EVAL Syntax
Example: Using the %EVAL Function

%SYSEVALF function

A Brief Overview
%SYSEVALF Syntax
Example: Using the %SYSEVALF Function

%SYSFUNC function

A Brief Overview
%SYSFUNC Syntax

macro variable support

A Brief Overview
%SYSFUNC Syntax
A Brief Overview
%EVAL Syntax
Example: Using the %EVAL Function
%SYSEVALF Syntax
Example: Using the %SYSEVALF Function

G

GCHART procedure

global symbol table

%GLOBAL statement

macro variables and

GPLOT procedure

GROUP BY clause, SELECT statement (SQL)

grouping

selecting groups

summarizing groups of data

A Brief Overview
Groups and Summary Function Processing

summary functions and

groups, selecting

H

hash iterator object

Using Hash Iterator Objects
Declaring and Defining a Hash Object and a Hash Iterator Object
Retrieving Hash Object Data with the Hash Iterator Object

DECLARE statement

DEFINEDATA method

DEFINEDONE method

DEFINEKEY method

FIRST method

LAST method

NEXT method

PREV method

hash object processing

ADD method

CALL missing routine

DECLARE statement

DEFINEDATA method

DEFINEDONE method

DEFINEKEY method

FIND method

hash objects

ADD method

The ADD Method
Example: Adding Key and Data Values

CALL MISSING routine

declaring

defining

FIND method

multiple data variables

OUTPUT method

The OUTPUT Method
Controlling Output with the OUTPUT Method

retrieving matching data

structure

A Brief Overview
Data Components

HAVING clause, SELECT statement (SQL)

grouping

I

IF-THEN/ELSE statement

%IF-%THEN comparison

IN conditional operator

description

syntax

INFORMAT= option, CREATE TABLE statement (SQL)

inner joins

defined

INOBS= option, SQL procedure

INPUT function

INPUTC function

INPUTN function

INSERT statement, SQL procedure

FROM clause

The INSERT Statement with SELECT and FROM Clauses
A Brief Overview
INSERT Statement with SELECT and FROM Clause Syntax

inserting rows from query results

The INSERT Statement with SELECT and FROM Clauses
A Brief Overview
INSERT Statement with SELECT and FROM Clause Syntax

inserting rows of data in tables

A Brief Overview
The VALUES Clause
VALUES Clause Syntax

SELECT clause

A Brief Overview
INSERT Statement with SELECT and FROM Clause Syntax

SET clause

syntax

SET Clause Syntax
The VALUES Clause
VALUES Clause Syntax
A Brief Overview

updating views

VALUES clause

The VALUES Clause
VALUES Clause Syntax

INTERSECT set operator

ALL keyword and

Example: Using the Keyword ALL with the INTERSECT Set Operator
Example: Using the Keywords ALL and CORR with the INTERSECT Set Operator

CORR keyword and

Example: Using the Keyword CORR with the INTERSECT Set Operator
Example: Using the Keywords ALL and CORR with the INTERSECT Set Operator

functionality

Introducing Set Operators
A Brief Overview

INTO clause, SELECT statement (SQL)

IS MISSING conditional operator

Using Operators in PROC SQL
Using the IS MISSING or IS NULL Operator to Select Missing Values

IS NULL conditional operator

Using Operators in PROC SQL
Using the IS MISSING or IS NULL Operator to Select Missing Values

J

joining data sets

defined

joining tables

advantages

COALESCE function

When Only Some of the Values Match
COALESCE Function Syntax

comparing with DATA step match-merges

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

defined

EXCEPT set operator

for rows with matching values

inner joins

INTERSECT set operator

outer joins

OUTER UNION set operator

processes defined

set operations

UNION set operator

K

keyword parameters

Macros That Include Keyword Parameters
Macros That Include Mixed Parameter Lists

keywords, modifying set operations

L

LABEL= option

CREATE TABLE statement (SQL)

LAG function

The LAG Function
LAG Function Syntax
Example: Retrieving Previous Values
Example: Calculating a Moving Average

calculating a moving average

retrieving previous values

syntax

The LAG Function
LAG Function Syntax

LEFT function

left outer join

A Brief Overview
Example: Using a Left Outer Join

LIBNAME statement

FedSQL procedure

librefs, views and

LIKE clause, CREATE TABLE statement (SQL)

LIKE conditional operator

description

specifying patterns

syntax

LIMIT clause

FedSQL procedure

local symbol table

Local Symbol Table
A Brief Overview

%LOCAL statement

M

macro character functions

%INDEX function

%INDEX Syntax
Example: Using the %INDEX Function

%LENGTH function

%SCAN function

A Brief Overview
%SCAN Syntax

%SUBSTR function

A Brief Overview
%SUBSTR Syntax

%UPCASE function

The %UPCASE Function
%UPCASE Syntax
Example: Using the %UPCASE Function
Example: Using the %SUBSTR Function

manipulating character strings

macro definitions

macro programs

%DO-%END statement

A Brief Overview
%DO-%END Statement with %IF-%THEN Statement Syntax

%GLOBAL statement

%IF-%THEN/%ELSE macro statement

A Brief Overview
The %IF-%THEN and %ELSE Statement Syntax

%LOCAL statement

A Brief Overview
%LOCAL Statement Syntax

calling

comments in

A Brief Overview
Comment Statement Syntax
Example: Use Macro Comments

compiling

A Brief Overview
The MCOMPILENOTE= Option

conditionally processing statements

A Brief Overview
The %IF-%THEN and %ELSE Statement Syntax

debugging

A Brief Overview
The MPRINT Option Syntax
Example: Using the MPRINT Option
Comment Statement Syntax
Example: Use Macro Comments
A Brief Overview
MLOGIC System Option Syntax

defined

%MACRO and %MEND Statements Syntax
Example: Defining a Macro

developing

A Brief Overview
The MPRINT Option Syntax
Example: Using the MPRINT Option
Comment Statement Syntax
Example: Use Macro Comments
A Brief Overview
MLOGIC System Option Syntax

executing

iterative processing for

A Brief Overview
Example: Generating Complete Steps

log control

A Brief Overview
The MPRINT Option Syntax
A Brief Overview
MLOGIC System Option Syntax

monitoring execution

A Brief Overview
The MPRINT Option Syntax
Example: Using the MPRINT Option
Comment Statement Syntax
Example: Use Macro Comments
A Brief Overview
MLOGIC System Option Syntax

parameters and

Passing Information into a Macro Using Parameters
Macros That Include Positional Parameters
Macros That Include Keyword Parameters
Macros That Include Mixed Parameter Lists

storing in autocall libraries

A Brief Overview
Creating an Autocall Library
Default Autocall Library
Overview

storing macro in external files

storing session compiled macros

symbol tables

Scope of Macro Variables
%GLOBAL Statement Syntax
Local Symbol Table
A Brief Overview
%LOCAL Statement Syntax
A Brief Overview
The MPRINT Option Syntax
A Brief Overview
MLOGIC System Option Syntax

macro quoting functions

%NRSTR function

%STR function

masking special characters

macro variable in PROC SQL

displaying values in SAS log

macro variables

%PUT statement

automatic

What Are Automatic Macro Variables?
Scope of Macro Variables

CALL SYMPUTX routine

CALL SYMPUTX Routine Syntax
Example: Using the CALL SYMPUTX Routine with Literal Strings

combining references with text

creating during PROC SQL execution

delimiters in names

displaying values in SAS log

SYMBOLGEN Option Syntax
%PUT Statement Syntax

in symbol tables

Scope of Macro Variables
%GLOBAL Statement Syntax
Local Symbol Table
A Brief Overview
%LOCAL Statement Syntax
A Brief Overview
The MPRINT Option Syntax
A Brief Overview
MLOGIC System Option Syntax

macro parameters and

Passing Information into a Macro Using Parameters
Macros That Include Positional Parameters
Macros That Include Keyword Parameters
Macros That Include Mixed Parameter Lists

PUT function

A Brief Overview
A Brief Overview
PUT Function Syntax

referencing

referencing macro variables indirectly

SAS function support

A Brief Overview
%SYSFUNC Syntax
A Brief Overview
%EVAL Syntax
Example: Using the %EVAL Function
%SYSEVALF Syntax
Example: Using the %SYSEVALF Function

SAS statement support

A Brief Overview
%SYMDEL Syntax
Example: Using the %SYMDEL Statement

SYMBOLGEN system option

SYMPUT routine

SYMPUTX routine

A Brief Overview
Using SYMPUTX with a Literal

user-defined

%LET Statement Syntax
Scope of Macro Variables

masking special characters

%NRSTR function

%STR function

macro function support

MAUTOSOURCE system option

MCOMPILENOTE= system option

metadata

MLOGIC system option

A Brief Overview
MLOGIC System Option Syntax

MPRINT system option

A Brief Overview
The MPRINT Option Syntax

N

natural joins

syntax

A Brief Overview
Natural Join Syntax

NMISS function

NOEXEC option, SQL procedure

noncorrelated subqueries

defined

multiple-value

single-value

subsetting data

NONUMBER option, SQL procedure

NOPRINT option

SELECT statement (SQL)

NOT EXISTS conditional operator

NUMBER option, SQL procedure

numeric data types

Specifying Data Types for Columns
Specifying Column Widths

O

OPTIONS statement, SQL procedure

MAUTOSOURCE option

Overview
The MAUTOSOURCE System Option

MCOMPILENOTE= option

MLOGIC option

A Brief Overview
MLOGIC System Option Syntax

MLOGIC system option

A Brief Overview
MLOGIC System Option Syntax

SASAUTOS= option

Overview
The SASAUTOS System Option

SYMBOLGEN option

ORDER BY clause, SELECT statement (SQL)

ordering by multiple columns

ordering by position

ordering rows

A Brief Overview
ORDER BY Clause Syntax
Example: Ordering Rows by the Values of a Single Column

outer joins

functionality

syntax

OUTER UNION set operator

CORR keyword and

functionality

Introducing Set Operators
A Brief Overview
Example: Using the OUTER UNION Operator Alone

OUTOBS= option, SQL procedure

restricting row processing

syntax

output

controlling

Example: Including a Column of Row Numbers
Controlling Output with the OUTPUT Method

eliminating duplicate rows from

including row numbers

OUTPUT method

The OUTPUT Method
Controlling Output with the OUTPUT Method

P

parameters

in macro programs

Passing Information into a Macro Using Parameters
Macros That Include Positional Parameters
Macros That Include Keyword Parameters
Macros That Include Mixed Parameter Lists

keyword

Macros That Include Keyword Parameters
Macros That Include Mixed Parameter Lists

positional

Macros That Include Positional Parameters
Macros That Include Mixed Parameter Lists

patterns

percent sign (%)

macro programs and

specifying directives

tokens and

performing pattern matching

metacharacters

Using Metacharacters
Example: Using Metacharacters

Perl regular expressions

metacharacters

Using Metacharacters
Example: Using Metacharacters

pattern matching

A Brief Overview
Using Metacharacters
Example: Using Metacharacters

PRXCHANGE function

The PRXCHANGE Function
PRXCHANGE Function Syntax
Example: Using the PRXCHANGE Function to Standardize Data
Example: Changing the Order Using the PRXCHANGE Function
Example: Capture Buffers for Substitution Using the PRXCHANGE Function

PRXMATCH function

A Brief Overview
PRXMATCH Syntax
Example: PRXMATCH Function Using a Constant
Example: PRXMATCH Function Using a Column

PRXPARSE function

A Brief Overview
PRXPARSE Function Syntax
Example: PRXPARSE and PRXMATCH Function Using a Pattern ID Number

PICTURE statement

DATATYPE= option

digit selectors

Example: Using the PICTURE Statement
PICTURE Statement Syntax with Digit Selectors
Digit Selectors with Non-Numeric Characters
The MULTIPLIER= Option
The PREFIX= Option
The ROUND Option

directives

MULTIPLIER= option

PICTURE Statement Syntax with Digit Selectors
The MULTIPLIER= Option

PREFIX= option

PICTURE Statement Syntax with Digit Selectors
The PREFIX= Option

ROUND option

PICTURE Statement Syntax with Digit Selectors
The ROUND Option

syntax

PICTURE statement, FORMAT procedure

DATATYPE= option

specifying directives

positional parameters

Macros That Include Positional Parameters
Macros That Include Mixed Parameter Lists

procedures

FCMP

Creating Functions with PROC FCMP
PROC FCMP Syntax
CMPLIB= Option Syntax

FedSQL

A Brief Overview
Comparing PROC FedSQL and PROC SQL
PROC FEDSQL Syntax
The LIBNAME Statement
The LIMIT Clause
The PUT Function
System Options in FedSQL

FORMAT

A Brief Overview
PICTURE Statement Syntax
Example: Using the PICTURE Statement
Creating Custom Date, Time, and Datetime Formats Using Directives
Creating Custom Date, Time, and Datetime Formats Using Directives [2]
PICTURE Statement Syntax with Digit Selectors
Digit Selectors with Non-Numeric Characters
The MULTIPLIER= Option
The PREFIX= Option
The ROUND Option

SQL

What Is PROC SQL?
Comparing PROC FedSQL and PROC SQL

process management

CALL SYMPUTX routine

CALL SYMPUTX Routine Syntax
Example: Using the CALL SYMPUTX Routine with Literal Strings

conditional processing for macro programs

A Brief Overview
The %IF-%THEN and %ELSE Statement Syntax

controlling execution

controlling output

creating macro variables in PROC SQL

Dictionary tables

for macro programs

A Brief Overview
The MPRINT Option Syntax
Example: Using the MPRINT Option
Comment Statement Syntax
Example: Use Macro Comments
A Brief Overview
MLOGIC System Option Syntax

iterative processing for macro programs

A Brief Overview
Example: Generating Complete Steps

PUT function

A Brief Overview
A Brief Overview
PUT Function Syntax

referencing macro variables indirectly

specifying SQL options

SYMPUT routine

SYMPUTX routine

A Brief Overview
Using SYMPUTX with a Literal

PRXCHANGE function

The PRXCHANGE Function
PRXCHANGE Function Syntax
Example: Using the PRXCHANGE Function to Standardize Data
Example: Changing the Order Using the PRXCHANGE Function
Example: Capture Buffers for Substitution Using the PRXCHANGE Function

capture buffers

changing order

standardize data

syntax

The PRXCHANGE Function
PRXCHANGE Function Syntax

PRXMATCH function

A Brief Overview
PRXMATCH Syntax
Example: PRXMATCH Function Using a Constant
Example: PRXMATCH Function Using a Column

referring to columns

syntax

A Brief Overview
PRXMATCH Syntax
A Brief Overview
PRXPARSE Function Syntax
Example: PRXPARSE and PRXMATCH Function Using a Pattern ID Number

validating data

PRXPARSE function

A Brief Overview
PRXPARSE Function Syntax
Example: PRXPARSE and PRXMATCH Function Using a Pattern ID Number

PUT function

A Brief Overview
A Brief Overview [2]
PUT Function Syntax

FedSQL procedure

PUTC function

PUTN function

Q

queries

creating tables from

Dictionary tables and

displaying all columns

eliminating duplicate rows from output

grouping

GROUP BY Clause Syntax
HAVING Clause Syntax

inserting rows from

A Brief Overview
INSERT Statement with SELECT and FROM Clause Syntax

limiting number of rows displayed

ordering rows

A Brief Overview
ORDER BY Clause Syntax
Example: Ordering Rows by the Values of a Single Column

selecting columns

specifying subsetting criteria

A Brief Overview
WHERE Clause Syntax

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

validating syntax

viewing all columns

views in

quotation marks

macro quoting functions and

R

referencing macro variables indirectly

right outer join

A Brief Overview
Example: Using a Right Outer Join

rows

counting all

counting number of

duplicate

Example: Eliminating Duplicate Rows from Output
Processing Unique versus Duplicate Rows

eliminating duplicates from output

inserting in tables

A Brief Overview
The VALUES Clause
VALUES Clause Syntax

joining tables with matching values

limiting number displayed

ordering

A Brief Overview
ORDER BY Clause Syntax
Example: Ordering Rows by the Values of a Single Column

ordering by multiple columns

ordering by position

processing unique vs. duplicate

restricting processing

row numbers in output

S

SAS log

%PUT statement

displaying macro variable values

SYMBOLGEN Option Syntax
%PUT Statement Syntax

MCOMPILENOTE= option

SYMBOLGEN system option

SASAUTOS= system option

security, table views

SELECT clause

INSERT statement (SQL)

A Brief Overview
INSERT Statement with SELECT and FROM Clause Syntax

SELECT statement (SQL)

Example: Selecting Columns
Example: Displaying All Columns Using SELECT *
Example: Using the FEEDBACK Option
Example: Eliminating Duplicate Rows from Output
SELECT Clause Columns and Summary Function Processing

SELECT statement, SQL procedure

creating new columns

DISTINCT keyword

FEEDBACK option

FROM clause

FROM Clause Syntax
Generating a Cartesian Product

functionality

A Brief Overview
SELECT Statement Syntax

GROUP BY clause

A Brief Overview
GROUP BY Clause Syntax
Groups and Summary Function Processing
Example: Using a Summary Function with a GROUP BY Clause
Example: Selecting Groups by Using the HAVING Clause

grouping data

GROUP BY Clause Syntax
HAVING Clause Syntax

HAVING clause

HAVING Clause Syntax
Example: Selecting Groups by Using the HAVING Clause

INTO clause

NOPRINT option

ORDER BY clause

A Brief Overview
ORDER BY Clause Syntax
Example: Ordering Rows by the Values of a Single Column
Example: Ordering by Multiple Columns
Example: Ordering Columns by Position

ordering by multiple columns

ordering by position

ordering rows

A Brief Overview
ORDER BY Clause Syntax
Example: Ordering Rows by the Values of a Single Column

SELECT clause

Example: Selecting Columns
Example: Displaying All Columns Using SELECT *
Example: Using the FEEDBACK Option
Example: Eliminating Duplicate Rows from Output
SELECT Clause Columns and Summary Function Processing

selecting columns

specifying subset criteria

A Brief Overview
WHERE Clause Syntax

specifying tables

summarizing groups of data

syntax for inner join

syntax for natural join

A Brief Overview
Natural Join Syntax

syntax for outer join

syntax for set operations

VALIDATE keyword

viewing all columns

WHERE clause

A Brief Overview
WHERE Clause Syntax
Understanding How PROC SQL Processes Calculated Columns
Example: Using Calculated Values in a WHERE Clause

semicolon (;) in macro programs

session compiled macros

SET clause

INSERT statement (SQL)

A Brief Overview
SET Clause Syntax
Example: Inserting Rows by Using the SET Clause

set operations

combining and overlaying columns

defined

EXCEPT set operator

Introducing Set Operators
A Brief Overview
Example: Using the Keyword ALL with the EXCEPT Operator
Example: Using the Keyword CORR with the EXCEPT Operator
Example: Using the Keywords ALL and CORR with the EXCEPT Operator

INTERSECT set operator

Introducing Set Operators
A Brief Overview
Example: Using the Keyword ALL with the INTERSECT Set Operator
Example: Using the Keyword CORR with the INTERSECT Set Operator
Example: Using the Keywords ALL and CORR with the INTERSECT Set Operator

modifying results via keywords

OUTER UNION set operator

Introducing Set Operators
A Brief Overview

processing multiple operations

A Brief Overview
Processing Multiple Set Operators

processing single operations

processing unique vs. duplicate rows

UNION set operator

Introducing Set Operators
A Brief Overview
Example: Using the Keyword ALL with the UNION Operator
Example: Using the Keyword CORR with the UNION Operator
Example: Using the Keywords ALL and CORR with the UNION Operator

sorting

ordering by multiple columns

ordering by position

ordering rows

A Brief Overview
ORDER BY Clause Syntax
Example: Ordering Rows by the Values of a Single Column

sounds-like conditional operator

description

syntax

special characters, masking

%NRSTR function

%STR function

macro function support

spelling variations

SQL procedure

accessing metadata

creating macro variables

FEEDBACK option

functionality

INOBS= option

NOEXEC option

NONUMBER option

NUMBER option

OUTOBS= option

Restricting Row Processing
Example: Limiting the Number of Rows Displayed

selecting patterns

specifying options

syntax

statements

%GLOBAL

%IF-%THEN

%LOCAL

%MACRO

%MEND

%SYMDEL

A Brief Overview
%SYMDEL Syntax
Example: Using the %SYMDEL Statement

ARRAY

DO

macro variable support

A Brief Overview
%SYMDEL Syntax
Example: Using the %SYMDEL Statement

storage

in autocall libraries

A Brief Overview
Creating an Autocall Library
Default Autocall Library
Overview

macro definitions in external files

session compiled macros

subqueries

comparison operator in

correlated

noncorrelated

subsetting data

subsetting data

correlated subqueries and

noncorrelated subqueries and

subqueries and

subsetting rows

specifying criteria

A Brief Overview
WHERE Clause Syntax

using calculated values

using conditional operators

summarizing data

GROUP BY clause, SELECT statement (SQL)

A Brief Overview
Groups and Summary Function Processing

SELECT clause and

summary functions

functionality

GROUP BY clause and

number of arguments

on groups of data

SELECT clause and

with columns outside function

with multiple arguments

with single arguments

symbol tables

%GLOBAL statement

%LOCAL statement

A Brief Overview
%LOCAL Statement Syntax

global

local

Local Symbol Table
A Brief Overview

SYMBOLGEN system option

SYMPUTX routine

DATA step variables and

syntax

SYSDATE automatic macro variable

SYSDATE9 automatic macro variable

What Are Automatic Macro Variables?
Example: Using the %SUBSTR Function
A Brief Overview
%SYSFUNC Syntax

SYSDAY automatic macro variable

SYSENV automatic macro variable

SYSERR automatic macro variable

SYSJOBID automatic macro variable

SYSLAST automatic macro variable

SYSPARM automatic macro variable

SYSSCP automatic macro variable

SYSTIME automatic macro variable

What Are Automatic Macro Variables?
A Brief Overview
%SYSFUNC Syntax

SYSVER automatic macro variable

T

table aliases, specifying

tables

creating from query results

creating like others

displaying structure

empty

generating Cartesian products

methods of creating

specifying

specifying data types

views and

A Brief Overview
Creating a View to Enhance Table Security

virtual

text, macro variable references and

tokens

percent sign and

U

UNION set operator

ALL keyword and

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

CORR keyword and

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

functionality

Introducing Set Operators
A Brief Overview

update process

for views

UPDATE statement, SQL procedure

updating views

WHERE clause

user-defined macro variables

%LET statement and

global symbol table and

USING clause, CREATE VIEW statement (SQL)

V

VALIDATE keyword

VALUES clause, INSERT statement (SQL)

functionality

The VALUES Clause
VALUES Clause Syntax

variables

hash objects and

views

benefits

creating

deleting

displaying definitions

dropping

enhancing table security

functionality

in queries

librefs and

managing

tables and

A Brief Overview
Creating a View to Enhance Table Security

updating

virtual tables

W

WHERE clause

SELECT statement (SQL)

A Brief Overview
WHERE Clause Syntax
Example: Using Calculated Values in a WHERE Clause

UPDATE statement (SQL)