Index
Special Characters
_ATDATETIME_ audit trail variable 680
_ATMESSAGE_ audit trail variable 680
_ATOBSNO_ audit trail variable 680
_ATOPCODE_ audit trail variable 680, 681
_ATRETURNCODE_ audit trail variable 680
_ATUSERID_ audit trail variable 680
DSEMTR mnemonic 668
DSENMR mnemonic 668
DSENOM mnemonic 668
_IORC_ automatic variable 565, 668
SOK mnemonic 668
TEMPO RARY_ keyword 581
; (semicolon) 394
. (period) 334
& (ampersand) 307
% (percent sign)
macro programs and 394
specifying directives 632
tokens and 321
%a directive 632
%A directive 632
%b directive 632
%B directive 632
%BQUOTE function 321
%CMPRES statement 451
%COPY statement 457
%d directive 632
%DATATYP statement 451
%DO-%END statement 416, 426, 429
%EVAL function 430
%GLOBAL statement 406
%H directive 632
%I directive 632
%IF-%THEN/%ELSE macro statement 415, 416, 419, 422, 425
%INCLUDE statement 443
%INDEX function 327
%j directive 632
%LEFT statement 451
%LENGTH function 325
%LET statement
macro parameter support 400
processing 348
user-defined macro variables and 310
%LOCAL statement 408
%LOWCASE statement 451
%m directive 632
%M directive 632
%MACRO statement
creating stored compiled macros 454
general form 391
macro parameter support 401, 402, 404
PARMBUFF option 405
STORE option 454
%MEND statement 391
%NRSTR function 321
%p directive 632
%PUT statement 316
%QLOWCASE statement 451
%QSCAN function 329
%QSUBSTR function 326
%QSYSFUNC function 331
%QUPCASE function 324
%S directive 632
%SCAN function 328
%STR function 319
%SUBSTR function 325
%SYSEVALF function 432
%SYSFUNC function 330
%SYSRC autocall macro 668
%TRIM statement 451
%U directive 632
%UPCASE function 323
%w directive 632
%y directive 632
%Y directive 632
=* conditional
operator description 36
general form 42
WHERE conditions and 870
A
access methods, selecting 865
accumulator variables 559
ADD clause, ALTER TABLE statement (SQL) 218
ADD method 606
aliases
in-line views 112
table 94
ALL conditional operator 36, 71
ALL keyword
DELETE statement (DATASETS) 690
EXCEPT set operator and 142, 143
INTERSECT set operator and 147, 149
set operations and 138
UNION set operator and 153, 155
ALTER TABLE statement, SQL procedure
ADD clause 218
DROP clause 220
functionality 76
general form 218
MODIFY clause 221
ALTER= data set option 679
ampersand (&) 307
ANY conditional operator
comparison operator and 69
description 36
APPEND procedure
BASE= data set option 514
DATA= data set option 514
general form 514
handling large data set sorts 830
arguments, summary functions and 51, 53, 54
arithmetic expressions in macro programs 430, 432
ARRAY statement
combining data with 540
creating arrays 585
general form 581
loading array elements 586
lookup values and 540
stored array values and 584
arrays
combining data in 540
creating 585
defined 540
loading elements 586
multidimensional 581
reading values 589
stored values 584, 585, 586, 589
AS keyword, CREATE TABLE statement (SQL) 186
ATTRIB statement 540
AUDIT statement, DATASETS procedure 678, 683
audit trails
controlling 683
initiating 678
overview 677
reading files 679
USERVAR statement 682
autocall libraries
accessing macros 452
creating 450
default 451
defined 450
automatic macro variables
defined 306
functionality 308
global symbol table and 306, 406
AVG function 58
B
base table 535
BASE= data set option 514
BEFOREIMAGE option, LOG statement 681
benchmark guidelines 707
best practices
conditional logic 770
creating multiple output data sets 782
DO groups 777
eliminating data passes 781, 782, 784, 785
executing only necessary statements 767
reading/writing essential data 786, 788, 789, 792
subsetting variables 792, 793, 794
BETWEEN-AND conditional operator
description 36
general form 36
identifying conditions to optimize 869
binary search 540
buffers
controlling number of 713, 715, 716
controlling page size 713, 714
BUFNO= data set option 716
BUFNO= system option 716
BUFSIZE= system option 715
BY statement, DATA step
GROUPFORMAT option 817
handling duplicate values 662
handling large data set sorts 830
indexes and 812
TRANSPOSE procedure 595
BY variable
DATA step match-merge 541, 550
C
CALCULATED keyword 43
CALL MISSING routine 605
CASE expression
general form 211
INSERT statement (SQL) 215
SELECT statement (SQL) 215
UPDATE statement (SQL) 210, 212, 214
case operand
CASE expression, UPDATE statement (SQL) 212, 214
defined 212
case sensitivity in macro comparisons 425
CATALOG access method, FILENAME statement 447
CATALOG procedure
managing formats 635
catalogs, storing macro definitions 446, 447
CEIL function 475
CENTILES option, CONTENTS procedure 872
CHANGE statement, DATASETS procedure 490, 689
character constants 48
character data types
column widths and 180
defined 179
flowing characters in columns 283
character strings
%INDEX function 327
%LENGTH function 325
%QSCAN function 329
%QSUBSTR function 326
%QUPCASE function 324
%SCAN function 328
% SUBSTR function 325
%UPCASE function 323
macro character functions 322
character variables, storing 732
CHART procedure 334
CHECK constraint type
in column specification 197
CLASS statement
MEANS procedure 819
SUMMARY procedure 819
UNIVARIATE procedure 819
class variables
combining 894
summary statistics and 886, 888
CNTLESN option, FORMAT procedure 641
CNTLOUT= option, FORMAT procedure 645
column alias
CALCULATED keyword and 43
defined 9
renaming columns with 92
column constraints 197
column modifiers 181
column widths 180
columns
adding to tables 218
altering in tables 218
as multiple arguments for summary functions 54
combining 137
counting non-missing values 59
counting unique values 60
creating constraints 197
creating new 9
creating tables by defining 177
deleting from tables 220
eliminating duplicate 91
flowing characters within 283
modifying in tables 221
ordering by multiple 12
outside summary functions 55
overlaying 137
processing calculated 42
renaming with column aliases 92
rows numbers in output 282
selecting 9
specifying formats and labels 46
specifying in multiple tables 14
specifying subsets in tables 184
viewing all 31
comments in macro programs 399
COMPARE procedure 736
comparison operators
ALL conditional operator and 71
ANY conditional operator and 69
identifying conditions to optimize 869
subqueries and 68
compiling macro programs 392, 393
composite indexes
COMPRESS function 509
COMPRESS= data set option 743
COMPRESS= system option 743
compressing data files
costs of 876
storage considerations 741, 742, 743, 746, 747, 748
concatenating data
appending data sets 514, 516, 517, 519
creating delimiting list of values 376
defined 502
FILENAME statement 503
INFILE statement 506, 508, 509, 510, 511, 512
conditional operators 35
conditional processing
best practices 770
case sensitivity in macro programs 425
for macro programs 415, 416, 419, 422, 425
CONTAINS conditional operator
description 36
general form 37
identifying conditions to optimize 869
CONTENTS procedure
CENTILES option 872
displaying index specifications 247
maintaining indexes 486
reporting buffer information 714
CONTENTS statement
CATALOG procedure 446
DATASETS procedure 486, 675, 714, 872
conventional join 89
COPY procedure 489
COPY statement, DATASETS procedure 489
copying
tables 187
CORR keyword
EXCEPT set operator and 142, 143
INTERSECT operator and 148, 149
OUTER UNION set operator and 160, 163
set operations and 138
UNION set operator and 154, 155
correlated subqueries
defined 65
EXISTS conditional operator 73
indexes and 241
NOT EXISTS conditional operator 73
subsetting data 73
COUNT function 58
CPUCOUNT= system option 826
CREATE INDEX statement, SQL procedure
creating composite indexes 244, 245
creating multiple indexes 244
creating simple indexes 245
displaying index specifications 246
functionality 238
CREATE statement, SQL procedure 76
CREATE TABLE statement, SQL procedure
AS keyword 186
copying tables 187
creating constraints 197
creating constraints outside column specifications 199
creating empty tables 177
creating like other tables 183
creating output tables 19
creating tables from query results 186
displaying table structure 182
FORMAT= option 181
FROM clause 187
general form for creating output tables 19
general form with column specifications 177
general form with constrain specification 200
general form with constraint specification 198
general form with LIKE clause 183
general form with query clauses 186
INFORMAT= option 181
LABEL= option 181
LIKE clause 183
SELECT clause 187
specifying column modifiers 181
specifying column widths 181
specifying data types 179
specifying empty tables like other tables 183
specifying subsets of columns 184
CREATE VIEW statement, SQL procedure
FROM clause 266
general form 261
USING clause 266
D
DATA_IMAGE option, LOG statement 681
data files
accessing observations directly 746
COMPRESS= data set option 743
COMPRESS= system option 743
compressed structure 742
number of pages 875
POINTOBS= data set option 746
reasons for compressing 743
REUSE= data set option 747
REUSE= system option 747
uncompressed structure 741
data sets
accessing observations directly 746
audit trail overview 677
audit trails, controlling 683
audit trails, controlling data in 680, 681
audit trails, initiating 678
audit trails, reading files 679
controlling update process 667, 668
creating formats from 641, 644
creating from formats 645
creating multiple output 782
handling large sorts 830
hash objects and 607
integrity constraint overview 669, 670, 673, 674
integrity constraints, documenting 675
integrity constraints, placing in 671, 673, 674
integrity constraints, removing 676
listing variables 642
lookup values outside 539, 540
missing values in 663
modifying observations located by indexes 663
renaming 490
storing data 797
storing raw data file names 522
transposed 595, 596, 597, 598, 599
DATA step
ATTRIB statement 540
BY statement 660, 662, 668, 812, 817, 830
comparing joins and 105, 106, 107
COMPRESS= data set option 743
conserving storage space 753, 754, 755, 756, 757, 758
creating indexes in 480
creating macro variables in 345
creating multiple macro variables in 360
DESCRIBE statement 755
FIRST, automatic variable 848
FORMAT procedure 540
hash objects and 602
IF-THEN/ELSE statement 416
INDEX= option 480
KEEP statement 643, 792, 793, 794
LAST, automatic variable 848
match-merges 105, 106, 107, 541, 542, 546, 550, 551, 554
obtaining macro variable values 369
OUTPUT statement 667
REMOVE statement 667
RENAME statement 643
REPLACE statement 667
RETAIN statement 643
REUSE= data set option 747
SET statement 746
subsetting IF statement 768
sum statement 559
SYMGET function 369
SYMPUT routine and 349, 350, 351, 353, 360
SYMPUTX routine and 358
table lookups and 545
tools for summarizing data 883
UNIQUE option 481
UPDATE statement 567
VIEW= option 755
WHERE statement 786, 788, 862, 864, 865, 867
data types
appending variables with different 519
creating tables by defining columns 179
DATA= data set option 514
DATASETS procedure
CONTENTS statement 486, 675, 714, 872
COPY statement 489
creating tables with integrity constraints 196
displaying index specifications 247
general form 637
IC CREATE statement 671
IC DELETE statement 676
INDEX CREATE statement 483
INDEX DELETE statement 483
INITIATE statement 678
LIBRARY= option 487
managing indexes 483
permanently assigning formats 637
RENAME statement 490
DATATYPE= option, PICTURE statement (FORMAT) 632
DATE function 511
debugging
FEEDBACK option, SQL procedure 32
DECLARE statement 608
DEFAULT= option, LENGTH statement (DATA) 734
DEFINEDONE method 605
DELETE statement, DATASETS procedure 689, 690
DELETE statement, SQL procedure
audit trails 677
functionality 76
general form 216
updating views 268
deleting
columns from tables 220
indexes 252
rows in tables 216
tables 225
views 270
delimiters
in macro programs 394
in macro variable names 334
dense match 538
DESCRIBE statement
DATA step 755
SQL procedure 76
DESCRIBE TABLE CONSTRAINTS statement, SQL procedure 205
DESCRIBE TABLE statement, SQL procedure
displaying Dictionary table definitions 290
displaying index specifications 246
displaying indexes 238
displaying table structure 182
DESCRIBE VIEW statement, SQL procedure 264
detail reports 880
Dictionary tables
querying 290
digit selectors 630
directives, specifying pictures 630, 632
DISTINCT keyword 33
DO UNTIL loops 559
DO WHILE loops 477
dot notation method 604
DOUBLE option, SQL procedure 282
DROP clause, ALTER TABLE statement (SQL) 220
DROP INDEX statement, SQL procedure 252, 485
DROP statement
SQL procedure 76
DROP statement, SQL procedure 793, 794
DROP TABLE statement, SQL procedure 225
DROP VIEW statement, SQL procedure 270
DROP= data set option
general form 184
subsetting variables 792, 793, 794
duplicate columns, eliminating 91
duplicate rows
eliminating from output 33
processing unique vs. 137
removing efficiently 841, 842, 844, 846, 848
duplicate values in data sets 662, 665
E
efficiency
benchmark guidelines 707
comparing resource usage 874, 875, 876
computer resources 702
detail reports 880
estimating observations 871
identifying available indexes 865, 867
identifying conditions to optimize 869, 870, 871
indexes and WHERE processing 862, 864
removing duplicate observations 841, 842, 844, 846, 848
tools for summarizing data 883, 884, 889
tracking resources 706
trade-offs in 705
END= option, INFILE statement 510
EQUALS option, SORT procedure 846
ERRORIMAGE option, LOG statement 681
error handling
for row insertions 202
monitoring I/O error conditions 668
stopping SQL procedure 293
ERRORSTOP option, SQL procedure 293
evaluating performance 285
EXCEPT set operator
EXCLUDE statement, FORMAT procedure 634, 645
EXEC option, SQL procedure 293
EXISTS conditional operator
correlated queries and 73
description 36
external files
selecting observations from 789
storing macro definitions in 443
storing raw data filenames in 522
F
FEEDBACK option, SQL procedure 31
FILEVAR= option, INFILE statement 506
FIRST, automatic variable 816, 848
FIRSTOBS= data set option 830
FIRSTOBS= option, WHERE statement (PRINT) 789
FLOW option, SQL procedure 283
FMTERR system option 639
FMTLIB keyword, FORMAT procedure 634
FMTSEARCH= system option 638
FOOTNOTE statement, SQL procedure 47
FORCE option, APPEND procedure 516, 517, 519
FOREIGN KEY constraint type
constraint specification 200
in column specification 197
FORMAT procedure
binary search 540
CNTLESN option 641
CNTLOUT= option 645
FMTLIB keyword 634
general form 634
LIB= option 638
VALUE statement 540, 541, 626, 628
FORMAT statement, TABULATE procedure 629
FORMAT= option
CREATE TABLE statement (SQL) 181
SELECT statement, SQL procedure 46
TABULATE procedure 629
formats
associating with variables 540
creating data sets from 645
creating from data sets 641, 644
creating with PICTURE statement 630, 632
creating with VALUE statement 626, 628
FMTERR system option 639
FMTSEARCH= system option 638
multilabel 629
NOFMTERR system option 639
permanently assigning 637, 638, 639
specifying for columns 46
substituting to avoid errors 639
with overlapping ranges 628
Forward Re-Scan rule 364
FROM clause
CREATE TABLES (SQL) 187
CREATE VIEW statement (SQL) 266
INSERT statement (SQL) 194
SELECT statement (SQL) 10, 15, 87, 110
FSEDIT procedure 746
FSLIST procedure 503
FULLSTIMER system option 706
functions
%QSYSFUNC function 331
%SYSFUNC function 330
macro variable support 330
G
general integrity constraints 197, 670
generation data sets
creating 686
defined 685
initiating 686
GENMAX= option, MODIFY statement (DATASETS) 686
GENNUM= data set option 687, 688, 690
global symbol table
%GLOBAL statement 406
defined 306
GROUP BY clause, SELECT statement (SQL)
selecting groups 61
summarizing groups of data 17, 51, 52
GROUPFORMAT option, BY statement (DATA) 817
groups, selecting 61
H
hash objects
CALL MISSING routine 605
creating from data sets 607
DATA step component objects 602
defined 600
defining keys and data 604
loading key/data values 606
multiple data variables 608
processing 606
retrieving matching data 606
retrieving multiple data values 609
return codes with FIND method 610
SET statement and 608
structure 601
HAVING clause, SELECT statement (SQL) 20, 61
HIST keyword 690
host sort utilities 851
I
I/O processing
comparing resource usage 874, 875, 876
measuring 713
IC CREATE statement, DATASETS procedure 671
IC DELETE statement, DATASETS procedure 676
IDXNAME= data set option
controlling index usage 879
functionality 249
general form 250
IDXWHERE= data set option
controlling index usage 879
functionality 249
general form 250
IF-THEN/ELSE statement
%IF -%THEN comparison 416
large data set sorts 830
lookup values outside data sets 539, 540
IN conditional operator
description 36
general form 38
identifying conditions to optimize 869
in-line views
assigning aliases 112
functionality 110
multiple tables and 111
INDEX CREATE statement, DATASETS procedure 483
INDEX DELETE statement, DATASETS procedure 483
INDEX= option, DATA step 480
indexes
accessing rows in tables 239
BY statement and 812
combining data with 561, 562, 565
composite 240, 244, 245, 470, 480
controlling usage 879
copying data sets 489
creating 244
creating in DATA step 480
creating multiple 244
displaying specifications 246
documenting 486
dropping 252
identifying available 865, 867
maintaining 486
managing with DATASETS procedure 483
managing with SQL procedure 485
modifying observations located by 663
reasons for not using 482
renaming data sets 490
renaming variables 490
subsetting data 243
INFILE statement
assigning names of files to be read 508
COMPRESS function 509
date functions 511
END= option 510
FILEVAR= option 506
general form 506
INTNX function 512
INFORMAT= option, CREATE TABLE statement (SQL) 181
INITIATE statement, DATASETS procedure 678
inner joins
combining data horizontally 546, 550, 551, 554
general form with outer join 105
INOBS= option, SQL procedure 280
input data sources 537
INPUTC function 331
INPUTN function 331
INSERT statement, SQL procedure
audit trails 677
CASE expression 215
controlling UNDO processing 203
FROM clause 194
functionality 76
handling errors in row insertions 202
inserting rows from query results 194
inserting rows of data in tables 189, 190, 191
SELECT clause 194
updating views 268
instantiating hash objects 603, 604
integrity constraints
creating outside of column specifications 199
creating tables with 196
displaying for tables 205
documenting 675
enforcing 673
general form with specifications 200
in column specifications 197
placing in data sets 671, 673, 674
removing 676
INTERSECT set operator
INTNX function 512
INTO clause, SELECT statement (SQL) 371, 373, 376
IS MISSING conditional operator 36, 39, 869
IS NULL conditional operator 36, 39, 869
J
joining data sets
combining summary/detail data 555, 556, 559
DATA step match-merge 541, 542
defined 534
multiple SET statements 554
relationships between input data sources 537
SQL procedure support 545, 546, 550, 551, 554
terminology 535
transactional data sets 566, 567
transposed data sets 595, 596, 597, 598, 599
joining tables
advantages 109
comparing with DATA step match-merges 105, 106, 107
EXCEPT set operator 139
for rows with matching values 93
indexes and 241
inner joins with outer-join general form 105
INTERSECT set operator 146
outer joins 98
OUTER UNION set operator 158, 163
processes defined 91
set operations 133
UNION set operator 152
with views 114
K
KEEP statement, DATA step 643, 792, 793, 794
KEEP= data set option
general form 184
subsetting variables 792, 793, 794
key values
return codes with FIND method 610
key variables 535
KEY= option
MODIFY statement (DATA) 578, 663, 666, 668
SET statement (DATA) 562
keywords, modifying set operations 138
L
LABEL= option
CREATE TABLE statement (SQL) 181
SELECT statement, SQL procedure 46
labels, column 46
LAST, automatic variable 816, 848
LEFT function 332
LENGTH statement, DATA step 732, 734
LIB= option, FORMAT procedure 638
LIBNAME statement 266
LIBRARY= option, DATASETS procedure 487
librefs, views and 266
LIKE clause, CREATE TABLE statement (SQL) 183
LIKE conditional operator
description 36
general form 40
identifying conditions to optimize 869
specifying patterns 40
literal tokens 314
local symbol table 407, 408, 410
LOG statement
BEFOREIMAGE option 681
controlling data in audit trails 681
DATA_IMAGE option 681
ERROR_IMAGE option 681
logical expressions in macro programs 430, 432
lookup tables
hash objects as 600
multidimensional arrays and 581
multiple 542
stored array values 584, 585, 586, 589
transposed data sets 595, 596, 597, 598, 599
LOOPS= option, SQL procedure 293
M
macro character functions
%INDEX function 327
%LENGTH function 325
%QSCAN function 329
%QSUBSTR function 326
%QUPCASE function 324
%SCAN function 328
%SUBSTR function 325
%UPCASE function 323
manipulating character strings 322
macro definitions 443
macro language 315
macro processor
functionality 347
macro facility and 315
macro variables rules 409
referencing macro variables 307
macro programs
%DO-%END statement 416
%EVAL function 430
%GLOBAL statement 406
%IF-%THEN/%ELSE macro statement 415, 416, 419, 422, 425
%LOCAL statement 408
%SYSEVALF function 432
arithmetic/logical expressions 430, 432
automatic evaluation 432
calling 393
case sensitivity and 425
comments in 399
conditionally processing statements 415, 416, 419, 422, 425
defined 391
executing 395
functionality 390
iterative processing for 426, 429
monitoring execution 397, 398, 399
parameters and 400, 401, 402, 404, 405
Stored Compiled Macro Facility 454, 455, 456, 457
storing definitions in catalog SOURCE entries 446, 447
storing in autocall libraries 446, 450, 451, 452
storing macro in external files 443
storing session-compiled macros 442
symbol tables 406, 407, 408, 409, 410, 412, 414
macro quoting functions
%BQUOTE function 321
%NRSTR function 321
%STR function 319
masking special characters 318
macro triggers 315
macro variables
%PUT statement 316
combining references with text 332
creating delimited list of values 376
creating during DATA step 345
creating during PROC SQL execution 371
creating multiple during DATA step 360
creating with INTO clause 373
DATALINES statement and 306
delimiters in names 334
displaying values in SAS log 315, 316
Forward Re-Scan rule 364
in symbol tables 406, 407, 408, 409, 410, 412, 414
macro facility and 306
macro parameters and 400, 401, 402, 404, 405
macro processor rules 409
obtaining values during DATA step 369
processing 312
referencing indirectly 363
SAS function support 330
SYMBOLGEN system option 315
SYMGET function 369
SYMGETN function 380
SYMPUT routine 349, 350, 351, 353, 360
SYMPUTN routine 380
SYMPUTX routine 358
tokenization 313
triggers for 315
masking special characters
%BQUOTE function 321
%NRSTR function 321
%STR function 319
macro function support 318
MAUTOLOCDISPLAY system option 453
MAUTOSOURCE system option 452
MCOMPILENOTE= system option 393
MEANS procedure
CLASS statement 819
general form 556
multilabel formats 629
NOPRINT option 556
NWAY option 894
tools for summarizing data 883, 886, 887
TYPES statement 891
WAYS statement 900
WHERE= data set option 896
memory
controlling number of buffers 713, 715, 716
controlling page size 713, 714
MEMRPT system option 706
MERGE statement, DATA step 541, 542
message characters 630
MESSAGE= option, CREATE TABLE statement (SQL) 198, 200
metadata 278
methods 602
missing values in data sets 663
MLF option, TABULATE procedure 629
MLOGIC system option 398
MLOGICNEST system option 414
MODIFY clause, ALTER TABLE statement (SQL) 221
MODIFY statement, DATA step
audit trails and 677
general form 659
KEY= option 578, 663, 666, 668
POINT= option 746
UNIQUE option 666
UPDATEMODE= option 663
MODIFY statement, DATASETS procedure 483, 686
MONTH function 511
MPRINT system option 397
MPRINTNEST system option 412
MSGLEVEL= system option 248, 481, 879
MSGTYPE= option, CREATE TABLE statement (SQL) 198, 200
MSTORED system option 454, 456
multidimensional arrays 581
multilabel formats 629
MULTILABEL option, VALUE statement (FORMAT) 628
multiple indexes 244
multiple tables
querying 13
specifying columns in 14
specifying names 15
N
NAME= option, TRANSPOSE procedure 593
NMISS function 58
NOBS= option, SET statement 473
NODOUBLE option, SQL procedure 282
NODUPKEY option, SORT procedure 842
NODUPRECS option, SORT procedure 844
NOEQUALS option, SORT procedure 846
NOERRORSTOP option, SQL procedure 293
NOEXEC option, SQL procedure 75, 293
NOFLOW option, SQL procedure 283
NOFMTERR system option 639
NOFULLSTIMER system option 707
NOMEMRPT system option 707
noncorrelated subqueries
defined 65
multiple-value 67
single-value 66
subsetting data 66
NONUMBER option, SQL procedure 282
NOPRINT option
MEANS procedure 556
SELECT statement (SQL) 371
NOPROMPT option, SQL procedure 281, 293
NOSTATS system option 707
NOSTIMER option, SQL procedure 285
NOSTIMER system option 707
NOT EXISTS conditional operator 73
NOT NULL constraint type
constraint specification 200
in column specification 197
NOTHREADS option, SORT procedure 825
NOTHREADS system option 825
NOTSORTED option
TRANSPOSE procedure 596
NUMBER option, SQL procedure 282
number tokens 314
numeric variables, storing 733, 734, 735, 736
NWAY option, MEANS procedure 894
O
OBS= data set option 830
OBS= option, WHERE statement (PRINT) 789
OBSBUF= data set option 756
OPTIONS statement, SQL procedure
BUFNO= option 716
BUFSIZE= option 715
COMPRESS= option 743
CPUCOUNT= option 826
FMTERR option 639
FMTSEARCH= option 638
FULLSTIMER option 706
MAUTOLOCDISPLAY option 453
MAUTOSOURCE option 452
MCOMPILENOTE= option 393
MEMRPT option 706
MLOGIC option 398
MLOGIC system option 398
MLOGICNEST option 414
MLOGICNEST system option 414
MPRINT option 397
MPRINT system option 397
MPRINTNEST option 412
MPRINTNEST system option 412
MSGLEVEL= system option 481
NOFMTERR option 639
NOFULLSTIMER option 707
NOMEMRPT option 707
NOSTATS option 707
NOSTIMER option 707
NOTHREADS option 825
REUSE= option 747
SASAUTOS option 452
SASAUTOS= option 452
SASMSTORE option 456
SASMSTORE= option 454
SORTCUT= system option 852
SORTCUTP= system option 852
SORTNAME= system option 853
SORTPGM= system option 851
SORTSIZE= system option 828
STATS option 706
SYMBOLGEN option 315
THREADS option 825
ORDER BY clause, SELECT statement (SQL)
ordering by multiple columns 12
OUT= option, SORT procedure 830
outer joins
functionality 98
general form 99
inner joins and 105
OUTER UNION set operator
OUTOBS= option, SQL procedure
general form 32
restricting row processing 280
summary functions and 57
output
adding character constants 48
controlling 282
detail reports 880
double-spacing 282
eliminating duplicate rows from 33
enhancing for queries 45
flowing characters in columns 283
including row numbers 282
SYMPUT routine support 353
OUTPUT statement
DATA step 667
output tables, creating 19
P
parameters
in macro programs 400, 401, 402, 404, 405
PARMBUFF option 405
PARMBUFF option, %MACRO statement 405
patterns 40
percent sign (%)
macro programs and 394
specifying directives 632
tokens and 321
performance
testing and evaluating 285
period (.) 334
PICTURE statement, FORMAT procedure
DATATYPE= option 632
general form 630
specifying directives 632
specifying pictures 630
PLOT procedure 334
POINT= option
MODIFY statement (DATA) 746
POINTOBS= data set option 746
positional parameters 401, 404
PREFIX= option, TRANSPOSE procedure 593
PRIMARY KEY constraint type
constraint specification 200
in column specification 197
PRINT procedure
detail reports 880
UNIFORM option 757
WHERE statement 789
process management
conditional processing for macro programs 415, 416, 419, 422, 425
controlling execution 280
controlling output 282
creating macro variables in DATA step 345
creating macro variables in PROC SQL 371, 373, 376
creating multiple macro variables in DATA step 360
error handling 293
for macro programs 397, 398, 399
Forward Re-Scan rule 364
hash objects 606
iterative processing for macro programs 426, 429
macro variables in SCL programs 379, 380
obtaining macro variable values in DATA step 369
referencing macro variables indirectly 363
resetting options 287
restricting number of loops 293
specifying SQL options 279
SYMGET function 369
SYMGETN function 380
SYMPUT routine 349, 350, 351, 353, 360
SYMPUTN routine 380
SYMPUTX routine 358
testing and evaluating performance 285
working with views 378
PROMPT option, SQL procedure 281, 293
PUTC function 331
PUTN function 331
Q
queries
comparing resource usage 874, 875, 876
creating output tables 19
creating tables from 186
detail reports 880
Dictionary tables and 290
displaying all columns 31
eliminating duplicate rows from output 33
enhancing output 45
estimating observations 871
identifying available indexes 865, 867
identifying conditions to optimize 869, 870, 871
in-line views 110
indexes and 241, 862, 864, 865
inserting rows from 194
joining multiple tables and views 114
limiting number of rows displayed 32
querying multiple tables 13
selecting columns 9
specifying subsetting criteria 10
specifying tables 10
subqueries for subsetting data 64
subsetting data using correlated subqueries 73
subsetting data using noncorrelated subqueries 66
subsetting rows using calculated values 42
subsetting rows with conditional operators 35
summarizing groups of data 17, 51
tools for summarizing data 883, 884, 889
validating general form 75
viewing all columns 31
viewing SELECT statement general form 30
writing SQL procedure steps 7
quotation marks
literal tokens 314
macro quoting functions and 318
macro triggers and 315
referencing macro variables 307
R
random numbers, generating 474, 475
random samples
creating with replacements 474, 475
creating without replacements 477
defined 470
raw data filenames, storing 522
referential integrity constraints 197, 670
REMOVE statement, DATA step 667
RENAME statement
DATA step 643
DATASETS procedure 490
RENAME= data set option 594
REPLACE statement, DATA step 667
REPORT procedure 883
RESET statement, SQL procedure 76, 287
resource management
benchmark guidelines 707
comparing probable usage 874, 875, 876
comparing summarization tools 884, 887
computer resources 702
efficiency trade-offs 705
system options for 706
RETAIN statement, DATA step 643
return codes (FIND method) 610
REUSE= data set option 747
REUSE= system option 747
rows
accessing in tables 239
counting all 58
counting number of 58
deleting in tables 216
eliminating duplicates from output 33
estimating number of 871
handling insertion errors 202
inserting in tables 189, 190, 191
joining tables with matching values 93
limiting number displayed 32
ordering by multiple columns 12
processing unique vs. duplicate 137
restricting processing 280
row numbers in output 282
updating based on CASE expression 212, 214, 215
updating values 207
updating with different expressions 210
updating with same expression 207
S
samples
defined 470
SAS log
%PUT statement 316
displaying macro variable values 315, 316
MCOMPILENOTE= option 393
SYMBOLGEN system option 315
SASAUTOS= system option 452
SASMSTORE system option 456
SASMSTORE= system option 454
SCL programs
SYMGETN function 380
SYMPUTN routine 380
security, table views 267
SELECT clause
CREATE TABLE statement (SQL) 187
INSERT statement (SQL) 194
SELECT statement (SQL) 9, 14, 31, 33, 51, 52
SELECT statement, FORMAT procedure
best practices 770
SELECT statement, SQL procedure
best practices 770
CASE expression 215
creating new columns 9
DISTINCT keyword 33
FEEDBACK option 31
general form 30
general form for inner join 89, 105
general form for outer join 99
general form for set operations 133
GROUP BY clause 17, 51, 52, 54, 57, 61
LABEL= option 46
NOPRINT option 371
ordering by multiple columns 12
ordering rows 11
querying multiple tables 13
SELECT clause 9, 14, 31, 33, 51, 52
selecting columns 9
specifying columns in multiple tables 14
specifying multiple table names 15
specifying subset criteria 10
specifying tables 10
summarizing groups of data 51, 52
VALIDATE keyword 76
viewing all columns 31
WHERE clause 10, 15, 35, 42, 43
SELECT/WHEN statement 777, 830
semicolon (;) in macro programs 394
sequential access 864
session-compiled macros 442
SET clause
INSERT statement (SQL) 189, 190
UPDATE statement (SQL) 207
set operations
combining and overlaying columns 137
defined 133
EXCEPT set operator 136, 139, 142, 143
INTERSECT set operator 136, 146, 147, 148, 149
modifying results via keywords 138
OUTER UNION set operator 136, 158, 163
processing multiple operations 135
processing single operations 134
processing unique vs. duplicate rows 137
UNION set operator 136, 152, 153, 154, 155
SET statement, DATA step
handling large data set sorts 830
hash objects and 608
KEY= option 562
multiple 554
NOBS= option 473
simple indexes
creating 245
SORT procedure
calculating space requirements 826
EQUALS option 846
NODUPKEY option 842
NODUPRECS option 844
NOEQUALS option 846
NOTHREADS option 825
OUT= option 830
SORTSIZE= option 828
TAGSORT option 840
THREADS option 825
SORTCUT= system option 852
SORTCUTP= system option 852
SORTEDBY= data set option 823
sorting
allocating resources 828
avoiding unnecessary 812, 814, 816, 817, 819, 823
calculating resources 826
host sort utilities 851
large data sets 830
ordering by multiple columns 12
ordering rows 11
SORTCUT= system option 852
SORTCUTP= system option 852
SORTNAME= system option 853
SORTPGM= system option 851
threaded 825
SORTNAME= system option 853
SORTPGM= system option 851
SORTSIZE= option, SORT procedure 828
SORTSIZE= system option 828
sounds-like conditional operator
description 36
general form 42
WHERE conditions and 870
SOURCE entries, storing 446, 447
SOURCE option, %MACRO statement 455, 457
sparse match 538
special characters, masking
%BQUOTE function 321
%NRSTR function 321
%STR function 319
macro function support 318
special tokens 314
spelling variations 42
SQL procedure
accessing metadata 278
combining data horizontally 546, 550, 551, 554
creating macro variables 371, 373, 376
detail reports 880
DOUBLE option 282
ERRORSTOP option 293
EXEC option 293
FEEDBACK option 31
FLOW option 283
functionality 5
INOBS= option 280
INSERT statement 189
joining data 545
LOOPS= option 293
managing indexes 485
NODOUBLE option 282
NOERRORSTOP option 293
NOFLOW option 283
NONUMBER option 282
NOSTIMER option 285
NUMBER option 282
specifying options 279
STIMER option 285
tools for summarizing data 883
UNDO_POLICY option 203
unique features 5
writing steps 7
SQLOOPS macro variable 293
STATS system option 706
STIMER option, SQL procedure 285
STOP statement 472
storage
array values 584, 585, 586, 589
best practices 797
compressing data files 741, 742, 743, 746, 747, 748
conserving with DATA step views 753, 754, 755, 756, 757, 758
in autocall libraries 446, 450, 451, 452
macro definitions in catalog SOURCE entries 446, 447
macro definitions in external files 443
permanent locations for formats 638
raw data filenames in data sets 522
raw data filenames in external files 522
reducing for character variables 732
reducing for numeric variables 733, 734, 735, 736
session-compiled macros 442
Stored Compiled Macro Facility 454, 455, 456, 457
STORE option, %MACRO statement 454
Stored Compiled Macro Facility 454, 455, 456, 457
SUBMIT block 379
subqueries
comparison operator in 68
noncorrelated 65
subsetting data 64
subsetting columns in tables 184
subsetting data
correlated subqueries and 73
indexes and 243
noncorrelated subqueries and 66
subqueries and 64
subsetting IF statement 768, 786, 788
subsetting rows
functionality 15
specifying criteria 10
using calculated values 42
using conditional operators 35
subsetting variables 792, 793, 794
sum statement 559
summarizing data
comparing tools 883, 884, 887, 889
GROUP BY clause, SELECT statement (SQL) 17, 51, 52
SELECT clause and 52
summary functions
number of arguments 51
on groups of data 52
SELECT clause and 52
with columns outside function 55
with multiple arguments 54
with single arguments 53
SUMMARY procedure
CLASS statement 819
multilabel formats 629
tools for summarizing data 883
summary statistics
combining data and 555, 556, 559
sum statement 559
symbol tables
%GLOBAL statement 406
%LOCAL statement 408
defined 306
SYMBOLGEN system option 315
SYMGETN function 380
SYMPUT routine
creating multiple macro variables 360
DATA step expressions and 353
DATA step variables and 351
SCL program support 380
SYMPUTN routine 380
SYMPUTX routine 358
SYSDATE automatic macro variable 308
SYSDATE9 automatic macro variable 308, 326, 330
SYSDAY automatic macro variable 308
SYSERR automatic macro variable 308
SYSJOBID automatic macro variable 308
SYSLAST automatic macro variable 308, 328
SYSPARM automatic macro variable 308
SYSSCP automatic macro variable 308
system options, tracking resources 706
systematic samples
defined 470
from known number of observations 471
from unknown number of observations 473
SYSTENV automatic macro variable 308
SYSTIME automatic macro variable 308, 330
SYSVER automatic macro variable 308
T
table aliases, specifying 94
TABLE statement, TABULATE procedure 629
tables
base 535
copying 187
creating from query results 186
creating like others 183
creating output tables 19
creating with integrity constraints 196, 197
displaying integrity constraints 205
displaying structure 182
dropping 225
generating Cartesian products 87
joining with SQL procedure 545
methods of creating 176
querying multiple 13
specifying 10
specifying data types 179
specifying multiple table names 15
symbol 306
updating values 207
virtual 261
TABULATE procedure
FORMAT statement 629
FORMAT= option 629
MLF option 629
multilabel formats 629
TABLE statement 629
tools for summarizing data 883
TAGSORT option, SORT procedure 840
testing performance 285
text, macro variable references and 332
threaded sorts 825
THREADS option, SORT procedure 825
THREADS system option 825
TITLE statement, SQL procedure 47
titles, specifying 47
tokens
literal 314
macro triggers and 315
macro variables and 313
number 314
percent sign and 321
special 314
transaction data sets 566, 567, 660
TRANSPOSE procedure
adding variable names 593
BY statement 595
general form 590
NAME= option 593
NOTSORTED option 596
PREFIX= option 593
RENAME= data set option 594
VAR statement 591
transposed data sets 595, 596, 597, 598, 599
TRIM function 869
TYPE= data set option 679
TYPES statement, MEANS procedure 891
U
UNDO_POLICY option, SQL procedure 203
UNIFORM option, PRINT procedure 757
UNION set operator
UNIQUE constraint type
constraint specification 200
in column specification 197
UNIQUE option
DATA step 481
MODIFY statement (DATA) 666
UNIVARIATE procedure 771, 819, 883
update process
for views 268
UPDATE statement, DATA step 567
UPDATE statement, SQL procedure
audit trails and 677
controlling UNDO processing 203
functionality 76
general form 207
SET clause 207
updating table row values 207
updating views 268
UPDATEMODE= option, MODIFY statement (DATA) 663
USERVAR statement 682
user variables 682
user-defined macro variables
%LET statement and 310
defined 306
global symbol table and 406
USING clause, CREATE VIEW statement (SQL) 266
V
VALIDATE keyword 76
VALUE statement, FORMAT procedure
combining data 541
creating formats with overlapping ranges 628
MULTILABEL option 628
value/identifier pairs 479
VALUES clause, INSERT statement (SQL)
handling errors for row insertions 202
VAR statement, TRANSPOSE function 591
variables
accumulator 559
adding descriptive names 593
appending with different lengths 517
appending with different types 519
associating formats with 540
best practices 785
character 732
hash objects and 608
key 535
listing in data sets 642
renaming 490
summary statistics and 886, 888
user 682
WHERE conditions and 870
VIEW= option, DATA step 755
views
benefits 261
conserving storage space 753, 754, 755, 756, 757, 758
creating 261
deleting 270
displaying definitions 264
dropping 270
enhancing table security 267
in queries 260
librefs and 266
managing 265
updating 268
VIEWTABLE window 677
virtual tables 261
W
WAYS statement, MEANS procedure 900
WHERE clause
SELECT statement (SQL) 10, 15, 35, 43
UPDATE statement (SQL) 207, 210, 268
WHERE condition
compound optimization and 871
controlling index usage 879
not optimized 870
optimizing 869
printing centile information 872
WHERE statement
DATA step 786, 788, 862, 864, 865, 867
PRINT procedure 789
WHERE= data set option 896
word scanner
macro triggers and 315
tokenization and 313