Producing List Reports

About This Presentation

Title:

Producing List Reports

Description:

... clarence c e00095 mech03 m 6 wang robert b. e00112 mech01 m 7 brumfield judith e00132 mech02 f 8 baker donald a. e00151 ... – PowerPoint PPT presentation

Number of Views:89

Avg rating:3.0/5.0

Slides: 229

Provided by: KathyK91

Category:

more less

Transcript and Presenter's Notes

Title: Producing List Reports

1
Chapter 4

Producing List Reports

2
Section 4.1

Getting Started with the PRINT Procedure

3
Objectives

Generate simple list reports using the PRINT
procedure (PROC PRINT).
Display selected variables (columns) in a list
report.
Display selected observations (rows) in a list
report.
Display a list report with column totals.

4
SAS Code/Vocabulary
PROC PRINT Operand
Obs Comparison operators
NOOBS Logical operators
VAR Special operators
WHERE CONTAINS
Operator LIKE
5
Overview of PROC PRINT
List reports are typically generated with the
PRINT procedure. This means that the data set is
simply displayed one row after the other. Here is
an example of default output from the PRINT
procedure.
The SAS System
Emp Job Obs
ID LastName FirstName Code
Salary 1 0031 GOLDENBERG DESIREE
PILOT 50221.62 2 0040 WILLIAMS
ARLENE M. FLTAT 23666.12 3 0071
PERRY ROBERT A. FLTAT 21957.71
6
Overview of PROC PRINT

You can change the following
alter titles
revise the column headings
display dollar signs and commas for Salary
remove the OBS column

You can display
titles and footnotes
descriptive column headings
formatted data values.

Salary Report
Emp Job
Annual Obs ID Last Name First Name
Code Salary 1 0031 GOLDENBERG
DESIREE PILOT 50,221.62 2 0040
WILLIAMS ARLENE M. FLTAT 23,666.12
3 0071 PERRY ROBERT A. FLTAT
21,957.71 4 0082 MCGWIER-WATTS
CHRISTINA PILOT 96,387.39 5 0091
SCOTT HARVEY F. FLTAT 32,278.40
6 0106 THACKER DAVID S. FLTAT
24,161.14 7 0355 BELL THOMAS
B. PILOT 59,803.16 8 0366 GLENN
MARTHA S. PILOT 120,202.38
8
Overview of PROC PRINT

You can also display column totals.

The SAS System Emp
Job Obs
ID LastName FirstName Code Salary
1 0031 GOLDENBERG DESIREE PILOT
50221.62 2 0040 WILLIAMS ARLENE M. FLTAT
23666.12 3 0071 PERRY ROBERT A. FLTAT
21957.71 4 0082 MCGWIER-WATTS CHRISTINA
PILOT 96387.39 5 0091 SCOTT HARVEY F.
FLTAT 32278.40 6 0106 THACKER DAVID S.
FLTAT 24161.14 7 0355 BELL THOMAS B.
PILOT 59803.16 8 0366 GLENN
MARTHA S. PILOT 120202.38
428677.92
9
Overview of PROC PRINT
You can break your output into groups.
The SAS
System ------------------------ JobCodeFLTAT
------------------------- Emp
Obs ID LastName FirstName
Salary 1 0040 WILLIAMS
ARLENE M. 23666.12 2 0071
PERRY ROBERT A. 21957.71 3
0091 SCOTT HARVEY F. 32278.40
4 0106 THACKER DAVID S.
24161.14 ------------------------
JobCodePILOT -------------------------
Emp Obs ID LastName
FirstName Salary 5 0031
GOLDENBERG DESIREE 50221.62
6 0082 MCGWIER-WATTS CHRISTINA
96387.39 7 0355 BELL
THOMAS B. 59803.16 8 0366
GLENN MARTHA S. 120202.38
10
Overview of PROC PRINT

You can get subtotals for each group.

The SAS System ---------------
--------- JobCodeFLTAT -------------------------
Emp Obs ID
LastName FirstName Salary 1
0040 WILLIAMS ARLENE M. 23666.12
2 0071 PERRY ROBERT A.
21957.71 3 0091 SCOTT
HARVEY F. 32278.40 4 0106
THACKER DAVID S. 24161.14 -------
---------
JobCode
102063.37 ------------------------
JobCodePILOT -------------------------
Emp Obs ID LastName
FirstName Salary 5 0031
GOLDENBERG DESIREE 50221.62
6 0082 MCGWIER-WATTS CHRISTINA
96387.39 7 0355 BELL
THOMAS B. 59803.16 8 0366
GLENN MARTHA S. 120202.38
-------
--------- JobCode
326614.55

428677.92
11
Overview of PROC PRINT
You can put the different groups on different
pages.
The SAS
System ------------------------ JobCodeFLTAT
------------------------- Emp
Obs ID LastName FirstName
Salary 1 0040 WILLIAMS
ARLENE M. 23666.12 2 0071
PERRY ROBERT A. 21957.71 3
0091 SCOTT HARVEY F. 32278.40
4 0106 THACKER DAVID S.
24161.14 -------
--------- JobCode
102063.37
The SAS
System ------------------------ JobCodePILOT
------------------------- Emp
Obs ID LastName FirstName
Salary 5 0031 GOLDENBERG
DESIREE 50221.62 6 0082
MCGWIER-WATTS CHRISTINA 96387.39
7 0355 BELL THOMAS B.
59803.16 8 0366 GLENN
MARTHA S. 120202.38 -------
--------- JobCode
326614.55

428677.92
12
Creating a Default List Report

General form of the PRINT procedure
Example

PROC PRINT DATASAS-data-setRUN
Remember, you need to submit the LIBNAME
statement once in your SAS session.
libname ia 'SAS-data-library' proc print
dataia.empdata run
13
Creating a Default List Report
libname ia 'SAS-data-library' proc print
dataia.empdata run
Print all variables.
The SAS System
Emp Job Obs
ID LastName FirstName Code
Salary 1 0031 GOLDENBERG DESIREE
PILOT 50221.62 2 0040 WILLIAMS
ARLENE M. FLTAT 23666.12 3 0071
PERRY ROBERT A. FLTAT 21957.71
14
Exercise

This exercise reinforces the concepts discussed
previously.

15
Exercise
Use PROC PRINT to create a list report of the
ia.newmechs data set.
16
Exercise
proc print dataia.newmechs run
Partial Output
The SAS System
Job Obs
LastName FirstName EmpID Code
Gender 1 MASSENGILL ANNETTE M.
E00007 MECH01 F 2 KEARNEY
ANGELA E. E00014 MECH02 F 3
OVERBY JR. WOUTER E00065 MECH02
M 4 WARD ROBERT A. E00074
MECH03 M 5 HICKS CLARENCE C
E00095 MECH03 M 6 WANG
ROBERT B. E00112 MECH01 M 7
BRUMFIELD JUDITH E00132 MECH02
F 8 BAKER DONALD A. E00151
MECH01 M 9 ROHLOFF PATRICIA J.
E00181 MECH02 F 10 OWENS
CLAIRE B. E00214 MECH02 F 11
SAWYER STEPHANIE E00216 MECH03
F 12 WALLACE MEI-YIU E00218
F 13 SPENCE MICHAEL
E00239 MECH03 M
17
Creating a Default List Report
What is the Obs column?
The SAS System
Emp Job Obs
ID LastName FirstName Code
Salary 1 0031 GOLDENBERG DESIREE
PILOT 50221.62 2 0040 WILLIAMS
ARLENE M. FLTAT 23666.12 3 0071
PERRY ROBERT A. FLTAT 21957.71
18
Creating a Default List Report

The Obs column helps you identify a row. This is
helpful if one row of data cannot fit on one line
in your report. SAS breaks the row into "chunks".
The Obs column helps you find where the row
continues.

The SAS System Obs
Division HireDate LastName
FirstName Country 1 FLIGHT
OPERATIONS 11MAR1992 MILLS DOROTHY E
USA 2 FINANCE IT
19DEC1983 BOWER EILEEN A.
USA 3 HUMAN RESOURCES 12MAR1985
READING TONY R. USA

Job Obs City Phone
EmpID Code Salary 1
CARY 2380 E00001
FLTAT3 25,000 2 CARY 1214
E00002 FINCLK 27,000 3
CARY 1428 E00003
VICEPR 120,000
19
Suppressing the Obs Column

The NOOBS option suppresses the row numbers on
the left side of the report.
General form of the NOOBS option
? The Obs column is special to the PRINT
procedure. It is not stored with the data. It
is for display purposes only.

PROC PRINT DATASAS-data-set NOOBSRUN
20
Suppressing the Obs Column
ia.empdata
proc print dataia.empdata noobs var JobCode
EmpID Salary run
Suppress the Obs column.
The SAS System
Job Emp Code
ID Salary PILOT
0031 50221.62 FLTAT
0040 23666.12 FLTAT
0071 21957.71
21
Exercise

This exercise reinforces the concepts discussed
previously.

22
Exercise
Use PROC PRINT to create a list report of the
ia.newmechs data set. Suppress the Obs column.
23
Exercise
proc print dataia.newmechs noobs run
Options can be in any order after the procedure
name.
proc print noobs dataia.newmechs run
Partial Output
The SAS System
Job LastName
FirstName EmpID Code Gender
MASSENGILL ANNETTE M. E00007 MECH01
F KEARNEY ANGELA E. E00014
MECH02 F OVERBY JR. WOUTER
E00065 MECH02 M WARD ROBERT
A. E00074 MECH03 M
24
Printing Selected Variables

What if you do not want all the columns
displayed in your output?
What if you want the columns in a different
order?

25
Printing Selected Variables

The VAR statement enables you to
select variables to include in the report
define the order of the variables in the report.
General form of the VAR statement
Variable names should be separated by a single
space.

VAR variable(s)
26
Using Variable Names

SAS variable names are not case sensitive. You
can use all lower, upper, or mixed case.
These are considered to be the same. The variable
names do not have to match the case that is
stored in the descriptor portion.

proc print dataia.empdata var JobCode EmpID
Salary run
proc print dataia.empdata var jobCode empid
SALARY run
27
Printing Selected Variables
ia.empdata
Select and order variables to print.
proc print dataia.empdata var JobCode EmpID
Salary run
The SAS System
Job Emp Obs
Code ID Salary 1
PILOT 0031 50221.62 2
FLTAT 0040 23666.12 3
FLTAT 0071 21957.71
28
Printing Selected Variables

Does the VAR statement change what is stored in
the data set?
Were the LastName and FirstName variables deleted?

29
Printing Selected Variables

The PROC PRINT step only displays information
based on your syntax.
It does not change what is stored in the
underlying data set.

30
Creating a Default List Report

If you submit another PROC PRINT step without a
VAR statement, you can see that all the variables
are displayed.

proc print dataia.empdata run
The SAS System
Emp Job Obs
ID LastName FirstName Code
Salary 1 0031 GOLDENBERG DESIREE
PILOT 50221.62 2 0040 WILLIAMS
ARLENE M. FLTAT 23666.12 3 0071
PERRY ROBERT A. FLTAT 21957.71
31
Exercise

This exercise reinforces the concepts discussed
previously.

32
Exercise
Use the ia.newmechs data set to create a list
report with the following variables in the
following order EmpID, FirstName, and LastName
33
Exercise
proc print dataia.newmechs noobs var EmpID
FirstName LastName run
Partial Output
The SAS System EmpID FirstName
LastName E00007 ANNETTE M.
MASSENGILL E00014 ANGELA E.
KEARNEY E00065 WOUTER OVERBY
JR. E00074 ROBERT A. WARD
E00095 CLARENCE C HICKS
E00112 ROBERT B. WANG E00132
JUDITH BRUMFIELD E00151
DONALD A. BAKER
34
Subsetting Data WHERE Statement

What if you did not want all of the rows?
For example, what if you only want to seethe
rows for people who are pilots?
What if you want a report of pilots who make
more than 50,000 a year?

35
Subsetting Data WHERE Statement

You can use a WHERE statement.
The WHERE statement
enables you to select observations that meet a
certain condition or conditions
can be used with most SAS procedures (PROCs).

36
Subsetting Data WHERE Statement

General form of the WHERE statement
where-expression is a sequence of operands and
operators.
An operand is the item in the relationship.
The operator describes the relationship.
Example where LastName "Jones"
LastName is an operand and is the operator.

WHERE where-expression
37
Subsetting Data WHERE Statement

Operands include the following
variables (Salary, LastName, JobCode)
constants (the actual values that the WHERE
statement compares) ( 5, 'Jones', 50000)
Operators include the following
comparison operators (, gt, lt, gt, lt )
logical operators (and, or, not)
special operators (between and, contains)
functions (discussed in Chapter 7)

38
Comparison Operators
Mnemonic Symbol Definition
? You can use the mnemonic or symbol.
39
Comparison Operators Examples

The following two examples yield the same result.
The first example uses a symbol for greater than
(gt) and the second example uses the mnemonic (gt).

proc print dataia.empdata2 where Salary gt
50000 run
proc print dataia.empdata2 where Salary gt
50000 run
40
Comparison Operators - Examples

Output
? All the rows in the output represent people who
make more than 50,000 a year.

Emp Job Obs Id LastName
FirstName Code Salary 2 0456
IVEY ANGELA B. FLTAT 62296.30
3 0151 BAKER DONALD A. MECH
63853.70 6 0481 YANG BETTY
A. FLTAT 70083.33 7 0342 LEHMAN
JUDY L. FLTAT 65411.11 8
0262 WEISS MARY E. PILOT
56066.67 10 0423 DUNHAM DEANNA
FLTAT 62296.30 11 0145 KRAMER
ALISSA W. PILOT 95001.85 12 0214
OWENS CLAIRE B. MECH 68525.93
13 0316 TAYLOR CHRISTINA PILOT
70083.33
41
Comparison Operators Examples

Did SAS remove the rows from the data set where
the salaries were less than or equal to 50,000?

42
Comparison Operators Examples

Did SAS remove the rows from the data set where
the salaries were less than or equal to 50,000?
Answer No.
PROC PRINT does not change data. It simply
lists and displays the data in the report.

43
Comparison Operators - Examples

What does this PROC PRINT code produce?
Character comparisons are case-sensitive.
Character strings must be enclosed in matching
quotation marks.
PILOT is not the same character string as
Pilot.

proc print data ia.empdata2 where JobCode
'PILOT' run
44
Comparison Operators

Output

Emp Job Id
LastName FirstName Code
Salary 0262 WEISS MARY E.
PILOT 56066.67 0402 GOODLING
DAVID PILOT 43607.41 0145
KRAMER ALISSA W. PILOT
95001.85 0316 TAYLOR CHRISTINA
PILOT 70083.33 0225 VANCE
MICHAEL PILOT .
45
Exercise

This exercise reinforces the concepts discussed
previously.

46
Exercise
Use the ia.newmechs data set to createa list
report for all the Level 02 mechanics.
47
Exercise
proc print dataia.newmechs noobs where
jobcode 'MECH02' run
48
Comparison Operators Examples

What does this PROC PRINT code produce?
Look at the WHERE statement. Is EmpID a
character or numeric variable? Why?

proc print data ia.empdata2 noobs where
EmpID '0262' run
49
Comparison Operators Examples

Output
Answer EmpID is a character string.
It begins with a zero. If it were numeric, SAS
would not display the leading zero.
You will not perform calculations on this
variable.
It needs quotation marks around the value because
it is a character string, but not around the
variable name.
How much storage would EmpID take if it were a
numeric variable?

Emp Job
Id LastName FirstName Code
Salary 0262 WEISS MARY E.
PILOT 56066.67
50
Comparison Operators Examples

What does this PROC PRINT produce?
A blank space means a missing value for
character values.
A period means a missing value for numeric
values.

proc print dataia.empdata2 where Salary
. run
51
Comparison Operators Examples

Output
PROC PRINT displays the row where Salary is
missing.

Emp Job Id
LastName FirstName Code
Salary 0225 VANCE MICHAEL PILOT
.
52
Comparison Operators Examples
What does this PROC PRINT produce? A
blank space means a missing value for character
values. A period means a missing value for
numeric values.
proc print dataia.empdata2 where LastName
' ' run
53
Comparison Operators Examples

Output
PROC PRINT displays the row where LastName is
missing.

Emp Job Id
LastName FirstName Code
Salary 0181 PATRICIA J MECH
28033.33
54
Exercise

This exercise reinforces the concepts discussed
previously.

55
Exercise
Use the ia.newmechs data set to create a list
report for people with missing job codes.
56
Exercise
proc print dataia.newmechs noobs where
jobcode' ' run
57
WHERE Statement Rules

Examples
? You can use only one WHERE statement in a PROC
PRINT step.

where Salary gt 50000 where EmpID
'0262' where Salary . where LastName '
' where JobCode 'PILOT'
58
Comparison Operators Example

If the data set contains pilots, flight
attendants, and mechanics, what will the
following statements return?
Both statements return the rows where JobCode is
PILOT or FLTAT.
The IN operator enables commas or blanks to
separate values.

where JobCode in('PILOT','FLTAT') where JobCode
in('PILOT' 'FLTAT')
59
Comparison Operators

Output

Emp Job Id
LastName FirstName Code
Salary 0048 MOELL ESTHER
FLTAT 29590.74 0456 IVEY
ANGELA B. FLTAT 62296.30 0481
YANG BETTY A. FLTAT
70083.33 0342 LEHMAN JUDY L.
FLTAT 65411.11 0262 WEISS
MARY E. PILOT 56066.67 0402
GOODLING DAVID PILOT
43607.41 0423 DUNHAM DEANNA
FLTAT 62296.30 0145 KRAMER
ALISSA W. PILOT 95001.85 0316
TAYLOR CHRISTINA PILOT
70083.33 0225 VANCE MICHAEL
PILOT .
60
Subsetting Data WHERE Statement

Test your WHERE statement knowledge.
What if you want to see the rows for the
pilots only?

61
Printing Selected Observations
ia.empdata
Selectrowsto print.
proc print dataia.empdata noobs where
JobCode'PILOT' run
The SAS System

Emp Job
ID LastName FirstName Code
Salary 0031 GOLDENBERG DESIREE
PILOT 50221.62 0082 MCGWIER-WATTS
CHRISTINA PILOT 96387.39 0355 BELL
THOMAS B. PILOT 59803.16
0366 GLENN MARTHA S. PILOT
120202.38
62
Printing Selected Observations and Variables

You can have multiple statements in a step.

ia.empdata
Selectrowsto print.
proc print dataia.empdata noobs var JobCode
EmpID Salary where JobCode'PILOT' run
The SAS System
Job Emp Code
ID Salary PILOT
0031 50221.62 PILOT
0082 96387.39 PILOT
0355 59803.16
63
Printing Selected Observations

Statements can be in any order inside a PROC
step. The WHERE statement can be listed after the
VAR statement.
? The output is the same as the previous slide.

proc print dataia.empdata noobs where
JobCode'PILOT' var JobCode EmpID Salary run
The SAS System
Job Emp Code
ID Salary PILOT
0031 50221.62 PILOT
0082 96387.39 PILOT
0355 59803.16
64
Exercise

This exercise reinforces the concepts discussed
previously.

65
Exercise
Use the ia.newmechs data set to create a list
report for people with job codes of mechanic
levels 03 and 01.
66
You can also use a space between 'MECH01' and
'MECH03' instead of a comma .
Exercise
proc print dataia.newmechs noobs where
jobcode in ('MECH01','MECH03') run
Partial Output
The SAS System
Job LastName FirstName
EmpID Code Gender MASSENGILL
ANNETTE M. E00007 MECH01 F WARD
ROBERT A. E00074 MECH03 M
HICKS CLARENCE C E00095 MECH03
M WANG ROBERT B. E00112
MECH01 M BAKER DONALD A.
E00151 MECH01 M SAWYER
STEPHANIE E00216 MECH03 F SPENCE
MICHAEL E00239 MECH03 M
FREEMAN LAUREN A. E00240 MECH03
F
67
Subsetting Data WHERE Statement

Earlier, you learned the different comparison
operators (, lt, gt) for the WHERE statement.
In this section, you use the logical operators
and the special operators.

68
Logical Operators

Logical operators link expressions together. They
include those shown below

69
Logical Operators AND
Emp Job Id
LastName FirstName Code
Salary 0456 IVEY ANGELA B.
FLTAT 62296.30 0481 YANG
BETTY A. FLTAT 70083.33 0342
LEHMAN JUDY L. FLTAT
65411.11 0423 DUNHAM DEANNA
FLTAT 62296.30
70
Exercise

This exercise reinforces the concepts discussed
previously.

71
Exercise
Use the ia.newmechs data set to create a list
report for the males who are level 02 mechanics.
72
Exercise
proc print dataia.newmechs noobs where
jobcode'MECH02' AND gender'M' run
73
Logical Operators OR
Emp Job Id
LastName FirstName Code
Salary 0048 MOELL ESTHER
FLTAT 29590.74 0456 IVEY
ANGELA B. FLTAT 62296.30 0481
YANG BETTY A. FLTAT
70083.33 0342 LEHMAN JUDY L.
FLTAT 65411.11 0262 WEISS
MARY E. PILOT 56066.67 0402
GOODLING DAVID PILOT
43607.41 0423 DUNHAM DEANNA
FLTAT 62296.30 0145 KRAMER
ALISSA W. PILOT 95001.85 0316
TAYLOR CHRISTINA PILOT
70083.33 0225 VANCE MICHAEL
PILOT .
74
Logical Operators
Correct
where JobCode'PILOT' or JobCode'FLTAT'
Incorrect
where JobCode'PILOT' or 'FLTAT'
This is missing the variable nameJobCode.
75
Exercise

This exercise reinforces the concepts discussed
previously.

76
Exercise
Use the ia.newmechs data set to create a list
report for mechanics with job codes of level 03
and 01. Use an OR operator.
77
Exercise
proc print dataia.newmechs noobs where
jobcode'MECH03' or jobcode'MECH01' ru
n
78
Logical Operators NOT
Emp Job Id
LastName FirstName Code
Salary 0151 BAKER DONALD A.
MECH 63853.70 0308 RIPPERTON
DAVID D. MECH 34262.96 0181
PATRICIA J MECH
28033.33 0214 OWENS CLAIRE B.
MECH 68525.93
79
Exercise

This exercise reinforces the concepts discussed
previously.

80
Exercise
Use the ia.employees data set create a list
report for people outside of the USA. Use the
country variable.
81
Exercise
proc print dataia.employees noobs where
country not in ('USA') run or you can code
it this way proc print dataia.employees
noobs where country ne 'USA' run
82
Special Operators

Example

Emp Job Id
LastName FirstName Code
Salary 0456 IVEY ANGELA B.
FLTAT 62296.30 0151 BAKER
DONALD A. MECH 63853.70 0423
DUNHAM DEANNA FLTAT
62296.30
83
Exercise

This exercise reinforces the concepts discussed
previously.

84
Exercise
Use the ia.employees data set to create a list
report for those who have a salary between
25,000 and 40,000, inclusive.
85
Exercise
proc print dataia.employees noobs where
Salary between 25000 and 40000 run
SAS does not store numbers with or commas.
86
Special Operators
?
Word
BLAME
ALARM
LAMB
LAMENT
CRAB
FLAMING
proc print dataia.compare noobs where Word ?
'LAM' run
Which word(s) will be included?
...
87
Special Operators
?
Word
BLAME
ALARM
LAMB
LAMENT
CRAB
FLAMING
proc print dataia.compare noobs where Word ?
'LAM' run
Word
BLAME
LAMB
LAMENT
FLAMING
88
Exercise

This exercise reinforces the concepts discussed
previously.

89
Exercise
Use the ia.employees data set to create a list
report for people whose job code contains the
string 'CLK'.
90
Exercise
proc print dataia.employees where jobcode ?
'CLK' run
or
proc print dataia.employees where jobcode
contains 'CLK' run
91
Another Special Operator

The LIKE operator selects observations by
comparing character values to specified
patterns.
A percent sign () replaces any number of
characters.
An underscore (_) replaces one character.
The operator selects observations where the value
of Code begins with an E, followed by a single
character, followed by a U, followed by any
number of characters.

where Code like 'E_U'
92
Special Operators

If the following statement were in a PROC PRINT
step, which rows would be selected?

where Code like 'E_U'
Code Is the row selected? Why or why not?
ECU
ECUT
ETCU
Ecure
...
93
Special Operators

If the following statement were in a PROC PRINT
step, which rows would be selected?

If the following statement were in a PROC PRINT
step, which rows would be selected?

If the following statement were in a PROC PRINT
step, which rows would be selected?

where Code like 'E_U'
Code Is the row selected? Why or why not?
ECU Yes The value starts with an E, followed by any one character, followed by a U, and followed by zeroto any number of characters.
ECUT Yes The same reasons as above.
ETCU No Value does not fit pattern. There are two characters between the E and the U. The underscore represents one character.
Ecure
...
96
Special Operators

If the following statement were in a PROC PRINT
step, which rows would be selected?

This exercise reinforces the concepts discussed
previously.

98
Exercise
Using the ia.employees data set, create a list
report for people whose last name begins with the
letter B and contains a W. List only the first
and last names in the output.
99
Exercise
proc print dataia.employees where lastname
like 'BW' var FirstName LastName run
The SAS System Obs FirstName
LastName 2 EILEEN A.
BOWER 14 SHANNON T.
BROWN 24 LYNNE C.
BOWMAN 26 MARK
BREWER 114 BEN F.
BROWNRIGG 122 SUSANNA
BROWNING 177 JANINE
BRASWELL 193 LINDA B.
BROWN 252 MONICA E.
BOWDEN 296 LESLIE
BOWEN 329 PETER M.
BROWN 334 JACK M.
BROWN 477 FILIP BAUWENS
100
Exercise
The SAS System Obs FirstName
LastName 2 EILEEN A.
BOWER 14 SHANNON T.
BROWN 24 LYNNE C.
BOWMAN 26 MARK
BREWER 114 BEN F.
BROWNRIGG 122 SUSANNA
BROWNING 177 JANINE
BRASWELL 193 LINDA B.
BROWN 252 MONICA E.
BOWDEN 296 LESLIE
BOWEN 329 PETER M.
BROWN 334 JACK M.
BROWN 477 FILIP BAUWENS
Notice the OBS column. The value corresponds to
the row number from the data set. This would
be handy if you wanted to look at the row
later or wanted to change a row later.
101
Exercise
Modify your previous step. Using the ia.employees
data set, create a list report for people whose
last name begins with the letter B, and the third
letter is a W. List only the first and last
names in the output.
102
Exercise
proc print dataia.employees where lastname
like 'B_W' var FirstName LastName run
The SAS System Obs FirstName
LastName 2 EILEEN A.
BOWER 24 LYNNE C.
BOWMAN 252 MONICA E.
BOWDEN 296 LESLIE BOWEN
103
Exercise
proc print dataia.employees where lastname
like 'BW_' var firstname lastname run
The SAS System Obs FirstName
LastName 14 SHANNON T.
BROWN 193 LINDA B.
BROWN 329 PETER M.
BROWN 334 JACK M.
BROWN
104
Exercise Section 4.1

This exercise reinforces the concepts discussed
previously.

105
Section 4.2

Sequencing and Grouping Observations

106
Objectives

Sequence (sort) observations in a SAS data set.
Group observations in a list report.
Print column subtotals and grand totals in a list
report.
Control page breaks for subgroups.

107
SAS Code/Vocabulary

SUM
PROC SORT
OUT Option
DESCENDING
BY SUM
BYLINE
PAGEBY

108
Requesting Column Totals

You want to have a total of all the salaries
shown at the bottom of the PROC PRINT output.

The SAS System
Job Emp Code
ID Salary PILOT
0031 50221.62 FLTAT
0040 23666.12 FLTAT
0071 21957.71 . . .
. . .

428677.92
109
Requesting Column Totals

The SUM statement produces column totals.
General form of the SUM statement
The SUM statement also produces subtotals if you
print the data in groups.

SUM variable(s)
110
Requesting Column Totals

Produce column totals with the SUM statement.

proc print dataia.empdata noobs var JobCode
EmpID Salary sum Salary run
The SAS System
Job Emp Code
ID Salary PILOT
0031 50221.62 FLTAT
0040 23666.12 FLTAT
0071 21957.71 ...
... ...

428677.92
111
Exercise

This exercise reinforces the concepts discussed
previously.

112
Exercise
Use the ia.allsales data set to create a list
report with the sum of the all the sales.
Display only the variables Month, Region, and
Sales.
113
Exercise
proc print dataia.allsales noobs var Month
Region Sales sum Sales run
114
Sorting a SAS Data Set

The last report displayed the grand total for all
sales. To get subtotals for the North American
sales and the European sales, you need to group
or sort the data.

115
Sorting a SAS Data Set

The SORT procedure
rearranges the observations in a SAS data set
can create a new SAS data set containing the
rearranged observations
can sort on multiple variables
can sort variable contents in ascending (default)
or descending order
does not generate printed (displayed) output
treats missing values as the smallest possible
value.

116
Sorting a SAS Data Set

General form of the PROC SORT step
Example

PROC SORT DATAinput-SAS-data-set BY
ltDESCENDINGgt by-variable(s)RUN
proc sort dataia.empdata by Salaryrun
This procedure, as it is above, overwrites the
original data set with the sorted version of
itself. Data was reordered, but no observations
were added or removed. The default ascending
order shows missing values first, and then lowest
to highest salaries.
117
Sorting a SAS Data Set
It is a good practice to use the OUT option to
create a new data set, which will be the sorted
version of the original data set.
proc sort dataia.empdata outwork.jobsal
by Salaryrun
If you submit a SORT step and do not submit a
PRINT step, you will not have output in the
Output window. The SORT procedure only sorts
data it does not produce a report.
118
Sorting a SAS Data Set

ia.empdata
work.empdata

proc sort dataia.empdata outwork.empdata by
JobCode run
119
Sorting a SAS Data Set

By default, PROC SORT sorts data in ascending
order. What if you want the highest paid person
to appear first?
Use the DESCENDING keyword in front of the
variable name.
You cannot abbreviate the descending keyword.

proc sort dataia.empdata outwork.jobsal by
descending Salaryrun
120
Sorting a SAS Data Set

What if you want the data sorted
by job codes in ascending order
so that the highest paid person appears first in
each job code?
Fill in the blank area inside the step with your
answer.

proc sort dataia.empdata outwork.jobsal
___________________________ run
121
Sorting a SAS Data Set

What if you want the data sorted
by job codes in ascending order
so that the highest paid person appears first in
each job code?
The keyword descending is in front of Salary.
JobCode is sorted in ascending order by default.

proc sort dataia.empdata outwork.jobsal
by JobCode descending Salaryrun
122
Sorting a SAS Data Set

What if you want the data sorted
by job codes in descending order
so that the highest paid person appears first in
each job code?
Fill in the blank area inside the step with your
answer.

proc sort dataia.empdata outwork.jobsal
___________________________ run
123
Sorting a SAS Data Set

What if you want the data sorted
by job codes in descending order
so that the highest paid person appears first in
each job code?
The keyword descending is in front of both
variables.

proc sort dataia.empdata outwork.jobsal
by JobCode descending Salary
descending Salary run
124
Exercise

This exercise reinforces the concepts discussed
previously.

125
Exercise
Use the ia.pilots data set to create a list
report sorted by City in descending order and by
LastName in ascending order. Output the data to a
new data set named PilSort. (var City, LName)
126
Exercise
proc sort dataia.pilots
outwork.PilSort by descending City
LName run proc print datawork.PilSort
noobs run
PROC SORT does not produce output. To see your
sorted results, you must also use PROC PRINT.
127
WHERE Statement in PROC SORT

Remember the WHERE statement you used in the PROC
PRINT step?
You can also use it in the SORT procedure.

128
WHERE Statement in PROC SORT

Example
Write a report sorted by Month and display only
the rows from Europe.

Example
What if you want a report sorted by Month and to
display only the rows from Europe?

proc sort dataia.allsales
outtempsales by Month run proc print
datatempsales var Month Region Sales
where Region'Europe' run
The SAS System Obs Month Region
Sales 1 1 Europe 2118222
3 2 Europe 1960034 5
3 Europe 2094220 7 4
Europe 2130248 9 5 Europe
2100211 11 6 Europe 2164796
13 7 Europe 2252662 15
8 Europe 2159234 17 9
Europe 2146457 19 10 Europe
2294300 21 11 Europe 2144188
23 12 Europe 2247953
Notice the Obs column numbers.
...
130
WHERE Statement in PROC SORT
Example What if you want a report sorted by
Month and to display only the rows from Europe?
proc sort dataia.allsales
outtempsales by Month where
Region'Europe' run proc print datatempsales
var Month Region Sales run
The SAS System Obs Month Region
Sales 1 1 Europe 2118222
2 2 Europe 1960034 3
3 Europe 2094220 4 4
Europe 2130248 5 5 Europe
2100211 6 6 Europe 2164796
7 7 Europe 2252662 8
8 Europe 2159234 9 9
Europe 2146457 10 10 Europe
2294300 11 11 Europe 2144188
12 12 Europe 2247953
Notice the Obs column numbers.
131
WHERE Statement in PROC SORT

What is the difference?
By putting the WHERE statement in the SORT step,
the sorted output data set work.tempsales
contains only the rows where Region is Europe.
Because work.tempsales is a new data set, the Obs
number represents the order of the row in that
data set.

132
WHERE Statement in PROC SORT
If you put a WHERE statement in your PROC SORT
step and you do not use an OUT option, you will
overwrite your original data set. Data that does
not meet the WHERE statement condition is not
included in the output data set. Use caution
when you use the WHERE statement in the PROC SORT
step. It is good practice to always use the OUT
option if you use the WHERE statement in PROC
SORT.
133
Compare the SAS Logs

Use the WHERE statement in the PROC PRINT step.

Notice the number of observations written to
tempsales.
134
Compare the SAS Logs

Use the WHERE statement in the PROC SORT step.

Notice the number of observations written to
tempsales.
135
Exercise

This exercise reinforces the concepts discussed
previously.

136
Exercise
Use the ia.allsales data set to create a list
report sorted by Sales. Output to the data set
work.sortsales. Select only the rows where
Region is North America. Code two separate
programs, that is, one using the WHERE statement
in the PROC PRINT step and one using the WHERE in
the PROC SORT step.
137
Exercise
proc sort dataia.allsales
outwork.sortsales by Sales run proc print
datawork.sortsales var Month Region Sales
where Region'North America' run
proc sort dataia.allsales
outwork.sortsales by Sales where
Region'North America' run proc print
datawork.sortsales var Month Region
Sales run
The SAS System Obs Month
Region Sales 13 9 North
America 2863599 14 2 North America
2926929 15 11 North America
2943110 16 10 North America
3049582 17 3 North America
3065902 18 6 North America
3088312 19 7 North America
3104977 20 8 North America
3113833 21 5 North America
3135696 22 1 North America
3135765 23 12 North America
3153807 24 4 North America 3496058
The SAS System Obs Month Region
Sales 1 9 North America
2863599 2 2 North America 2926929
3 11 North America 2943110 4 10
North America 3049582 5 3 North
America 3065902 6 6 North America
3088312 7 7 North America 3104977
8 8 North America 3113833 9 5
North America 3135696 10 1 North
America 3135765 11 12 North America
3153807 12 4 North America 3496058
138
Printing Grand Totals

Print the data set in order by JobCode with a
grand total for the Salary column.
If you want the report to be displayed in sorted
order, you must sort the data first, then use
PROC PRINT.

proc sort dataia.empdata outwork.empdata
by JobCoderun proc print datawork.empdata
sum Salary run
139
Printing Grand Totals

Output

The SAS System Emp
Job Obs ID
LastName FirstName Code
Salary 1 0040 WILLIAMS ARLENE M.
FLTAT 23666.12 2 0071 PERRY
ROBERT A. FLTAT 21957.71 3 0091
SCOTT HARVEY F. FLTAT 32278.40
4 0106 THACKER DAVID S. FLTAT
24161.14 5 0031 GOLDENBERG
DESIREE PILOT 50221.62 6 0082
MCGWIER-WATTS CHRISTINA PILOT 96387.39
7 0355 BELL THOMAS B. PILOT
59803.16 8 0366 GLENN MARTHA
S. PILOT 120202.38
428677.92
140
Exercise

This exercise reinforces the concepts discussed
previously.

141
Exercise
Use the ia.allsales data set to create a list
report sorted by Region, output it to
work.salesort, and include a grand total.
142
Exercise
proc sort dataia.allsales
outwork.salesort by region run proc print
datawork.salesort noobs sum Sales run
143
Exercise
144
Printing Subtotals and Grand Totals

Using a BY statement and a SUM statement together
in a PROC PRINT step produces subtotals and grand
totals.

proc sort dataia.empdata outwork.empdata
by JobCoderun proc print datawork.empdata
by JobCode sum Salary run
continued...
145
Printing Subtotals and Grand Totals
The SAS
System ------------------------ JobCodeFLTAT
------------------------- Emp
Obs ID LastName FirstName
Salary 1 0040 WILLIAMS
ARLENE M. 23666.12 2 0071
PERRY ROBERT A. 21957.71 3
0091 SCOTT HARVEY F. 32278.40
4 0106 THACKER DAVID S.
24161.14 -------
--------- JobCode
102063.37 ---------------------
--- JobCodePILOT -------------------------
Emp Obs ID LastName
FirstName Salary 5 0031
GOLDENBERG DESIREE 50221.62
6 0082 MCGWIER-WATTS CHRISTINA
96387.39 7 0355 BELL
THOMAS B. 59803.16 8 0366
GLENN MARTHA S. 120202.38
-------
--------- JobCode
326614.55

428677.92
146
Printing Subtotals and Grand Totals

The dashed line is known as the BY line. The
variable(s) in the BY statement in the PROC PRINT
step are listed in the BYLINE option, along with
their values.

If you want to use a BY statement in your PROC
PRINT step, your data must be in sorted order
by the variable listed in the BY statement.
Otherwise, you will see an error message in the
SAS log that the data set is not in proper
sequence.

148
Exercise

This exercise reinforces the concepts discussed
previously.

149
Exercise
Use the ia.allsales data set to create a list
report sorted and subtotaled by Region. Write the
sorted data to work.salesort.
150
Exercise
proc sort dataia.allsales
outwork.salesort by region run proc print
datawork.salessort noobs by Region sum
Sales run
151
Page Breaks

The PAGEBY statement puts each subgroup on a
separate page.
General form of the PAGEBY statement
The PAGEBY statement must be used with a BY
statement.

proc print datawork.empdata by
JobCode pageby JobCode sum Salary run
PAGEBY by-variable
152
Page Breaks
First Page
The SAS System
1 ------------------------
JobCodeFLTAT -------------------------
Emp Obs ID LastName
FirstName Salary 1 0040
WILLIAMS ARLENE M. 23666.12 2
0071 PERRY ROBERT A. 21957.71
3 0091 SCOTT HARVEY F.
32278.40 4 0106 THACKER
DAVID S. 24161.14 -------
--------- JobCode
102063.37
153
Page Breaks
Second Page
The SAS System
2 ------------------------
JobCodePILOT -------------------------
Emp Obs ID LastName
FirstName Salary 5 0031
GOLDENBERG DESIREE 50221.62
6 0082 MCGWIER-WATTS CHRISTINA
96387.39 7 0355 BELL
THOMAS B. 59803.16 8 0366
GLENN MARTHA S. 120202.38
-------
--------- JobCode
326614.55

428677.92
154
Printing Subtotals and Grand Totals

If you want to use a PAGEBY statement in your
PROC PRINT step, you must also use a BY
statement.
If you want to use a BY statement, your data
must be sorted.

...
155
Compare SORT, BY, and PAGEBY
SORT
Sort, BY,SUM
SORT, BY,PAGEBY, SUM
Original
Page 1
538999
538999
Page 2
778789
778789
Page 3
4453235
156
Compare SORT, BY, and PAGEBY
SORT
SORT, BY, SUM
SORT, BY, PAGEBY, SUM
157
Compare SORT, BY, and PAGEBY
SORT
158
Compare SORT, BY, and PAGEBY
SORT, BY, SUM
159
Compare SORT, BY, and PAGEBY
SORT, BY, PAGEBY, SUM
160
Compare SORT, BY, and PAGEBY
SORT
SORT, BY, SUM
SORT, BY, PAGEBY, SUM
161
Exercise

This exercise reinforces the concepts discussed
previously.

162
Exercise
Use the ia.allsales data set to create a list
report sorted by Region with subtotals and a
grand total for Sales. Each region should appear
on a separate page.
163
Exercise
? Your page numbers will have different values.
This is discussed further in the next chapter.
164
Exercise
165
Exercise
proc sort dataia.allsales outwork.salesort
by Region run proc print datawork.salesort
noobs by Region pageby Region sum
Sales run
166
Review

PROC SORT orders the rows, but does not display
output.
The BY statement in the PROC SORT step tells SAS
what order you want the rows.

167
Review
SORT
Original
168
Review

The BY statement in the PROC PRINT step tells SAS
you want to separate the report into BY groups.
A dashed line with the variable(s) and their
values is added to the output, known as the BY
line. Extra blank lines are added to separate the
groups visually.

169
Review
Data Sorted, BY Statement in PRINT step
Sorted Data
The BY statement breaks groups apart in the
output.
170
Review

In order to put a BY statement in the PROC PRINT
step, your data must be in what form?

...
171
Review
In order to put a BY statement in the PROC PRINT
step, your data must be in what form? Your data
must be sorted. If it is not sorted, you see an
error message in your SAS log.
172
Review

The PAGEBY statement in the PROC PRINT step tells
SAS you want to put the groups on different pages.

173
Review
Data Sorted, Both a BY and PAGEBY Statement in
PRINT Step
Data Sorted, BY Statement in PRINT Step
The PAGEBY statement puts the groups on different

Write a Comment

User Comments (0)