SASAdv12月真题-63题.doc

1. When attempting to minimize memory usage, the most efficient way to do group processing when using the MEANS procedure is to use:

A. the BY statement.
B. GROUPBY with the NOTSORTED specification.
C. the CLASS statement.
D. multiple WHERE statements.
(The question is pretty clear-cut, IMO. It alludes to the "standard" long-
> bearded practical trick used in the situation when MEANS/SUMMARY with CLASS
> uses too much memory. It does not ask what is more efficient in general
> (which would be to use something else instead of MEANS if possible), just
> what leads to the minimal memory footprint, and the answer is A. (Answer B
> is just a distraction - a common device in computer-adaptive tests). The
> reason is that without BY, MEANS creates and builds its AVL tree for the
> entire file and all categorical values crossings found in CLASS. With BY,
> the proc builds the tree for the current BY group only, computes the stats,
> then erases the tree before the next BY group starts, creates and builds it
> again, and so on. The erase/create process is not a zero cost one, so the
> smaller the more numerous the BY groups are, the more it slows does the
> processing; however, the smaller is the largest BY group, the smaller is the
> memory footprint.)

2. The SAS data set WORK.CHECK has a variable named Id_Code in it. Which SQL statement would create an index on this variable?
A. create index Id_Code on WORK.CHECK;
B. create index(Id_Code) on WORK.CHECK;
C. make index=Id_Code from WORK.CHECK;
D.define index(Id_Code) in WORK.CHECK;

3. Given the SAS data sets:
WORK.EMPLOYEE WORK.NEWEMPLOYEE

Name Dept Names Salary
-------- ----- -------- ---
Alan Sales Michelle 50000
Michelle Sales Paresh 60000

A SAS program is submitted and
the following is written to the SAS log:

101 proc sql;
102 select dept, name
103 from WORK.EMPLOYEE
104 where name=select names
from newemployee
where salary > 40000)
ERROR: Subquery evaluated to more than one row.
105 ;
106 quit;

What would allow the program to successfully execute without errors?

A. Replace the where clause with:

where EMPLOYEE.Name=(select Names delimited with ','
from WORK.NEWEMPLOYEE
where Salary > 40000);

B. Replace line 104 with:

where EMPLOYEE.Name =ANY (select Names separated with ','
from WORK.NEWEMPLOYEE
where Salary > 40000);
（any表示要有多个值，而=只能要求一个值。）
C. Replace the equal sign with the IN operator.

D. Qualify the column names with the table names.

4. Given the SAS data set SASUSER.HIGHWAY:

Steering Seatbelt Speed Status Count
---- -------- ----- ------- -----
absent No 0-29 serious 31
absent No 0-29 not 1419
absent No 30-49 serious 191
absent no 30-49 not 2004
absent no 50+ serious 216

The following SAS program is submitted:

proc sql noprint;
select distinct
Speed [_insert_SQL_clause_]
from SASUSER.HIGHWAY
;
quit;

title1 "Speed values represented are: &GROUPS";
proc print data=SASUSER.HIGHWAY;
run;

Which SQL clause stores the text 0-29, 30-49, 50+ in the macro variable GROUPS?

A. into &GROUPS
B. into :GROUPS
C. into :GROUPS separated by ','
D. into &GROUPS separated by ','
(2012/11/17日考题中选项有改动，但是答案没有变。)
5. The SAS data set WORK.CHECK has an index on the variable Code and the following SAS program is submitted.
proc sort data=WORK.CHECK;
by Code;
run;

Which describes the result of submitting the SAS program?
A. The index on Code is deleted.
B. The index on Code is updated.
C. The index on Code is uneffected.
D. The sort does not execute.
（在于有了index的variable，不能再用by来SORT了。）
6. The table WORK.PILOTS contains the following data:
WORK.PILOTS
Id Name Jobcode Salary
--- ------ ------- ------
001 Albert PT1 50000
002 Brenda PT1 70000
003 Carl PT1 60000
004 Donna PT2 80000
005 Edward PT2 90000
006 Flora PT3 100000

The data set was summarized to include average
salary based on jobcode:

Jobcode Salary Avg
------- ------ -----
PT1 50000 60000
PT1 70000 60000
PT1 60000 60000
PT2 80000 85000
PT2 90000 85000
PT3 100000 100000

Which SQL statement could NOT generate this result?

A. select
Jobcode,
Salary,
avg(Salary) label='Avg'
from WORK.PILOTS
group by Jobcode
order by Id
;

B. select
Jobcode,
Salary,
(select avg(Salary)
from WORK.PILOTS as P1
where P1.Jobcode=P2.Jobcode) as Avg
from WORK.PILOTS as P2
order by Id
;
（这里利用的是自身的数据集，分别设置不同的别名，可以当做两个数据集来使用。不过这里WHERE语句很值得学习，我个人对这里的WHERE语句这样理解的：(select avg(Salary) from WORK.PILOTS as P1 where P1.Jobcode=P2.Jobcode)启示执行之后返回的是三个AVG均值，但是因为这三个AVG均值都是与JOBCODE匹配的，那么前面的select Jobcode, Salary，所以当执行到(select avg(Salary) from WORK.PILOTS as P1 where P1.Jobcode=P2.Jobcode)这里时，实际上是选择了select jobcode 中jobcode对应的avg均值。）
C. select
Jobcode,
Salary,
(select avg(Salary)
from WORK.PILOTS
group by Jobcode) as Avg
from WORK.PILOTS
order by Id
;
(子查询涉及不止一项。)
D. select
Jobcode,
Salary,
Avg
from
WORK.PILOTS,
(select
Jobcode as Jc,
avg(Salary) as Avg
from WORK.PILOTS
group by 1)
where Jobcode=Jc
order by Id
;

7. A quick rule of thumb for the space required to run PROC SORT is:
A. two times the size of the SAS data set being sorted.
B. three times the size of the SAS data set being sorted.
C. four times the size of the SAS data set being sorted.
D. five times the size of the SAS data set being sorted.
(140-2010: Dear Miss SASAnswers: A Guide to Sorting Your Data
中的描述是这样的：
If you want the sort to complete entirely in memory, a simple rule of thumb is four times the size of the data set.
In releases prior to SAS 9, the required workspace is approximately three to four
times the size of the data file. Beginning with SAS 9, the required workspace is
approximately twice the size of the data file. The workspace can be allocated in memory and/or on disk as a utility file, depending on which sort utility and options are specified.

Dear MissSASAnswers,
Can you please give me some guidance on how much memory it takes to sort a 2GB table Signed,
Memory Deprived
Dear Memory Deprived,
If you want the sort to complete entirely in memory, a simple rule of thumb is four times the size of the data set.
And I'm assuming the data set is not compressed or being subset with a DROP=/KEEP= data set option or a WHERE statement.
A better estimate would be to use this formula to predict the amount of memory:
((length of observation+sum of lengths of BY variables)*number of observations)* 1.10
The amount of space that the SAS sort needs depends on the following conditions:
• whether the sort can be done with threading
• the length of the observations
• the number of variables in the BY statement and their storage lengths
• the operating environment in which PROC SORT executes
• whether the LINGUISTIC= option is being used (that takes more memory)
Use the SORTSIZE= option in the PROC SORT statement to do the following:
• specify the amount of memory that is available to the SORT procedure
• improve the sort performance by restricting the swapping of memory to disk that is controlled by the
operating system
)
8. Multi-threaded processing for PROC SORT will effect which of these system resources?
A. CPU time will decrease, wall clock time will decrease
B. CPU time will increase, wall clock time will decrease
C. CPU time will decrease, wall clock time will increase
D. CPU time will increase, wall clock time will increase

9. Given the SAS data set WORK.TRANSACT:
Rep Cost Ship
----- ----- -----
SMITH 200 50
SMITH 400 20
JONES 100 10
SMITH 600 100
JONES 100 5

The following output is desired:

Rep
----- ----
JONES 105
SMITH 250

Which SQL statement was used?

A. select
rep,
min(Cost+Ship)
from WORK.TRANSACT
order by Rep
;

B. select
Rep,
min(Cost,Ship) as Min
from WORK.TRANSACT
summary by Rep
order by Rep
;

C. select
Rep,
min(Cost,Ship)
from WORK.TRANSACT
group by Rep
order by Rep
;
（改成min(sum(cost,ship))才对）

D. select
Rep,
min(Cost+Ship)
from WORK.TRANSACT
group by Rep
order by Rep
;

10. The following SAS program is submitted:
%let Value=9;
%let Add=5;
%let Newval=%eval(&Value/&Add);
%put &Newval;

What is the value of the macro variable Newval when the %PUT statement executes?
A. 0.555
B. 2
C. 1.8
D. 1

11. The following SAS code is submitted:
data WORK.TEMP WORK.ERRORS / view=WORK.TEMP;
infile RAWDATA;
input Xa Xb Xc;
if Xa=. then output WORK.ERRORS;
else output WORK.TEMP;
run;

Which of the following is true of the WORK.ERRORS data set?
A. The data set is created when the DATA step is submitted.
B. The data set is created when the view TEMP is used in another SAS step.
C. The data set is not created because the DATA statement contains a syntax error.
D. The descriptor portion of WORK.ERRORS is created when the DATA step is submitted.
（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.
假设在d盘建SAS文件夹下有view.txt文件：
. 10 11
10 11 12
. 10 12
21 22 23
在SAS编程如下：
filename rawdata 'd:\sas\view.txt';
data WORK.TEMP WORK.ERRORS / view=WORK.TEMP;
infile rawdata;
input Xa Xb Xc;
if Xa=. then output WORK.ERRORS;
else output WORK.TEMP;
run;

proc print data=temp;
run;这道题很有味道。

如果rawdata是在SAS安装系统的默认地址下，那么加上分号就可以了。
）
12. Which title statement would always display the current date?
A. title "Today is: &sysdate.";
B. title "Today is: &sysdate9.";
C. title "Today is: &today.";
D. title "Today is: %sysfunc(today(),worddate.)";

13. Given the SAS data sets:
WORK.ONE WORK.TWO

Id Name Id Salary
--- ------ --- ------
112 Smith 243 150000
243 Wei 355 45000
457 Jones 523 75000

The following SAS program is submitted:
data WORK.COMBINE;
merge WORK.ONE WORK.TWO;
by Id;
run;
( like horizontal full join )
Which SQL procedure statement produces the same results?
A. create table WORK.COMBINE as
select
Id,
Name,
Salary
from
WORK.ONE
full join
WORK.TWO
on ONE.Id=TWO.Id
;
B.create table WORK.COMBINE as
select
coalesce(ONE.Id, TWO.Id) as Id,
Name,
Salary
from
WORK.ONE,
WORK.TWO
where ONE.Id=TWO.Id
;

C. create table WORK.COMBINE as
select
coalesce(ONE.Id, TWO.Id) as Id,
Name,
Salary
from
WORK.ONE
full join
WORK.TWO
on ONE.Id=TWO.Id
order by Id
;
D. create table WORK.COMBINE as
select
coalesce(ONE.Id, TWO.Id) as Id,
Name,
Salary
from
WORK.ONE,
WORK.TWO
where ONE.Id=TWO.Id
order by ONE.Id
;

14. The following SAS program is submitted:
proc contents data=TESTDATA.ONE;
run;

Which SQL procedure step produces similar information about the column attributes of TESTDATA.ONE?

A. proc sql;
contents from TESTDATA.ONE;
quit;
B. proc sql;
describe from TESTDATA.ONE;
quit;
C. proc sql;
contents table TESTDATA.ONE;
quit;
D. proc sql;
describe table TESTDATA.ONE;
quit;

15 Given the SAS data set WORK.ONE:
Rep Cost
----- ----
SMITH 200
SMITH 400
JONES 100
SMITH 600
JONES 100

The following SAS program is submitted;
proc sql;
select
Rep,
avg(Cost)
from WORK.ONE
order by Rep
;
quit;

Which result set would be generated?

A.
JONES 280
JONES 280
SMITH 280
SMITH 280
SMITH 280
B.
JONES 600
SMITH 100
C.
JONES 280
SMITH 280
D.
JONES 100
JONES 100
SMITH 600
SMITH 600
SMITH 600

16. Given the SAS data sets:
WORK.MATH1A WORK.MATH1B
Name Fi Name Fi
------- -- ------ --
Lauren L Smith M
Patel A Lauren L
Chang Z Patel A
Hillier R

The following SAS program is submitted:
proc sql;
select *
from WORK.MATH1A
[_insert_set_operator_]
select *
from WORK.MATH1B
;
quit;

The following output is desired:

Name Fi
------- --
Lauren L
Patel A
Chang Z
Hillier R
Smith M
Lauren L
Patel A

Which SQL set operator completes the program and generates the desired output?
A. append corr
B. union corr
（select unique rows）
C. outer union corr
(using corr to overlay by the same names)
D. intersect corr

17. Which of the following is an advantage of SAS views?
A. SAS views can access the most current data in files that are frequently updated.
B. SAS views can avoid storing a SAS copy of a large data file.
C. SAS views can decrease programming time.
D. both A and B are true

18. In what order does SAS search for format definitions by default?
A. 1. WORK.FORMATS 2. LIBRARY.FORMATS
B. 1. LIBRARY.FORMATS 2. WO