SAS 9.4 SQL Procedure User's Guide, Third Edition, 3rd Edition

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

457

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, 252

replacing missing values 35

returning values 320

selecting 22, 252, 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

458 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 237

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 459