• Print  |
  • Feedback  |

Training & Bookstore

Bookstore Questions?

Quick Search for  Books
 

Related Links

Conversations

linkedin sasCommunity blog YouTube

SAS Training and Books on Facebook

Browse Resources» SAS Certification Prep Guide: Advanced Programming for SAS 9, Second Edition


Updates for SAS Certification Prep Guide: Advanced Programming for SAS 9 The following chapter numbers and page numbers apply to SAS Certification Prep Guide: Advanced Programming, ISBN number 978-1-60764-044-8. You can find the ISBN number on the copyright page. Page numbers in other ISBN versions might vary.

Chapter Page Update
3 84 Replace the note that follows the syntax box with the following information:
Note: The maximum number of tables that you can combine in a single inner join depends on your version of SAS. For more information, see the SAS documentation.

If the join involves views (either in-line views or PROC SQL views), it is the number of tables that underlie the views, not the number of views themselves, that counts towards the limit. In-line views are covered later in this chapter and PROC SQL views are discussed in Chapter 7, "Creating and Managing Views Using PROC SQL," on page XXX.

3 98 Replace the notes on these pages with the following:
The DATA step match-merge creates a data set whereas the PROC SQL inner join, as shown here, creates only a report as output. To make these two programs completely identical, the PROC SQL inner join could be rewritten to create a table.
Note: For detailed information about creating tables with PROC SQL, see Chapter 5, "Creating and Managing Tables Using PROC SQL," on page XXX.
Note: If the order of rows in the output does not matter, the ORDER BY clause can be removed from the PROC SQL join. Without the ORDER BY clause, this join is more efficient, because PROC SQL does not need to make a second pass through the data.
3 99 The sentence, e.COALESCE function can also be used with inner join operators." should be "…The COALESCE function can also be used with inner join operators."
3 100 Replace the syntax box and the text that immediately follows it with the following:
General form, COALESCE function in a basic SELECT clause:
SELECT COALESCE (column-1<,...column-n>) where column-1 through column-n are the names of two or more columns to be overlaid. The COALESCE function requires that all arguments have the same data type. The COALESCE function overlays the specified columns by checking the value of each column in the order in which the columns are listed returning the first value that is a SAS nonmissing value.
Note: If all returned values are missing, COALESCE returns a missing value.

When the COALESCE function is added to the preceding PROC SQL full outer join, the DATA step match-merge (with PROC PRINT step) and the PROC SQL full outer join will combine rows in the same way. The two programs, the tables, and the output are shown below.

3 114 In the Syntax section, SELECT COALESCE item, remove the 2nd line
3 116 In the Points to Remember section, revise the second bullet to be:
The maximum number of tables that you can combine in a single inner join depends on your version of SAS. If the join involves views, it is the number of tables that underlie the views, not the number of views, that counts towards the limit. An outer join can be performed on only two tables or views at a time.
3 122 Replace quiz question number 9's question and answer. The answer is now d instead of c. The new material follows:
In which of the following ways is a PROC SQL join different from a DATA step match-merge?
a. A PROC SQL join does not overlay common columns by default.
b. A PROC SQL join does not require the data to be sorted first.
c. A PROC SQL join produces printed output.
d. all of the above
5 193 Displaying Integrity Constraints for a Table," delete the paragraph that follows the note.
7 244 The PROC SQL code includes two occurrences of format= dollar. The second occurrence should be dollar12.2 instead of dollar12.
9 310 The first line of code is missing a semicolon. The corrected code is %put C: &c;
11 393 In step 3 of the example "The macro processor encounters %local variY" should be "The macro processor encounters %local variX".
15 538 In step 1, "crop=totalrev" should be "by drop=totalrev".
16 562 Table 16.1 is missing the fifth observation, which is "5 FEB1998 $215,959,695.50 2".
16 576 The title of Table 16.24 should be "SAS Data Set Work.Mnthsum2 (sorted, first twelve observations".
18 646 The first note on the page should be rewritten as "Note: When you add an integrity constraint to the table that contains data, SAS checks all data values to determine whether they satisfy the constraint before the constraint is added.
18 669 Syntax," "PROC DATASETS," in the CONTENTS statement "data=" should be in bold uppercase.
21 736 Replace the entire "Compressing Data Files" section with the following: By default, a SAS data file is uncompressed. You can compress your data files in order to conserve disk space, although some files are not good candidates for compression. The file structure of a compressed data file is different from the structure of an uncompressed file. You use the COMPRESS= data set option or system option to compress a data file. You use the POINTOBS= data set option to enable SAS to access observations in compressed files directly rather than sequentially. You use the REUSE= data set option or system option to specify that SAS should reuse space in a compressed file when observations are deleted or updated.
21 736 Question 2, choice b, the word "spaced" should be "space".
23 817 Replace the entire "Example" section with the following:
When Company.Reorder is sorted using the BY variable Product_Line and the NODUPRECS option, the duplicate observation that contains the product name Kids Children's Fleece Hat is eliminated because it exactly matches the observation that was previously written to the output data set. The duplicate observation that contains the product name Ski Jacket w/Removable Fleece is retained because it does not exactly match the observation that was previously written to the output data set.
24 873 Deciding Whether to Create an Index," "MSGLEVEL=|" should be "MSGLEVEL=I
Appendix 1 893 Quiz question number 8, C is indeed the correct answer. No alias is required to reference the values in a calculated column in an ORDER BY clause; the column can be referenced positional instead.
Appendix 1 893 Replace quiz question number 9's question and answer. The answer is now d instead of c. The new material follows:
In which of the following ways is a PROC SQL join different from a DATA step match-merge?
a. A PROC SQL join does not overlay common columns by default.
b. A PROC SQL join does not require the data to be sorted first.
c. A PROC SQL join produces printed output.
d. all of the above

Correct answer: d

Unlike a DATA step match-merge, a PROC SQL join does not overlay common columns by default. Additionally, a PROC SQL join does not require the data to be sorted first, but a DATA step match-merge requires sorted data sets as input. Finally, by default a PROC SQL join produces printed output but a DATA step match-merge produces a data set.