Scribd
Upload
14 views106 pages

SAS Data Step Programming Guide

This document discusses topics in Chapter 7 of a SAS programming textbook, including: 1. Reading SAS data sets and creating variables in the DATA step 2. Conditional processing using IF-THEN logic 3. Dropping and keeping variables, and reading Excel spreadsheets containing date fields The objectives are to conditionally execute statements, control the length of character variables, create and manipulate SAS variables, and control which variables are included in output SAS data sets.

Uploaded by

sameer_kini
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PPT, PDF, TXT or read online on Scribd
14 views106 pages

SAS Data Step Programming Guide

This document discusses topics in Chapter 7 of a SAS programming textbook, including: 1. Reading SAS data sets and creating variables in the DATA step 2. Conditional processing using IF-THEN logic 3. Dropping and keeping variables, and reading Excel spreadsheets containing date fields The objectives are to conditionally execute statements, control the length of character variables, create and manipulate SAS variables, and control which variables are included in output SAS data sets.

Uploaded by

sameer_kini
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PPT, PDF, TXT or read online on Scribd

Chapter 7: DATA Step Programming

7.1 Reading SAS Data Sets and Creating Variables

7.2 Conditional Processing

7.3 Dropping and Keeping Variables (Self-Study)


7.4 Reading Excel Spreadsheets Containing Date Fields
(Self-Study)

Chapter 7: DATA Step Programming


7.1 Reading SAS Data Sets and Creating Variables
7.2 Conditional Processing

7.3 Dropping and Keeping Variables (Self-Study)


7.4 Reading Excel Spreadsheets Containing Date Fields
(Self-Study)

Objectives

Create a SAS data set using another SAS data set


as input.
Create SAS variables.
Use operators and SAS functions to manipulate
data values.
Control which variables are included in a
SAS data set.

Reading a SAS Data Set


Create a temporary SAS data set named onboard
from the permanent SAS data named [Link]
and create a variable that represents the total passengers
on board.
Sum FirstClass and Economy values to compute
Total.
SAS date values
New
[Link]
Variable

Flight Date

Dest

439
921
114

LAX
DFW
LAX

14955
14955
14956

FirstClass Economy
20
20
15

137
131
170

Reading a SAS Data Set


To create a SAS data set using a SAS data set as
input, you must use the following:
DATA statement to start a DATA step and name
the SAS data set being created (output data set:
onboard)
SET statement to identify the SAS data set being
read (input data set: [Link])
To create a variable, you must use an assignment
statement to add the values of the variables
FirstClass and Economy and assign the sum
to the variable Total.

Reading a SAS Data Set


General form of a DATA step:
DATA
DATAoutput-SAS-data-set;
output-SAS-data-set;
SET
SET input-SAS-data-set;
input-SAS-data-set;
<additional
<additionalSAS
SASstatements>
statements>
RUN;
RUN;
By default, the SET statement reads all of the following:
observations from the input SAS data set
variables from the input SAS data set

Assignment Statements
An assignment statement does the following:
evaluates an expression
assigns the resulting value to a variable
General form of an assignment statement:
variable=expression;
variable=expression;

SAS Expressions
An expression contains operands and operators
that form a set of instructions that produce a value.
Operands are
variable names
constants.

Operators are
symbols that request
arithmetic calculations
SAS functions.

Using Operators
Selected operators for basic arithmetic calculations
in an assignment statement:

Compiling the DATA Step


libname ia 'SAS-data-library';
data onboard;
set [Link];
Total=FirstClass+Economy;
run;

PDV

10

c07s1d1

...

Compiling the DATA Step


libname ia 'SAS-data-library';
data onboard;
set [Link];
Total=FirstClass+Economy;
run;

PDV

11

c07s1d1

...

Executing the DATA Step

[Link]

data onboard;
PDV is
set [Link];
Total=FirstClass+Economy;initialized
run;

PDV
Flight Date
.

Dest FirstClass Economy Total


.
.
.

onboard
Flight Date

12

Dest FirstClass Economy Total


...

Executing the DATA Step

PDV

[Link]

data onboard;
set [Link];
Total=FirstClass+Economy;
run;

onboard
Flight Date

13

Dest FirstClass Economy Total


...

Executing the DATA Step

PDV

[Link]

data onboard;
set [Link];
Total=FirstClass+Economy;
run;

onboard
Flight Date

14

Dest FirstClass Economy Total


...

Executing the DATA Step

PDV

data onboard;
set [Link];
Total=FirstClass+Economy;
run;

Flight Date

15

[Link]

Dest FirstClass Economy Total


...

Executing the DATA Step

PDV

[Link]

data onboard;
set [Link];
Total=FirstClass+Economy;
run;

onboard Automatic output


Flight Date

16

Dest FirstClass Economy Total


...

Executing the DATA Step

[Link]

Automatic Return

PDV

data onboard;
set [Link];
Total=FirstClass+Economy;
run;

onboard Automatic output


Flight Date

17

Dest FirstClass Economy Total


...

Executing the DATA Step

[Link]

Reinitialize Total
to missing

PDV

data onboard;
set [Link];
Total=FirstClass+Economy;
run;

onboard
Flight Date

18

Dest FirstClass Economy Total


...

Executing the DATA Step

PDV

[Link]

data onboard;
set [Link];
Total=FirstClass+Economy;
run;

onboard

19

...

Executing the DATA Step

PDV

[Link]

data onboard;
set [Link];
Total=FirstClass+Economy;
run;

onboard

20

...

Executing the DATA Step

PDV

[Link]

data onboard;
set [Link];
Total=FirstClass+Economy;
run;

onboard

21

...

Executing the DATA Step

PDV

[Link]

data onboard;
set [Link];
Total=FirstClass+Economy;
run;

onboard Automatic Output


Flight Date
Dest FirstClass Economy Total
439
12/11/00 LAX
20
137
157
22

...

Executing the DATA Step

[Link]

Automatic return

PDV

data onboard;
set [Link];
Total=FirstClass+Economy;
run;

onboard Automatic output


Flight Date
Dest FirstClass Economy Total
439
12/11/00 LAX
20
137
157
23

...

Executing the DATA Step

Continue until
end of file

PDV

onboard

24

[Link]

data onboard;
set [Link];
Total=FirstClass+Economy;
run;

Assignment Statements
proc print data=onboard;
format Date date9.;
run;
The SAS System
Obs
1
2
3
4
5
6
7
8
9
10

25

Flight
439
921
114
982
439
982
431
982
114
982

Date
11DEC2000
11DEC2000
12DEC2000
12DEC2000
13DEC2000
13DEC2000
14DEC2000
14DEC2000
15DEC2000
15DEC2000

Dest
LAX
DFW
LAX
dfw
LAX
DFW
LaX
DFW
LAX
DFW

First
Class
20
20
15
5
14
15
17
7
.
14

Why is Total missing in observation 9?

Economy

Total

137
131
170
85
196
116
166
88
187
31

157
151
185
90
210
131
183
95
.
45
c07s1d1

Using SAS Functions


A SAS function is a routine that returns a value
that is determined from specified arguments.
General form of a SAS function:
function-name(argument1,argument2,
function-name(argument1,argument2,.. ...).)
Example:

Total=sum(FirstClass,Economy);

26

Using SAS Functions


SAS functions can do the following:
perform arithmetic operations
compute sample statistics (for example: sum, mean,
and standard deviation)
manipulate SAS dates and process character values
perform many other tasks
Sample statistics functions ignore missing values.

27

Using the SUM Function


data onboard;
set [Link];
Total=sum(FirstClass,Economy);
run;

28

c07s1d2

Using the SUM Function


proc print data=onboard;
format Date date9.;
run;
The SAS System
Obs
1
2
3
4
5
6
7
8
9
10

29

Flight
439
921
114
982
439
982
431
982
114
982

Date
11DEC2000
11DEC2000
12DEC2000
12DEC2000
13DEC2000
13DEC2000
14DEC2000
14DEC2000
15DEC2000
15DEC2000

Dest
LAX
DFW
LAX
dfw
LAX
DFW
LaX
DFW
LAX
DFW

First
Class
20
20
15
5
14
15
17
7
.
14

Economy

Total

137
131
170
85
196
116
166
88
187
31

157
151
185
90
210
131
183
95
187
45
c07s1d2

Using Date Functions


You can use SAS date functions to do the following:
create SAS date values
extract information from SAS date values

30

Date Functions: Create SAS Dates


TODAY()

obtains the date value from


the system clock.

MDY(month,day,year) uses numeric month, day, and


year values to return the
corresponding SAS date value.

31

Date Functions: Extracting Information


YEAR(SAS-date)

extracts the year from a SAS date


and returns a four-digit value for
year.

QTR(SAS-date)

extracts the quarter from a SAS


date and returns a number from
1 to 4.

MONTH(SAS-date)

extracts the month from a SAS


date and returns a number from
1 to 12.

WEEKDAY(SAS-date) extracts the day of the week from


a SAS date and returns a number
from 1 to 7, where 1 represents
Sunday, and so on.
32

Using the WEEKDAY Function


Add an assignment statement to the DATA step to
create a variable that shows the day of the week
that the flight occurred.
data onboard;
set [Link];
Total=sum(FirstClass,Economy);
DayOfWeek=weekday(Date);
run;
Print the data set, but do not display the variables
FirstClass and Economy.

33

c07s1d3

Using the WEEKDAY Function


proc print data=onboard;
var Flight Dest Total DayOfWeek Date;
format Date weekdate.;
run;
The SAS System

Obs
1
2
3
4
5
6
7
8
9
10

34

Flight
439
921
114
982
439
982
431
982
114
982

Dest
LAX
DFW
LAX
dfw
LAX
DFW
LaX
DFW
LAX
DFW

Total

Day
Of
Week

157
151
185
90
210
131
183
95
187
45

2
2
3
3
4
4
5
5
6
6

Date
Monday,
Monday,
Tuesday,
Tuesday,
Wednesday,
Wednesday,
Thursday,
Thursday,
Friday,
Friday,

December
December
December
December
December
December
December
December
December
December

11,
11,
12,
12,
13,
13,
14,
14,
15,
15,

2000
2000
2000
2000
2000
2000
2000
2000
2000
2000

What if you do not want the variables FirstClass


and Economy in the data set?

c07s1d3

Selecting Variables
You can use a DROP or KEEP statement
in a DATA step to control which variables
are written to the new SAS data set.
General form of DROP and KEEP statements:
DROP
DROPvariables;
variables;
KEEP
KEEPvariables;
variables;

35

Selecting Variables

Equivalent

Do not store the variables FirstClass and


Economy in the onboard data set.
data onboard;
set [Link];
drop FirstClass Economy;
Total=sum(FirstClass,Economy);
run;
keep Flight Date Dest Total;
D

PDV
Flight Date Dest FirstClass Economy Total
.
.
.
.
36

c07s1d4

Selecting Variables
proc print data=onboard;
format Date date9.;
run;
The SAS System
Obs
1
2
3
4
5
6
7
8
9
10

37

Flight
439
921
114
982
439
982
431
982
114
982

Date
11DEC2000
11DEC2000
12DEC2000
12DEC2000
13DEC2000
13DEC2000
14DEC2000
14DEC2000
15DEC2000
15DEC2000

Dest

Total

LAX
DFW
LAX
dfw
LAX
DFW
LaX
DFW
LAX
DFW

157
151
185
90
210
131
183
95
187
45
c07s1d4

Selecting Variables
proc contents data=onboard;
run;
Partial Output
Alphabetic List of Variables and Attributes

38

Variable

Type

Len

2
3
1
4

Date
Dest
Flight
Total

Num
Char
Char
Num

8
3
3
8

Exercises
This exercise reinforces the concepts discussed
previously.

39

Chapter 7: DATA Step Programming


7.1 Reading SAS Data Sets and Creating Variables

7.2 Conditional Processing


7.3 Dropping and Keeping Variables (Self-Study)
7.4 Reading Excel Spreadsheets Containing Date Fields
(Self-Study)

40

Objectives

41

Execute statements conditionally using IF-THEN logic.


Control the length of character variables explicitly with
the LENGTH statement.
Select rows to include in a SAS data set.
Use SAS date constants.

Conditional Execution
International Airlines wants to compute revenue for
Los Angeles and Dallas flights based on the prices
in the table below.

DESTINATION CLASS
LAX
First
Economy
DFW
First
Economy

42

AIRFARE
2000
1200
1500
900

Conditional Execution
General form of IF-THEN and ELSE statements:
IF
IFexpression
expressionTHEN
THENstatement;
statement;
ELSE
ELSE statement;
statement;
An expression contains operands and operators that form
a set of instructions that produce a value.
Operands are
variable names
constants.

43

Operators are
symbols that request
a comparison
a logical operation
an arithmetic calculation
SAS functions.
Only one executable statement is allowed in an IF-THEN
or ELSE statement.

Conditional Execution
Compute revenue figures based on flight destination.
DESTINATION CLASS
AIRFARE
LAX
First
2000
Economy
1200
DFW
First
1500
Economy
900
data flightrev;
set [Link];
Total=sum(FirstClass,Economy);
if Dest='LAX' then
Revenue=sum(2000*FirstClass,1200*Economy);
else if Dest='DFW' then
Revenue=sum(1500*FirstClass,900*Economy);
run;
44

c07s2d1

Conditional Execution
data flightrev;
set [Link];
Total=sum(FirstClass,Economy);
if Dest='LAX' then
Revenue=sum(2000*FirstClass,1200*Economy);
else if Dest='DFW' then
Revenue=sum(1500*FirstClass,900*Economy);
run;
PDV (First Observation)
Flight Date Dest First Economy Total Revenue
Class
439
14955 LAX
20
137
157
.

45

...

Conditional Execution
data flightrev; TRUE
set [Link];
Total=sum(FirstClass,Economy);
if Dest='LAX' then
Revenue=sum(2000*FirstClass,1200*Economy);
else if Dest='DFW' then
Revenue=sum(1500*FirstClass,900*Economy);
run;
PDV (First Observation)
Flight Date Dest First Economy Total Revenue
Class
439
14955 LAX
20
137
157
.

46

...

Conditional Execution
data flightrev; TRUE
set [Link];
Total=sum(FirstClass,Economy);
if Dest='LAX' then
Revenue=sum(2000*FirstClass,1200*Economy);
else if Dest='DFW' then
Revenue=sum(1500*FirstClass,900*Economy);
run;
PDV (First Observation)

47

...

Conditional Execution
data flightrev;
set [Link];
Total=sum(FirstClass,Economy);
if Dest='LAX' then
Revenue=sum(2000*FirstClass,1200*Economy);
else if Dest='DFW' then
Revenue=sum(1500*FirstClass,900*Economy);
run;
PDV (First Observation)
Flight Date Dest First Economy Total Revenue
Class
439
14955 LAX
20
137
157 204400

48

...

Conditional Execution
data flightrev;
set [Link];
Total=sum(FirstClass,Economy);
if Dest='LAX' then
Revenue=sum(2000*FirstClass,1200*Economy);
else if Dest='DFW' then
Revenue=sum(1500*FirstClass,900*Economy);
run;
PDV (Fourth Observation)
Flight Date Dest First Economy Total Revenue
Class
982
14956 dfw
5
85
90
.

49

...

Conditional Execution
data flightrev; FALSE
set [Link];
Total=sum(FirstClass,Economy);
if Dest='LAX' then
Revenue=sum(2000*FirstClass,1200*Economy);
else if Dest='DFW' then
Revenue=sum(1500*FirstClass,900*Economy);
run;
PDV (Fourth Observation)
Flight Date Dest First Economy Total Revenue
Class
982
14956 dfw
5
85
90
.

50

...

Conditional Execution
data flightrev;
set [Link];
Total=sum(FirstClass,Economy);
if Dest='LAX' then
Revenue=sum(2000*FirstClass,1200*Economy);
else if Dest='DFW' then
Revenue=sum(1500*FirstClass,900*Economy);
run;
PDV (Fourth Observation)
Flight Date Dest First Economy Total Revenue
Class
982
14956 dfw
5
85
90
.

51

...

Conditional Execution
data flightrev; FALSE
set [Link];
Total=sum(FirstClass,Economy);
if Dest='LAX' then
Revenue=sum(2000*FirstClass,1200*Economy);
else if Dest='DFW' then
Revenue=sum(1500*FirstClass,900*Economy);
run;
PDV (Fourth Observation)
Flight Date Dest First Economy Total Revenue
Class
982
14956 dfw
5
85
90
.

52

...

Conditional Execution
data flightrev;
set [Link];
Total=sum(FirstClass,Economy);
if Dest='LAX' then
Revenue=sum(2000*FirstClass,1200*Economy);
else if Dest='DFW' then
Revenue=sum(1500*FirstClass,900*Economy);
run;
PDV (Fourth Observation)
Flight Date Dest First Economy Total Revenue
Class
982
14956 dfw
5
85
90
.

53

...

Conditional Execution
proc print data=flightrev;
format Date date9.;
run;
The SAS System
Obs
1
2
3
4
5
6
7
8
9
10

Flight
439
921
114
982
439
982
431
982
114
982

Date
11DEC2000
11DEC2000
12DEC2000
12DEC2000
13DEC2000
13DEC2000
14DEC2000
14DEC2000
15DEC2000
15DEC2000

Dest
LAX
DFW
LAX
dfw
LAX
DFW
LaX
DFW
LAX
DFW

First
Class
20
20
15
5
14
15
17
7
.
14

Economy

Total

Revenue

137
131
170
85
196
116
166
88
187
31

157
151
185
90
210
131
183
95
187
45

204400
147900
234000
.
263200
126900
.
89700
224400
48900

Why are two Revenue values missing?


54

c07s2d1

The UPCASE Function


You can use the UPCASE function to convert letters
from lowercase to uppercase.
General form of the UPCASE function:
UPCASE
UPCASE (argument)
(argument)

55

Conditional Execution
Use the UPCASE function to convert the Dest values
to uppercase for the comparison.
data flightrev;
set [Link];
Total=sum(FirstClass,Economy);
if upcase(Dest)='LAX' then
Revenue=sum(2000*FirstClass,1200*Economy);
else if upcase(Dest)='DFW' then
Revenue=sum(1500*FirstClass,900*Economy);
run;

56

c07s2d2

Conditional Execution
data flightrev;
set [Link];
Total=sum(FirstClass,Economy);
if upcase(Dest)='LAX' then
Revenue=sum(2000*FirstClass,1200*Economy);
else if upcase(Dest)='DFW' then
Revenue=sum(1500*FirstClass,900*Economy);
run;
PDV (Fourth Observation)
Flight Date
982

57

Dest First Economy Total Revenue


Class
14956 dfw
5
85
90
.

...

Conditional Execution
data flightrev;
set [Link];
Total=sum(FirstClass,Economy);
if upcase(Dest)='LAX' then
Revenue=sum(2000*FirstClass,1200*Economy);
else if upcase(Dest)='DFW' then
Revenue=sum(1500*FirstClass,900*Economy);
run;
PDV (Fourth Observation)

upcase('dfw')='DFW'

Flight Date
982

58

Dest First Economy Total Revenue


Class
14956 dfw
5
85
90
.

...

Conditional Execution
FALSE
data flightrev;
set [Link];
Total=sum(FirstClass,Economy);
if upcase(Dest)='LAX' then
Revenue=sum(2000*FirstClass,1200*Economy);
else if upcase(Dest)='DFW' then
Revenue=sum(1500*FirstClass,900*Economy);
run;
PDV (Fourth Observation)

upcase('dfw')='DFW'

Flight Date
982

59

Dest First Economy Total Revenue


Class
14956 dfw
5
85
90
.

...

Conditional Execution
data flightrev;
set [Link];
Total=sum(FirstClass,Economy);
if upcase(Dest)='LAX' then
Revenue=sum(2000*FirstClass,1200*Economy);
else if upcase(Dest)='DFW' then
Revenue=sum(1500*FirstClass,900*Economy);
run;
PDV (Fourth Observation)

upcase('dfw')='DFW'

Flight Date
982

60

Dest First Economy Total Revenue


Class
14956 dfw
5
85
90
.

...

Conditional Execution
TRUE
data flightrev;
set [Link];
Total=sum(FirstClass,Economy);
if upcase(Dest)='LAX' then
Revenue=sum(2000*FirstClass,1200*Economy);
else if upcase(Dest)='DFW' then
Revenue=sum(1500*FirstClass,900*Economy);
run;
PDV (Fourth Observation)
Flight Date
982

61

upcase('dfw')='DFW'

Dest First Economy Total Revenue


Class
14956 dfw
5
85
90
84000

Conditional Execution
proc print data=flightrev;
format Date date9.;
run;
The SAS System
Obs
1
2
3
4
5
6
7
8
9
10

62

Flight
439
921
114
982
439
982
431
982
114
982

Date
11DEC2000
11DEC2000
12DEC2000
12DEC2000
13DEC2000
13DEC2000
14DEC2000
14DEC2000
15DEC2000
15DEC2000

Dest
LAX
DFW
LAX
dfw
LAX
DFW
LaX
DFW
LAX
DFW

First
Class
20
20
15
5
14
15
17
7
.
14

Economy

Total

Revenue

137
131
170
85
196
116
166
88
187
31

157
151
185
90
210
131
183
95
187
45

204400
147900
234000
84000
263200
126900
233200
89700
224400
48900
c07s2d2

Conditional Execution
You can use the DO and END statements to
execute a group of statements based on a condition.
General form of the DO and END statements:
IF
IF expression
expressionTHEN
THENDO;
DO;
executable
executablestatements
statements
END;
END;
ELSE
ELSEDO;
DO;
executable
executablestatements
statements
END;
END;

63

Conditional Execution
Use DO and END statements to execute a group
of statements based on a condition.
data flightrev;
set [Link];
Total=sum(FirstClass,Economy);
if upcase(Dest)='DFW' then do;
Revenue=sum(1500*FirstClass,900*Economy);
City='Dallas';
end;
else if upcase(Dest)='LAX' then do;
Revenue=sum(2000*FirstClass,1200*Economy);
City='Los Angeles';
end;
run;
64

c07s2d3

Conditional Execution
proc print data=flightrev;
var Dest City Flight Date Revenue;
format Date date9.;
run;
The SAS System
Obs
1
2
3
4
5
6
7
8
9
10

65

Dest
LAX
DFW
LAX
dfw
LAX
DFW
LaX
DFW
LAX
DFW

City

Flight

Los An
Dallas
Los An
Dallas
Los An
Dallas
Los An
Dallas
Los An
Dallas

439
921
114
982
439
982
431
982
114
982

Why are City values truncated?

Date

Revenue

11DEC2000
11DEC2000
12DEC2000
12DEC2000
13DEC2000
13DEC2000
14DEC2000
14DEC2000
15DEC2000
15DEC2000

204400
147900
234000
84000
263200
126900
233200
89700
224400
48900
c07s2d3

Variable Lengths
At compile time, the length of a variable is determined
the first time that the variable is encountered.
data flightrev;
set [Link];
Total=sum(FirstClass,Economy);
if upcase(Dest)='DFW' then do;
Revenue=sum(1500*FirstClass,900*Economy);
City='Dallas';
end;
else if upcase(Dest)='LAX' then do;
Revenue=sum(2000*FirstClass,1200*Economy);
City='Los Angeles';
end;
run;
66

...

Variable Lengths
At compile time, the length of a variable is determined
the first time that the variable is encountered.
data flightrev;
set [Link];
Total=sum(FirstClass,Economy);
if upcase(Dest)='DFW' then do;
Revenue=sum(1500*FirstClass,900*Economy);
City='Dallas';
end;
else if upcase(Dest)='LAX' then do;
Revenue=sum(2000*FirstClass,1200*Economy);
Six characters between
City='Los Angeles';
the quotation marks:
end;
Length=6
run;
67

...

The LENGTH Statement


You can use the LENGTH statement to define
the length of a variable explicitly.
General form of the LENGTH statement:
LENGTH
LENGTH variable(s)
variable(s) $$length;
length;
Example:
length City $ 11;

68

The LENGTH Statement


data flightrev;
set [Link];
length City $ 11;
Total=sum(FirstClass,Economy);
if upcase(Dest)='DFW' then do;
Revenue=sum(1500*FirstClass,900*Economy);
City='Dallas';
end;
else if upcase(Dest)='LAX' then do;
Revenue=sum(2000*FirstClass,1200*Economy);
City='Los Angeles';
end;
run;
69

c07s2d4

The LENGTH Statement


proc print data=flightrev;
var Dest City Flight Date Revenue;
format Date date9.;
run;
The SAS System

70

Obs

Dest

City

1
2
3
4
5
6
7
8
9
10

LAX
DFW
LAX
dfw
LAX
DFW
LaX
DFW
LAX
DFW

Los Angeles
Dallas
Los Angeles
Dallas
Los Angeles
Dallas
Los Angeles
Dallas
Los Angeles
Dallas

Flight
439
921
114
982
439
982
431
982
114
982

Date

Revenue

11DEC2000
11DEC2000
12DEC2000
12DEC2000
13DEC2000
13DEC2000
14DEC2000
14DEC2000
15DEC2000
15DEC2000

204400
147900
234000
84000
263200
126900
233200
89700
224400
48900
c07s2d4

Subsetting Rows
In a DATA step, you can subset the rows (observations) in
a SAS data set with the following statements:

WHERE statement
DELETE statement
subsetting IF statement

The WHERE statement in a DATA step is the same


as the WHERE statement you saw in a PROC step.

71

Deleting Rows
You can use a DELETE statement to control which
rows are not written to the SAS data set.
General form of the DELETE statement:
IF
IF expression
expressionTHEN
THENDELETE;
DELETE;
The expression can be any SAS expression.
The DELETE statement is valid only in a DATA step.

72

Deleting Rows
Delete rows that have a Total value that is less than
or equal to 175.
data over175;
set [Link];
length City $ 11;
Total=sum(FirstClass,Economy);
if Total le 175 then delete;
if upcase(Dest)='DFW' then do;
Revenue=sum(1500*FirstClass,900*Economy);
City='Dallas';
end;
else if upcase(Dest)='LAX' then do;
Revenue=sum(2000*FirstClass,1200*Economy);
City='Los Angeles';
end;
run;
73

c07s2d5

Deleting Rows
proc print data=over175;
var Dest City Flight Date Total Revenue;
format Date date9.;
run;
The SAS System

74

Obs

Dest

1
2
3
4

LAX
LAX
LaX
LAX

City
Los
Los
Los
Los

Angeles
Angeles
Angeles
Angeles

Flight
114
439
431
114

Date
12DEC2000
13DEC2000
14DEC2000
15DEC2000

Total

Revenue

185
210
183
187

234000
263200
233200
224400

c07s2d5

Selecting Rows
You can use a subsetting IF statement to control
which rows are written to the SAS data set.
General form of the subsetting IF statement:
IF
IFexpression;
expression;
The expression can be any SAS expression.
The subsetting IF statement is valid only
in a DATA step.

75

Process Flow of a Subsetting IF


Subsetting IF:

DATA Statement
Read Observation
or Record

IF Expression

False

True
Continue Processing
Observation
Output Observation
to SAS Data Set
76

Selecting Rows
Select rows that have a Total value that is greater
than 175.
data over175;
set [Link];
length City $ 11;
Total=sum(FirstClass,Economy);
if Total gt 175;
if upcase(Dest)='DFW' then do;
Revenue=sum(1500*FirstClass,900*Economy);
City='Dallas';
end;
else if upcase(Dest)='LAX' then do;
Revenue=sum(2000*FirstClass,1200*Economy);
City='Los Angeles';
end;
run;
77

c07s2d6

Selecting Rows
proc print data=over175;
var Dest City Flight Date Total Revenue;
format Date date9.;
run;
The SAS System

78

Obs

Dest

1
2
3
4

LAX
LAX
LaX
LAX

City
Los
Los
Los
Los

Angeles
Angeles
Angeles
Angeles

Flight
114
439
431
114

Date
12DEC2000
13DEC2000
14DEC2000
15DEC2000

Total

Revenue

185
210
183
187

234000
263200
233200
224400

c07s2d6

Selecting Rows
The variable Date in the [Link] data set
contains SAS date values (numeric values).
01JAN1960

01JAN1961

14DEC2000

366

???

01/01/1961

12/14/2000

store
0
display
01/01/1960

What if you only want flights that were before a specific


date, such as 14DEC2000?
79

Using SAS Date Constants


The constant 'ddMMMyyyy'd (example: '14dec2000'd)
creates a SAS date value from the date enclosed in
quotation marks.
dd

is a one- or two-digit value for the day.

MMM is a three-letter abbreviation for the month


(JAN, FEB, MAR, and so on).

80

yyyy

is a four-digit value for the year.

is required to convert the quoted string


to a SAS date.

Using SAS Date Constants


data over175;
set [Link];
length City $ 11;
Total=sum(FirstClass,Economy);
if Total gt 175 and Date lt '14dec2000'd;
if upcase(Dest)='DFW' then do;
Revenue=sum(1500*FirstClass,900*Economy);
City='Dallas';
end;
else if upcase(Dest)='LAX' then do;
Revenue=sum(2000*FirstClass,1200*Economy);
City='Los Angeles';
end;
run;
81

c07s2d7

Using SAS Date Constants


proc print data=over175;
var Dest City Flight Date Total Revenue;
format Date date9.;
run;
The SAS System

82

Obs

Dest

1
2

LAX
LAX

City
Los Angeles
Los Angeles

Flight
114
439

Date
12DEC2000
13DEC2000

Total

Revenue

185
210

234000
263200

c07s2d7

Subsetting Data
What if the data were in a raw data file instead
of a SAS data set?
data over175;
infile 'raw-data-file';
input @1 Flight $3. @4 Date mmddyy8.
@12 Dest $3. @15 FirstClass 3.
@18 Economy 3.;
length City $ 11;
Total=sum(FirstClass,Economy);
if Total gt 175 and Date lt '14dec2000'd;
if upcase(Dest)='DFW' then do;
Revenue=sum(1500*FirstClass,900*Economy);
City='Dallas';
end;
else if upcase(Dest)='LAX' then do;
Revenue=sum(2000*FirstClass,1200*Economy);
City='Los Angeles';
end;
83run;
c07s2d8

Subsetting Data
proc print data=over175;
var Dest City Flight Date Total Revenue;
format Date date9.;
run;
The SAS System

84

Obs

Dest

1
2

LAX
LAX

City
Los Angeles
Los Angeles

Flight
114
439

Date
12DEC2000
13DEC2000

Total

Revenue

185
210

234000
263200

c07s2d8

WHERE or Subsetting IF?


Step and Usage

WHERE

IF

Yes

No

No
No
Yes

Yes
Yes
Yes

Variable in ALL data sets

Yes

Yes

Variable not in ALL data sets

No

Yes

PROC step
DATA step (source of variable)
INPUT statement
Assignment statement
SET statement (single data set)
SET/MERGE (multiple data sets)

85

WHERE or Subsetting IF?


Use a WHERE statement and a subsetting IF statement
in the same step.
data over175;
set [Link];
where Date lt '14dec2000'd;
length City $ 11;
Total=sum(FirstClass,Economy);
if Total gt 175;
if upcase(Dest)='DFW' then do;
Revenue=sum(1500*FirstClass,900*Economy);
City='Dallas';
end;
else if upcase(Dest)='LAX' then do;
Revenue=sum(2000*FirstClass,1200*Economy);
City='Los Angeles';
end;
run;
86

c07s2d9

WHERE or Subsetting IF?


proc print data=over175;
var Dest City Flight Date Total Revenue;
format Date date9.;
run;
The SAS System

87

Obs

Dest

1
2

LAX
LAX

City
Los Angeles
Los Angeles

Flight
114
439

Date
12DEC2000
13DEC2000

Total
185
210

Revenue
234000
263200

c07s2d9

Exercises
This exercise reinforces the concepts discussed
previously.

88

Chapter 7: DATA Step Programming


7.1 Reading SAS Data Sets and Creating Variables

7.2 Conditional Processing

7.3 Dropping and Keeping Variables (Self-Study)


7.4 Reading Excel Spreadsheets Containing Date Fields
(Self-Study)

89

Objectives

90

Compare DROP and KEEP statements to DROP=


and KEEP= data set options.

Selecting Variables
You can use a DROP= or KEEP= data set option in a
DATA statement to control which variables are written
to the new SAS data set.
General form of the DROP= and KEEP= data set options:
SAS-data-set(DROP=variables)
SAS-data-set(DROP=variables)
or
or
SAS-data-set(KEEP=variables)
SAS-data-set(KEEP=variables)

91

Selecting Variables

Equivalent

Do not store the variables FirstClass and Economy in


the onboard data set.

data onboard(drop=FirstClass Economy);


set [Link];
Total=FirstClass+Economy;
run;
data onboard(keep=Flight Date Dest Total);
D
D
PDV
Flight Date Dest FirstClass Economy Total

.
92

.
c07s3d1

...

Selecting Variables
proc print data=onboard;
format Date date9.;
run;
The SAS System
Obs
1
2
3
4
5
6
7
8
9
10

93

Flight
439
921
114
982
439
982
431
982
114
982

Date
11DEC2000
11DEC2000
12DEC2000
12DEC2000
13DEC2000
13DEC2000
14DEC2000
14DEC2000
15DEC2000
15DEC2000

Dest

Total

LAX
DFW
LAX
dfw
LAX
DFW
LaX
DFW
LAX
DFW

157
151
185
90
210
131
183
95
.
45
c07s3d1

Equivalent
94

DROP= and KEEP= data set options in a DATA statement


are similar to DROP and KEEP statements.
data onboard(drop=FirstClass Economy);
set [Link];
Total=FirstClass+Economy;
run;
data onboard(keep=Flight Date Dest Total);
data onboard;
drop FirstClass Economy;
set [Link];
Total=FirstClass+Economy;
run;

Equivalent Steps

Equivalent

Selecting Variables

keep Flight Date Dest Total;


c07s3d2

...

Exercises
This exercise reinforces the concepts discussed
previously.

95

Chapter 7: DATA Step Programming


7.1 Reading SAS Data Sets and Creating Variables

7.2 Conditional Processing

7.3 Dropping and Keeping Variables (Self-Study)

7.4 Reading Excel Spreadsheets Containing


Date Fields (Self-Study)
96

Objectives

97

Create a SAS data set from an Excel spreadsheet that


contains date fields.
Create a SAS data set from an Excel spreadsheet that
contains datetime fields.

Business Task
The flight data for Dallas and Los Angeles are in an
Excel spreadsheet. The departure date is stored as
a date field in the spreadsheet.
Excel Spreadsheet

SAS Data Set

98

Importing Date Fields


Use the IMPORT procedure to create a SAS data set
from the spreadsheet containing date fields.
proc import out=[Link]
datafile='[Link]'
dbms=excel2000 replace;
run;
proc print data=[Link];
run;

99

c07s4d1

Importing Date Fields


PROC IMPORT automatically converts the spreadsheet
date fields to SAS date values and assigns the DATE9.
format.
The SAS System

100

Obs

Flight

1
2
3
4
5
6
7
8
9
10

439
921
114
982
439
982
431
982
114
982

Date
11DEC2000
11DEC2000
12DEC2000
12DEC2000
13DEC2000
13DEC2000
14DEC2000
14DEC2000
15DEC2000
15DEC2000

Dest

First
Class

Economy

LAX
DFW
LAX
dfw
LAX
DFW
LaX
DFW
LAX
DFW

20
20
15
5
14
15
17
7
.
14

137
131
170
85
196
116
166
88
187
31

Importing Date-Time Fields


PROC IMPORT also converts spreadsheet fields that
contain datetime information into SAS date values
and assigns the DATE9. format.
Excel Spreadsheet
Excel Spreadsheet
SAS Data Set

101

Importing Date-Time Fields


To import datetime fields as SAS datetime values, add
the USEDATE=NO statement to the PROC IMPORT step.
proc import out=[Link]
datafile='[Link]'
dbms=excel2000 replace;
usedate=no;
run;
proc print data=[Link];
run;

102

c07s4d2

SAS Datetime Values


A SAS datetime value is interpreted as the number
of seconds between midnight, January 1, 1960, and
a specific date and time.

01JAN1960:00:00:00
31DEC1959:23:59:00

01JAN1960:00:01:00

informat
-3600

-60

60

3600

format

31DEC1959:23:00:00
103

01JAN1960:01:00:00

Importing Date-Time Fields


The DATETIME19. format is assigned to the
SAS datetime values.
The SAS System

104

Obs

Flight

1
2
3
4
5
6
7
8
9
10

439
921
114
982
439
982
431
982
114
982

DateTime
11DEC2000:09:30:00
11DEC2000:13:40:00
12DEC2000:17:00:00
12DEC2000:18:10:00
13DEC2000:09:30:00
13DEC2000:18:10:00
14DEC2000:13:00:00
14DEC2000:18:10:00
15DEC2000:17:00:00
15DEC2000:18:10:00

Dest

First
Class

Economy

LAX
DFW
LAX
dfw
LAX
DFW
LaX
DFW
LAX
DFW

20
20
15
5
14
15
17
7
.
14

137
131
170
85
196
116
166
88
187
31

The DATEPART Function


You can use the DATEPART function to extract the
date portion of a SAS datetime value.
DATEPART(SASdatetime) returns the SAS date value
from a SAS datetime value.

data convert;
Time='01DEC00:09:15'dt;
Date=datepart(Time);
run;
PDV

Time
1291281300
105

Date
14945

Exercises
This exercise reinforces the concepts discussed
previously.

106

You might also like