Combining SAS

1
Chapter 14

• Combining SAS Data Sets

2
Section 14.1

• Concatenating SAS
• Data Sets

3
Objectives

• Define concatenation.
• Use the SET statement in a DATA step to
  concatenate two or more SAS data sets.
• Use the RENAME data set option to change the
  names of variables.
• Use the SET and BY statements in a DATA step to
  interleave two or more SAS data sets.

4
Combining SAS Data Sets

• In this chapter, you will look at combining data
  sets in two ways.

Joining multiple data sets side by side (merging)
Stacking multiple data sets (concatenation)
Data Set 1
Data Set 2
Data Set 3
Data Set 3
Data Set 3 Rows might or might not have matches
in each data set.
5
Combining SAS Data Sets

• In this chapter, you will look at combining data
  sets in two ways, concatenation and merging.

Stacking multiple data sets (concatenation)
Data Set 3
6
Concatenating SAS Data Sets

• Use the SET statement in a DATA step to
  concatenate SAS data sets.
• General form of a DATA step concatenation
• Data set names are separated by a space in the
  SET statement.

DATA SAS-data-set SET SAS-data-set1
SAS-data-set2 . . . ltother SAS statementsgtRUN
7
Concatenating SAS Data Sets

• You can read any number of SAS data sets with a
  single SET statement.

SAS data sets
work.qtr1
jan
data work.qtr1 set work.jan work.feb
work.mar run
feb
mar
...
8
Business Task

• Two SAS data sets, na1 and na2, contain data for
  newly hired navigators.
• Concatenate the data sets into a new data set
  named newhires.
• The data sets contain the same variables. You
  will see what happens when there are different
  variables.

na1
na2
9
Concatenating SAS Data Sets Compilation
...
10
Concatenating SAS Data Sets Compilation
...
11
Concatenating SAS Data Sets Compilation
na1
na2
PDV
data newhires set na1 na2 run
newhires
...
12
Concatenating SAS Data Sets Execution
na1
na2
PDV
data newhires set na1 na2 run
newhires
Initialize PDV to missing
...
13
Concatenating SAS Data Sets Execution
na1
na2
PDV
data newhires set na1 na2 run
newhires
...
14
Concatenating SAS Data Sets Execution
na1
na2
PDV
data newhires set na1 na2 run
newhires
15
Concatenating SAS Data Sets Execution
na1
na2
PDV
data newhires set na1 na2 run
Automatic output
newhires
...
16
Concatenating SAS Data Sets Execution
na1
na2
PDV
data newhires set na1 na2 run
Automatic output
newhires
...
17
Concatenating SAS Data Sets Execution
na1
na2
PDV
data newhires set na1 na2 run
Automatic output
newhires
...
18
Concatenating SAS Data Sets Execution
na1
na2
PDV
data newhires set na1 na2 run
newhires
End of File
...
19
Concatenating SAS Data Sets Execution
na1
na2
PDV
data newhires set na1 na2 run
Initialize PDV to missing
newhires
When SAS moves to a new data set, the values in
the PDV are reinitialized to missing.
...
20
Concatenating SAS Data Sets Execution
na1
na2
PDV
data newhires set na1 na2 run
newhires
...
21
Concatenating SAS Data Sets Execution
na1
na2
PDV
data newhires set na1 na2 run
newhires
...
22
Concatenating SAS Data Sets Execution
na1
na2
PDV
data newhires set na1 na2 run
End of File
newhires
...
23
Concatenating SAS Data Sets Execution

• When SAS reaches the end of the file on the last
  data set, DATA step execution ends.
• What if the data sets contain different
  variables?

newhires
24
Business Task

• Two SAS data sets, fa1 and fa3, contain data for
  newly hired flight attendants.
• Concatenate the data sets into a new data set
  named newfa.
• The data sets contain similar data, but each has
  one unique variable (JobCode versus Country).

fa1
fa3
25
Concatenating SAS Data Sets Compilation
fa1
fa3
PDV
data newfa set fa1 fa3 run
...
26
Concatenating SAS Data Sets Compilation
fa1
fa3
PDV
data newfa set fa1 fa3 run
Add additional variable.
...
27
Concatenating SAS Data Sets Compilation
fa1
fa3
PDV
data newfa set fa1 fa3 run
newfa
...
28
Concatenating SAS Data Sets Execution
fa1
fa3
PDV
data newfa set fa1 fa3 run
newfa
...
29
Concatenating SAS Data Sets Execution

• When SAS moves from one data set to another, the
  values in the PDV are reinitialized to missing.

30
Concatenating SAS Data Sets Execution
If SAS did not re-initialize the PDV when it
moved from one data set to another, then JobCode
would contain previous data for the rows from fa2.
fa1
fa2
newfa
...
31
Business Task

• Two SAS data sets, fa1 and fa2, contain data for
  newly hired flight attendants.
• Concatenate the data sets into a new data set
  named newfa.

fa1
fa2
The data sets contain similar data, but the
column names are not the same.
32
Concatenating SAS Data Sets Execution
fa1
fa2
JobCode and JCode represent the same data and
should be in one column.
data newfa set fa1 fa2 run
newfa
...
33
The RENAME Data Set Option

• You can use a RENAME data set option to change
  the name of a variable.
• General form of the RENAME data set option

SAS-data-set(RENAME(old-name-1new-name-1

old-name-2new-name-2
.
.

.
old-name-nnew-name-n))
34
The RENAME Data Set Option
fa1
fa2
There are two sets of parentheses that are
required. The outer set is for the data set
options, such as RENAME and KEEP for fa2. They
appear inside the outer set of parentheses.
data newfa set fa1 fa2(rename(JCodeJobCode))
run
35
The RENAME Data Set Option
fa1
fa2
The inner parentheses surround all the variables
that you want to rename. Separate each variable
to rename with a space. For example, rename
Gender to Sex in fa2.
data newfa set fa1 fa2(rename(JCodeJobCode
GenderSex)) run
36
The RENAME Data Set Option
fa1
fa2
The equals sign is part of the RENAME option.
data newfa set fa1 fa2(rename(JCodeJobCode))
run
New Name
Old Name
37
The RENAME Data Set Option
fa1
fa2
data newfa set fa1 fa2(rename(JCodeJobCode))
run
PDV
...
38
The RENAME Data Set Option
fa1
fa2
data newfa set fa1 fa2(rename(JCodeJobCode))
run
PDV
SAS now knows JCode to be JobCode, which was
already created in the PDV.
? Reference JobCode, not JCode, in your DATA
step.
...
39
The RENAME Data Set Option
fa1
fa2
data newfa set fa1 fa2(rename(JCodeJobCode))
run
newfa
What if you want JCode instead of JobCode? How
would you modify the program?
...
40
The RENAME Data Set Option
fa1
fa2
data newfa set fa1(rename(JobCodeJCode))
fa2 run
newfa
The RENAME option is tangent to the data set
that you want to change.
...
41
Exercise

• This exercise reinforces the concepts discussed
  previously.

42
Exercises
Principal Skinner has seven data sets containing
student elective choices, one for each guidance
counselor. She wants one data set,
StudentElectives, that contains each row from all
seven data sets. The data sets to read from are
CraigStudents, LopezStudents, ShahStudents,
LiStudents, RobbinsStudents, PerryStudents, and
GreeneStudents. The data set needs to be in
sorted order by student ID number and should
contain StudentID, Name, and Choice1
columns. Produce a listing report with an
appropriate title. Produce a frequency report
analyzing Choice1 with an appropriate title.
(Suppress page numbers and the date and time, as
well as cumulative statistics.) Save your program
as skinner.sas to be used later.
43
Exercises
data StudentElectives set prog2.CraigStudents
prog2.LopezStudents prog2.ShahStudents
prog2.LiStudents prog2.RobbinsStudents
prog2.PerryStudents prog2.GreeneStudents
drop Choice2 Choice3 Keep StudentID Name
Choice1 run proc sort data StudentElectives
by StudentID run options nodate
nonumber title 'Student First Choice
Electives' proc print data StudentElectives run
title 'Frequency Counts for First Choice
Electives' proc freq data StudentElectives
tables Choice1 /nocum run
44
Exercises
Partial PROC PRINT Output
Student First Choice
Electives Student Obs ID Name
Choice1 1 1005 Chaz
Richardson SAT Verbal/Math Prep 2 1154
Barbara Muir Conversations in
Diversity 3 1155 Angel Reisman
Advanced Shop 4 1245 Leticia Ritter
African American Literature 5 1257
Richard Calle Astronomy 6 1258
Ronnie Trimpin Advanced Functions and
Modeling . . . 108 9940 Moises Curbelo
Astronomy 109 9942 Brooke Sears
Creative Writing 110 9961 Darnell
Lowrance Marine Ecology 111 9962 Jamie
Rowan Advanced Shop 112 9995
Kelvin Garcia Visual Arts 113 9997
Tiffany Pierre Visual Arts 114 9999
Randy Leifer Sculpture and Ceramics
45
Exercises
PROC FREQ Output
Frequency Counts for First Choice
Electives The FREQ
Procedure Choice1
Frequency Percent ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ
ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ
Advanced Functions and Modeling
5 4.39 Advanced Shop
7 6.14 African American
Literature 2 1.75
Anatomy and Physiology
2 1.75 Astronomy
9 7.89 Band
7 6.14
Conversations in Diversity
6 5.26 Creative Writing
3 2.63 Environmental
Science 7 6.14
Home Economics
2 1.75 Intro to Communications and
Mass Media 6 5.26 Java
Programming 3
2.63 Law and Justice
3 2.63 Marine Ecology
6 5.26
Newspaper
6 5.26 SAS Programming
4 3.51 SAT Verbal/Math
Prep 9 7.89
Sculpture and Ceramics
3 2.63 Textile Art
3 2.63 Theatre Workshop
7 6.14
Trends in Young Adult Literature
6 5.26 Visual Arts
3 2.63 Yearbook
5 4.39
46
Exercises
Open the program Lengths.sas, which creates two
data sets for newly hired navigators, NA1 and
NA2. The data sets are concatenated to create a
data set named newhires. The two data sets have
the same variables, but the variable Name has a
different length in each data set. Answer the
questions on the following slide as they relate
to the newhires data set.
47
Exercises

• Will the DATA step run successfully or will there
  be an error?
• If it runs successfully
• What do you think the length of Name will be in
  the new data set? Why?
• Submit the program to verify your answer.
• Modify the last DATA step to put NA2 before NA1
  in the SET statement.
• What do you think the length of Name will be in
  the new data set? Why?
• Submit the program to verify your answer.

48
Exercises Different Lengths
Did the DATA step run successfully or was there
an error? It ran successfully. What is the
length of Name in the new data set? Why? The
length of Name was 4, the length from the first
data set in the SET statement (NA1). After DATA
step modification The length of Name is 6, the
length from the first data set in the SET
statement (NA2). Why? Because at compile time,
SAS creates a spot in the PDV and assigns
variable attributes such as name, type, and
length in the order that it sees them. SAS sees
the data set NA1 or NA2 in the SET statement
first, so Name is assigned the length from that
data set.
49
Exercises
Open the program Formats.sas, which creates two
data sets for newly hired navigators, NA1 and
NA2. The data sets are concatenated to create a
data set named newhires. The two data sets have
the same variables, but the variable Name has a
different format in each data set. Answer the
questions on the following slide as they relate
to the newhires data set.
50
Exercises

• Will the DATA step run successfully or will there
  be an error?
• If it successfully runs
• What do you think the format of Salary will be in
  the new data set? Why?
• Submit the program to verify your answer.
• Modify the last DATA step to put NA2 before NA1
  in the SET statement.
• What do you think the format of Salary will be in
  the new data set? Why?
• Submit the program to verify your answer.

51
Exercises Different Formats
Did the DATA step run successfully or was there
an error? It ran successfully. What is the
format of Salary in the new data set? Why? The
format of Salary was DOLLAR10., the format from
the first data set in the SET statement
(NA1). After DATA step modification The format
of Salary is DOLLAR12.2, the format from the
first data set in the SET statement (NA2). Why?
Because at compile time, SAS creates a spot in
the PDV and assigns variable attributes such as
name, type, and length in the order that it sees
them. SAS sees the data set NA1 or NA2 in the SET
statement first, so Salary is assigned the format
from that data set.
52
Exercises
Open the program Labels.sas, which creates two
data sets for newly hired navigators, NA1 and
NA2. The data sets are concatenated to create a
data set named newhires. The two data sets have
the same variables, but the variable Name has a
different label in each data set. Answer the
questions on the following slide as they relate
to the newhires data set.
53
Exercises

• Will the DATA step run successfully or will there
  be an error?
• If it successfully runs
• What do you think the label of Salary will be in
  the new data set? Why?
• Submit the program to verify your answer.
• Modify the last DATA step to put NA2 before NA1
  in the SET statement.
• What do you think the label of Salary will be in
  the new data set? Why?
• Submit the program to verify your answer.

54
Exercises Different Labels
Did the DATA step run successfully or was there
an error? It ran successfully. What is the label
for Salary in the new data set? Why? The label of
Salary is Annual Salary, the label from the first
data set in the SET statement (NA1). After DATA
step modification The label of Salary is Yearly
Salary, the label from the first data set in the
SET statement (NA2). Why? Because at compile
time, SAS creates a spot in the PDV and assigns
variable attributes such as name, type, and
length in the order that it sees them. SAS sees
the data set NA1 or NA2 in the SET statement
first, so Salary is assigned the label from that
data set.
55
Exercises
Open the program types.sas, which creates two
data sets for newly hired navigators, NA1 and
NA2. The data sets are concatenated to create a
data set named newhires. The two data sets have
the same variables, but the variable Name has a
different data type in each data set. Answer
the questions on the following slide as they
relate to the newhires data set.
56
Exercises

• Will the DATA step run successfully or will there
  be an error?
• If it successfully runs
• What do you think the type of Salary will be in
  the new data set? Why?
• Submit the program to verify your answer.
• Modify the last DATA step to put NA2 before NA1
  in the SET statement.
• What do you think the type of Salary will be in
  the new data set? Why?
• Submit the program to verify your answer.

57
Exercises Different Types
Did the DATA step run successfully or was there
an error? It stopped due to an error. What is
the type for Salary in the new data set?
Why? Character is the first type of Salary that
SAS sees. However, there are no rows in the data
set because of an error at compile time. Why did
a difference in type cause an error? At compile
time, SAS creates a spot in the PDV and assigns
variable attributes such as name, type, and
length in the order that it sees them. SAS cannot
resolve the type for Salary because the data
types require different processing. Variables
must be the same data type.
58
Concatenating Data

• If both data sets are sorted before you combine
  them, they might not be sorted after the
  concatenation.
• You can sort the resulting data set, but it is
  more efficient to preserve the sorted order as
  you combine them.

59
Interleaving SAS Data Sets

• Use the SET statement with a BY statement in a
  DATA step to interleave SAS data sets.
• General form of a DATA step interleave

DATA SAS-data-set SET SAS-data-set1
SAS-data-set2 . . . BY BY-variable
ltother SAS statementsgtRUN
60
Interleaving SAS Data Sets

• Interleaving SAS data sets concatenates SAS data
  sets so that the observations in the resulting
  data set are in order.

ia.miamiemp
The original data sets must be in order.
work.allemp
ia.parisemp
data work.allemp set ia.miamiemp
ia.parisemp ia.romeemp by ID run
ia.romeemp
...
61
Interleaving SAS Data Sets

• Interleave the fa1 and fa2 data sets by Name.

fa1
fa2
The data sets must be sorted first.
proc sort datafa1 by name run
proc sort datafa2 by name run
...
62
Interleaving SAS Data Sets
fa1
fa2
data newfa set fa1 fa2(rename(JCodeJobCode))
by Name run
Which value comes first?
newfa
GRANT
PDV
...
63
Interleaving SAS Data Sets
fa1
fa2
data newfa set fa1 fa2(rename(JCodeJobCode))
by Name run
Which value comes first?
newfa
JONES
PDV
When SAS moves to a different data set, it
initializes the PDV to missing before loading the
JONES observation.
...
64
Interleaving SAS Data Sets
fa1
fa2
data newfa set fa1 fa2(rename(JCodeJobCode))
by Name run
newfa
...
65
Exercise

• This exercise reinforces the concepts discussed
  previously.

66
Exercises
Principal Skinner is a very efficient person. She
wants you to modify your program so that the rows
are concatenated using the interleaving
technique. Open the program named skinner.sas
that you saved earlier. Modify the program so
that the StudentElectives data set is created by
preserving sorted order by StudentID.
67
Exercises
proc sort dataprog2.CraigStudents
outCraigStudents by StudentID run proc
sort dataprog2.LopezStudents outLopezStudents
by StudentID run proc sort dataprog2.ShahStud
ents outShahStudents by StudentID run proc
sort dataprog2.LiStudents outLiStudents by
StudentID run proc sort dataprog2.RobbinsStuden
ts outRobbinsStudents by StudentID run proc
sort dataprog2.PerryStudents outPerryStudents
by StudentID run proc sort
dataprog2.GreeneStudents outGreeneStudents
by StudentID run
The data sets must be sorted.
continued...
68
Exercises
? Changes are in red and the PROC SORT after
the DATA step was removed.
data StudentElectives set CraigStudents
LopezStudents ShahStudents LiStudents
RobbinsStudents PerryStudents
GreeneStudents by StudentID drop
Choice2 Choice3 Keep StudentID Name
Choice1 run options nodate nonumber title
'Student First Choice Electives' proc print
data StudentElectives run title 'Frequency
Counts for First Choice Electives' proc freq
data StudentElectives tables Choice1
/nocum run
69
Exercises
Partial PROC PRINT Output
Student First Choice
Electives Student Obs ID Name
Choice1 1 1005 Chaz
Richardson SAT Verbal/Math Prep 2 1154
Barbara Muir Conversations in
Diversity 3 1155 Angel Reisman
Advanced Shop 4 1245 Leticia Ritter
African American Literature 5 1257
Richard Calle Astronomy 6 1258
Ronnie Trimpin Advanced Functions and
Modeling . . . 108 9940 Moises Curbelo
Astronomy 109 9942 Brooke Sears
Creative Writing 110 9961 Darnell
Lowrance Marine Ecology 111 9962 Jamie
Rowan Advanced Shop 112 9995
Kelvin Garcia Visual Arts 113 9997
Tiffany Pierre Visual Arts 114 9999
Randy Leifer Sculpture and Ceramics
70
Exercise Section 14.1

• This exercise reinforces the concepts discussed
  previously.

71
Section 14.2

• Merging SAS Data Sets

72
Objectives

• Prepare data for merging using the SORT procedure
  and data set options.
• Merge SAS data sets on a single common variable.

73
Combining SAS Data Sets

• Look at combining data sets horizontally, or
  merging data.

Joining multiple data sets side by side (merging)
Start with data sets where all rows have matches
and when there are no matches.
Data Set 1
Data Set 2
Data Set 3
Data Set 3 Rows might or might not have matches
in each data set.
74
Merging Data Sets

• A merge combines two or more existing data sets
  by joining observations side-by-side.

75
Match-Merge

• The most common type of merge is a match-merge,
  which uses a common variable to join
  observations.

76
Merging SAS Data Sets

• Use the MERGE statement in a DATA step to join
  corresponding observations from two or more SAS
  data sets.
• General form of a DATA step match-merge

DATA SAS-data-set MERGE SAS-data-sets BY
BY-variable(s) ltother SAS statementsgtRUN
In order to use a BY statement in the DATA step,
the data must be sorted by that variable.
77
Merging SAS Data Sets

• You can merge data sets without a BY statement.
  Using the MERGE statement without a BY statement
  results in a blind merge.
• SAS matches the first row from data set 1 to the
  first row in data set 2, the second row from data
  set 1 to the second row in data set 2, and so on.

DATA SAS-data-set MERGE SAS-data-sets ltother
SAS statementsgtRUN
78
Merging SAS Data Sets

• You can read any number of SAS data sets with a
  single MERGE statement.

SAS data sets
sales
goals
costs
taxes
data compare merge costs sales goals taxes
by Month run
compare
goals
taxes
costs
sales
79
Business Task

• International Airlines is comparing monthly sales
  performance to monthly sales goals.
• The sales and goals data are stored in separate
  SAS data sets.

80
Business Task
To calculate the difference between revenues and
goals, the performance and goals data sets must
be merged.
ia.performance
ia.goals
Match-merge the data sets by Month and compute
the difference between the variable values for
Sales and Goal.
ia.compare
81
Merging SAS Data Compilation
ia.goals
ia.performance
PDV
...
82
Merging SAS Data Compilation
ia.goals
ia.performance
PDV
...
83
Merging SAS Data Compilation
ia.goals
ia.performance
data ia.compare merge ia.performance
ia.goals by Month DifferenceSales-Goalr
un
Month already exists in the PDV, so SAS does not
re-create it.
PDV
...
84
Merging SAS Data Compilation
ia.goals
ia.performance
data ia.compare merge ia.performance
ia.goals by Month DifferenceSales-Goalr
un
PDV
...
85
Merging SAS Data Compilation
ia.goals
ia.performance
data ia.compare merge ia.performance
ia.goals by Month DifferenceSales-Goalr
un
PDV
...
86
Merging SAS Data Compilation
ia.goals
ia.performance
data ia.compare merge ia.performance
ia.goals by Month DifferenceSales-Goalr
un
PDV
Descriptor portion created
ia. compare
87
Merging SAS Data Execution
ia. goals
ia. performance
data ia.compare merge ia.performance
ia.goals by Month DifferenceSales-Goalr
un
PDV
Initialize PDV to missing
88
Merging SAS Data Execution
ia. goals
ia. performance
Do the BY variables match?
YES
PDV
...
89
Merging SAS Data Execution
ia. performance
ia. goals
Read 1 obs from each matching data set.
Month1
PDV
...
90
Merging SAS Data Execution
ia. performance
ia. goals
PDV
...
91
Merging SAS Data Execution
ia. goals
ia. performance
PDV
...
92
Merging SAS Data Sets Execution

• SAS reinitializes only the new variables in the
  PDV, not the variables that were read from the
  data sets listed in the MERGE statement.

93
Merging SAS Data Execution
ia. goals
ia. performance
data ia.compare merge ia.performance
ia.goals by Month DifferenceSales-Goalr
un
PDV
Reinitialize new variables to missing
94
Merging SAS Data Execution
ia. performance
ia. goals
Do the BY variables match?
YES
PDV
...
95
Merging SAS Data Execution
ia. performance
ia. goals
Did the BY variable change?
YES
PDV
Reinitialize rest of PDV to missing.
...
96
Merging SAS Data Sets Execution

• SAS reinitializes only the new variables in the
  PDV, not the variables that were read from the
  data sets listed in the MERGE statement.
• If the BY variable does not match what is in the
  PDV, the entire PDV is initialized.

97
Merging SAS Data Execution
ia. goals
ia. performance
Read 1 obs from each matching data set.
Month2
PDV
...
98
Merging SAS Data Execution
ia. goals
ia. performance
PDV
99
Merging SAS Data Execution
ia. goals
ia. performance
ia. compare
...
100
Merging SAS Data Execution
ia. goals
ia. performance
data ia.compare merge ia.performance
ia.goals by Month DifferenceSales-Goalr
un
PDV
Reinitialize new variables to missing.
101
Merging SAS Data Execution
ia. goals
ia. performance
Does the BY variable match?
YES
PDV
...
102
Merging SAS Data Execution
ia. goals
ia. performance
Did the BY variable change?
YES
PDV
Reinitialize rest of PDV to missing.
...
103
Merging SAS Data Execution
ia. goals
ia. performance
Read 1 obs from each matching data set.
Month3
PDV
...
104
Merging SAS Data Execution
ia. goals
ia. performance
PDV
...
105
Merging SAS Data Execution
ia. goals
ia. performance
PDV
ia. compare
...
106
Merging SAS Data Execution
ia. goals
ia. performance
End of File
End of File
PDV
ia. compare
107
Exercise

• This exercise reinforces the concepts discussed
  previously.

108
Exercises
Mr. Shah stores his data in four different data
sets ShahMaster - contains student IDs and
names Shah1 - contains student IDs and 1st
Elective Choice Shah2 - contains student IDs and
2nd Elective Choice Shah3 - contains student IDs
and 3rd Elective Choice He decided that this is
not the best way to store the data. Create a data
set called myStudents that matches all of Mr.
Shahs students and electives. Create a listing
report of all of Mr. Shahs students with no page
numbers and no date/time.
109
Exercises
/ Data sets are all sorted by Student ID. You
can add a PROC SORT step for each data
set./ data mystudents merge prog2.ShahMaster
prog2.Shah1 prog2.Shah2
prog2.Shah3 by StudentID run options
nodate nonumber ls90 proc print
datamystudents run
110
Exercises - Partial Output
Student Obs ID Name
Choice1 1 2001 Troy Pruska
Advanced Shop 2 2335 Taylor
Lowet Band 3 2584 Patrice
Ray Java Programming 4 3259
Marie Hibara Theatre Workshop 5
3489 Evelyn Hehmann Intro to
Communications and Mass Media 6 4433
Cole Billy Yearbook Obs
Choice2
Choice3 1 Anatomy and Physiology
Law and Justice 2 SAS Programming
Band 3 African
American Literature Law and
Justice 4 SAT Verbal/Math Prep
Theatre Workshop 5 Intro to
Communications and Mass Media Java
Programming 6 Creative Writing
SAS Programming 7 Astronomy
Band
111
Match-Merging

• When you match-merge two or more data sets, it is
  common to have the following
• repeated BY values
• non-matches

112
DATA Step Merge
B
data new merge a b by ID run
NEW
ID 1 2 3 4
Name SAM SUE BOB
Age 31 42 21
113
Business Task

• What if the data sets have rows that do not
  match?
• Merge two data sets to acquire the names of the
  German crew who are scheduled to fly next week.

ia.gercrew
ia.gersched
114
Business Task
ia.gercrew
ia.gersched
To match-merge the data sets by EmpID, the data
sets must be ordered by EmpID.
proc sort dataia.gersched
outwork.gersched by EmpID run
115
Merging SAS Data Execution
work. gersched
ia. gercrew
data work.nextweek merge ia.gercrew
work.gersched by EmpID run
PDV
Initialize PDV to missing
...
116
Merging SAS Data Execution
ia. gercrew
work. gersched
Read 1 obs from each matching data set for first
BY group.
First BY GroupEmpIDE00632
data work.nextweek merge ia.gercrew
work.gersched by EmpID run
PDV
...
117
Merging SAS Data Execution
work. gersched
ia. gercrew
data work.nextweek merge ia.gercrew
work.gersched by EmpID run
PDV
...
118
Merging SAS Data Execution
ia. gercrew
work. gersched
Does the BY variable match?
NO
Which one comes FIRST?
...
119
Merging SAS Data Execution
work. gersched
ia. gercrew
Did the BY variable change?
YES
PDV
Reinitialize PDV to missing.
...
120
Merging SAS Data Execution
work. gersched
ia. gercrew
Read 1 obs from each matching data set for next
BY group.
Next BY GroupEmpIDE01483
data work.nextweek merge ia.gercrew
work.gersched by EmpID run
PDV
...
121
Merging SAS Data Execution
work. gersched
ia. gercrew
data work.nextweek merge ia.gercrew
work.gersched by EmpID run
PDV
work. nextweek
...
122
Merging SAS Data Sets Execution

• SAS reinitializes only the new variables in the
  PDV, not the variables that were read from the
  data sets listed in the MERGE statement.
• If the BY variable does not match what is in the
  PDV, the entire PDV is initialized.

123
Merging SAS Data Execution
work. gersched
ia. gercrew
PDV
work. nextweek
If SAS did not reinitialize the PDV when the BY
variables did not match, then observation 2 would
have the value from the previous FlightNum (5250).
124
Merging SAS Data Execution
ia. gercrew
work. gersched
Does the BY variable match?
YES
PDV
...
125
Merging SAS Data Execution
work. gersched
ia. gercrew
Does the BY variable match what is in the PDV?
NO
PDV
Reinitialize PDV to missing.
...
126
Merging SAS Data Execution
work. gersched
ia. gercrew
Read 1 obs from each matching data set for next
BY group.
Next BY GroupEmpIDE01996
data work.nextweek merge ia.gercrew
work.gersched by EmpID run
PDV
...
127
Merging SAS Data Execution
work. gersched
ia. gercrew
data work.nextweek merge ia.gercrew
work.gersched by EmpID run
PDV
work. nextweek
...
128
Merging SAS Data Execution
work. gersched
ia. gercrew
Do the BY variable match?
Yes
PDV
...
129
Merging SAS Data Execution
work. gersched
ia. gercrew
Did the BY variable change?
Yes
PDV
Reinitialize PDV to missing.
...
130
Merging SAS Data Execution
ia. gercrew
work. gersched
Read 1 obs from each matching data set for next
BY group.
Next BY GroupEmpIDE04064
data work.nextweek merge ia.gercrew
work.gersched by EmpID run
PDV
...
131
Merging SAS Data Execution
work. gersched
ia. gercrew
data work.nextweek merge ia.gercrew
work.gersched by EmpID run
PDV
work. nextweek
...
132
Merging SAS Data Execution
work. gersched
ia. gercrew
End of File
End of File
PDV
work. nextweek
133
Eliminating Nonmatches

• You want only the people who will to fly next
  week.
• One person is not scheduled for next week.
• Use IF-THEN logic to delete the row.

ia.gercrew
work.gersched
data work.nextweek merge ia.gercrew
work.gersched by EmpID if FlightNum ' '
then delete run
134
Eliminating Nonmatches
What if the row is in work.gersched (meaning they
are scheduled to fly) but the flight number is
not assigned?
ia.gercrew
work.gersched
The IF statement deletes a row that you might not
want to remove.
data work.nextweek merge ia.gercrew
work.gersched by EmpID if FlightNum ' '
then delete run
135
Identifying Data Set Contributors

• When you read multiple SAS data sets in one DATA
  step, you can use the IN data set option to
  detect which data set contributed to an
  observation.
• General form of the IN data set option
• where variable is any valid SAS variable name.
• The variable name can be any unique, valid
  variable name. The programmer must supply the
  variable name. SAS supplies the value.

SAS-data-set(INvariable)
136
The IN Data Set Option

• variable is a temporary numeric variable with one
  of the following values
• 0 to indicate false. The data set did not
  contribute to the current observation.
• 1 to indicate true. The data set
  did contribute to the current observation.
• Variables created with IN are automatically
  dropped from the output data set.

137
The IN Data Set Option
work. gersched
ia. gercrew
data work.combine merge ia.gercrew(inInCrew)
work.gersched(inInSched) by
EmpID run
PDV
...
138
The IN Data Set Option
work. gersched
ia. gercrew
data work.combine merge ia.gercrew(inInCrew)
work.gersched(inInSched) by
EmpID run
PDV
...
139
The IN Data Set Option
work. gersched
ia. gercrew
data work.combine merge ia.gercrew(inInCrew)
work.gersched(inInSched) by
EmpID run
PDV
...
140
The IN Data Set Option
work. gersched
ia. gercrew
data work.combine merge ia.gercrew(inInCrew)
work.gersched(inInSched) by
EmpID run
PDV
...
141
Eliminating Nonmatches
work. gersched
ia. gercrew
data work.nextweek merge ia.gercrew
work.gersched(inInSched) by EmpID if
InSched1 run
True
PDV
work. nextweek
...
142
Eliminating Nonmatches
work. gersched
ia. gercrew
data work.nextweek merge ia.gercrew
work.gersched(ininSched) by EmpID if
InSched1 run
False
PDV
work. nextweek
...
143
Eliminating Nonmatches
work. gersched
ia. gercrew
data work.nextweek merge ia.gercrew
work.gersched(inInSched) by EmpID if
InSched1 run
True
PDV
work. nextweek
...
144
Eliminating Nonmatches
work. gersched
ia. gercrew
data work.nextweek merge ia.gercrew
work.gersched(inInSched) by EmpID if
InSched1 run
True
PDV
work. nextweek
145
Exercise

• This exercise reinforces the concepts discussed
  previously.

146
Exercises
Create a data set called students_left that
contains the students who are not advised by Mrs.
Perry or Mr. Greene. The other_GCs data set
contains student IDs of students advised by Mrs.
Perry and Mr. Greene. The data set prog2.schools
contains all of the students. The students_left
data set should only contain student IDs and
names. It should contain 84 observations. Create
a listing report with an appropriate title.
147
Exercises
proc sort dataprog2.other_GCs outother_GCs
by StudentID run data students_left merge
prog2.schools(keepstudentID name)
other_GCs(inA) by StudentID if not A
if A0 / Another way to code it.
/ run title 'Students not assigned to Perry and
Greene' proc print datastudents_left run
148
Exercises- Partial Output
Students not assigned to Perry and Greene
Student Obs ID
Name 1 1005 Chaz
Richardson 2 1154 Barbara
Muir 3 1155 Angel Reisman
4 1245 Leticia Ritter
5 1257 Richard Calle
6 1258 Ronnie Trimpin . . .
81 9940 Moises Curbelo 82
9962 Jamie Rowan 83 9997
Tiffany Pierre 84 9999
Randy Leifer
149
Exercise Section 14.2

• This exercise reinforces the concepts discussed
  previously.

150
Section 14.3

• More Match-Merging Two or More SAS Data Sets

151
Objectives

• Perform a match-merge.
• Perform explicit output for matching and
  non-matching observations.

152
Match-Merging

• The data set transact contains an account number
  and information on transactions for a week. The
  data set branches contain an account number and
  the branch location for that account.

Act Num Trans Amnt 56891 D
126.32 56891 C 560 57900 C
235 58876 D 14.56 59987 C 371.69
Act Num Branch 56891 N. Lincoln 56900
S. Cicero 58876 W. Argyle 59900 N.
Damen 59987 E. Wacker
153
Desired Output

• The bank manager wants to see reports based on
  three data sets.
• Goal Create a data set named newtrans that shows
  this week's transactions.

Act Num Trans Amnt Branch 56891
D 126.32 N. Lincoln 56891 C
560 N. Lincoln 58876 D
14.56 W. Argyle 59987 C 371.69
E. Wacker
154
Desired Output

• Goal Create a data set named noactiv that shows
  accounts with no transactions this week.

Act Num Branch 56900 S. Cicero 59900
N. Damen
155
Desired Output

• Goal Create a data set named noacct that shows
  transactions with no matching account number.

Act Num Trans Amnt 57900 C
235
156
Using the IN Data Set Option
data newtrans noactiv (dropTrans Amnt)
noacct (dropBranch) merge
prog2.transact(inInTrans)
prog2.branches(inInBanks) by ActNum
additional SAS statements run
157
If the Observation Is a Match

• Both transact and branches contributed to the
  observation.
• InTrans1 and InBanks1

prog2.transact
prog2.branches
Current Observation
...
158
If the Observation Is Not a Match

• branches contributed to the observation. transact
  did not. (The account had no transactions this
  week.)
• InTrans0 and InBanks1

prog2.transact
prog2.branches
Current Observation
...
159
If the Observation Is Not a Match

• transact contributed to the observation. branches
  did not. (A transaction occurred, but the account
  number was invalid.)
• InTrans1 and InBanks0

prog2.transact
prog2.branches
Current Observation
...
160
Using IN to Identify Matches and Non-Matches
data newtrans noactiv (dropTrans Amnt)
noacct (dropBranch) merge
prog2.transact(inInTrans)
prog2.branches(inInBanks) by ActNum if
InTrans1 and InBanks1 then output
newtrans else if InBanks1 and InTrans0
then output noactiv else if InBanks0 and
InTrans1 then output noacct run
161
Using IN to Identify Matches andNon-Matches

• Alternate Solution

data newtrans noactiv (dropTrans Amnt)
noacct (dropBranch) merge
prog2.transact(inInTrans)
prog2.branches(inInBanks) by ActNum if
InTrans and InBanks then output newtrans
else if InBanks and not InTrans then
output noactiv else if intrans and not
InBanks then output noacct run
162
Act Num Trans Amnt 56891 D
126.32 56891 C 560 57900 C
235 58876 D 14.56 59987 C 371.69
Act Num Branch 56891 N. Lincoln 56900
S. Cicero 58876 W. Argyle 59900 N.
Damen 59987 E. Wacker
data newtrans noactiv (dropTrans Amnt) noacct
(dropBranch) merge prog2.transact(inInTrans)
prog2.branches(inInBanks) by ActNum if
InTrans and InBanks then output newtrans else
if InBanks and not InTrans then output noactiv
else if InTrans and not InBanks then output
noacct run
...
163
Act Num Trans Amnt 56891 D
126.32 56891 C 560 57900 C
235 58876 D 14.56 59987 C 371.69
Act Num Branch 56891 N. Lincoln 56900
S. Cicero 58876 W. Argyle 59900 N.
Damen 59987 E. Wacker
data newtrans noactiv (dropTrans Amnt) noacct
(dropBranch) merge prog2.transact(inInTrans)
prog2.branches(inInBanks) by ActNum if
InTrans and InBanks then output newtrans else
if InBanks and not InTrans then output noactiv
else if InTrans and not InBanks then output
noacct run
AMNT
INTRANS
INBANKS
.
0
0
...
164
Act Num Trans Amnt 56891 D
126.32 56891 C 560 57900 C
235 58876 D 14.56 59987 C 371.69
Act Num Branch 56891 N. Lincoln 56900
S. Cicero 58876 W. Argyle 59900 N.
Damen 59987 E. Wacker
Match
data newtrans noactiv (dropTrans Amnt) noacct
(dropBranch) merge prog2.transact(inInTrans)
prog2.branches(inInBanks) by ActNum if
InTrans and InBanks then output newtrans else
if InBanks and not InTrans then output noactiv
else if InTrans and not InBanks then output
noacct run
INBANKS
INTRANS
AMNT
.
0
0
1
D
56891
126.32
...
165
Act Num Trans Amnt 56891 D
126.32 56891 C 560 57900 C
235 58876 D 14.56 59987 C 371.69
Act Num Branch 56891 N. Lincoln 56900
S. Cicero 58876 W. Argyle 59900 N.
Damen 59987 E. Wacker
data newtrans noactiv (dropTrans Amnt) noacct
(dropBranch) merge prog2.transact(inInTrans)
prog2.branches(inInBanks) by ActNum if
InTrans and InBanks then output newtrans else
if InBanks and not InTrans then output noactiv
else if InTrans and not InBanks then output
noacct run
INBANKS
INTRANS
AMNT
.
0
0
1
D
56891
126.32
...
166
Act Num Trans Amnt 56891 D
126.32 56891 C 560 57900 C
235 58876 D 14.56 59987 C 371.69
Act Num Branch 56891 N. Lincoln 56900
S. Cicero 58876 W. Argyle 59900 N.
Damen 59987 E. Wacker
data newtrans noactiv (dropTrans Amnt) noacct
(dropBranch) merge prog2.transact(inInTrans)
prog2.branches(inInBanks) by ActNum if
InTrans and InBanks then output newtrans else
if InBanks and not InTrans then output noactiv
else if InTrans and not InBanks then output
noacct run
INBANKS
INTRANS
AMNT
.
0
0
1
D
N. Lincoln
56891
126.32
1
...
167
Act Num Trans Amnt 56891 D
126.32 56891 C 560 57900 C
235 58876 D 14.56 59987 C 371.69
Act Num Branch 56891 N. Lincoln 56900
S. Cicero 58876 W. Argyle 59900 N.
Damen 59987 E. Wacker
data newtrans noactiv (dropTrans Amnt) noacct
(dropBranch) merge prog2.transact(inInTrans)
prog2.branches(inInBanks) by ActNum if
InTrans and InBanks then output newtrans else
if InBanks and not InTrans then output noactiv
else if InTrans and not InBanks then output
noacct run
INBANKS
INTRANS
AMNT
BRANCH
.
N. Lincoln
1
0
1
D
56891
126.32
...
168
Act Num Trans Amnt 56891 D
126.32 56891 C 560 57900 C
235 58876 D 14.56 59987 C 371.69
Act Num Branch 56891 N. Lincoln 56900
S. Cicero 58876 W. Argyle 59900 N.
Damen 59987 E. Wacker
data newtrans noactiv (dropTrans Amnt) noacct
(dropBranch) merge prog2.transact(inInTrans)
prog2.branches(inInBanks) by ActNum if
InTrans and InBanks then output newtrans else
if InBanks and not InTrans then output noactiv
else if InTrans and not InBanks then output
noacct run
True
Explicit Output
INBANKS
INTRANS
AMNT
.
0
0
1
D
N. Lincoln
56891
126.32
1
...
169
Act Num Trans Amnt 56891 D
126.32 56891 C 560 57900 C
235 58876 D 14.56 59987 C 371.69
Act Num Branch 56891 N. Lincoln 56900
S. Cicero 58876 W. Argyle 59900 N.
Damen 59987 E. Wacker
data newtrans noactiv (dropTrans Amnt) noacct
(dropBranch) merge prog2.transact(inInTrans)
prog2.branches(inInBanks) by ActNum if
InTrans and InBanks then output newtrans else
if InBanks and not InTrans then output noactiv
else if InTrans and not InBanks then output
noacct run
Automatic Return
INBANKS
INTRANS
AMNT
.
0
0
1
D
N. Lincoln
56891
126.32
1
...
170
Act Num Trans Amnt 56891 D
126.32 56891 C 560 57900 C
235 58876 D 14.56 59987 C 371.69
Act Num Branch 56891 N. Lincoln 56900
S. Cicero 58876 W. Argyle 59900 N.
Damen 59987 E. Wacker
No Match
data newtrans noactiv (dropTrans Amnt) noacct
(dropBranch) merge prog2.transact(inInTrans)
prog2.branches(inInBanks) by ActNum if
InTrans and InBanks then output newtrans else
if InBanks and not InTrans then output noactiv
else if InTrans and not InBanks then output
noacct run
INBANKS
INTRANS
AMNT
.
0
0
1
D
N. Lincoln
56891
126.32
1
...
171
Act Num Trans Amnt 56891 D
126.32 56891 C 560 57900 C
235 58876 D 14.56 59987 C 371.69
Act Num Branch 56891 N. Lincoln 56900
S. Cicero 58876 W. Argyle 59900 N.
Damen 59987 E. Wacker
Which one comes first?
data newtrans noactiv (dropTrans Amnt) noacct
(dropBranch) merge prog2.transact(inInTrans)
prog2.branches(inInBanks) by ActNum if
InTrans and InBanks then output newtrans else
if InBanks and not InTrans then output noactiv
else if InTrans and not InBanks then output
noacct run
INBANKS
INTRANS
AMNT
.
0
0
1
D
N. Lincoln
56891
126.32
1
...
172
Act Num Trans Amnt 56891 D
126.32 56891 C 560 57900 C
235 58876 D 14.56 59987 C 371.69
Act Num Branch 56891 N. Lincoln 56900
S. Cicero 58876 W. Argyle 59900 N.
Damen 59987 E. Wacker
Did the BY variable change?
data newtrans noactiv (dropTrans Amnt) noacct
(dropBranch) merge prog2.transact(inInTrans)
prog2.branches(inInBanks) by ActNum if
InTrans and InBanks then output newtrans else
if InBanks and not InTrans then output noactiv
else if InTrans and not InBanks then output
noacct run
INBANKS
INTRANS
AMNT
.
0
0
1
D
N. Lincoln
56891
126.32
1
...
173
Act Num Trans Amnt 56891 D
126.32 56891 C 560 57900 C
235 58876 D 14.56 59987 C 371.69
Act Num Branch 56891 N. Lincoln 56900
S. Cicero 58876 W. Argyle 59900 N.
Damen 59987 E. Wacker
NO.
data newtrans noactiv (dropTrans Amnt) noacct
(dropBranch) merge prog2.transact(inInTrans)
prog2.branches(inInBanks) by ActNum if
InTrans and InBanks then output newtrans else
if InBanks and not InTrans then output noactiv
else if InTrans and not InBanks then output
noacct run
Do not reinitialize PDV.
INBANKS
INTRANS
AMNT
.
0
0
1
D
N. Lincoln
56891
126.32
1
...
174
Merging SAS Data Sets Execution

• SAS reinitializes only the new variables in the
  PDV, not the variables that were read from the
  data sets listed in the MERGE statement.
• If the BY variable does not match what is in the
  PDV, the entire PDV is initialized.
• If the BY variables do match, SAS will not
  reinitialize the PDV, enabling the current row to
  match to more than one row.

175
Act Num Trans Amnt 56891 D
126.32 56891 C 560 57900 C
235 58876 D 14.56 59987 C 371.69
Act Num Branch 56891 N. Lincoln 56900
S. Cicero 58876 W. Argyle 59900 N.
Damen 59987 E. Wacker
data newtrans noactiv (dropTrans Amnt) noacct
(dropBranch) merge prog2.transact(inInTrans)
prog2.branches(inInBanks) by ActNum if
InTrans and InBanks then output newtrans else
if InBanks and not InTrans then output noactiv
else if InTrans and not InBanks then output
noacct run
INBANKS
INTRANS
AMNT
.
0
0
1
D
N. Lincoln
560
56891
126.32
1
C
...
176
Act Num Trans Amnt 56891 D
126.32 56891 C 560 57900 C
235 58876 D 14.56 59987 C 371.69
Act Num Branch 56891 N. Lincoln 56900
S. Cicero 58876 W. Argyle 59900 N.
Damen 59987 E. Wacker
data newtrans noactiv (dropTrans Amnt) noacct
(dropBranch) merge prog2.transact(inInTrans)
prog2.branches(inInBanks) by ActNum if
InTrans and InBanks then output newtrans else
if InBanks and not InTrans then output noactiv
else if InTrans and not InBanks then output
noacct run
INBANKS
INTRANS
AMNT
.
0
0
1
D
N. Lincoln
560
56891
126.32
1
C
...
177
Act Num Trans Amnt 56891 D
126.32 56891 C 560 57900 C
235 58876 D 14.56 59987 C 371.69
Act Num Branch 56891 N. Lincoln 56900
S. Cicero 58876 W. Argyle 59900 N.
Damen 59987 E. Wacker
data newtrans noactiv (dropTrans Amnt) noacct
(dropBranch) merge prog2.transact(inInTrans)
prog2.branches(inInBanks) by ActNum if
InTrans and InBanks then output newtrans else
if InBanks and not InTrans then output noactiv
else if InTrans and not InBanks then output
noacct run
True
Explicit Output
INBANKS
INTRANS
AMNT
.
0
0
1
D
N. Lincoln
560
56891
126.32
1
C
...
178
Act Num Trans Amnt 56891 D
126.32 56891 C 560 57900 C
235 58876 D 14.56 59987 C 371.69
Act Num Branch 56891 N. Lincoln 56900
S. Cicero 58876 W. Argyle 59900 N.
Damen 59987 E. Wacker
data newtrans noactiv (dropTrans Amnt) noacct
(dropBranch) merge prog2.transact(inInTrans)
prog2.branches(inInBanks) by ActNum if
InTrans and InBanks then output newtrans else
if InBanks and not InTrans then output noactiv
else if InTrans and not InBanks then output
noacct run
Automatic Return
INBANKS
INTRANS
AMNT
.
0
0
1
D
N. Lincoln
560
56891
126.32
1
C
...
179
Act Num Trans Amnt 56891 D
126.32 56891 C 560 57900 C
235 58876 D 14.56 59987 C 371.69
Act Num Branch 56891 N. Lincoln 56900
S. Cicero 58876 W. Argyle 59900 N.
Damen 59987 E. Wacker
No Match
data newtrans noactiv (dropTrans Amnt) noacct
(dropBranch) merge prog2.transact(inInTrans)
prog2.branches(inInBanks) by ActNum if
InTrans and InBanks then output newtrans else
if InBanks and not InTrans then output noactiv
else if InTrans and not InBanks then output
noacct run
Which one comes first?
INBANKS
INTRANS
AMNT
.
0
0
1
C
N. Lincoln
56891
560
1
...
180
Act Num Trans Amnt 56891 D
126.32 56891 C 560 57900 C
235 58876 D 14.56 59987 C 371.69
Act Num Branch 56891 N. Lincoln 56900
S. Cicero 58876 W. Argyle 59900 N.
Damen 59987 E. Wacker
No Match
Did the BY variable change?
data newtrans noactiv (dropTrans Amnt) noacct
(dropBranch) merge prog2.transact(inInTrans)
prog2.branches(inInBanks) by ActNum if
InTrans and InBanks then output newtrans else
if InBanks and not InTrans then output noactiv
else if InTrans and not InBanks then output
noacct run
INBANKS
INTRANS
AMNT
.
0
0
1
C
N. Lincoln
56891
560
1
...
181
Act Num Trans Amnt 56891 D
126.32 56891 C 560 57900 C
235 58876 D 14.56 59987 C 371.69
Act Num Branch 56891 N. Lincoln 56900
S. Cicero 58876 W. Argyle 59900 N.
Damen 59987 E. Wacker
Yes
data newtrans noactiv (dropTrans Amnt) noacct
(dropBranch) merge prog2.transact(inInTrans)
prog2.branches(inInBanks) by ActNum if
InTrans and InBanks then output newtrans else
if InBanks and not InTrans then output noactiv
else if InTrans and not InBanks then output
noacct run
Reinitialize PDV.
INBANKS
INTRANS
AMNT
ACTNUM
TRANS
BRANCH
560
56891
C
N. Lincoln
1
1

.


0
0
...
182
Act Num Trans Amnt 56891 D
126.32 56891 C 560 57900 C
235 58876 D 14.56 59987 C 371.69
Act Num Branch 56891 N. Lincoln 56900
S. Cicero 58876 W. Argyle 59900 N.
Damen 59987 E. Wacker
Read from data set with lower BY value.
data newtrans noactiv (dropTrans Amnt) noacct
(dropBranch) merge prog2.transact(inInTrans)
prog2.branches(inInBanks) by ActNum if
InTrans and InBanks then output newtrans else
if InBanks and not InTrans then output noactiv
else if InTrans and not InBanks then output
noacct run
INBANKS
INTRANS
AMNT
ACTNUM
TRANS
BRANCH
.



0
0
56900
1
S. Cicero
...
183
Act Num Trans Amnt 56891 D
126.32 56891 C 560 57900 C
235 58876 D 14.56 59987 C 371.69
Act Num Branch 56891 N. Lincoln 56900
S. Cicero 58876 W. Argyle 59900 N.
Damen 59987 E. Wacker
data newtrans noactiv (dropTrans Amnt) noacct
(dropBranch) merge prog2.transact(inInTrans)
prog2.branches(inInBanks) by ActNum if
InTrans and InBanks then output newtrans else
if InBanks and not InTrans then output noactiv
else if InTrans and not In