O'Reilly logo

SAS 9.4 SQL Procedure User's Guide, Fourth Edition, 4th Edition by SAS Institute

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Index
A
abbreviating column names 76
aggregate functions 56
creating macro variables from result of
158
HAVING clause with 70
table of 56
using 56
with unique values 61
alias
assigning a column alias 30
referring to calculated columns by 31
table aliases 76
ALL keyword 345
set operators and 143
ALTER TABLE statement 232
ANSI standard
SQL procedure and 387
arithmetic operators 388
asterisk (*) notation 254
automatic macro variables 157, 162
averages, weighted 178
B
BETWEEN condition 316
BETWEEN-AND operators
retrieving rows 51
Boolean operators
retrieving rows 47
BTRIM function 316
C
CALCULATED 317
calculated columns 29
assigning column alias to 30
referring to by alias 31
sorting by 40
SQL 318
Cartesian product 75, 328, 330
cross joins 87
CASE expression 318
assigning values conditionally 32
case-control studies 309
CASE-OPERAND form
assigning values conditionally 34
character strings
converting to lowercase 343
converting to uppercase 369
trimming 317
COALESCE function 319
in joins 91
replacing missing values 35
collating sequence
alternate 388
column alias 30
ANSI standard extensions 147
assigning to calculated columns 30
referring to calculated columns 31
specifying 146
column attributes 235, 321
list of 27
specifying 36
column definitions
creating tables from 112
column headers
suppressing 28
column modifiers 388
column names
abbreviating 76
qualifying 76
column-definition component 320
column-modifier component 321
column-name component 323
columns 4
adding 123
aliases 146
altering 123, 232
assigning values conditionally 32
calculated 317, 318
calculating values 29
changing formats 125
changing informats 125
changing labels 125
changing width 125
combinations of values 303
creating 27
459
deleting 126
DICTIONARY.COLUMNS 154
grouping by multiple columns 65
grouping by one column 64
indexes on 236, 237, 252
inserting values 251
length of 321
list of, with attributes 27
locating specific columns 154
modifiers 388
modifying 125
multicolumn joins 80
renaming 125, 236, 253
replacing missing values 35
returning values 320
selecting 22, 253, 323
selecting all 22
selecting specific 23
sorting by 38
sorting by column position 41
sorting by multiple columns 38
sorting by unselected columns 42
sorting, with missing values 43
SQL procedure 216
storing values of 255
summarizing data in multiple columns
192
unique values 25
updating values 267
COMMIT statement 389
comparison operators
inner joins with 78
retrieving rows with 45
truncated string 53
composite indexes 127, 238
concatenating
query results 107
values in macro variables 160
conditional operators
retrieving rows with 49
CONNECT statement 236
CONNECTION TO component 324
CONSTDATETIME option 222
CONTAINS condition 324, 388
correlated subqueries 98, 357
CORRESPONDING keyword 345
COUNT(*) function 362
counting
all rows 62
duplicate rows 187
nonmissing values 62
unique values 61
CREATE INDEX statement 127, 237
CREATE TABLE statement 238
CREATE VIEW statement 243
cross joins 87, 334
D
data files
See tables
data set options
creating tables with 115
SQL procedure with 156
DATA step
compared with SQL procedure 5
match-merges 92
DATA step views
SQL procedure 217
data summaries 361
DATE function
replacing references to 146
DATETIME function
replacing references to 146
DBMS
accessing with SAS/ACCESS 167
connecting with LIBNAME statement
168
connecting with Pass-Through Facility
171
deploying PUT function and SAS
formats 145
DBMS connections
ending 248
sending DBMS statements to 249
SQL procedure 236
storing in views 245
DBMS queries 324
DBMS tables 4
PROC SQL views of 170
querying 169
debugging queries 138
DELETE statement 246
DESCRIBE statement 247
DESCRIBE VIEW statement 152
DICTDIAG option 223
DICTIONARY tables 149
performance and 155
reporting from 281
retrieving information about 151
using 153
views and 149
DICTIONARY.COLUMNS 154
DICTIONARY.TABLES 153
DISCONNECT statement 248
DOUBLE option 223
DQUOTE= option 223
DROP statement 248
E
embedded LIBNAME statements 245
embedded SQL 390
equijoins 328
460 Index
errors
caused by missing values 63
grouping errors caused by missing
values 67
update errors 122
ERRORSTOP option 223
example tables 7
EXCEPT operator 349
combining queries 103, 105
EXEC option 224
EXECUTE statement 249
execution time 140
existence of a group of values 99
EXISTS condition 99, 325
EXITCODE option 224
expanded SELECT * statement 140
F
FEEDBACK option 224
expanding SELECT * statement with
140
fields
See columns
files
See tables
filtering grouped data 69
HAVING clause versus WHERE clause
70
using a simple HAVING clause 69
using HAVING clause with aggregate
functions 70
FLOW option 224
foreign key 129
formats
changing column format 125
deploying inside a DBMS 145
for columns 321
FROM clause 20, 261
full outer joins 86
functions
FCMP procedure 389
SQL procedure and 389
sql-expression and 352
G
general integrity constraints 129
GROUP BY clause 21, 263
grouping data 64
by multiple columns 65
by one column 64
filtering grouped data 69
finding errors caused by missing values
67
grouping and sorting 66
with missing values 67
without summarizing 64
H
HAVING clause 21, 264
aggregate functions with 70
filtering grouped data 69
filtering grouped data, versus WHERE
clause 70
hierarchical data
expanding in tables 189
host-variable references 157
I
IN condition 325
IN operator
multiple-value subqueries and 97
retrieving rows 50
in-database procedures
generating SQL for 374
in-line views 134, 262, 388
querying 296
temporary tables versus 143
indexes 127
composite 127
composite indexes 238
creating 127
creating with CREATE INDEX
statement 127
deleting 128, 248
managing 238
on altered columns 236
on columns 237, 252
query performance and 142
simple indexes 238
SQL procedure 238
tips for creating 127
UNIQUE keyword 237
unique values 127
informats
changing column informat 125
for columns 321
INNER JOIN keywords 78
inner joins 75, 329
comparison operators for 78
creating with INNER JOIN keywords
78
data from multiple tables 82
multicolumn joins 80
null values and 79
order of output 77
reflexive joins 83
self-joins 83
showing relationships within a table 83
Index 461

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required