Index
A
accessing data 11–13, 81–86
ACCUMULATE() method 18
accumulator 17
ADD() method 19, 65, 81
adding data to Hash package 81
amt attribute 18, 19, 65
ANSI mode 40–41
arrays
assignment of 37–40
deferred dimensioning 35–36
dimensions of 36–37
temporary 34
variable 34–35
assignments
about 30–31
of arrays 37–40
ATTRIB statement 23
avgC variable 7
avgF variable 66
B
big data 1
BIGINT 24
BINARY() 24
Binary data types 24, 29
binding SQMSTMT 100
bindResults() method 83
bmi method 52
BY GROUP 92
by reference parameters 55–57
BY statement
SET statement with 89–92
SET statement with no 89
by value parameters 55
C
calling methods 79
CHAR() 24
Character data types 24, 28–29
clarity 2
clauses
HAVING 34
ORDER BY 92
select 99
WHERE 84, 87–88, 89
cnt attribute 18, 19, 65
complete key 79, 80
completing declarations 80
constructor 64–65
conversion, of DATA steps 27–28, 28–29
C_to_F() method 12, 63
D
data
accessing 11–13, 81–86
adding to Hash package 81
big 1
complete key and 79
defining 80
options for SET statement 89
partial key and 79
types of 4, 23–42, 24
data() method 80
data sources
about 87–88
sample tables 88–89
SET statement 89–93
data space 4–5
DATA step
conversion of 27–28, 28–29
DS2 procedure as an equivalent to 5–13
FCMP procedure with functions in a 69
datasource parameter 79
DATE 24
Date data types 24, 29, 30
DECIMAL() 24
declarations, completing 80
DECLARE PACKAGE statement 79, 80
DECLARE statement 2, 23, 26–27, 34, 99
DECLARE THREAD statement 96
DECLARE TX statement 36
declaring Hash package 79
deferred dimensioning 35–36
defineData() method 80
definekey() method 80
defining
data 80
keys 80
methods 47–59
degC variable 7
degF variable 66
dimensioning, deferred 35–36
dimensions, of arrays 36–37
dot(.) notation 63
DOUBLE/FLOAT 24
drop command 84
DROP= option 89
DROP statement 45
DS2
examples 75–86
FCMP package with functions in 70–71
methods, compared with FCMP procedure 71
DS2 procedure
See also specific topics
about 2
as a DATA step equivalent 5–13
DS2COND option 24
duplicate parameter 79
E
Eberhardt, Peter 68
embedded SQL 4
encapsulation 3–4
end= option, SET statement 49
ENDTHREAD statement 96
examples
DS2 75–86
Hash package 78–81
of object-oriented programming (OOP) 16–20
TZ package 72–73
F
FCMP package 68–71
FCMP procedure
compared with DS2 methods 71
with functions 69
with functions in a DATA step 69
FedSQL
MERGE statement with as data source 95
SET statement with data source as 92–93
fetch() method 83
find() method 81
firstOfMonth method 58
FORMAT statement 23, 26
forward reference 54–55
functions
FCMP package in DS2 with 70–71
FCMP procedure in a DATA step with 69
FCMP procedure with 69
IFC() 32–33
IFN() 32–33
G
get methods 19
getAmt() method 65
getAvg() method 19, 65, 66
getCnt method 65
GETLOCALTIME method 71
getMax method 20
getMin method 20
GETOFFSET method 71
getRange() method 9, 11
GETTIMEZONEID method 71
GETTIMEZONENAME method 72
GETUTCTIME method 72
H
HASH object. See Hash package
Hash package
adding data 81
declaring 79
example 78–81
hashexp parameter 79
Hash/Hash iterator package 68
HAVING clause 34
HPDS2 procedure 2
HTTP package 68
I
identifier 2
IF DONE statement 49
IF operator 32–33
IF statement 51
IFC() function 32–33
IFN() function 32–33
IF/THEN statement 32–33
IN= option 89
in-database processing 5
inDate parameter 58
INFORMAT option 26
inFromDate parameter 83
INIT() method 3, 7–8, 44, 47, 77, 84, 98
initialization 7–8
IN_OUT statement 55
INPUT statement 87–88
instantiation 62–63
INTEGER 24
inToDate parameter 83
ISO 8601 date formats and functions 30
J
JSON package 68
K
KEEP= option 89
KEEP statement 45
keys, defining 80
L
LABEL option 26
LENGTH statement 23
Logger package 68
lookup and retrieval 81
M
Matrix package 68
MERGE statement
about 87–88, 93–95, 100
with FedSQL as data source 95
method parameter, packages as 67
method return variable, packages as 67
methods
about 3
ACCUMULATE() 18
ADD() 19, 65, 81
bindResults() 83
bmi 52
calling 79
C_to_F() 12, 63
data() 80
defineData() 80
definekey() 80
defining 47–59
fetch() 83
find() 81
firstOfMonth 58
forward reference 54–55
get 19
getAmt() 65
getAvg() 19, 65, 66
getCnt 65
GETLOCALTIME 71
getMax 20
getMin 20
GETOFFSET 71
getRange() 9, 11
GETTIMEZONEID 71
GETTIMEZONENAME 72
GETUTCTIME 72
INIT() 3, 7–8, 44, 47, 77, 84, 98
modularity and 53–54
overloading 18, 51–53
packages and 9–11
REPLACE() 81
RUN() 3, 7–8, 12, 13, 44, 47, 49, 58, 76, 82, 98
scope and 57–59
setAmt 65
setCnt 65
system-defined 47–49
TERM() 3, 7–8, 19, 47, 98
thread 98
TOISO8601 72
TOLOCALTIME 72
TOTIMESTAMPZ 72
TOUTCTIME 72
user-defined 8–9, 49–59
missing values 40–42
modularity
about 3–4
methods and 53–54
multidata parameter 79
N
naming variables 25–26
NCHAR() 24
NULL values 40–42
Numeric() 24
Numeric data types 24, 27–28
NVARCHAR() 24
O
object-oriented programming (OOP)
about 2
background of 15–16
defined 15
example of 16–20
future of 20–21
objects, packages as 64–67
OOP. See object-oriented programming (OOP)
operations 30–31
options
DROP= 89
DS2COND 24
end= 49
IN= 89
INFORMAT 26
KEEP= 89
LABEL 26
overwrite=yes 13
RENAME= 89
SCOND 24
WHERE= 89
ORDER BY clause 92
ordered parameter 79
OUTPUT statement 98
overloading methods 51–53
overwrite=yes option 13
P
packages
about 61
instantiation 62–63
as method parameter 67
as method return variable 67
methods and 9–11
as objects 64–67
scope and 67
system-defined 68–73
user-defined 61–67
using package variables 63–64
parameterized threaded read 82–86
parameters
datasource 79
duplicate 79
hashexp 79
inDate 58
inFromDate 83
inToDate 83
method 67
multidata 79
ordered 79
by reference 55–57
suminc 79
thread 98–99
by value 55
partial key 79, 80
PDV (Program Data Vector) 3, 44–45
problem space 2–4
procedures
DS2 2, 5–13
FCMP 69, 71
HPDS2 2
processing 7–8
Program Data Vector (PDV) 3, 44–45
programming blocks
common 3–4
scope in other 45
Q
QUIT statement 6
R
recursion 59
RENAME= option 89
REPLACE() method 81
retrieval, lookup and 81
RETURN statement 9, 50
returning rows 100
rows, returning 100
RUN() method 3, 7–8, 12, 13, 44, 47, 49, 58, 76, 82, 98
S
SAS mode 40–41
saving tables 29
SCOND option 24
scope
about 2–3, 43–44
methods and 57–59
in other programming blocks 45
packages and 67
Program Data Vector (PDV) 44–45
Secosky, Jason 68
select clause 99
SELECT statement 33–34, 93
SET FROM statement 4, 96
SET statement
about 4, 11–12, 12–13, 82, 87–88
Data set options 89
end= option 49
with FedSQL as data source 92–93
with no BY statement 89
with BY statement 89–92
setAmt method 65
setCnt method 65
SMALLINT 24
SQL, embedded 4
sqlstmt package 82–86
SQLSTMT package
about 68, 99
binding 100
returning the rows 100
statements
BY 89–92
ATTRIB 23
DECLARE 2, 23, 26–27, 34, 99
DECLARE PACKAGE 79, 80
DECLARE THREAD 96
DECLARE TX 36
DROP 45
ENDTHREAD 96
FORMAT 23, 26
IF 51
IF DONE 49
IF/THEN 32–33
IN_OUT 55
INPUT 87–88
KEEP 45
LENGTH 23
MERGE 87–88, 93–95, 100
OUTPUT 98
QUIT 6
RETURN 9, 50
SELECT 33–34, 93
SET 4, 11–13, 49, 82, 87–93
SET FROM 4, 96
THREAD 96
VARARRAY 34–35, 36
VARARRAY OUT 36
suminc parameter 79
system-defined methods 47–49
system-defined packages 68–73
T
tables
sample 88–89
saving 29
temporary arrays 34
temps variable 66
TERM() method 3, 7–8, 19, 47, 98
termination 7–8
testing for missing or NULL values 41–42
THREAD statement 96
threaded data access 4
threaded read
about 82
parameterized 82–86
threads
about 12–13, 96–98
methods 98
parameters 98–99
TIME() 24
Time data types 24
TIMESTAMP() 24
TINYINT 24
TOISO8601 method 72
TOLOCALTIME method 72
TOTIMESTAMPZ method 72
TOUTCTIME method 72
%TSLIT() macro 31
TZ package
about 68, 71–72
example of 72–73
U
Unicode UTF-8 29
user-defined methods 8–9, 49–59
user-defined packages 61–67
V
values, missing and NULL 40–42
VARARRAY OUT statement 36
VARARRAY statement 34–35, 36
VARBINARY() 24
VARCHAR() 24
variable arrays 34–35
variables
about 3
avgC 7
avgF 66
data types and 23–42
degC 7
degF 66
naming 25–26
package 63–64
temps 66
W
WHERE clause 84, 87–88, 89
WHERE= option 89