Advanced SAS 9 ExamPrep SritejGunta

Advanced SAS 9 Exam Prep: A00-212 | Prepared for Sritej Gunta 1.

The following SAS program is submitted: DATA TEMP; LENGTH A 1 B 3 X; INFILE ‘FILE-REFERENCE’; INPUT A B X; RUN; What is the result? A. B. C. D.

2.

The The The The

data data data data

set set set set

TEMP TEMP TEMP TEMP

is is is is

created and variable X has a length of 8 not created because variable A has an invalid length not created because variables A and B have invalid lengths created, but variable X is not created

The data set SASUSER.HIGHWAY is given below: STEERING -------Absent Absent Absent Absent Absent

SEATBELT -------No No No No No

SPEED ----0-29 0-29 30-49 30-49 50+

STATUS ------Serious Not Serious Not Serious

COUNT ----31 1419 191 2004 216

The following SAS program is submitted: %MACRO HIGHWAY; PROC SQL NOPRINT; SELECT COUNT(DISTINCT STATUS) INTO :NUMGRP FROM SASUSER.HIGHWAY; %LET NUMGRP=&NUMGRP; SELECT DISTINCT STATUS INTO :GROUP1-:GROUP&NUMGRP FROM SASUSER.HIGHWAY; QUIT; %DO i=1 %TO &NUMGRP; PROC PRINT DATA=SASUSER.HIGHWAY; WHERE STATUS=’&&GROUP&i’; RUN; %END; %MEND; %HIGHWAY How many reports are produced by the above program? A. B. C. D.

3.

1 0 2 5

The data set ONE is given below: NUM VAR --- --1 A 2 B 3 C Which one of the following SQL programs deletes the SAS data set ONE? A. PROC SQL; DELETE TABLE ONE; QUIT; B. PROC SQL; ALTER TABLE ONE DROP NUM, VAR; QUIT;

Advanced SAS 9 Exam Prep: A00-212 | Prepared for Sritej Gunta C. PROC SQL; DROP TABLE ONE; QUIT; D. PROC SQL; DELETE FROM ONE; QUIT;

4.

The data set ONE is given below: CATEGORY -------M M M M F F F

AGE --28 25 28 33 18 25 35

SALARY -----200 100 300 300 100 200 400

BONUS ----20 10 10 30 50 10 50

The following SAS program is submitted: PROC SQL; CREATE TABLE TWO AS SELECT DISTINCT AGE FROM ONE WHERE AGE < 33; QUIT; How many rows are written to the SAS data set TWO? A. B. C. D.

5.

3 5 6 4

The following SAS program is submitted: DATA SASUSER.HISTORY; SET SASUSER.HISTORY (KEEP=STATE X Y RENAME=(STATE=ST)); TOTAL=SUM(X,Y); RUN; The SAS data set SASUSER.HISTORY has an index on the variable STATE. Which describes the result of submitting the SAS program? A. B. C. D.

6.

The The The The

index index index index

on on on on

STATE STATE STATE STATE

is is is is

updated as an index on ST deleted and an index on ST is created recreated as an index on ST deleted

The SAS data set ONE contains fifty million observations and contains the variables PRICE, QUANTITY, FIXED, and VARIABLE. Which SAS program successfully creates three new variables TOTREV, TOTCOST, and PROFIT and requires the least amount of CPU resources to be processed? A. DATA TWO; SET ONE; TOTREV=SUM(PRICE*QUANTITY); TOTCOST=SUM(FIXED,VARIABLE); IF TOTREV > 1000; PROFIT=SUM(TOTREV,-TOTCOST); RUN;

Advanced SAS 9 Exam Prep: A00-212 | Prepared for Sritej Gunta B. DATA TWO; SET ONE; WHERE TOTREV > 1000; TOTREV=SUM(PRICE*QUANTITY); TOTCOST=SUM(FIXED,VARIABLE); PROFIT=SUM(TOTREV,-TOCOST); RUN; C. DATA TWO; SET ONE; TOTREV=SUM(PRICE*QUANTITY); IF TOTREV > 1000; TOTCOST=SUM(FIXED,VARIABLE); PROFIT=SUM(TOTREV,-TOTCOST); RUN; D. DATA TWO; SET ONE; TOTREV=SUM(PRICE*QUANTITY); WHERE TOTREV > 1000; TOTCOST=SUM(FIXED,VARIABLE); PROFIT=SUM(TOTREV,-TOTCOST); RUN;

7.

The following ARRAY statement is submitted: ARRAY SCORE{*} A4-A10 A25; Which one of the following is the maximum number of elements stored? A. B. C. D.

8.

11 3 7 8

The SAS data set ONE is given below: REP ----SMITH SMITH JONES SMITH JONES JONES JONES SMITH JONES JONES

AREA ----NORTH SOUTH EAST NORTH WEST NORTH NORTH NORTH WEST WEST

COST ---100 200 100 300 100 200 400 400 100 300

The following SAS program is submitted: PROC SQL; SELECT REP, AREA, COUNT(*) AS TOTAL FROM ONE GROUP BY REP, AREA; QUIT; Which of the following reports is generated? A. REP JONES JONES JONES SMITH SMITH SMITH SMITH

AREA EAST NORTH WEST NORTH SOUTH NORTH SOUTH

TOTAL 1 2 3 3 1 3 1

Advanced SAS 9 Exam Prep: A00-212 | Prepared for Sritej Gunta

9.

B. REP JONES JONES JONES SMITH SMITH

AREA EAST NORTH WEST NORTH SOUTH

TOTAL 100 600 500 800 200

C. REP JONES JONES JONES SMITH SMITH


TOTAL 100 600 500 800 200

D. REP JONES JONES JONES SMITH SMITH


TOAL 1 2 3 3 1

Which statement(s) in the DATASETS procedure alter(s) the name of a SAS data set stored in a SAS data library? A. B. C. D.

10.

RENAME CHANGE MODIFY MODIFY

statement only statement only and RENAME statements and CHANGE statements

The following SAS program is submitted: %LET VALUE=9; %LET ADD=5; %LET NEWVAL=%EVAL(&VALUE/&ADD); Which is the value of the macro variable NEWVAL? A. B. C. D.

1.

1.8 2 1 Null

(B) The LENGTH statement does not determine the width of the display value (that is controlled by FORMAT) but rather the number of bytes used for storing variable values. Because the SAS default is to assign numeric type to input variables (and this example lacks the necessary $ preceding the number for character type), these variables are clearly assigned a numeric type. This program fails initially because numeric values must be assigned a length of between 2 to 8 (typically 3 to 8) depending on the operating environment. So, variable A has been assigned an improper length. An additional error would occur on variable X. It is assigned in the LENGTH statement, but it is not given a variable length.

2.

(C) This SAS program is composed of a macro program called HIGHWAY which spans from the %MACRO statement to the %MEND statement. When the macro program is called, it executes the code within that span. The NOPRINT option in the PROC SQL statement indicates that no report will be generated; that is, no table is printed in the output window. The INTO clause creates macro variables equal to the number of distinct values in the STATUS column. Therefore, there are two values (1 and 2) for the two distinct statuses (SERIOUS and NOT).

Advanced SAS 9 Exam Prep: A00-212 | Prepared for Sritej Gunta The PRINT procedure generates a number of reports based on a %DO loop which gets its span from the number of distinct STATUS values. So, 1 %TO 2. Therefore, two reports are generated from two PRINT procedures.

3.

(C) A ‘table’ in SQL language is synonymous with ‘data set’ in SAS. So, to delete an entire data set using PROC SQL, you must invoke the DROP TABLE statement using the following syntax: DROP TABLE table-name-1 <, … table-name-n>; Where table-name specifies the name of the table(s) to be dropped.

4.

(A) The first thing PROC SQL does is execute the FROM clause. This eliminates the observations where the variable AGE possesses a value greater than or equal to 33. So, this leaves five observations. The next thing it does is create a new table called TWO while only using the variable AGE from data set ONE. Within this, it uses the keyword DISTINCT to select only unique values for the variable AGE. This eliminates the duplicates and leaves only three observations.

5.

(D) An index is an optional file that can be created for a SAS data file in order to provide direct access to specific observations. SAS maintains indexes for all changes to the table, whether the changes originate from PROC SQL or some other source, as long as the entire table is not re-created. If you alter a column’s definition or update its values, then SAS will update the indexes also. However, if a key column in a table is dropped (deleted), then the index on that column is also dropped. As you can see, the SET statement identifies the same data set as the DATA statement. Therefore, the data set is recreated and all indexes are deleted.

6.

(C) First, understand that SAS evaluates code line by line sequentially. Without knowing anything else besides that, you can eliminate option (B) because it is attempting to apply a WHERE clause with an expression to a variable that doesn’t even exist in the data set. Therefore, SAS will clearly return an error and the program will fail. The next thing to understand is a fundamental difference between WHERE statements and the subsetting IF statement. Normally, when you’re attempting to subset data, the WHERE statement is more efficient because it can evaluate data in the buffer before it decides whether or not to pass it along to the program data vector (PDV). On the other hand, the subsetting IF statement must wait until the data has entered the PDV before it can be evaluated based on prescribed conditions. The catch in this instance is that a WHERE statement can only select observations from SAS data sets, whereas the IF statement can select records from external files, observations from SAS data sets, observations created with an INPUT statement, or, in this case, observations based on the value of a computed or derived variable. Because TOTREV is a created variable, the WHERE statement is unable to subset it in the same DATA step, and the program fails. This leaves you with only options (A) and (C) to consider. Both would accomplish the task, but option (A) does not attempt to subset until after it’s already created an additional variable (TOTCOST). Therefore, it’s more efficient to evaluate the subsetting IF and decide whether or not to pass on the record immediately after TOTREV has been created instead of creating an additional variable that may just be discarded anyway.

7.

(D) The ARRAY statement has the following syntax: ARRAY array-name {subscript} <$> <(initial-value-list)>; In this example, the array name is SCORE. The subscript describes the number and arrangement of elements in the array. In this instance, the asterisk is used which specifies that SAS is to determine the subscript by counting the variables in the array rather than you specifically identifying it. The array-elements specify the names of the elements that make up the array. If they happen to possess a consecutively named format, a hyphen can be used for a short-hand listing method. So, A4-A10 creates seven separate elements and A25 is the additional eighth.

8.

Advanced SAS 9 Exam Prep: A00-212 | Prepared for Sritej Gunta (D) The first thing SAS does is evaluate the FROM clause. It grabs the table (or data set) called ONE, and then using the SELECT clause it extracts the variables REP and AREA from that table. It then creates a variable called TOTAL by implementing the COUNT summary function which basically just counts the number of rows. Once they’ve been grouped, you can see there is one row that possesses the attributes JONES and EAST, two rows that have JONES and NORTH, and three rows of JONES and WEST. Note: The COST variable was left out from the GROUP BY clause. If it had been included, all of the values for TOTAL would have been 1 except for JONES and WEST. There’d be two listings for JONES and WEST because of the differing COST variable.

9.

(B) You can use the DATASETS procedure to alter the name of an established data set using the CHANGE statement. The syntax is as follows: PROC DATASETS LIBRARY=libref ; CHANGE old-data-set-name = new-data-set-name; QUIT; Note: The RENAME statement allows you to rename variables as a file management task using the DATASETS procedure. This method does not require DATA step processing. The MODIFY statement changes the attributes of a SAS file and, through the use of subordinate statements, the attributes of variables in the SAS file.

10.

(C) The %LET statement allows you to create your own macro variable and assign a value to it. In this example, the macro variable VALUE is created with a value of 9, and the macro variable ADD is created with a value of 5. The %EVAL function evaluates integer arithmetic or logical expressions. However, if an operation results in a noninteger value, %EVAL truncates the value to an integer. Using standard arithmetic, we know that 9 divided by 5 equals 1.8. As was previously mentioned, SAS truncates noninteger values. It does not round them. So, the 1.8 is truncated to an integer value of 1.

11.

Which one of the following SORT procedure options eliminates identical, consecutive observations? A. B. C. D.

12.

DISTINCT NODUPKEY NODUP UNIQUE

The following SAS program is submitted: DATA NEW (BUFSIZE=6144 BUFNO=4); SET OLD; RUN; What is the difference between the usage of BUFSIZE= and BUFNO= options? A. B. C. D.

13.

BUFSIZE= BUFSIZE= BUFSIZE= BUFSIZE=

specifies specifies specifies specifies

the the the the

size size size size

of of of of

the the the the

The following SAS code is submitted: %macro houses(dsn=houses, sub=RANCH); DATA &dsn; SET sasuser.houses; IF style=’&sub’; RUN; %mend; %houses(sub=SPLIT)

output buffer in kilobytes; BUFNO= specifies the number of output buffers input buffer in bytes; BUFNO= specifies the number of input buffers input buffer in kilobytes; BUFNO= specifies the number of input buffers output buffer in bytes; BUFNO= specifies the number of output buffers

Advanced SAS 9 Exam Prep: A00-212 | Prepared for Sritej Gunta %houses(dsn=ranch) %houses(sub=TWOSTORY) Which of the following is the value of the automatic macro variable SYSLAST? A. B. C. D.

14.

WORK.HOUSES work.houses WORK.RANCH work.ranch

The data set SASHELP.CLASS is given below: NAME -----Mary Philip Robert Ronald

AGE --15 16 12 15

The following SAS program is submitted: %LET VALUE=Philip; PROC PRINT DATA=SASHELP.CLASS; < insert WHERE statement here > RUN; Which WHERE statement successfully completes the program and produces a report? A. B. C. D.

15.

WHERE WHERE WHERE WHERE

UPCASE(NAME)=UPCASE(&VALUE); UPCASE(NAME)=”UPCASE(&VALUE)”; UPCASE(NAME)=”%UPCASE(&VALUE)”; UPCASE(NAME)=%UPCASE(&VALUE);

The non-indexed SAS data set TEMP is given below: X P P A A R R R

Y -52 45 13 56 34 12 78

The following SAS program is submitted: PROC PRINT DATA=TEMP; < insert BY statement here > RUN; Which BY statement completes the program, creates a listing report that is grouped by X, and completes without errors? A. B. C. D.

16.

BY BY BY BY

X; DESCENDING X; X GROUPED; X NOTSORTED;

The following SAS program is submitted: %LET A=cat; %MACRO ANIMAL(A=frog); %LET A=bird; %MEND; %ANIMAL(A=pig) %PUT A is &A;

Advanced SAS 9 Exam Prep: A00-212 | Prepared for Sritej Gunta Which one of the following is written to the SAS log? A. B. C. D.

17.

A A A A

is is is is

bird cat &A pig

The following SAS program is submitted: %MACRO CHECK(NUM=4); %LET RESULT=%EVAL(&NUM GT 5); %PUT Result is &RESULT; %MEND; %CHECK(NUM=10) What is written to the SAS log? A. B. C. D.

18.

Result Result Result Result

is is is is

10 GT 5 TRUE 1 0

The following SAS program is submitted: %MACRO CHECK(NUM=4); %LET RESULT=%SYSEVALF(&NUM+0.5); %PUT Result is &RESULT; %MEND; %CHECK(NUM=10) What is written to the SAS log? A. B. C. D.

19.

is 10+0.5 is is 10 is 10.5

Which one of the following is an advantage of creating and using a SAS DATA step view? A. B. C. D.

20.

Result Result Result Result

It It It It

works quickly through multiple passes of the data always accesses the most current data can store an index is useful when the underlying data file structure changes

The following SAS program is submitted: DATA TEMP; ARRAY POINTS{2,3} (10,15,20,25,30,35); RUN; What impact does the ARRAY statement have in the Program Data Vector (PDV)? A. B. C. D.

11.

The variables named POINTS10, POINTS15, POINTS20, POINTS25, POINTS30, and POINTS35 are created in the PDV The variables named POINTS1, POINTS2, POINTS3, POINTS4, POINTS5, and POINTS6 are created in the PDV The variables named POINTS11, POINTS12, POINTS13, POINTS21, POINTS22, and POINTS23 are created in the PDV No variables are created in the PDV

(C) The NODUP option causes PROC SORT to compare all variable values for an observation to the previous one written to the output data set. Therefore, when using the NODUP option, the data set must be sorted by enough variables to ensure that the observations are in the correct order to remove all duplicates.

Advanced SAS 9 Exam Prep: A00-212 | Prepared for Sritej Gunta Note: The NODUPKEY option checks for and eliminates observations with duplicate BY values. If you specify this option, then PROC SORT compares all BY values for each observation to the ones for the previous observation that is written to the output data set. If an exact match is found, then the observation is not written to the output data set.

12.

(D)

13.

(A) SYSLAST is an automatic SAS variable that names the most recently created SAS data set, in the form LIBREF.NAME. This value is always stored in all capital letters. If no data set has been created, the value is _NULL_. In this example, the %MACRO statement assigns two keyword parameters (dsn and sub) with the two values (houses and RANCH, respectively). These macro variables are the defaults for the macro program houses. The first time the macro houses is called, it executes with the macro variable dsn having the value houses, and the macro variable sub has its default value replaced with SPLIT. Hence, the most recently executed data set is WORK.HOUSES. The macro houses is called a second time. This time the dsn macro variable has a substituted value of ranch, and the macro variable sub has a value of RANCH. Hence, the most recently executed data set is WORK.RANCH. The macro houses is called a third time. In this instance, the macro variable dsn has a value of houses, and the macro variable sub has a value of TWOSTORY. Hence, the most recently executed data set, and the answer, is WORK.HOUSES. Note: You may view automatic SAS variables values by evoking the SYMBOLGEN system option. For example: OPTIONS SYMBOLGEN;

14.

(C) The UPCASE function copies a character argument, converts all lowercase letters to uppercase letters, and returns the altered value as a result. When you’re attempting to apply the UPCASE function to a macro variable, you must precede the enclosed macro variable in parentheses with the %UPCASE function.

15.

(D) Option (A) returns an error because in the data set TEMP, it was not already sorted by the variable X in ascending order. Option (B) returns an error as well because the data set TEMP was not previously sorted in ascending order. Option (C) returns an error because GROUPED is not an option. SAS assumes it’s a variable that it can’t find. The NOTSORTED option allows you to group observations in a data set by the formatted values of the BY variables without requiring that the data be sorted or indexed.

16.

(B) In this example, you have the creation of a global macro variable A with a value of cat created at the beginning. There’s also a macro program called ANIMAL with a keyword parameter attached. When the macro ANIMAL is called, it has a new keyword parameter assigned: variable A with the value pig. However, when this parameter is applied to the macro, the value of variable A is immediately overwritten with bird. Therefore, you have a local macro variable A which resolves to bird within the macro program, and a global macro variable A that resolves to cat outside the macro program.

17.

(C) The macro program CHECK creates a local macro variable NUM with a value of 4. The macro call %CHECK(NUM=10) resolves the variable NUM with a value of 10 when initialized. The %EVAL function evaluates integer arithmetic or logical expressions. In this instance, as a result of the %Let statement in the macro program CHECK, it is being asked to perform a logical expression to check if the value of &NUM (which is 10) is greater than 5. If the result of the logical expression is true, SAS returns a value of 1. If it is false, SAS returns a value of 0. Quite clearly, 10 is greater than 5, so SAS returns a value of 1. Note: If the macro call %CHECK(NUM=10) had omitted the additional element (i.e. simply %CHECK), then SAS would have evaluated 4 against 5 and returned a value of 0.

18.

Advanced SAS 9 Exam Prep: A00-212 | Prepared for Sritej Gunta (D) The macro program CHECK creates a local macro variable NUM with a value of 4. The macro call %CHECK(NUM=10) resolves the variable NUM with a value of 10 when initialized. The %SYSEVALF function is the only macro function that can evaluate arithmetic or logical expressions using floating point or missing values. As you can see from the expression &NUM+0.5, floating point is necessary. When the macro call %CHECK(NUM=10) is initialized, it resolves the macro variable NUM to 10. So, the expression is evaluated as 10+.05. Therefore, the variable RESULT resolves to 10.5.

19.

(B)

20.

(B) The ARRAY statement syntax is as follows: ARRAY array-name {subscript} <$> <(initial-value-list)>; In this example, the array-name is POINTS, but there are no array-elements explicitly named. When this is the case, SAS sequentially names each variable with the array-name. POINTS1 … POINTSn.

21.

The following SAS program is submitted: DATA NEW (BUFNO=4); SET OLD (BUFNO=4); RUN; Why are the BUFNO options used? A. B. C. D.

22.

To To To To

reduce reduce reduce reduce

memory usage the amount of data read network traffic the number of I/O operations

The following SAS program is submitted: %MACRO TEST(VAR); %LET JOBS=BLACKSMITH WORDSMITH SWORDSMITH; %LET TYPE=%INDEX(&JOBS,&VAR); %MEND; %TEST(SMITH) Which one of the following is the resulting value of the macro variable TYPE? A. B. C. D.

23.

Null 0 6 3

At the start of a new SAS session, the following program is submitted: %MACRO ONE; DATA _NULL_; CALL SYMPUT(‘PROC’,’MEANS’); RUN; PROC &PROC DATA=SASHELP.CLASS; RUN; %MEND; %ONE() What is the result? A. The program fails to execute because PROC is a reserved keyword B. The macro variable PROC is stored in the local symbol table

Advanced SAS 9 Exam Prep: A00-212 | Prepared for Sritej Gunta C. The macro variable PROC is stored in the global symbol table D. The macro variable PROC is stored in the SAS catalog WORK.SASMCR

24.

Which one of the following displays the definition of a stored SQL procedure view in the SAS log? A. B. C. D.

25.

Which SAS procedure changes the name of a permanent format for a variable stored in a SAS data set? A. B. C. D.

26.

ECHOVIEW option EXPANDVIEW option DESCRIBE VIEW statement VALIDATE VIEW statement

FORMAT DATASETS REGISTRY MODIFY

The SAS data set ONE contains the variables X, Y, Z, and W, and it is sorted in ascending order by the X variable. The following SAS program is submitted: PROC TRANSPOSE DATA=ONE OUT=TRANS NAME=NEW; BY X; VAR Y; RUN; What are the names of all of the columns created by the TRANSPOSE procedure? A. B. C. D.

27.

X, X, Y, X,

Y, and _COL1_ and Y only and COL1 only and COL1 only

Which one of the following SAS integrity constraint types ensures that a specific set or range of values are the only values in a variable? A. B. C. D.

28.

NEW, NEW, NEW, NEW,

CHECK DISTINCT UNIQUE FORMAT

The following SAS program is submitted: DATA NEW; DO i=1,2,3; NEXTFILE=COMPRESS(“MARCH” || i || “.dat”); INFILE ABC FILEVAR=NEXTFILE END=EOF; END; DO UNTIL (EOF); INPUT DEPT $ SALES; END; RUN; What is the purpose of the FILEVAR= option on the INFILE statement? A. B. C. D.

It It It It

names names names names

the the the the

variable variable variable variable

NEXTFILE, NEXTFILE, NEXTFILE, NEXTFILE,

whose whose whose whose

values point to an aggregate storage location change in value causes an INFILE statement to open a new input file value is output to the SAS data set NEW value is a SAS file reference


The following SAS program is submitted: %MACRO ONE(INPUT); %TWO; %PUT THE VALUE IS &DATE; %MEND; %MACRO TWO; DATA _NULL_; CALL SYMPUT(‘DATE’,’12SEP2008’); RUN; %MEND; %LET DATE=31DEC2006; %ONE(&DATE) What is the result when the %PUT statement executes? A. B. C. D.

30.

A A A A

macro macro macro macro


DATE DATE DATE DATE

with with with with

the the the the

value value value value

12SEP2008 12SEP2008 12SEP2008 31DEC2006

The SAS data set ONE is given below: LEVEL ----1 2 3 2 1 2 3 2 3 1

AGE --10 20 20 10 10 30 10 20 30 10

The following SAS program is submitted: PROC SQL; SELECT LEVEL, MAX(AGE) AS MAX FROM ONE GROUP BY LEVEL HAVING MAX(AGE) > (SELECT AVG(AGE) FROM ONE); QUIT; Which one of the following reports is generated? A. LEVEL AGE 2 20 3 20 B. LEVEL AGE 2 30 3 30 C. LEVEL MAX 2 20 3 30 D. LEVEL MAX 2 30 3 30

is is is is

retrieved retrieved retrieved retrieved

from from from from

the the the the

local symbol table for the ONE macro local symbol table for the TWO macro global symbol table global symbol table

21.

22.

Advanced SAS 9 Exam Prep: A00-212 | Prepared for Sritej Gunta (D) The BUFNO= system or data set option controls the number of buffers that are available for reading or writing a SAS data set. By increasing the number of buffers, you can control how many pages of data are loaded into memory with each I/O transfer.

(C) The %INDEX function enables you to determine the position of the first character of a string within another string. The syntax for the %INDEX function is as follows: %INDEX(source,string) Both source and string are character strings or text expressions that can include constant text, macro variable references, macro functions, or macro calls. In this example, the macro variable JOBS acts as the source and resolves to BLACKSMITH WORDSMITH SWORDSMITH. The macro variable VAR acts as the string and resolves to SMITH once the macro call %TEST(SMITH) is initialized. So, what SAS does is begin scanning from left to right within the source variable (BLACKSMITH WORDSMITH SWORDSMITH) until it finds the identified string variable (SMITH). At that point it marks its location where the string variable first appears (6). Note: You can print this identifying location to the SAS log if you include the following statement within the macro program: %PUT &VAR is at position &TYPE.; Also, if you change the element in the macro call from SMITH to SWORD, your new value will be 22.

23.

(C) When you use the DATA _NULL_ statement, SAS processes the DATA step without writing observations to a data set. Using the DATA _NULL_ statement can considerably increase program efficiency. The SYMPUT routine assigns DATA step information to a macro variable. The syntax is as follows: CALL SYMPUT(argument-1,argument-2); argument-1 specifies a character expression that identifies the macro variable that is assigned a value. If the macro variable does not exist, the routine creates it. argument-2 specifies a character constant, variable, or expression that contains the value that is assigned. In this instance, the macro variable PROC is created, and it is assigned a value of MEANS. So, when the macro ONE is called via %ONE(), the macro variable PROC resolves to MEANS and executes the MEANS procedure. The macro ONE is created without any additional parameters (neither KEYWORD nor POSITIONAL parameters). When the macro ONE is called via %ONE(), the parentheses indicate that the default parameters for the macro are to be used. However, because none were used, it is moot. This method is synonymous with simply using %ONE; Typically, macro variables that are created within a macro are stored in the local symbol table and exist only during the execution of that macro that defines the variable. However, in this instance the macro variable is being used to execute an active report (the MEANS procedure), and is therefore passed to the global symbol table. Note: You can verify this after running the PROC SQL code by viewing the DICTIONARY.MACROS table. PROC SQL; SELECT * FROM DICTIONARY.MACROS; QUIT;

24.

(C) The DESCRIBE VIEW statement displays a definition of a view in the SAS log.

25.

(B) The DATASETS procedure, when used in conjunction with the FORMAT statement, permanently assigns, changes, and removes variable formats in the SAS data set specified in the MODIFY statement. The syntax is as follows: FORMAT variable-1 <…variable-n >; Where variable-1 <…variable-n> specifies one or more variables whose format you want to assign, change or remove. If you want to disassociate a format with a variable, list the variable last in the list with no format following.


(D) The PROC TRANSPOSE statement creates an output data set by restructuring values in a SAS data set, transposing selected variables into observations. The syntax is as follows: PROC TRANSPOSE ; BY variable-1 ... variable-n ; VAR variable(s); RUN; If an output-data-set does not exist, PROC TRANSPOSE creates it by using the DATA n naming convention. Also, if you omit the VAR statement, the TRANSPOSE procedure transposes all of the numeric variables in the input data set that are not listed in another statement. And finally, you must list all character variables in a VAR statement if you want to transpose them. So, in this example, we are transposing the data set ONE. The NAME= option specifies the name of the variable in the output data set that contains the name of the variable that is being transposed to create the current observation (if it were omitted, the default name would be _NAME_). The VAR statement names one or more variables to transpose. With the NAME= option and VAR statement combined, you get a column named NEW with all the values in that column being Y. Because the output data set is being sorted with a BY statement, all the variables included with that BY statement will also be in the output data set. Therefore, X is also a column name. Finally, the remaining variables listed are those of the listed variables. Here, the only transposed variable is Y and because the PREFIX= option was not used, the default column name is COL1.

27.

(A) The integrity constraint CHECK ensures that a specific set or range of values are the only values in a column. It can also check the validity of a value in one column based on a value in another column within a row.

28.

(B) The FILEVAR= option enables you to dynamically change the currently opened input file to a new input file. INFILE file-specification FILEVAR=variable; Where FILEVAR=variable names a variable whose change in value causes the INFILE statement to close the current input file and open a new input file, and variable contains a character string that is a physical filename. When you use an INFILE statement with the FILEVAR= option, the file specification is a placeholder, not an actual filename or a fileref that had been assigned previously to a file. SAS uses this placeholder for reporting processing information to the SAS log. The file specification must conform to the same rules as a fileref.

29.

(C) If you define a local macro variable and a global macro variable with the same name, the macro facility uses the value of the local variable during the execution of the macro that contains that local variable. When the macro that contains the local variable is not executing, the macro facility uses the value of the global variable. In this example, you can clearly see that the %LET statement creates a global macro variable called DATE with a value of 31DEC2006. The CALL SYMPUT function in macro TWO also creates global a macro variable called DATE but with a value of 12SEP2008. However, this only occurs if the macro program TWO is called. As you can see, the macro program ONE is called with a macro variable parameter &DATE that resolves to 31DEC2006. So, when the macro program ONE executes, the initial value of the global macro variable DATE is 31DEC2006. However, the first line of code within the ONE macro program calls the macro program TWO which then executes within the ONE macro program and the CALL SYMPUT function creates a global macro variable called DATE with a value of 12SEP2008 which overwrites the previous DATE macro variable value. This new value is then displayed in the PUT statement. Note: If the macro program TWO hadn’t been called (that is, removing %TWO; from macro program ONE), the %PUT statement would have displayed: THE VALUE IS 31DEC2006. Additionally, if the %PUT statement had been placed before the call of macro program TWO (%TWO;), then the %PUT statement would have printed 31DEC2006 before the macro variable DATE had its value overwritten.


(D) The first thing to notice is the SELECT clause. It extracts the variable LEVEL and creates the variable MAX. Hence, you can immediately eliminate options (A) and (B). A HAVING clause further refines a PROC SQL query by working with the GROUP BY clause to restrict the groups that are displayed to the output, based on one or more specific conditions. In of 2, in

31.

When reading a SAS data file, what does the NOBS= option on the SET statement represent? A. B. C. D.

32.

this example, the HAVING clause specifies only groups which have a maximum AGE value greater than the average AGE the entire table are to be displayed. The average AGE for the entire table is 17. The maximum values for groups 1, and 3 are 10, 30, and 30 (respectively). Therefore, group 1 does not qualify and only groups 2 and 3 are displayed the output.

A A A A


that that that that

represents represents represents represents

the current observation number a flag indicating the end of the file the total number of observations in the input data set(s) the total number of observations in the output data set(s)

The following SAS program is submitted: %LET VALUE=0; %LET VALUE2=9; %LET NEWVAL=%EVAL(&VALUE/&VALUE2); Which one of the following is the resulting value of the macro variable NEWVAL? A. B. C. D.

33.

Which one of the following is the purpose of the IDXNAME= data set option? A. B. C. D.

34.

9 2 Null 0

It It It It

instructs instructs instructs instructs

SAS SAS SAS SAS

to to to to

use any available index for WHERE processing use a specific index for WHERE processing name and store a specific index store an index in a particular location

The following SAS program is submitted: PROC CONTENTS DATA=TESTDATA.ONE; RUN; Which one of the following SQL statements produces similar information about the column attributes as the above CONTENTS procedure? A. PROC SQL; SHOW TABLE TESTDATA.ONE; QUIT; B. PROC SQL; SHOW TESTDATA.ONE; QUIT; C. PROC SQL; DESCRIBE TABLE TESTDATA.ONE; QUIT; D. PROC SQL; DESCRIBE TESTDATA.ONE; QUIT;


The following SAS program is submitted: %LET LIB=%UPCASE(sasuser); PROC SQL; SELECT NVAR FROM DICTIONARY.TABLES WHERE LIBNAME=”&LIB”; QUIT; Given that several SAS data sets exist in the SASUSER library, which one of the following is generated as output? A. B. C. D.

36.

A syntax error in the log A report showing the number of columns in each table in SASUSER No result set A report showing the names of each table in SASUSER

Which one of the following SAS programs uses the most amount of memory resources for output buffers? A. DATA NEW (BUFSIZE=2000 BUFNO=3); SET TEMP; RUN; B. DATA NEW (BUFSIZE=4000 BUFNO=1); SET TEMP; RUN; C. DATA NEW (BUFSIZE=1000 BUFNO=5); SET TEMP; RUN; D. DATA NEW (BUFSIZE=1000 BUFNO=2); SET TEMP; RUN;

37.

The SAS data sets CLASS1 and CLASS2 are given below: CLASS1 NAME COURSE ------ -----Lauren MATH1 Patel MATH1 Chang MATH1 Chang MATH3

CLASS2 NAME COURSE ------ -----Smith MATH2 Farmer MATH2 Patel MATH2 Hiller MATH2

The following SAS program is submitted: PROC SQL; SELECT NAME FROM CLASS1 < insert SQL set operator here > SELECT NAME FROM CLASS2; QUIT; The following output is desired: NAME -----Chang Chang Lauren Which SQL set operator completes the program and generates the desired output? A. EXCEPT ALL B. INTERSECT ALL

Advanced SAS 9 Exam Prep: A00-212 | Prepared for Sritej Gunta C. UNION ALL D. OUTER UNION ALL

38.

Which one of the following statements is true regarding a SAS DATA step view? A. B. C. D.

39.

It It It It

contains a partially compiled DATA step allows write capabilities contains global statements contains data and a descriptor portion

The SAS data set WORK.TEMPDATA contains the variables FMTNAME, START, and LABEL and it consists of 10 observations. The following SAS program is submitted: PROC FORMAT CNTLIN=WORK.TEMPDATA; RUN; What is the result of submitting the FORMAT procedure? A. B. C. D.

40.

An ERROR message is written to the SAS log because the program is incomplete No formats are created in this step All formats created will be stored in the WORK.TEMPDATA SAS data set It uses the WORK.TEMPDATA SAS data set as input to create the format

The SAS data sets ONE and TWO are given below: ONE NUM COUNTRY --- ------1 CANADA 2 FRANCE 3 GERMANY 4 BELGIUM 5 JAPAN

TWO NUM CITY --- ------3 BERLIN 5 TOKYO

The following SAS program is submitted: PROC SQL; SELECT COUNTRY FROM ONE WHERE NOT EXISTS (SELECT * FROM TWO WHERE ONE.NUM=TWO.NUM); QUIT; Which of the following reports is generated? A. COUNTRY CANADA FRANCE BELGIUM B. COUNTRY FRANCE BELGIUM C. COUNTRY GERMANY JAPAN D. COUNTRY CANADA FRANCE GERMANY


(C) The NOBS= option creates and names a temporary variable whose value is usually the total number of observations in the input data set or data sets. If more than one data set is listed in the SET statement, NOBS= the total number of observations in the data sets that are listed. The number of observations includes those observations that are marked for deletion but are not yet deleted.

32.

(D) A numerator is allowed to take on the value of zero in a fraction. Any legal fraction (denominator not equal to zero) with a numerator equal to zero has an overall value of zero. Note: Null is synonymous with missing. If the zero had instead been in the denominator, the answer would have instead been Null and an error would have been written to the log. ERROR: Division by zero in %EVAL is invalid.

33.

(B) The IDXNAME= data set option directs SAS to use a specific index to match the conditions of a WHERE expression. Note: DO not confuse with the IDXWHERE= option which specifies whether or not SAS should use an index to process the WHERE expression, no matter which access method SAS estimates is faster.

34.

(C) The DESCRIBE TABLE statement lists all indexes for one or more tables that you specify, along with other information about the table(s).

35.

(B) The macro variable LIB is assigned the value sasuser. The %UPCASE function capitalizes all letters of the value, which is essential because that’s how it is stored within SAS, and encloses it in quotation marks. So, the library name has been specified. The variable of note is NVAR, which specifies the number of columns (or variables) for each table in the SASUSER library.

36.

(A) The BUFNO= option specifies the number of buffers to be used. The BUFSIZE= option controls the page size of an output data set. To calculate which option requires the most amount of memory resources, simply multiply the value for BUFNO by the value for BUFSIZE.

37.

(A) The set operator EXCEPT does both of the following: (1) It selects unique rows from the first table (the table specified in the first query) that are not found in the second table (the table specified in the second query). (2) It overlays columns. The keyword ALL follows the operator and tells SAS to make only one pass through the data and does not remove duplicate rows. Note: If the data set CLASS2 contained a NAME variable with the value CHANG, the output table would have CHANG and LAUREN because one CHANG value in CLASS2 would cancel one of the two CHANG values in CLASS1.

38.

(A) A DATA step view contains a partially compiled DATA step program that can read data from a variety of sources. A DATA step view can be created only in a DATA step. A DATA step view cannot contain global statements, host-specific data set options, or most host-specific FILE and INFILE statements. Also, a DATA step view cannot be indexed or compressed.

39.

(D) You can create a format from a SAS data set that contains value information (called a control data set). To do this, you use the CNTLIN= option to read the data and create the format. The syntax is as follows: PROC FORMAT LIBRARY=libref.catalog CNTLIN=SAS-data-set; Where libref.catalog is the name of the catalog in which you want to store the format and SAS-data-set is the name of the SAS data set that you want to use to create the format.

Advanced SAS 9 Exam Prep: A00-212 | Prepared for Sritej Gunta The data set that is used to create a format with the CNTLIN= option must have the variables FmtName, Start, and Label. If a range is specified, it must also include the variable End.

40.

(A) The first thing you should notice is that the SELECT clause will only display one variable (or column), COUNTRY. The second is that the program involves a subquery. A subquery, or inner query, is a query-expression that is nested as part of another query-expression. In this instance, the subquery is used in conjunction with the WHERE NOT EXISTS expression. This expression calls for all observations to be output where the value of the variables NUM in data sets ONE and TWO are not equal to each other. So, CANADA, FRANCE, and BELGIUM.

41.

Given the following SAS program: PROC SQL; SELECT PRODUCT, TYPE, SUM(SALES) AS REVENUE FROM ONE GROUP BY PRODUCT, TYPE; QUIT; Which one of the following clauses should be added to the program to sort the output by PRODUCT and decreasing REVENUE? A. B. C. D.

42.

ORDER BY 1, 3 DESC ORDERBY PRODUCT, REVENUE DESC ORDER BY 1, 3 ORDER BY PRODUCT, DESC REVENUE

The SAS data set ONE is given below: REP ----SMITH SMITH JONES SMITH JONES JONES JONES SMITH JONES JONES

COST ---200 400 100 600 100 200 400 800 100 300

The following SAS program is submitted: PROC SQL; SELECT REP, AVG(COST) AS AVERAGE FROM ONE GROUP BY REP HAVING AVG(COST) > (SELECT AVG(COST) FROM ONE); QUIT; Which one of the following reports is generated? A. REP AVERAGE SMITH 320 B. REP AVERAGE JONES 320 C. REP AVERAGE SMITH 500 D. REP AVERAGE JONES 200


The following SAS program is submitted: %MACRO TEST(VAR); PROC PRINT DATA=SASUSER.CLASS; WHERE AGE > &VAR; RUN; %MEND; Which type of parameter is the macro variable VAR? A. B. C. D.

44.

Positional Command Keyword Default

The following SAS program is submitted: %LET VAR=CHICAGO, 1; DATA A; VAR=’NEW YORK, 2’; NEWVAR=%SCAN(&VAR,2,%STR()); RUN; Which one of the following explains why the program fails to execute? A. B. C. D.

45.

The KEEP The KEEP created The KEEP The KEEP

statement selects the variables read from the input data set(s) statement applies only to the first data set created within the same DATA step if more than one data set statement is available in both the DATA and PROC steps statement applies to all data sets created within the same DATA step

Which one of the following is the purpose of the REUSE=YES option in a compressed SAS data set? A. B. C. D.

49.

User-defined macro variables only Macros stored in the autocall macro library only Both user- and system-defined macro variables System-defined macro variables

Which one of the following is true regarding the KEEP statement? A. B. is C. D.

48.

SOURCE MACRO SYMBOLGEN SOURCE2

The DICTIONARY.MACROS table stores information about which of the following? A. B. C. D.

47.

%SCAN function has too many arguments macro variable VAR does not get created properly %STR() is invalid syntax %SCAN function does not exist

Which one of the following options displays the value of a macro variable in the SAS log? A. B. C. D.

46.

The The The The

It It It It

allows new observations to be inserted wherever enough free space exists specifies that a new empty data set with a given name replaces an existing data set with the same name allows users to update the same SAS data set concurrently temporarily compresses observations in a SAS data set

The SAS data set ONE is given below:

Advanced SAS 9 Exam Prep: A00-212 | Prepared for Sritej Gunta GROUP ----A B C

SUM --765 123 564

The following SAS program is submitted: DATA _NULL_; SET ONE; CALL SYMPUT(GROUP,SUM); RUN; Which one of the following is the result when the program finishes execution? A. B. C. D.

50.

Macro Macro Macro Macro


GROUP C has GROUP C has

has a value of 564 a value of 1452 has a value of 1452 a value of 564

The following SAS program is submitted: FILENAME SALES (‘EXTERNAL-FILE1’ ‘EXTERNAL-FILE2’); DATA NEW; INFILE SALES; INPUT DATE DATE9. COMPANY $ REVENUE; RUN; Which one of the following is the result of including the FILENAME statement in this program? A. The FILENAME B. The FILENAME C. The FILENAME into one record D. The FILENAME

41.

statement associates SALES with EXTERNAL-FILE2 followed by EXTERNAL-FILE1 statement associates SALES with EXTERNAL-FILE1 followed by EXTERNAL-FILE2 statement reads record 1 from EXTERNAL-FILE1, reads record 1 from EXTERNAL-FILE2, and combines them statement produces and ERROR message in the SAS log

(A) The output table will contain three columns: PRODUCT, TYPE, and the created REVENUE column. When referencing these columns, they can also be identified by their position in the SELECT clause. So, PRODUCT=1, TYPE=2, and REVENUE=3. To sort the output data by PRODUCT and decreasing REVENUE, you can order by 1 to indicate PRODUCT and 3 to indicate REVENUE. To list REVENUE by decreasing values, place the keyword DESC after the column name. Note: The DESC keyword in SQL performs the same task as the DESCENDING keyword in the BY statement of a SORT procedure. However, in the SORT procedure, the DESCENDING keyword comes before the variable you intend to reorder. Additionally, option (B) would have also been correct had the ORDER BY clause been written properly.

42.

(C) Note that the two columns in the output table will be REP and the created column AVERAGE. The GROUP BY clause places REP into the number of unique groups associated with the REP column (SMITH and JONES). A HAVING clause further refines a PROC SQL query by working with the GROUP BY clause to restrict the groups that are displayed to the output, based on one or more specific conditions. In this example, the HAVING clause compares 320) against a subquery of the average COST will then only output the groups which have JONES group does not meet this requirement.

43.

the average value of the COST column of the entire data set ONE (which is of each group (SMITH and JONES) which are 500 and 200, respectively. It an average cost greater than the average cost of the entire table. The Therefore, SMITH is the only group that is output.

(A) Parameters in a macro take the following syntax:

Advanced SAS 9 Exam Prep: A00-212 | Prepared for Sritej Gunta %MACRO macro-name(parameter-1<,...,parameter-n>); text %MEND ; A positional macro parameter variable is one that can quite simply be referenced by its position in the parameter list. In this example, we have the macro statement %MACRO TEST(VAR);. The macro variable VAR is in the first position because it is the only listed variable. We could easily list multiple macro variables. For example, %MACRO TEST(VAR,TYPE,WEIGHT); where the macro variable VAR is position 1, TYPE is position 2, and WEIGHT is position 3. Note: This differs from keyword parameters because the order in which positional parameters are listed is important, and there are no assigned keywords. For example, %MACRO TEST(X1=VAR,X2=TYPE,X3=WEIGHT); is an example of keyword parameter macro variables where X1-X3 are the keywords that can be referenced in a macro which assigns their respected values.

44.

(A) This is a rather tricky question. The syntax for the %SCAN function is as follows: %SCAN (argument, n<,delimiters>) Where argument consists of constant text, macro variable references, macro functions, or macro calls, and n is an integer or a text expression that yields an integer, which specifies the position of the word to return. If n is greater than the number of words in argument, the function returns a null string. Lastly, the delimiters specify an optional list of one or more characters that separate “words” or test expressions that yield one or more characters. As you can see, the %LET statement creates a macro variable VAR with the entire value CHICAGO, 1. This macro variable is called upon in the data step. Once it’s inserted into the %SCAN function, the %SCAN function reads too many arguments because it is read as %SCAN(CHICAGO, 1,2,%STR()).

45.

(C) When you submit a macro variable reference, the macro processor resolves the reference and passes the value directly back to the input stack. You can use the SYMBOLGEN system option to monitor the value that is substituted for a macro variable reference.

46.

(C) DICTIONARY tables are special, read-only PROC SQL tables or views. They retrieve information about all the SAS libraries, SAS data sets, SAS system options, and external files that are associated with the current SAS session. DICTIONARY.MACROS contains information about currently defined macro variables (both automatic and user-created).

47.

(D) The KEEP statement causes a DATA step to write only the variables listed in the KEEP statement to one or more data sets. The KEEP statement applies to all data sets that are created within the same DATA step. The KEEP statement is only available in the DATA step.

48.

(A) In a compressed data set, SAS appends new observations to the end of the data set by default. If you delete an observation within the data set, empty disk space remains in its place. However, it is possible to track and reuse free space within the data set when you delete or update observations. The SAS REUSE= system option and the REUSE= data set option specify whether or not SAS reuses space when observations are added to a compressed data set. If you set the REUSE= data set option to YES in a DATA statement, SAS tracks and reuses space in the compressed data set that is created in that DATA step. If you set the REUSE= system option to YES, SAS tracks and reuses free space in all compressed data sets that are created for the remainder of the current SAS session.

49.

(D) The CALL statement invokes a CALL routine. The syntax is as follows: CALL routine(parameter-1<, ...parameter-n>); The routine specifies the name of the SAS CALL routine that you want to invoke. The parameter is a piece of information to be passed to or returned from the routine. In this instance, we’re using the SYMPUT routine. The SYMPUT routine assigns DATA step information to a macro variable. The syntax is as follows:

Advanced SAS 9 Exam Prep: A00-212 | Prepared for Sritej Gunta CALL SYMPUT(argument-1,argument-2); argument-1 specifies a character expression that identifies the macro variable that is assigned a value. If the macro variable does not exist, the routine creates it. argument-2 specifies a character constant, variable, or expression that contains the value that is assigned. In this example, we are using a variable name GROUP, to create macro variables with the value of SUM for the same observation. What this does is create a macro variable A with a value of 765, a macro variable B with a value of 123, and a macro variable C with a value of 564. Note: When you use the SYMPUT routine to create a macro variable in a DATA step, the macro variable is not actually created and assigned a value until the DATA step is executed. Therefore, you cannot successfully reference a macro variable that is created with the SYMPUT routine by preceding its name with an ampersand within the same DATA step in which it is created.

50.

(B) The FILENAME statement can be used to associate a fileref with a single raw data file. However, as it is in this instance, it can also be used to concatenate raw data files by assigning a single fileref to the raw data files that you want to combine. The syntax is as follows: FILENAME fileref (‘external-file1’ ‘external-file2’ ... ‘external-filen’); Where fileref is any SAS name that is eight characters or fewer, and external-file is the physical name of an external file. The physical name is the name that is recognized by the operating environment. (i.e. C:/folders/myfolders/) When the fileref is specified in an INFILE statement, each raw data file that has been referenced is sequentially read into the data set using an INPUT statement. Note: If you are not familiar with the content and structure of your raw data files, you can use PROC FSLIST to view them.

51.

The SAS data sets ONE and TWO are given below: ONE COMMON -----A A A B C C

X -10 13 14 9 8 14

TWO COMMON Y ------ -A 1 A 3 B 4 B 2 C 5

The following SAS data step is submitted: DATA COMBINE; MERGE ONE TWO; BY COMMON; RUN; Which one of the following represents the data values stored in data set COMBINE? A. OBS 1 2 3 4 5 6 7

COMMON A A A B B C C

X 10 13 14 9 9 8 14

Y 1 3 3 4 2 5 5

B. OBS 1 2 3 4

COMMON X Y A 10 1 A 13 3 B 9 4 C 8 5


52.

C. OBS 1 2 3 4 5 6 7 8 9 10

COMMON A A A A A A B B C C

X 10 13 14 10 13 14 9 9 8 14

Y 1 1 1 3 3 3 4 2 5 5

D. OBS 1 2 3 4 5

COMMON A A B B C

X 10 13 14 9 8

Y 1 3 4 2 5

Which one of the following techniques concatenates data in SAS? A. B. C. D.

53.

DATA step with a COMBINE statement APPEND procedure INTERSECT operator in the SQL procedure DATA step with a MERGE statement

Which SET statement option names a variable that contains the number of the observation to read during the current iteration of the DATA step? A. B. C. D.

54.

The The The The

NOBS=POINTOBS POINT=POINTOBS OBS=POINTOBS KEY=POINTOBS

Which one of the following programs contains a syntax error? A. PROC SQL; SELECT PRODUCT.*, COST.UNITCOST, SALES.QUANTITY FROM PRODUCT P, COST C, SALES S WHERE P.ITEM=C.ITEM AND P.ITEM=S.ITEM; QUIT; B. PROC SQL; SELECT PRODUCT.*, COST.UNITCOST, SALES.QUANTITY FROM PRODUCT, COST, SALES WHERE PRODUCT.ITEM=COST.ITEM AND PRODUCT.ITEM=SALES.ITEM; QUIT; C. PROC SQL; SELECT P.*, C.UNITCOST, S.QUANTITY FROM PRODUCT AS P, COST AS C, SALES AS S WHERE P.ITEM=C.ITEM AND P.ITEM=S.ITEM; QUIT; D. PROC SQL; SELECT P.*, C.UNITCOST, S.QUANTITY FROM PRODUCT, COST, SALES WHERE PRODUCT.ITEM=COST.ITEM AND PRODUCT.ITEM=SALES.ITEM; QUIT;

55.

Which SQL procedure deletes rows from the data set CLASS?

Advanced SAS 9 Exam Prep: A00-212 | Prepared for Sritej Gunta A. PROC SQL; ALTER FROM CLASS DELETE WHERE AGE < (SELECT STOP_AGE FROM THRESHOLD); QUIT; B. PROC SQL; MODIFY TABLE CLASS DELETE WHERE AGE < (SELECT STOP_AGE FROM THRESHOLD); QUIT; C. PROC SQL; SELECT * FROM CLASS WHERE AGE < (SELECT STOP_AGE FROM THRESHOLD); QUIT; D. PROC SQL; DELETE FROM CLASS WHERE AGE < (SELECT STOP_AGE FROM THRESHOLD); QUIT;

56.

The following SAS program is submitted: PROC DATASETS LIB=TESTDATA; MODIFY ONE; LABEL NUM=’Number’; FORMAT NUM 4.; QUIT; Which one of the following SQL codes produces the same results as the above DATASETS procedure? A. PROC SQL; ALTER TABLE TESTDATA.ONE MODIFY NUM (FORMAT=4. LABEL=’Number’); QUIT; B. PROC SQL; ALTER TABLE TESTDATA.ONE MODIFY NUM FORMAT=4. LABEL=’Number’; QUIT; C. PROC SQL; MODIFY TABLE TESTDATA.ONE ALTER NUM FORMAT=4. LABEL=’Number’; QUIT; D. PROC SQL; MODIFY TABLE TESTDATA.ONE NUM FORMAT=4. LABEL=’Number’; QUIT;

57.

The following SAS program is submitted: %LET %LET %LET %LET %PUT

TEST=ONE; ONE=TWO; TWO=THREE; THREE=LAST; What displays is &&&&&TEST;

What is written to the SAS log? A. B. C. D.

What What What What

displays displays displays displays

is is is is

LAST ONE THREE TWO


The data set ONE is given below: NAME ----Hans Maria Jose Ariel

SALARY -----200 205 310 523

The following output is desired: SALARY -----200 205 310 523

BONUS ----20 20.5 31 52.3

The following SAS program is submitted: PROC SQL; < insert SQL clause here > FROM ONE; QUIT; Which SQL procedure clause completes the program and generates the desired output? A. B. C. D.

59.

SELECT SELECT SELECT SELECT

SALARY, SALARY, SALARY, SALARY,

SALARY*.10 SALARY*.10 SALARY*.10 SALARY*.10

COLUMN=’BONUS’ LABEL=’BONUS’ NAME=’BONUS’ VAR=BONUS

The following SAS program is submitted: %MACRO COLS1; NAME AGE; %MEND; %MACRO COLS2; HEIGHT WEIGHT; %MEND; PROC PRINT DATA=SASHELP.CLASS; < insert VAR statement here > RUN; Which VAR statement successfully completes the program and produces a report? A. B. C. D.

60.

VAR VAR VAR VAR

HEIGHT %COLS1 %COLS1 %COLS2

%COLS1; HEIGHT; %COLS2 HEIGHT; %COLS1;

The following SAS program is submitted: DATA TEMP; ARRAY POINTS{3,2} _TEMPORARY_ (10,20,30,40,50,60); SCORE=POINTS{2,1}; RUN; Which one of the following is the value of the variable SCORE in the data set TEMP? A. B. C. D.

51.

30 20 40 10

(A) A MERGE statement joins observations from two or more SAS data sets into a single observation.

Advanced SAS 9 Exam Prep: A00-212 | Prepared for Sritej Gunta The BY statement controls the operation of a SET, MERGE, MODIFY, or UPDATE statement in the DATA step and sets up special grouping variables. In this example, the BY statement joins the two data sets with the variable COMMON. So, in row one of both data sets, they each contain the common variable A. These overlap and the resulting, new record is “A 10 1”. The next row in each data set follows this as well. So, the second record in the new data set is “A 13 3”. The third one is not a match. There is a third “A” observation in data set ONE, but it is lacking in data set TWO. So, what SAS does is drag down the previous Y variable value to complete the record. Hence, “A 14 3”.

52.

(B) To concatenate data sets is basically to stack one data set on top of the other. The APPEND procedure adds the observations from one SAS data set to the end of another SAS data set. PROC APPEND does not process the observations in the first data set. It adds the observations in the second data set directly to the end of the original data set. The syntax is as follows: PROC APPEND BASE=base-SAS-data-set ; Where base-SAS-data-set names the SAS data set to which you want to append the observations. If this data set does not exist, then SAS creates it. At the completion of PROC APPEND, the value of base-SAS-data-set becomes the current (most recently created) SAS data set. SAS-data-set-to-append names the SAS data set that contains the observations to add to the end of the base data set. If you omit this option, then PROC APPEND adds the observations in the current SAS data set to the end of the base data set. FORCE forces PROC APPEND to concatenate the files in some situations in which the procedure would normally fail. Note: This differs from using a MERGE statement. A MERGE statement will join your data horizontally.

53.

(B) The POINT= option in the SET statement names a variable. You must use program statements to assign a value to this variable during execution of the DATA step, before execution of the SET statement. Also, the value of the POINT= variable should be a number that corresponds to an observation number in the input data set, and it should be different each time the SET statement executes.

54.

(D) To mitigate the time required to type long table names, you may assign abbreviated table aliases in the FROM clause. Typically, it’s considered good programming practice to use the AS keyword to assign you’re alias, but it’s not required. The reason why option (D) is the correct choice is because the SELECT statement attempts to references variables using a table alias where one was not assigned.

55.

(D) To delete some or all of the rows in a table, use the DELETE statement.

The syntax is as follows:

DELETE FROM table-name ; Table-name specifies the name of the table in which rows will be deleted, and WHERE is optionally used to specify an expression that subsets the rows to be deleted. If you want to delete only a subset of rows in the table, you must specify a WHERE clause or all rows in the table will be deleted. In this example, in order to perform successfully, the PROC SQL program abides by this logic and uses a subquery as part of the WHERE clause.

56.

(B) The MODIFY statement changes the attributes of a SAS file and, through the use of subordinate statements, the attributes of variables in the SAS file. The DATASETS procedure is applying a label to the NUM variable as well as changing its format without affecting any other aspect of the TESTDATA.ONE data set. The equivalent method using the SQL procedure involves the use of the ALTER TABLE clause.

57.

Advanced SAS 9 Exam Prep: A00-212 | Prepared for Sritej Gunta (D) This program involves what’s known as the “Forward Re-Scan Rule”. Whenever the macro processor encounters multiple ampersands or percentage signs preceding a token, the macro processor resolves two ampersands (&&) to one ampersand (&), and re-scans the reference. To re-scan a reference, the macro processor scans and resolves tokens from left to right from the point where multiple ampersands or percent signs are coded, until no more triggers can be resolved. In order to better grasp this, let’s evaluate our given problem in steps. %PUT What displays is &&&&&TEST; Here, we have five ampersands preceding the macro variable TEST. As has been said, two ampersands resolve to one. Once you’ve resolved two ampersands to one, imagine you drop that single ampersand to a level below your evaluation level for each pair. &&&&&TEST; So, two pair can be cancelled out and dropped down a level, but we are still left with &TEST which can be resolved to ONE. &&&&&TEST;  ONE If you’re able to evaluate as we’ve done, drop that resolved value (ONE) down to your row. &&ONE; Here, repeat the process as before. The two ampersands resolve to one ampersand and are dropped to a new level. However, this time there is not a single ampersand before the macro variable, so, it can’t be executed. &&ONE;  No execution So, once more, drop down your evaluated value (which remains ONE and repeat the process). &ONE; Here, there are no more multiple ampersands to resolve, so, you can simply resolve the macro variable itself. &ONE;  TWO Therefore, “What displays is TWO”.

58.

(B) The SAS data set option LABEL= specifies the label to be displayed for the column. Note: This method does not create a column name; it merely labels the heading. Without assigning a column name, SAS assigns the default _TEMA001. The code could have been written SELECT SALARY, SALARY*.10 AS PORTION LABEL=’BONUS’ and it would have created the column PORTION which possessed and displayed the label BONUS.

59.

(A) This is another example of a problem that needs particular attention. There are two macro programs: COLS1 and COLS2. The only thing within each program is a character string that gets printed when the program is called. Notice, though, that each character string in each macro program contains a semicolon. Each available answer option will attempt to call one or both macro programs which print the contents of each in place where they’re called. If you chose option (B), the semicolon, when printed, would cause an error. The result would look like: VAR NAME AGE; HEIGHT; and the additional semicolon is the root of the problem. The other options, (C) and (D), fail for the same reason. The only way that successfully executes is (A) because the macro program %COLS1 executes at the end of the VAR statement and SAS is able to ignore the additional semicolon. VAR HEIGHT NAME AGE;;

60.

(A) The ARRAY statement in this instance is an example of a multidimensional array. Its syntax is as follows: ARRAY array-name {rows,cols,…} <$> <(initial values)>; As you can see, the array is divided into three rows and two columns. The initial values are filled into that temporary array sequentially (Row1-Col1, Row1-Col2, Row2-Col1, etc.). The assignment statement creates the variable SCORE and it extracts a value from the temporary array. It goes to the second row, first column and extracts the value 30.


61.

The SAS data set ONE consists of five million observations and has 25 variables. Which one of the following SAS programs successfully creates three new variables, TOTREV, TOTCOST, and PROFIT and requires the least CPU time to be processed? A. DATA TWO; SET ONE; WHERE TOTREV > 1000; TOTREV=SUM(PRICE*QUANTITY); TOTCOST=SUM(FIXED,VARIABLE); PROFIT=SUM(TOTREV,TOTCOST); RUN; B. DATA TWO; SET ONE; TOTREV=SUM(PRICE*QUANTITY); TOTCOST=SUM(FIXED,VARIABLE); PROFIT=SUM(TOTREV,TOCOST); IF TOTREV > 1000; RUN; C. DATA TWO; SET ONE; TOTREV=SUM(PRICE*QUANTITY); WHERE TOTREV > 1000; TOTCOST=SUM(FIXED,VARIABLE); PROFIT=SUM(TOTREV,TOTCOST); RUN; D. DATA TWO; SET ONE; TOTREV=SUM(PRICE*QUANTITY); IF TOTREV > 1000; TOTCOST=SUM(FIXED,VARIABLE); PROFIT=SUM(TOTREV,TOTCOST); RUN;

62.

What is the purpose of the SASFILE statement? A. B. C. D.

63.

requests requests requests requests

that that that that

a a a a

SAS SAS SAS SAS

data data data data

set set set set

be be be be

opened opened opened opened

and and and and

loaded loaded loaded loaded

into into into into

SAS SAS SAS SAS

memory memory memory memory

one variable at a time one page at a time one observation at a time in its entirety

Which one of the following statements about compressed SAS data sets is always true? A. B. C. D.

64.

It It It It

New observations are added to the end of the SAS data set Each observation is treated as a single string of bytes Each observation occupies the same number of bytes An updated observation is stored in its original location

The SAS data set ONE is given below: REP ----SMITH SMITH JONES SMITH JONES

COST ---200 400 100 600 100

The following SAS program is submitted:

Advanced SAS 9 Exam Prep: A00-212 | Prepared for Sritej Gunta PROC SQL; SELECT REP, AVG(COST) AS AVERAGE FROM ONE GROUP BY REP < insert SQL procedure clause here > QUIT; The following output is desired: REP AVERAGE ----- ------JONES 100 Which SQL procedure clause completes the program and generates the desired output? A. B. C. D.

65.

WHERE CALCULATED AVERAGE > (SELECT AVG(COST) FROM ONE); HAVING AVG(COST) > (SELECT AVG(COST) FROM ONE); WHERE AVG(COST) > (SELECT AVG(COST) FROM ONE); HAVING AVG(COST) < (SELECT AVG(COST) FROM ONE);

Consider the following SAS log: 239 DATA SASUSER.RANCH SASUSER.CONDO / VIEW=SASUSER.RANCH; 230 SET SASUSER.HOUSES; 231 IF STYLE=’RANCH’ THEN OUTPUT SASUSER.RANCH; 232 ELSE IF STYLE=’CONDO’ THEN OUTPUT SASUSER.CONDO; 233 RUN; NOTE: DATA STEP view saved on file SASUSER.RANCH. NOTE: A stored DATA STEP view cannot run under a different operating system. 234 235 PROC PRINT DATA=SASUSER.CONDO; ERROR: File SASUSER.CONDO.DATA does not exist. 236 RUN; NOTE: The SAS System stopped processing this step because of errors. Which one of the following explains why the PRINT procedure fails? A. B. C. D.

66.

Which one of the following options is available for SAS macro debugging? A. B. C. D.

67.

The view SASUSER.RANCH must be processed before SASUSER.CONDO is created SASUSER.CONDO is a stored DATA step program A second VIEW=SASUSER.CONDO option was omitted on the DATA statement A SAS data file and SAS data view cannot be created in the same DATA step

MLOGIC MDEBUG MSGLEVEL MAUTOSOURCE

The following SAS program is submitted: %LET FIRST=YOURNAME; %LET LAST=FIRST; %PUT &&&LAST; Which one of the following is the result in the log of the %PUT statement? A. B. C. D.

68.

&FIRST YOURNAME &YOURNAME FIRST

Which one of the following options controls the page size of a SAS data set? A. BUFSIZE= B. BUFNO=

Advanced SAS 9 Exam Prep: A00-212 | Prepared for Sritej Gunta C. PAGESIZE= D. SIZE=

69.

The following SAS program is submitted: %MACRO LOOP; DATA ONE; %DO i=1 %TO 3; VAR&i=&i; %END; RUN; %MEND; %LOOP After this program is executed; the following is written to the SAS log: (LOOP): (LOOP): (LOOP): (LOOP): (LOOP): (LOOP):

Beginning execution %DO loop beginning; index %DO loop index variable i %DO loop index variable i %DO loop index variable i Ending execution

variable i; start value is 1; stop value is 3; by value is 1 is now 2; loop will iterate again is now 3; loop will iterate again is now 4; loop will iterate again

Which SAS system option displays the notes in the SAS log? A. B. C. D.

70.

Assume today is Tuesday, July 23, 2002. Which of the following statements submitted at the beginning of a SAS session assigns the value Tuesday, July 23, 2002 to the macro variable START? A. B. C. D.

61.

MACRO SYMBOLGEN MLOGIC MPRINT

%LET %LET %LET %LET

START=%SYSFUNC(%TODAY(),WEEKDATE.); START=TODAY(), FORMAT=WEEKDATE.; START=TODAY(),WEEKDATE.; START=%SYSFUNC(TODAY(), WEEKDATE.);

(D) First, understand that SAS evaluates code line by line sequentially. Without knowing anything else besides that, you can eliminate option (A) because it is attempting to apply a WHERE clause with an expression to a variable that doesn’t even exist in the data set. Therefore, SAS will clearly return an error and the program will fail. The next thing to understand is a fundamental difference between WHERE statements and the subsetting IF statement. Normally, when you’re attempting to subset data, the WHERE statement is more efficient because it can evaluate data in the buffer before it decides whether or not to pass it along to the program data vector (PDV). On the other hand, the subsetting IF statement must wait until the data has entered the PDV before it can be evaluated based on prescribed conditions. The catch in this instance is that a WHERE statement can only select observations from SAS data sets, whereas the IF statement can select records from external files, observations from SAS data sets, observations created with an INPUT statement, or, in this case, observations based on the value of a computed or derived variable. Because TOTREV is a created variable, the WHERE statement is unable to subset it in the same DATA step, and the program fails. This leaves you with only options (B) and (D) to consider. Both would accomplish the task, but option (B) does not attempt to subset until after it’s already created the other two variables (TOTCOST and PROFIT). Therefore, it’s more efficient to evaluate the subsetting IF and decide whether or not to pass on the record immediately after TOTREV has been created instead of creating all three variables and then discarding it.

62.

(D) The SASFILE statement holds an entire SAS data file in memory so that the data is available to multiple program steps. Keeping the data file open reduces open/close operations, including the allocation and freeing of memory for buffers.


(B) Compressed data files treat an observation as a single string of bytes by ignoring variable types and boundaries. Note: Options (A), (C), and (D) refer to uncompressed data file structures.

64.

(D) In order to further subset observations within a GROUP BY clause, you must use a HAVING clause. Therefore, you can instantly eliminate options (A) and (C). Both HAVING clauses in (B) and (D) compare the average COST of each group to the average COST of the entire table. However, only option (D) returns the observation that results in the desired output because the average COST for JONES was less than the average COST of the table.Jones was

65.

(A)

66.

(A) The system option MLOGIC prints messages that indicate macro actions that were taken during macro execution. The general form is as follows: OPTIONS MLOGIC | NOMLOGIC; Where NOMLOGIC is the default setting, and specifies that messages about macros are not printed to the SAS log during macro execution. MLOGIC specifies that messages about macro actions are printed to the log during macro execution. Note: Option (B), MDEBUG, isn’t even a SAS option. Option (C) MSGLEVEL is a system option that concerns index creation. Option (D), MAUTOSOURCE, is a system option that causes the macro processor to search the autocall libraries for a member with the requested name when a macro name is not found in the WORK library.

67.

(B) This program involves what’s known as the “Forward Re-Scan Rule”. Whenever the macro processor encounters multiple ampersands or percentage signs preceding a token, the macro processor resolves two ampersands (&&) to one ampersand (&), and re-scans the reference. To re-scan a reference, the macro processor scans and resolves tokens from left to right from the point where multiple ampersands or percent signs are coded, until no more triggers can be resolved. In order to better grasp this, let’s evaluate our given problem in steps. %PUT &&&LAST; Here, we have three ampersands preceding the macro variable LAST. As has been said, two ampersands resolve to one. Once you’ve resolved two ampersands to one, imagine you drop that single ampersand to a level below your evaluation level for each pair. &&&LAST; So, one set of ampersands can be cancelled out and dropped down a level, but we are still left with &LAST which can be resolved to FIRST. &&&LAST;  FIRST &FIRST; Here, there are no more multiple ampersands to resolve, so, you can simply resolve the macro variable itself. &FIRST;  YOURNAME Therefore, the result of the %PUT statement is YOURNAME.

68.

(A) The page size is the amount of data that can be transferred for a single I/O operation to one buffer. The page size is a permanent attribute of the data set and is used when the data set is processed. A larger page size can speed up execution time by reducing the number of times SAS has to read from or write to the storage medium. However, the improvement in execution time comes at the cost of increased memory consumption. Note: Be sure not to confuse the DATA set option BUFSIZE= with the system option PAGESIZE=. The system option PAGESIZE specifies the number of lines that compose a page of SAS output.

69.

Advanced SAS 9 Exam Prep: A00-212 | Prepared for Sritej Gunta (C) The MLOGIC option prints messages that indicate macro actions that were taken during macro execution. When the MLOGIC system option is in effect, the information that is displayed in SAS log messages includes: the beginning of macro execution, the results of arithmetic and logical macro operations, and the end of macro executions. Note: The system option MACRO is automatically enabled at the startup of the SAS system. It can be disabled and prevent SAS from executing macro language statements, calls, or variable references with the NOMACRO system option. SYMBOLGEN specifies whether the results of resolving macro variable references are written to the SAS log for debugging. MPRINT specifies whether SAS statements generated by macro execution are traced for debugging.

70.

(D) You may immediately eliminate options (B) and (C). Without the %SYSFUNC function, each value of those macro variables are considered to be character strings. The %SYSFUNC function makes it possible to execute most SAS functions from within a macro.

71.

The following SAS program is submitted: %LET NAME=Patel’s Restaurant; Which one of the following statements avoids problems associated with the unbalanced quotation mark? A. B. C. D.

72.

%LET %LET %LET %LET

NAME=Patel%’s Restaurant; NAME=%STR(Patel’s Restaurant); NAME=Patel%STR(‘)s Restaurant; NAME=%STR(Patel%’s Restaurant);

The data set ONE is given below: SALARY -----200 205 523 The following SAS program is submitted: PROC SQL; SELECT * FROM ONE < insert WHERE expression here >; QUIT; The following output is desired: SALARY -----200 205 523 Which WHERE expression completes the program and generates the desired output? A. B. C. D.

73.

WHERE WHERE WHERE WHERE

Salary Salary Salary Salary

IS NE IS NE

NOT MISSING NOT MISSING NULL

The following SAS program is submitted: DATA VIEW=SASUSER.RANCH; DESCRIBE; RUN;

Advanced SAS 9 Exam Prep: A00-212 | Prepared for Sritej Gunta What is the result? A. B. C. D.

74.

program program program program

retrieves creates a retrieves creates a

the SAS source DATA step view the SAS source DATA step view

code that creates the view and places it in the output window called SASUSER.RANCH and places the program code in the current editor window code that creates the view and places it in the SAS log called SASUSER.RANCH and places it in the SAS log

Which of the following is true about the COMPRESS=YES data set option? A. B. C. D.

75.

The The The The

It It It It

is most effective with numeric data that represents large numeric values is most effective with character data that contains patterns, rather than simple repetitions is most effective with character data that contains repeated characters uses the Ross Data Compression method to compress numeric data

The data sets ONE and TWO are given below: ONE ID NAME --- ----112 Smith 243 Wei 457 Jones

TWO ID SALARY --- -----213 150000 355 45000 523 75000

The following SAS program is submitted: DATA COMBINE; MERGE ONE TWO; BY ID; RUN; Which SQL procedure produces the same results? A. PROC SQL; CREATE TABLE COMBINE AS SELECT COALESCE(ONE.ID, TWO.ID) AS ID, NAME, SALARY FROM ONE FULL JOIN TWO ON ONE.ID=TWO.ID; QUIT; B. PROC SQL; CREATE TABLE COMBINE AS SELECT ONE.ID, NAME, SALARY FROM ONE INNER JOIN TWO ON ONE.ID=TWO.ID; QUIT; C. PROC SQL; CREATE TABLE COMBINE AS SELECT COALESCE(ONE.ID, TWO.ID) AS ID, NAME, SALARY FROM ONE, TWO WHERE ONE.ID=TWO.ID; QUIT; D. PROC SQL; CREATE TABLE COMBINE AS SELECT ONE.ID, NAME, SALARY FROM ONE FULL JOIN TWO WHERE ONE.ID=TWO.ID; QUIT;

76.

Which one of the following SAS programs displays the descriptor portion of each data set stored in the SASUSER library?

Advanced SAS 9 Exam Prep: A00-212 | Prepared for Sritej Gunta A. PROC DATASETS LIB=SASUSER._ALL_; QUIT; B. PROC DATASETS LIB=SASUSER; CONTENTS DATA=ALL; QUIT; C. PROC DATASETS LIB=SASUSER; CONTENTS DATA=_ALL_; QUIT; D. PROC DATASETS LIB=SASUSER.ALL; QUIT;

77.

The following SAS program is submitted: OPTIONS REUSE=YES; DATA SASUSER.REALESTATE (COMPRESS=CHAR); SET SASUSER.HOUSES; RUN; What is the effect of the REUSE=YES system option? A. B. C. D.

78.

It It It It

tracks allows allows allows

and recycles free space users to access the same SAS data set concurrently updates in place a permanently stored SAS data set to be replaced

The following SAS program is submitted: %LET VALUE=.5; %LET ADD=5; %LET NEWVAL=%EVAL(&VALUE + &ADD); Which one of the following is the resulting value of the macro variable NEWVAL? A. B. C. D.

79.

.5 + 5 5.5 5 Null

The data sets ONE and TWO are given below:

YEAR ---2001 2001 2002

ONE QTR --3 4 1

BUDGET -----500 400 700

YEAR ---2001 2002

TWO QTR --4 1

SALES ----300 600

Th following SAS program is submitted: PROC SQL; SELECT ONE.*, SALES FROM ONE, TWO; QUIT; Which one of the following reports is generated? A. YEAR 2001 2001 2002 2001 2001 2002

QTR 3 4 1 3 4 1

BUDGET 500 400 700 500 400 700

SALES 300 300 300 600 600 600

Advanced SAS 9 Exam Prep: A00-212 | Prepared for Sritej Gunta B. YEAR 2001 2001 2002

QTR 3 4 1

BUDGET 500 400 700

SALES 300 300 600

C. YEAR 2001 2001 2002

QTR 3 4 1

BUDGET 500 400 700

SALES . 300 600

D. YEAR QTR BUDGET SALES 2001 4 400 300 2002 1 700 600

80.

The following are values of the variable STYLE from the SAS data set SASUSER.HOUSES: STYLE -------RANCH SPLIT SPLIT CONDO TWOSTORY RANCH CONDO SPLIT SPLIT The following SAS program is submitted: PROC SQL NOPRINT; SELECT DISTINCT STYLE INTO :STYLES SEPARATED BY “ “ FROM SASUSER.HOUSES ORDER BY STYLE; QUIT; Which one of the following is the value of the resulting macro variable? A. B. C. D.

71.

RANCH CONDO RANCH CONDO

SPLIT RANCH SPLIT RANCH

CONDO RANCH CONDO SPLIT

TWOSTORY RANCH SPLIT SPLIT SPLIT SPLIT SPLIT TWOSTORY TWOSTORY TWOSTORY

(D) The %STR function is used to mask (or quote) tokens during compilation so that the macro processor does not interpret them as macro-level syntax. That is, the %STR function hides the normal meaning of a semicolon and other special tokens and mnemonic equivalents of comparison or logical operators so that they appear as constant text. In this example, without the %STR function, SAS would read the apostrophe as a single quote and scan for the second, closing quote. Note: You can achieve the same result as option (D) with the following syntax: %LET NAME=Patel%STR(%’)s Restaurant;

72.

(C) When evaluating missing values as part of a WHERE clause in PROC SQL, you must invoke the IS condition. The syntax is as follows: Sql-expression IS NULL | MISSING Note: In addition to option (C), the following clause would have accomplished the same feat: WHERE Salary IS NOT NULL;

73.

Advanced SAS 9 Exam Prep: A00-212 | Prepared for Sritej Gunta (C) Before this code was written, a DATA step view had to have been created previously similar to the one below: DATA SASUSER.RANCH / VIEW=SASUSER.RANCH; INFILE ; ; RUN; What the code in question 73 does is invoke the DESCRIBE statement. What the DESCRIBE statement does is write a copy of the source code (above) to the SAS log.

74.

(C) The COMPRESS=YES data set option is particularly useful when your data set contains many values that have repeated characters or binary zeros. In character data, the most frequently encountered repeated value is the blank. Long text fields, such as comments and addresses, often contain repeated blanks.

75.

(A) The MERGE statement in SAS joins two or more SAS data sets into a single observation. In this instance, none of the BY variable values match, so the observations will be interleaved with each other, resulting in missing values for each observation in the new table in either the NAME or SALARY column. The method to achieve a similar result in PROC SQL is the FULL JOIN. A full outer join retrieves both matched rows and nonmatching rows from both tables. Because both tables contain the column ID, you need to implement the COALESCE function. The syntax is as follows: SELECT COALESCE(column-1<,…column-n>) < column-alias> <,…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. Column-alias is a temporary name for the coalesced column. The alias cannot contain any spaces. If another clause in the SELECT statement, such as an ORDER BY clause, references the coalesced column, a column alias must be specified.

76.

(C) PROC DATASETS is a SAS procedure used to manage data sets. The CONTENTS statement in PROC DATASETS allows you to view the descriptor portion of a data set. _ALL_ is a special SAS name list that, when used in place of the SAS-data-set-name, lists all data sets within the identified library with the libref. Note: You may also use the CONTENTS procedure to accomplish the same task.

77.

(A) The SAS REUSE= system option and the REUSE= data set option specify whether or not SAS reuses space when observations are added to a compressed data set. If you set the REUSE= data set option to YES in a DATA statement, SAS tracks and reuses space in the compressed data set that is created in that DATA step.

78.

(D) The character operand + is found within the %EVAL function. This produces a null result because the %SYSEVALF function is the only macro function that can evaluate arithmetic or logical expressions using floating point or missing values. Imitate the following to correct the code: %LET NEWVAL=%SYSEVALF(&VALUE + &ADD); The resulting value of the macro variable NEWVAL would be the expected 5.5.

79.

(A) When you specify multiple tables in the FROM clause but do not include a WHERE statement to subset data, PROC SQL returns the Cartesian product of the tables. In a Cartesian product, each row in the first table is combined with every row in the second table.

80.

(D) In addition to a DATA step, you can also create or update macro variables during the execution of a PROC SQL step. Remember that the SELECT statement in a PROC SQL step retrieves and displays data.

Advanced SAS 9 Exam Prep: A00-212 | Prepared for Sritej Gunta The INTO clause in a SELECT statement enables you to create or update macro variables. SEPARATED BY is a specification of the INTO clause that determines what or if there is to be some separating character between a list of macro variable values. So, in this example, SAS is retrieving the distinct (or unique) values from the variable STYLE in data set ONE. The INTO clause then forces those distinct values into a macro variable called STYLES. Each of the listed values are then separated by a blank character value via the SEPARATED BY specification. Finally, they are ordered alphabetically with the ORDER BY clause.

81.

The following SAS program is submitted: OPTIONS MPRINT; %MACRO TEST(PARM); PROC &PARM DATA=SASHELP.PRDSALE; RUN; %MEND; %TEST(PRINT) What is the result of the MPRINT options? A. B. C. D.

82.

It It It It

writes writes has no echoes

the original program code inside the macro definition to the SAS log the macro execution messages to the SAS log effect in this example the text sent to the SAS compiler as a result of macro execution in the SAS log

In which one of the following SAS programs is the SAS data set index named CHAR1 always used? A. PROC SQL; CREATE TABLE THREE AS SELECT * FROM ONE, TWO WHERE ONE.CHAR1 > TWO.CHAR1; QUIT; B. DATA THREE; SET ONE; SET TWO KEY=CHAR1; RUN; C. DATA THREE; SET ONE; WHERE CHAR1 IN(‘New York’ ‘Los Angeles’); RUN; D. DATA THREE; SET ONE; IF CHAR1 IN(‘New York’ ‘Los Angeles’); RUN;

83.

A partial SAS log is given below: NOTE: SQL TABLE SASHELP.CLASS was created like: CREATE TABLE SASHELP.CLASS (BUFSIZE=4096) ( NAME CHAR(8), SEX CHAR(1), AGE NUM, HEIGHT NUM, WEIGHT NUM ); Which SQL procedure statement generated this output?

Advanced SAS 9 Exam Prep: A00-212 | Prepared for Sritej Gunta A. B. C. D.

84.

LIST TABLE DESCRIBE TABLE CREATE TABLE VALIDATE TABLE

Text is sent to the SAS compiler as a result of macro execution. Which one of the following SAS system options writes that text to the log? A. B. C. D.

85.

MLOGIC SOURCE2 MSOURCE MPRINT

The following SAS program is submitted: PROC SQL; SELECT * FROM DICTIONARY.TABLES; QUIT; Which one of the following is reported? A. B. C. D.

86.

Metadata Metadata Metadata Metadata

on on on on

all all all all

tables tables tables tables

in in in in

the the the all

SASUSER library only WORK library only DICTIONARY library only libraries

The SAS data set ONE is given below: REP ----SMITH SMITH JONES SMITH JONES

COST ---200 400 100 600 100

The following SAS program is submitted: PROC SQL; SELECT REP, AVG(COST) AS AVERAGE FROM ONE GROUP BY REP < insert SQL procedure clause here > QUIT; The following output is desired: REP AVERAGE ----- ------SMITH 400 Which SQL procedure clause completes the program and generates the desired output? A. B. C. D.

87.

WHERE CALCULATED AVERAGE > (SELECT AVG(COST) FROM ONE); HAVING AVG(COST) > (SELECT AVG(COST) FROM ONE); WHERE AVG(COST) > (SELECT AVG(COST) FROM ONE); HAVING AVG(COST) < (SELECT AVG(COST) FROM ONE);

The following SAS program is submitted: DATA TEMP; SET SASUSER.HISTORY (KEEP=DATE); FORMAT DATE QTR.; < insert BY statement here >

Advanced SAS 9 Exam Prep: A00-212 | Prepared for Sritej Gunta IF FIRST.DATE THEN TOTAL=0; TOTAL+1; IF LAST.DATE; RUN; PROC PRINT DATA=TEMP; RUN; SASUSER.HISTORY is sorted by the SAS date variable DATE. The following output is desired: DATE ---1 3 4

TOTAL ----13 15 25

Which BY statement completes the data step and successfully generates the desired output? A. B. C. D.

88.

BY BY BY BY

DATE DATE DATE DATE

GROUPFORMAT; NOTSORTED; QTR; FORMATTED;

The following SAS program is submitted: DATA ONE; DO i=1 TO 10; PTOBS=CEIL(RANUNI(0)*TOTOBS); SET TEMP POINT=PTOBS NOBS=TOTOBS; OUTPUT; END; STOP; RUN; The SAS data set TEMP contains 2,500,000 observations. Which one of the following represents the possible values for PTOBS? A. B. C. D.

89.

Any Any Any Any

integer between 1 and integer between 1 and real number between 0 real number between 1

2,500,000 10 and 1 and 2,500,000

The following SAS program is submitted: %MACRO EXECUTE; < insert statement here > PROC PRINT DATA=SASUSER.HOUSES; RUN; %END; %MEND; Which of the following completes the above program so that it executes on Tuesday? A. B. C. D.

90.

%IF %IF %IF %IF

“&SYSDAY” &SYSDAY = &SYSDAY = ‘&SYSDAY’

= Tuesday %THEN %DO; ‘Tuesday’ %THEN %DO; Tuesday %THEN %DO; = ‘Tuesday’ %THEN %DO;

The following SAS program is submitted: PROC SORT DATA=SALES TAGSORT; BY MONTH YEAR; RUN; Which of the following resource(s) is the TAGSORT option reducing?


81.

CPU usage only I/O and CPU usage Temporary disk usage I/O usage only

(D) The macro program TEST is created with a positional parameter called PARM. When the macro program TEST is called, it replaces the positional parameter with PRINT and during execution of the macro, PRINT replaces the macro variable &PARM for a final result of PROC PRINT. When the MPRINT option is specified, the text that is sent to the SAS compiler as a result of macro execution is printed to the SAS log. You may want to specify the MPRINT system option if: you have a SAS syntax error or execution error, or you want to see the generated SAS code. In this example, the printed text to the SAS log would appear as follows: MPRINT(TEST): PROC PRINT DATA=SASHELP.PRDSALE; MPRINT(TEST): RUN;

82.

(B) You can use multiple SET statements to combine data from multiple data sets if you want to combine only data from observations that have matching values for particular variables. To do this, you specify the KEY= option in the SET statement to use an index to retrieve observations from the input data set that have key values equal to the key variable value that is currently in the program data vector (PDV). The syntax is as follows: SET SAS-data-set-name KEY=index-name; Where index-name is the name of an index that is associated with the SAS-data-set-name data set.

83.

(B) The DESCRIBE TABLE statement writes a CREATE TABLE statement to the SAS log for the table specified in the DESCRIBE TABLE statement, regardless of how the table was originally created (for example, with a DATA step). If applicable, SAS data set options are included with the table definition. If indexes are defined on columns in the table, then CREATE INDEX statements for those indexes are also written to the SAS log. To create the example generated output, imitate the following: PROC SQL; DESCRIBE TABLE SASHELP.CLASS; QUIT;

84.

(D) When the MPRINT option is specified, the text that is sent to the SAS compiler as a result of macro execution is printed to the SAS log. You may want to specify the MPRINT system option if: you have a SAS syntax error or execution error, or you want to see the generated SAS code.

85.

(D) The DICTIONARY.TABLES table contains detailed information about tables. Dictionary column names are specified in the SELECT statement and the Dictionary table name, DICTIONARY.TABLES, is specified in the FROM clause. In this example, the asterisk (*) selects all column names for all tables in SAS. If you want to specify a particular library instead of all libraries, you must specify the library name in uppercase letters with a WHERE clause and enclose it in quotation marks. For example, to select all columns from just the WORK library: PROC SQL; SELECT * FROM DICTIONARY.TABLES WHERE LIBNAME=’WORK’; QUIT;


(B) Notice that the PROC SQL will have two variables in the output (REP and AVERAGE) as a result of the SELECT statement. The next thing to notice is that this program is going to group values in the REP column together (SMITH and JONES). A HAVING clause works with the GROUP BY clause to restrict the groups that are displayed in the output, based on one or more specified conditions. In this example, we have our grouped values (SMITH and JONES), and the output will depend on whether or not those groups meet a specified condition. The AVERAGE value for the SMITH group is 400. The AVERAGE value for the JONES group is 100. The average of the COST variable for all observations in data set ONE is 280. So, in order to print just the SMITH group with its AVERAGE value, you can apply the following clause: HAVING AVG(COST) > (SELECT AVG(COST) FROM ONE); Note: The WHERE clause can’t process calculated variables and is invalid.

87.

(A) First, notice the FORMAT statement. It assigns a format of QTR. (which must have been previously created as part of a FORMAT procedure) to the variable DATE. When the GROUPFORMAT option is used, the data set must be sorted by the GROUPFORMAT variable or grouped by the formatted values of the GROUPFORMAT variable. The GROUPFORMAT option uses the formatted values of a variable instead of the internal values to determine where a BY group begins and ends, and how FIRST.variable and LAST.variable are computed. The syntax is as follows: BY variable(s) GROUPFORMAT; Where variable(s) names each variable by which the data set is sorted or indexed.

88.

(A) The CEIL function returns the smallest integer that is greater than or equal to the argument, fuzzed to avoid unexpected floating-point results. The syntax is as follows: CEIL (argument) Where argument specifies a numeric constant, variable, or expression. You can use the CEIL function in conjunction with the RANUNI function to generate a random integer. The CEIL function returns the smallest integer that is greater than or equal to the argument. Therefore, if you apply the CEIL function to the result of the RANUNI function, you can generate a random integer. With this knowledge, you can look at the assignment statement that creates the variable PTOBS. TOTOBS is a variable equal to the total number of observations in the data set. The data set is described as having 2.5 million observations so PTOBS can be any random integer between 1 and 2,500,000.

89.

(C) SAS creates and defines several automatic macro variables for you. Automatic macro variables contain information about your computing environment, such as the date and time of the session, and the version of SAS you are running. These automatic variables: are created when SAS is invoked, are global (always available), are usually assigned values by SAS, and can be assigned values by the user in some cases. SYSDAY is one such automatic SAS macro variable with a value equal to the day of the week of the SAS invocation. In this example, options (A) and (D) can be excluded because no quotes should be placed around macro variables. Option (B) can be excluded because all macro variables are character strings, therefore it is unnecessary to quote the value Tuesday. What this program does is, when the macro program EXECUTE is called, it executes the program. It resolves the value of &SYSDAY, and if that value is equal to Tuesday then the PRINT procedure is executed.

90.

(C) The TAGSORT option can be used to sort a large data set. The TAGSORT option stores only the BY variables and the observation numbers in temporary files. The BY variables and the observation numbers are called tags. At the

Advanced SAS 9 Exam Prep: A00-212 | Prepared for Sritej Gunta completion of the sorting process, PROC SORT uses the tags to retrieve records from the input data set in sorted order. When the total length of the BY variables is small compared to the record length, TAGSORT reduces temporary disk usage considerably because sorting just the BY variables means sorting much less data. However, processing time is usually higher than if a regular sort is used because TAGSORT increases CPU time and I/O usage in order to save memory and disk space.

91.

The following SAS program is submitted: %LET DEPT=prod; %LET PROD=merchandise; The following message is written to the SAS log: The value is “merchandise” Which SAS system option writes this message to the SAS log? A. B. C. D.

92.

The The The The


is is is is

“&DEPT” “&&DEPT”; “&&&DEPT”; %QUOTE(&&&DEPT);

What is generated as a result of submitting the RANUNI function with a seed of 123? A. B. C. D.

93.

%PUT %PUT %PUT %PUT

A A A A

random number between 0 and 123 different sequence of random numbers with each program execution consistent sequence of random numbers with each program execution missing value because 123 is an invalid argument for the RANUNI function

The following SAS program is submitted: < insert statement here > %LET DEVELOPMENT=ontime; PROC PRINT DATA=SASUSER.HIGHWAY; TITLE1 “For &DEPT”; TITLE2 “This project was completed &DEVELOPMENT”; RUN; Which one of the following statements completes the above and resolves TITLE1 to “For research&development”? A. B. C. D.

94.

DEPT=%STR(research&development); DEPT=%NRSTR(research&development); DEPT=%STR(research%&development); DEPT=%NRSTR(research%&development);

Which one of the following statements is true? A. B. C. D.

95.

%LET %LET %LET %LET

The The The The

WHERE statement selects observations before they are brought into the PDV WHERE statement can be executed conditionally as part of an IF statement subsetting IF statement works on observations before they are read into the PDV WHERE and subsetting IF statements can be used interchangeably in all SAS programs

Which one of the following should be avoided when creating and using an SQL procedure view? A. B. C. D.

Using a HAVING clause Creating views on tables whose structures remain constant Using summary functions Referencing a view multiple times


Given the following SAS statement: %LET IDCODE=PROD567; Which one of the following statements stores the value 567 in the macro variable CODENUM? A. B. C. D.

97.

%LET %LET %LET %LET

CODENUM=SUBSTR(&IDCODE, LENGTH(&IDCODE)-3); CODENUM=%SUBSTR(&IDCODE, %LENGTH(&IDCODE)-3); CODENUM=%SUBSTR(&IDCODE, %LENGTH(&IDCODE)-2); CODENUM=SUBSTR(&IDCODE, LENGTH(&IDCODE)-2);

The variable attributes of SAS data sets ONE and TWO are given below:

# 2 1 1

ONE VARIABLE TYPE LEN POS -------- ---- --- --SALES NUM 8 8 YEAR NUM 8 0 YEAR NUM 8 0

# 2 3

TWO VARIABLE TYPE LEN POS -------- ---- --- --BUDGET NUM 8 8 SALES CHAR 8 16

Data set ONE contains 100 observations. Data set TWO contains 50 observations. Both data sets are sorted by the variable YEAR. The following SAS program is submitted: DATA THREE; MERGE ONE TWO; BY YEAR; RUN; Which one of the following is the result of the program execution? A. B. C. D.

98.

No messages are written to the Data set THREE is created with ERROR and WARNING messages are Data set THREE is created with

SAS log two variables and 50 observations written to the SAS log three variables and 100 observations

The SAS data sets ONE and TWO are given below: ONE NUM CHAR1 --- ----1 A1 1 A2 2 B1 2 B2 4 D

TWO NUM CHAR2 --- ----2 X1 2 X2 3 Y 5 V

The following SAS program is submitted creating the output table THREE: PROC SQL; CREATE TABLE THREE AS SELECT ONE.NUM, CHAR1, CHAR2 FROM ONE, TWO WHERE ONE.NUM=TWO.NUM; QUIT;

NUM --2 2 2 2

THREE CHAR1 ----B1 B1 B2 B2

CHAR2 ----X1 X2 X1 X2

Which one of the following DATA step programs creates an equivalent SAS data set THREE? A. DATA THREE; SET ONE; DO i=1 TO NUMOBS;

Advanced SAS 9 Exam Prep: A00-212 | Prepared for Sritej Gunta SET TWO(RENAME=(NUM=NUM2)) POINT=i NOBS=NUMOBS; IF NUM2=NUM THEN OUTPUT; END; DROP NUM2; RUN; B. DATA THREE; SET ONE; SET TWO; BY NUM; RUN; BY NUM; RUN; C. DATA THREE; SET ONE; SET TWO; BY NUM; RUN; MERGE ONE TWO; BY NUM; RUN; D. DATA THREE; MERGE ONE TWO; BY NUM; RUN;

99.

The following SAS FORMAT procedure is submitted: PROC FORMAT LIB=SASUSER; VALUE TEMPC LOW -< 0 =’BELOW FREEZING’ 0 -< 5 =’COLD’ 5 -< 10 =’MILD’ 10 -< 15 =’WARM’ 15 -< HIGH =’HOT’; RUN; How is the value 10 displayed when the format TEMPC is applied? A. B. C. D.

10 MILD WARM BELOW FREEZING

100. The SAS data set TEMP has the following distribution of values for variable A: A 1 2 6 8

FREQUENCY --------500,000 500,000 7,000,000 3,000

Which one of the following SAS programs requires the least CPU time to be processed? A. DATA NEW; SET TEMP; IF A=8 THEN b=’SMALL’; ELSE IF A IN(1,2) THEN B=’MEDIUM’; ELSE IF A=6 THEN B=’LARGE’; RUN; B. DATA NEW; SET TEMP; IF A IN(1,2) THEN B=’MEDIUM’; ELSE IF A=8 THEN B=’SMALL’;

Advanced SAS 9 Exam Prep: A00-212 | Prepared for Sritej Gunta ELSE IF A=6 THEN B=’LARGE’; RUN; C. DATA NEW; SET TEMP; IF A=6 THEN B=’LARGE’; ELSE IF A IN(1,2) THEN B=’MEDIUM’; ELSE IF A=8 THEN B=’SMALL’; RUN; D. DATA NEW; SET TEMP; IF A=6 THEN B=’LARGE’; IF A IN(1,2) THEN B=’SMALL’; RUN;

91.

(C) You can immediately dismiss option (D) because the %QUOTE function masks special characters and mnemonic operators in a resolved value at macro execution, it doesn’t put quotation marks around a character string. The other three options simply involve properly resolving the macro variable to the appropriate value. Refer to the explanations for 57 and/or 67 if you need a refresher on how to do that.

92.

(C) In order to create a random sample, you need to generate a random number. SAS provides several random number functions to generate random numbers from various distributions. One such example is the RANUNI function. RANUNI (seed) Where seed is a nonnegative integer with a value less than 231-1 (2,147,483,647) The RANUNI function generates streams of random numbers from an initial starting point, called the seed. If you use a positive seed, you can always replicate the stream of random numbers by using the same data step. If you use 0 as the seed, the computer clock initializes the stream, and the stream of random numbers is not replicable. Therefore, because this example is used with a seed of 123, there is a consistent sequence.

93.

(B) First, notice that a macro variable called DEVELOPMENT with a value of ontime has been created via a LET statement. The question requires that TITLE1 print a value that includes an ampersand (&) with the word development immediately after. Without any addition to the code, that title would resolve to “For researchontime”. Sometimes you might want to hide the normal meaning of an ampersand or a percentage sign. The %NRSTR function performs the same quoting functions %STR, except it also masks macro triggers (& and %). The NR in the name %NRSTR stands for No Resolution.

94.

(A) The WHERE statement examines what is in the input page buffer and selects observations before they are loaded in the program data vector, which results in a savings in CPU operations.

95.

(D) SAS executes a view each time it is referenced, even within one program. Therefore, if data is used many times in one program, it is more efficient to create and reference a temporary SAS data file than to create and reference a view.

96.

(C) When referencing a macro function within a LET statement, the percentage sign (%) must be used. Therefore, you can immediately eliminate options (A) and (D). The %SUBSTR function enables you to extract part of a character string from the value of a macro variable. The syntax is as follows: %SUBSTR (argument, position<,n>) Where argument is a character string or a text expression from which a substring will be returned.

Advanced SAS 9 Exam Prep: A00-212 | Prepared for Sritej Gunta position is an integer or an expression (text, logical, or mathematical) that yields an integer, which specifies the position of the first character in the substring. n is an optional integer or an expression (text, logical, or mathematical) that yields an integer that specifies the number of characters in the substring. If the length of n is greater than the number of characters following position in argument, %SUBSTR issues a warning message and returns a substring that contains the characters from position to the end of the string. If n is not specified, %SUBTR also returns a substring that contains the characters from position to the end of the string. The %LENGTH function returns the length of a string. The syntax is as follows: %LENGTH (character string | text expression) If the argument is a character string, %LENGTH returns the length of the string. If the argument is a text expression, %LENGTH returns the length of the resolved value. If the argument has a null value, %LENGTH returns 0. The values of position and n can also be the result of a mathematical expression that yields an integer. So, in this example we are given the macro variable IDCODE with a value of PROD567. Our task is to create another macro variable called CODENUM with a value of 567. In this instance, the %SUBSTR function references the value of the macro variable IDCODE (PROD567) as the argument. The position that is to be extracted is a mathematical expression which utilizes the %LENGTH function. %LENGTH is calling on the numerical length of the value of the macro variable IDCODE (which is 7). It is 7 because that’s how many characters are in the value (P R O D 5 6 7). Now, the mathematical expression tells the %SUBSTR function where to begin extracting the character string. We want to extract the value of 567 from PROD567. So, in order to do that, our code must reference the fifth position. The %LENGTH function gives us the value of 7. If we subtract 2 from 7, we have 5. Therefore, option (C) is the correct answer. Note: If the code had instead been: %LET CODENUM=%SUBSTR(&IDCODE, %LENGTH(&IDCODE)-3); Then the value of the macro variable CODENUM would have been D567. 7 minus 3 would have begun the %SUBTR extraction in position 4. If the code had instead been: %LET CODENUM=%SUBSTR(&IDCODE, %LENGTH(&IDCODE)-5); Then the value of the macro variable CODENUM would have been ROD567. 7 minus 5 would have begun the %SUBTR extraction in position 2.

97.

(C) This program produces error and warning messages because when it attempts to overlay the variable SALES, SALES is a numeric variable in data set ONE and a character variable in data set TWO. In order to merge, variables of the same name must also be of the same type.

98.

(A) In the example, the CREATE TABLE statement creates a table (data set) named THREE and populates the variables from the SELECT statement via the AS keyword. The result is an INNER JOIN based on the conditions in the WHERE statement which output a Cartesian product of four total observations where the values in the NUM column are all equal to 2. The DATA step that replicates this output is option (A).

99.

(C) This code creates a numeric format called TEMPC. The question asks, "How is the value 10 displayed when the format TEMPC is applied?" The "less-than" operator (<) is used to show a non-inclusive range. However, the side on which the hyphen falls determines which of the two values is included and which is excluded. For the label BELOW FREEZING, because the hyphen is on the left side of the less-than operator (range: LOW -< 0). This means that the exact value of zero is non-inclusive in the range.

Advanced SAS 9 Exam Prep: A00-212 | Prepared for Sritej Gunta For the label MILD (range: 5 -< 10), the value 10 is non-inclusive. For the label WARM (range: 10 -< 15), the value 15 is non-inclusive. Therefore, if the value is 10, the label would be WARM. Note: You can switch the hyphen to the other side of the less-than operator and switch the properties. For example, if the label WARM had a range such that (range: 10 <- 15), then the value 10 would be non-inclusive and the value 10 would no longer have the label WARM but instead MILD (if the same logic were applied to the MILD label).

100. (C) Because the value 6 is the most prevalent, it is most likely that for any given observation of variable A, the value will be 6. Therefore, it is most efficient to first evaluate an IF statement assuming the first value is 6. This eliminates options (A) and (B) immediately. The next thing to do is assign values to the next largest frequency values (1 and 2). Both options (C) and (D) do this, but the use if the ELSE statements allows SAS to accomplish this in one sweep. Therefore, option (C) is most efficient in terms of computer processing.

101. The SAS data set ONE is given below: DIVISION -------A A B

SALES ----1234 3654 5678

The following SAS program is submitted: DATA _NULL_; SET ONE; BY DIVISION; IF FIRST.DIVISION THEN DO; %LET MFIRST=SALES; END; RUN; What is the value of the macro variable MFIRST when the program finishes execution? A. B. C. D.

1234 SALES 5678 NULL

102. The SAS data set ONE has a variable, X, on which an index has been created. The data sets ONE and THREE are sorted by X. Which one of the following SAS programs uses the index to select observations from the data set ONE? A. DATA TWO; SET THREE; SET ONE KEY=X; RUN; B. DATA TWO; SET THREE KEY=X; SET ONE; RUN; C. DATA TWO; SET ONE; SET THREE KEY=X; RUN;

Advanced SAS 9 Exam Prep: A00-212 | Prepared for Sritej Gunta D. DATA TWO; SET THREE; SET ONE(KEY=X); RUN;

103. The following SAS program is submitted: OPTIONS YEARCUTOFF=1950; %MACRO Y2KOPT(DATE); %IF &DATE >= 14610 %THEN %DO; OPTIONS YEARCUTOFF=2000; %END; %ELSE %DO; OPTIONS YEARCUTOFF=1900; %END; %MEND; DATA _NULL_; DATE=’01JAN2000’D; CALL SYMPUT(‘DATE’,LEFT(DATE)); RUN; %Y2KOPT(&DATE); The SAS date for January 1, 2000 is 14610, and the SAS system option for YEARCUTOFF is set to 1920 prior to submitting the above program. Which one of the following is the value of YEARCUTOFF when the macro finishes execution? A. B. C. D.

1900 1920 1950 2000

104. The following SAS program is submitted: DATA TEMP; ARRAY POINTS{2,3} _TEMPORARY_; RUN; Which one of the following is the maximum number of elements that are stored? A. B. C. D.

2 3 5 6

105. The SAS data set ONE is given below: COUNTRY ------USA UK USA UK USA UK USA

CITY VISIT ------ ----BOSTON 10 LONDON 5 DALLAS 10 MARLOW 10 BOSTON 20 LONDON 15 DALLAS 10

The following SAS program is submitted: PROC SQL; SELECT COUNTRY, CITY, SUM(VISIT) AS TOTAL FROM ONE GROUP BY COUNTRY, CITY

Advanced SAS 9 Exam Prep: A00-212 | Prepared for Sritej Gunta ORDER BY COUNTRY, TOTAL DESC; QUIT; Which one of the following reports is generated? A. COUNTRY ------UK UK USA USA

CITY TOTAL ------ ----MARLOW 10 LONDON 20 BOSTON 30 DALLAS 20

B. COUNTRY ------UK UK USA USA

CITY TOTAL ------ ----LONDON 20 MARLOW 10 BOSTON 30 DALLAS 20

C. COUNTRY CITY TOTAL ------- ------ ----USA BOSTON 30 D. COUNTRY ------UK UK USA USA

CITY TOTAL ------ ----MARLOW 10 LONDON 20 DALLAS 20 BOSTON 30

106. What is an advantage of using a hash object in a SAS DATA step? A. B. C. D.

The The The The

hash hash hash hash

object object object object

persists after the DATA step has executed key values can be multiple numeric and character data values automatically sorts the data does not require unique keys

107. Which one of the following automatic SAS macro variables contains the return code from a previously executed step? A. B. C. D.

&RC &ERR &SYSRC &SYSERR

108. The SAS data set ONE is given below: CATEGORY -------M M F F

AGE --28 25 18 25

SALARY -----200 100 100 200

BONUS ----. 10 50 10

The following SAS program is submitted: PROC SQL; CREATE TABLE TWO AS SELECT CATEGORY, SALARY+BONUS AS EARNINGS FROM ONE; QUIT; Which one of the following represents the data values stored in the data set TWO? A. CATEGORY -------M M

EARNINGS -------200 110

Advanced SAS 9 Exam Prep: A00-212 | Prepared for Sritej Gunta F F

150 210

B. CATEGORY -------M M F F

EARNINGS -------. 110 150 210

C. CATEGORY -------M M F F

SALARY -----200 100 100 200

BONUS ----. 10 50 10

EARNINGS -------200 110 150 210

D. CATEGORY -------M M M M F F

SALARY -----200 100 200 100 100 200

BONUS ----. 10 . 10 50 10

EARNINGS -------. 110 200 110 150 210

109. The following SAS program is submitted: DATA TWO; Y=’2’; RUN; %LET X=10; %LET VAR=Y; DATA ONE; SET TWO (KEEP=&VAR); Z=&VAR * &X; RUN; Which one of the following is the value of the variable Z when the program finishes execution? A. B. C. D.

_ERROR_ 20 (As a numeric) 20 (As a character) . (Missing numeric)

110. Which of the following statements correctly creates a DATA step variable named Price and assigns to it the value of the macro variable daily_fee during DATA step execution? A. B. C. D.

price=&daily_fee; price=symget(daily_fee); price=symget(&daily_fee); price=symget(“daily_fee”);

101. (B) When you use the DATA _NULL_ statement, SAS processes the DATA step without writing observations to a data set. Using the DATA _NULL_ statement can considerably increase program efficiency. The BY statement in a DATA STEP applies only to the SET, MERGE, or UPDATE statement that precedes it in the DATA step, and only one BY statement can accompany each of these statements in a DATA step. The data sets that are listed in the SET, MERGE, or UPDATE statements must be sorted by the values of the variables that are listed in the BY statement or have an appropriate index. As a default, SAS expects the data sets to be arranged in ascending numeric order or in alphabetical order.

Advanced SAS 9 Exam Prep: A00-212 | Prepared for Sritej Gunta Now, macro variables are part of the SAS macro facility, which is a tool for extending and customizing SAS and for reducing the amount of program code you must enter to perform common tasks. Whether automatic or user-defined, a macro variable is independent of a SAS data set and contains one text string value that remains constant until you change it. So, when you execute your code, SAS first scans for ampersands and percentage signs (macro triggers) and resolves any necessary variables independent of the DATA steps. It then takes those values and stores them in either the local or global symbol table. As mentioned earlier, _NULL_ in a DATA statement means that nothing is written to any data set and because the %LET statement resolves independent of the DATA step, the DO loop doesn’t do anything. The entire program produces nothing, and the %LET statement could have been written anywhere in the editor window and it still would have resolved to SALES.

102. (A) You can use multiple SET statements to combine data from multiple data sets if you want to combine only data from observations that have matching values for particular variables. You specify the KEY= option in the SET statement to use an index to retrieve observations from the input data set that has key values equal to the key variable value that is currently in the program data vector (PDV). Note: To use the SET statement with the KEY= option to perform a lookup operation, your lookup values must be stored in a SAS data set that has an index. This technique is only appropriate when you are working with one-to-one matches, and you can use it with a lookup table of any size. It is possible to return multiple values with this technique, and you can use other DATA step syntax with it as well.

103. (D) The _NULL_ data set doesn’t write anything to a SAS data set, but it does execute. In its execution it creates a data set variable called DATE with a numeric value that is equal to the number of days since January 1, 2000 (14610). It then, using the CALL SYMPUT routine, creates a global macro variable called DATE and assigns a value the same as the data set variable DATE (14610). %Y2KOPT(&DATE); calls the Y2KOPT macro and applies the newly created DATE macro variable. It matches the 14610 within the %IF/%THEN and gets applied on the first iteration of the DO loop with a YEARCUTOFF of 2000. Note: If the data set variable DATE had instead been given a value of ‘31DEC1999’D then the YEARCUTOFF would have instead been 1900.

104. (D) When a lookup operation depends on more than one numerical factor, you can use a multidimensional array. The syntax is as follows: ARRAY array-name {rows,cols…} <$> <(initial values)>; As you can see from the question, the array POINTS is assigned two rows and three columns for a total of six elements. Note: The keyword _TEMPORARY_ may be used instead of array-elements to avoid creating new data set variables. Only temporary array elements are produced as a result of using _TEMPORARY_.

105. (B) The first thing SAS does is make use of the FROM clause and access the data set ONE. The next thing it does is extract the variables COUNTRY and CITY, then creates a new variable called TOTAL. The GROUP BY clause is used in queries that include one or more summary functions. Summary functions produce a statistical summary for each group that is defined in the GROUP BY clause. So, first the table is grouped by country. After that initial grouping, it is grouped again by city. From this point, the variable TOTAL displays the value of the sum of each of these groups’ visit. The ORDER BY clause then sorts the rows that the query returns by the value(s) of the specified column(s). First, these groups are sorted by the variable COUNTRY. By default, they are sorted in ascending order. So, UK comes before USA. Then are then sorted further by TOTAL in descending order. That’s why, for each group, the highest TOTAL is listed first. Note: If the keyword DESC had not been utilized, TOTAL would have sorted (after COUNTRY) in ascending order. This would have resulted in the flip-flop of LONDON 20 with MARLOW 10 and BOSTON 30 with DALLAS 20.

Advanced SAS 9 Exam Prep: A00-212 | Prepared for Sritej Gunta 106. (B) The hash object provides an efficient, convenient mechanism for quick data storage and retrieval. Unlike an array, which uses a series of consecutive integers to address array elements, a hash object can use any combination of numeric and character values as addresses. A hash object can be loaded from hard-coded values or a SAS data set, is sized dynamically, and exists for the duration of the DATA step.

107. (D) SAS creates and defines several automatic macro variables. Automatic macro variables contain information about your computing environment, such as the date and time of session, and the version of SAS you are running. Some automatic variables have values that automatically change based on submitted SAS statements. SYSERR is one such automatic variable that contains a return code status that is set by the DATA step and some SAS procedures to indicate if the step or procedure executed successfully.

108. (B) The addition sign (+) acts as an operator to perform a calculation between to variable values (SALARY and BONUS). However, when you use an arithmetic expression (+, -, *, /), the SQL procedure always sets the result of the expression to missing (.). If you use that result in another expression, the next result will be valued as missing (.) as well. Note: This method of treating missing values is called propagation of missing values.

109. (B) In data set TWO the variable Y is assigned a character value of 2. Additionally, via the %LET statement, there is a global macro variable called VAR with a value of Y. The data step, which creates the data set ONE, has a KEEP= option on the SET statement which includes the macro variable VAR which resolves to Y. This instructs the input data set to keep the Y variable from data set TWO. The Z variable is then created by multiplying the values of two macro variables which are considered character values. SAS, though, is able to temporarily convert these values to numeric in order to perform the required operation.

110. (D) The SYMGET function returns the value of an existing macro variable. SYMGET(macro-variable) Where macro-variable can be specified as one of the following: a macro variable name (enclosed in quotation marks), a DATA step variable name whose value is the name of a macro variable, or a DATA step character expression whose value is the name of a macro variable

111. The SAS data set ONE is given below: JOB LEVEL SALARY --- ----- -----ACC 2 300 SEC 1 100 SEC 2 200 MGR 3 700 ACC 1 . ACC 3 . MGR 2 400 The SAS data set TWO is created: JOB LEVEL BONUS --- ----- ----ACC 2 30 MGR 3 70 MGR 2 40 Which one of the following SAS programs creates data set TWO?

Advanced SAS 9 Exam Prep: A00-212 | Prepared for Sritej Gunta A. PROC SQL; CREATE TABLE TWO AS SELECT JOB, LEVEL, SALARY * 0.1 AS FROM ONE WHERE BONUS > 20; QUIT; B. PROC SQL; CREATE TABLE TWO AS SELECT JOB, LEVEL, SALARY * 0.1 AS FROM ONE WHERE SALARY > 20; QUIT; C. PROC SQL; CREATE TABLE TWO AS SELECT JOB, LEVEL, SALARY * 0.1 AS FROM ONE WHERE CALCULATED SALARY * 0.1 > 20; QUIT; D. PROC SQL; CREATE TABLE TWO AS SELECT JOB, LEVEL, SALARY * 0.1 AS FROM ONE WHERE CALCULATED BONUS > 20; QUIT;

BONUS

BONUS

BONUS

BONUS

112. The following SAS program is submitted: %MACRO LOCATION; DATA _NULL_; CALL SYMPUT(‘DEPT’,’SALES’); RUN; %LET COUNTRY=GERMANY; %PUT _GLOBAL_; %MEND; %LET COMPANY=ABC; %LOCATION Which macro variables are written to the SAS log? A. B. C. D.

COMPANY and DEPT only COMPANY, COUNTRY, and DEPT COMPANY only COMPANY and COUNTRY only

113. The following SAS program is submitted: DATA TEMP (< insert option here >); INFILE ‘RAWDATA’; INPUT X $ Y Z; RUN; RAWDATA is a file reference to an external file that is ordered by the variable X. Which option specifies how the data in the SAS data set TEMP will be sorted? A. B. C. D.

ORDEREDBY=X GROUPBY=X SORTEDBY=X SORTSYNC=X

114. The SAS data sets ONE and TWO are given below: ONE NUM CHAR1 --- ----1 A

TWO NUM CHAR2 --- ----2 X

Advanced SAS 9 Exam Prep: A00-212 | Prepared for Sritej Gunta 2 4

B D

3 5

Y V

The following SAS program is submitted; creating the output table THREE. DATA THREE; SET ONE TWO; RUN;

NUM CHAR1 CHAR2 --- ----- ----1 A 2 B 4 D 2 X 3 Y 5 V Which one of the following SQL programs creates an equivalent SAS data set THREE? A. PROC SQL; CREATE TABLE THREE AS SELECT * FROM ONE OUTER UNION CORR SELECT * FROM TWO; QUIT; B. PROC SQL; CREATE TABLE THREE AS SELECT * FROM ONE OUTER UNION SELECT * FROM TWO; QUIT; C. PROC SQL; CREATE TABLE THREE AS SELECT * FROM ONE UNION SELECT * FROM TWO; QUIT; D. PROC SQL; CREATE TABLE THREE AS SELECT * FROM ONE UNION CORR SELECT * FROM TWO; QUIT;

115. The SAS data set INTERNAT is given below: LOCATION -------USA EUR

SUM --30 40

The following SAS program is submitted: %LET LOC=USA; PROC SQL; SELECT * FROM INTERNAT

Advanced SAS 9 Exam Prep: A00-212 | Prepared for Sritej Gunta WHERE LOCATION=’&LOC’; QUIT; Which one of the following is the result when the above code is executed on the above data set? A. B. C. D.

A report is generated with one destination No report is generated as the case of the compared values is different No report is generated as the case of the macro variable name is different A report is generated with the two original observations as the WHERE clause does not work

116. When is it appropriate to create indexes on a SAS data set for efficient processing? A. B. C. D.

If If If If

small subsets of data are often retrieved the key variable has very unique values the data are often used for BY group processing the SAS data set file page count is less than three pages

117. %LET A=cat; %MACRO ANIMAL; %LET A=dog; %MEND; %ANIMAL %PUT A is &A; Which one of the following is written to the SAS log? A. B. C. D.

A A A A

is is &A is cat is dog

118. The SAS data set MYLIB.MYDATA is given below: NAME ----Max Brown Large

ANIMAL AGE ------ --Cat 9 Dog 22 Pig 1

The following SAS program is submitted: DATA _NULL_; SET MYLIB.MYDATA; CALL SYMPUT(‘animal’ || LEFT(_N_), name); RUN; %LET i=2; TITLE “The value is &&animal&I”; Which of the following does the TITLE statement resolve to? A. B. C. D.

The The The The


is is is is

Dog animal2 Brown &animal2

119. Which one of the following statements is true regarding the TRANSPOSE procedure? A. B. C. D.

The The The The

TRANSPOSE TRANSPOSE Transpose TRANSPOSE

procedure procedure procedure procedure

produces printed output by default rearranges all variables by default does not create an output data set by default creates one observation for each restructured variable for each BY group

120. Which SAS system option is used to identify format catalogs to SAS?


FMTERR FMTLIB NOFMTERR FMTSEARCH=

111. (D) When you use a column alias in the WHERE clause to refer to a calculated value, you must use the keyword CALCULATED along with the alias. The CALCULATED keyword informs PROC SQL that the value is calculated within the query.

112. (A) The question asks which variables are written to the SAS log. The only variables that will be written to the SAS log are those which are user-generated global macro variables due to the %PUT _GLOBAL_; statement. COMPANY will be written to the SAS log because it was created via a %LET statement outside of a macro program which makes it a global macro. DEPT will also be written to the SAS log because it was created in a data step via the CALL SYMPUT routine. COUNTRY, however, was created within the macro program and is therefore a local macro variable. Note: DEPT could only be created if its macro program was executed. If the macro LOCATION had not been called via %LOCATION, then the only variable in the log would have been COMPANY.

113. (C) If you’re working with input data that is already sorted, you can specify how the data is ordered by using the SORTEDBY= data set option. SORTEDBY=by-clause | _NULL_ Where by-clause indicates the data order. collate-name names the collating sequence that is used for the sort. _NULL_ removes any existing sort information. Note: Although the SORTEDBY= option does not sort a data set, it sets the value of the Sorted flag.

114. (A) The set operator OUTER UNION concatenates the results of the queries by: selecting all rows (both unique and nonunique) from both tables, and not overlaying columns. When two or more tables are being joined via OUTER JOIN and they contain two columns with the same name, the CORR keyword may be utilized to overlay the columns.

115. (B) If you need to reference a macro variable within quotation marks, you must use double quotation marks. The macro processor will not resolve macro variable references that appear within single quotation marks. Note: Because of the aforementioned information, the macro processor does not resolve ‘&LOC’. So, basically SAS is told to retrieve values of LOCATION that are equal to ‘&LOC’. Because there are none, no report is generated.

116. (A) An index can help you quickly locate one or more particular observations that you want to read. An index is an optional file that you can create for a SAS data set in order to specify the location of observations based on values of one or more key variables. Indexes can provide direct access to observations in SAS data sets to: yield faster access to small subsets of observations for WHERE processing, return observations in sorted order for BY processing, and perform table lookup operations.

117. (D) Initially, the global macro variable A is assigned a value of cat, and a macro called ANIMAL is created. When ANIMAL is called, its execution creates what otherwise would be a local macro variable. However, because this variable has the same name as the global macro variable (A), it overwrites its value with dog. Therefore, when the macro variable A is resolved in the %PUT statement, the value is now dog. If %LET A=cat had been written anywhere after the macro call, it would have resolved instead to cat.

Advanced SAS 9 Exam Prep: A00-212 | Prepared for Sritej Gunta 118. (C) The DATA step provides functions and CALL routine that enable you to transfer information between an executing DATA step and the macro processor. You can use the SYMPUT routine to create a macro variable and to assign to that variable any value that is available in the DATA step. The syntax is as follows: CALL SYMPUT(macro-variable,text); Where macro-variable is assigned the character value of text. The SYMPUT function is being used to create macro variables with a particular naming convention. First, each new macro variable will begin with the text string ANIMAL. The concatenation operator (||) is used to connect the next additional naming property directly to the end of the text string ANIMAL. The LEFT function left-aligns a character string and the argument here is _N_. _N_ is an automatic variable that is equal to the current iteration of the data step. The data step iterates once for each record in the input data set. So, when the SYMPUT function applies its logic to observation one in the data set MYLIB.MYDATA, it first begins by naming the macro variable ANIMAL and then concatenates the value of _N_ directly to it. So, for observation one, the macro variable ANIMAL1 is created. Looking back at the CALL SYMPUT syntax, the value of the macro variable ANIMAL1 is derived from the value of the variable NAME in that observation. The result is that the macro variable ANIMAL1 is created with a value of Max. This process continues for the remaining observations. The macro variable ANIMAL2 is created with a value of Brown. The macro variable ANIMAL3 is created with a value of Large. The %LET statement creates a macro variable i with a value of 2. With the forward re-scan rule, the two ampersands (&) are resolved for a value of ANIMAL and the macro variable i’s value of 2 completes it for ANIMAL2. Therefore, the title displays, “The value is Brown”

119. (D) The TRANSPOSE procedure creates an output data set by restructuring the values in a SAS data set. When the data set is restructured, selected variables are transposed into observations

120. (D) By default, SAS searches for custom formats in the Work and Library libraries. The FMTSEARCH= system option specifies other catalogs to search when a format is referenced.

121. The data sets ONE and TWO are given below:

YEAR ---2001 2001 2002

ONE QTR --3 4 1

BUDGET -----500 400 350

YEAR ---2001 2002

TWO QTR --4 1

SALES ----300 600

The following SAS program is submitted: PROC SQL; SELECT TWO.*, BUDGET FROM ONE TWO ON ONE.YEAR=TWO.YEAR; QUIT; The following output is desired:

Advanced SAS 9 Exam Prep: A00-212 | Prepared for Sritej Gunta YEAR ---2001 2001 2002

QTR --4 4 1

SALES ----300 300 600

BUDGET ----500 400 350

Which JOIN operator completes the program and generates the desired output? A. B. C. D.

FULL JOIN INNER JOIN LEFT JOIN RIGHT JOIN

122. The data sets ONE and TWO are given below: ONE YEAR ---2001 2001 2003

QTR --3 4 1

BUDGET -----300 500 350

TWO YEAR ---2001 2002

QTR --4 4

SALES ----300 600

The following SAS program is submitted: PROC SQL; SELECT TWO.*, BUDGET FROM ONE TWO ON ONE.YEAR=TWO.YEAR; QUIT; The following output is desired: YEAR ---2001 2001 2002

QTR --4 4 4

SALES ----300 300 600

BUDGET -----300 500 .

Which JOIN operator completes the program and generates the desired output? A. B. C. D.

FULL JOIN LEFT JOIN RIGHT JOIN INNER JOIN

121. (A) A full outer join retrieves both matching rows and nonmatching rows from both tables The ON keyword specifies join condition(s), which are expression(s) that specify the column or columns on which the tables are to be joined. Both the WHERE and ON keywords serve the same purpose. The both subset the Cartesian product of the input files. In this example, the SELECT statement aligns the output with the variables YEAR, QTR, SALES from data set TWO, as well as BUDGET from data set ONE in that order. The FULL JOIN, with the data set TWO being named second, indicate that the values in matching columns which satisfy the ON keyword will overwrite those of data set ONE. So, observation 1 in each data set matches on YEAR=2001, QTR is written with the value 4, SALES is unique to the observation so 300 is used, as well as 500 from BUDGET. Observation 2 from data set ONE matches with observation 1 from data set TWO. So, YEAR=2001, QTR is written with the value of the matching observation from data set TWO, SALES is written with the value from the matching observation in data set TWO, and BUDGET is written with the value in observation 2 of data set ONE. The same logic applies to the remaining output observation.

Advanced SAS 9 Exam Prep: A00-212 | Prepared for Sritej Gunta 122. (C) A right outer join retrieves all rows that match across tables, based on the specified matching criteria (join conditions), plus nonmatching rows from the right table (the second table specified in the FROM clause). The ON keyword specifies join condition(s), which are expression(s) that specify the column or columns on which the tables are to be joined. Both the WHERE and ON keywords serve the same purpose. The both subset the Cartesian product of the input files. In this example, there are two matching rows and one nonmatching row. Because the SELECT statement specifies that the values for YEAR, QTR, and SALES are to be output, those are the values that will be displayed on matching and nonmatching values. Observation 1 from data set TWO matches observation 1 from data set ONE. Therefore, YEAR=2001, QTR=4, SALES=300, and BUDGET=300. Observation 1 from data set TWO matches observation 2 from data set ONE. Therefore, YEAR=2001, QTR=4, SALES=300, and BUDGET=500. Observation 2 from data set TWO has no match (based on the specifications in the ON keyword) with data set ONE. However, the RIGHT JOIN specifies that all matching and nonmatching observations from the RIGHT data set are to be output. So, YEAR=2002, QTR=4, SALES=600, and because there was no match, BUDGET is set to MISSING.

Advanced SAS 9 ExamPrep SritejGunta

Recommend Documents