Download Advanced SAS 9 ExamPrep SritejGunta PDF

Page 30

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. WHERE CALCULATED AVERAGE > (SELECT AVG(COST) FROM ONE);

B. HAVING AVG(COST) > (SELECT AVG(COST) FROM ONE);

C. WHERE AVG(COST) > (SELECT AVG(COST) FROM ONE);
D. HAVING AVG(COST) < (SELECT AVG(COST) FROM ONE);



65. 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. The view SASUSER.RANCH must be processed before SASUSER.CONDO is created

B. SASUSER.CONDO is a stored DATA step program

C. A second VIEW=SASUSER.CONDO option was omitted on the DATA statement
D. A SAS data file and SAS data view cannot be created in the same DATA step



66. Which one of the following options is available for SAS macro debugging?

A. MLOGIC

B. MDEBUG

C. MSGLEVEL
D. MAUTOSOURCE



67. 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. &FIRST

B. YOURNAME

C. &YOURNAME
D. FIRST



68. Which one of the following options controls the page size of a SAS data set?

A. BUFSIZE=

B. BUFNO=

Page 59

Advanced SAS 9 Exam Prep: A00-212 | Prepared for Sritej Gunta
YEAR QTR SALES BUDGET

---- --- ----- -----

2001 4 300 500

2001 4 300 400
2002 1 600 350

Which JOIN operator completes the program and generates the desired output?

A. FULL JOIN

B. INNER JOIN

C. LEFT JOIN

D. RIGHT JOIN



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

ONE TWO

YEAR QTR BUDGET YEAR QTR SALES

---- --- ------ ---- --- -----

2001 3 300 2001 4 300

2001 4 500 2002 4 600
2003 1 350

The following SAS program is submitted:

PROC SQL;

SELECT TWO.*, BUDGET

FROM ONE

<Insert JOIN operator here>

TWO

ON ONE.YEAR=TWO.YEAR;

QUIT;

The following output is desired:

YEAR QTR SALES BUDGET

---- --- ----- ------

2001 4 300 300

2001 4 300 500
2002 4 600 .

Which JOIN operator completes the program and generates the desired output?

A. FULL JOIN

B. LEFT JOIN

C. RIGHT JOIN
D. 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.