SAS Advanced Programming Exam for SAS 9
Successful candidates for the SAS Certified Advanced Programmer credential should have
experience in programming and data management using SAS. They will be knowledgeable in
usi
...
SAS Advanced Programming Exam for SAS 9
Successful candidates for the SAS Certified Advanced Programmer credential should have
experience in programming and data management using SAS. They will be knowledgeable in
using advanced DATA step programming statements and efficiency techniques to solve
complex problems, writing and interpreting SAS SQL code, and creating and using the SAS
MACRO facility. Candidates should also be familiar with the enhancements and new
functionality that are available in SAS 9.
Exam Details
65 multiple-choice questions
Must answer 42 correctly to pass
2 hours to complete exam
Prometric Exam ID - (A00-212)
Test Content
Accessing Data Using SQL
Generate detail reports by working with a single table or joining tables using PROC
SQL and the appropriate options
Generate summary reports by working with a single table or joining tables using PROC
SQL and the appropriate options
Construct subqueries within a PROC SQL step
Compare solving a problem using the SQL procedure versus using traditional SAS
programming techniques
Access Dictionary Tables using the SQL procedure
Demonstrate advanced PROC SQL skills by creating and updating tables, updating
data values, working with indexes using the macro interface/creating macro variables
with SQL, defining integrity constraints, SQL views and SET operators
Successful candidates should have experience in programming and data management using
SAS 9 and should be able to
use advanced DATA step programming statements
use efficiency techniques to solve complex problems
write and interpret SAS SQL code
create and use the SAS MACRO facility
Page 3Macro Processing
Creating and using user-defined and automatic macro variables within the SAS Macro
Language
Automate programs by defining and calling macros using the SAS Macro Language
Understand the use of macro functions
Recognize various system options that are available for macro debugging and
displaying values of user-defined and automatic macro variables in the SAS log
Advanced Programming Techniques
Demonstrate advanced data set processing techniques such as updating master data sets,
transposing data, combining/merging data, sampling data, using generation data sets,
integrity constraints and audit trails
Reduce the space required to store SAS data sets and numeric variables within SAS
data sets by using compression techniques, length statements or DATA step views
Develop efficient programs by using advanced programming techniques such as
permanent formats and array processing
Use SAS System options and SAS data set options for controlling memory usage
Control the processing of variables and observations in the DATA step
Create sorted or indexed data in order to avoid unnecessary sorts, eliminate duplicate
data and to provide more efficient data access and retrieval
Use PROC DATASETS to demonstrate advanced programming skills (e.g. renaming
columns, displaying metadata, creating indexes, creating integrity constraints, creating
audit trails)
Page 463 Mark Items for Review
Item 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.
A
Item 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;
A
Item 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);
C. Replace the equal sign with the IN operator.
D. Qualify the column names with the table names.
C
Item 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 ‘,’
C
*Item 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.
D
(Note: You can sort an indexed data file only if you direct the output of the SORT procedure to a
new data file so that the original data file remains unchanged. If you sort an indexed data file
with the FORCE option, the index file is deleted.)
Item 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 Jobcodeorder 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
;
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
;
C
Item 7A 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.
C
Item 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
B
Item 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 Minfrom WORK.TRANSACT
summary by Rep
order by Rep
;
C.
select
Rep,
min(Cost,Ship)
from WORK.TRANSACT
group by Rep
order by Rep
;
D.
select
Rep,
min(Cost+Ship)
from WORK.TRANSACT
group by Rep
order by Rep
;
D
Item 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
D
Item 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.
B
Item 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.)”;
D
Item 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;
Which SQL procedure statement produces the same results?
A.
create table WORK.COMBINE as
select
Id,
Name,
Salary
from
WORK.ONE
full join
WORK.TWOon 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
;
C
Item 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;
D
Item 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 280SMITH 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
A
Item 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
C. outer union corr
D. intersect corr
C
Item 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
D
Item 18
In what order does SAS search for format definitions by default?
A. 1. WORK.FORMATS 2. LIBRARY.FORMATS
B. 1. LIBRARY.FORMATS 2. WORK.FORMATS
C. There is no default order, it must be defined by the user.
D. All user defined libraries that have a catalog named FORMATS, in alphabetic order.
A
Item 19
Given the dataset WORK.STUDENTS:
Name Age
------- ---
Mary 15
Philip 16
Robert 12
Ronald 15
The following SAS program is submitted:
%let Value=Philip;proc print data=WORK.STUDENTS;
[_insert_WHERE_statement_]
run;
Which WHERE statement successfully completes the program and produces a report?
A. where upcase(Name)=upcase(&Value);
B. where upcase(Name)=%upcase(&Value);
C. where upcase(Name)=“upcase(&Value)”;
D. where upcase(Name)=“%upcase(&Value)”;
D
Item 20
The following SAS program is submitted:
data WORK.TEMP;
length A B 3 X;
infile RAWDATA;
input A B X;
run;
What is the length of variable A?
A. 3
B. 8
C. WORK.TEMP is not created - X has an invalid length.
D. Unknown.
A
Item 21
The following SAS program is submitted:
data WORK.NEW;
do i=1, 2, 3;
Next=cats(‘March’|| i );
infile XYZ
filevar=Next
end=Eof;
do until (Eof);
input Dept $ Sales;
end;
end;
run;
The purpose of the FILEVAR=option on the INFILE statement is to name the variable Next, whose value:
A. points to a new input file.
B. is output to the SAS data set WORK.NEW.C. is an input SAS data set reference.
D. points to an aggregate storage location.
A
Item 22
Given the following partial SAS log:
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?
A. CONTENTS FROM SASHELP.CLASS;
B. CREATE FROM SASHELP.CLASS INTO LOG;
C. DESCRIBE TABLE SASHELP.CLASS;
D. VALIDATE SELECT * FROM SASHELP.CLASS;
C
Item 23
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:
%macro SPLIT;
proc sort
data=SASUSER.HIGHWAY
out=WORK.UNIQUES(keep=Status)
nodupkey;
by Status;
run;
data _null_;set uniques end=Lastobs;
call symputx(‘Status’||left(_n_),Status);
if Lastobs then call symputx(‘Count’,_n_);
run;
%local i;
data
%do i=1 %to &count;
[_insert_reference_]
%end;
;
set SASUSER.HIGHWAY;
select(Status);
%do i=1 %to &Count;
when(“[_insert_reference_]”) output [_insert_reference_];
%end;
otherwise;
end;
run;
%mend;
%SPLIT
What macro variable reference completes the program to create the WORK.NOT and WORK.SERIOUS data sets?
A. &Status&i
B. &&Status&i
C. &Status&Count
D. &&Status&Count
B
Item 24
The following SAS program is submitted:
%let Num1=7;
%let Num2=3;
%let Result=%eval(&Num1/&Num2);
%put &Result;
What is the value of the macro variable Result when the %PUT statement executes?
A. 2.3
B. 2
C. . (missing value)
D. 2.33333333333333
BItem 25
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:
%macro HIGHWAY(Belt=no);
proc print data=SASUSER.HIGHWAY;
where Seatbelt=“&Belt” ;
run;
%mend;
%HIGHWAY(Belt=No)
How many observations appear in the generated report?
A. 0
B. 2
C. 3
D. 5
C
Item 26
Given the following SAS data sets:
WORK.VISIT1 WORK.VISIT2
Id Expense Id Cost
--- ------- --- ----
001 500 001 300
001 400 002 600
003 350
The following result set was summarized and consolidated using the SQL procedure:
Id Cost
--- ----
001 300
001 900
002 600
003 350Which of the following SQL statements was most likely used to generate this result?
A.
select
Id,
sum(Expense) label=‘COST'
from WORK.VISIT1
group by 1
union all
select
Id,
sum(Cost)
from WORK.VISIT2
group by 1
order by 1,2
;
B.
select
id,
sum(expense) as COST
from
WORK.VISIT1(rename=(Expense=Cost)),
WORK.VISIT2
where VISIT1.Id=VISIT2.Id
group by Id
order by Id, Cost
;
C.
select
VISIT1.Id,
sum(Cost) as Cost
from
WORK.VISIT1(rename=(Expense=Cost)),
WORK.VISIT2
where VISIT1.Id=VISIT2.Id
group by Id
order by Id, Cost
;
D.select
Id,
sum(Expense) as Cost
from WORK.VISIT1
group by Id
outer union corr
select
Id,
sum(Cost)
from WORK.VISIT2
group by Id
order by 1,2
;
D
Item 27
Given the SAS data sets:
WORK.FIRST WORK.SECOND
Common X Common Y
------ -- ------ --
A 10 A 1
A 13 A 3
A 14 B 4
B 9 B 2
The following SAS program is submitted:
data WORK.COMBINE;
set WORK.FIRST;
set WORK.SECOND;
run;
What data values are stored in data set WORK.COMBINE?
A.
Common X Y
------ -- --
A 10 1
A 13 3
B 14 4
B 9 2
B.
Common X Y------ -- --
A 10 1
A 13 3
A 14 3
B 9 4
B 9 2
C.
Common X Y
------ -- --
A 10 1
A 13 3
A 14 .
B 9 4
B . 2
D.
Common X Y
------ -- --
A 10 1
A 13 1
A 14 1
A 10 3
A 13 3
A 14 3
B 9 4
B 9 2
A
Item 28
Which of the following ARRAY statements is similar to the statement
array Yr{1974:2007} Yr1974-Yr2007;
and will compile without errors?
A. array Yr{34} Yr1974-Yr2007;
B. array Yr{74:07} Yr1974-Yr2007;
C. array Yr{74-07} Yr1974-Yr2007;
D. array Yr{1974-2007} Yr1974-Yr2007;
A
Item 29
The following program is submitted to check the variables Xa, Xb, and Xc in the SASUSER.LOOK data set:
data _null_ WORK.BAD_DATA / view=WORK.BAD_DATA ;
set SASUSER.LOOK(keep=Xa Xb Xc);length _Check_ $ 10 ;
if Xa=. then _check_=trim(_Check_)!!“Xa” ;
if Xb=. then _check_=trim(_Check_)!!“Xb” ;
if Xc=. then _check_=trim(_Check_)!!“Xc” ;
put Xa= Xb= Xc= _check_= ;
run ;
When is the PUT statement executed?
A. when the code is submitted
B. only when the WORK.BAD_DATA view is used
C. both when the code is submitted and the view is used
D. never, the use of _null_ in a view is a syntax error
B
Item 30
The following SAS program is submitted:
%let product=merchandise;
[_insert_%put_statement_]
and the following message is written to the SAS log:
the value is “merchandise”
Which macro statement wrote this message?
A. %put the value is ‘“‘&product.’”’;
B. %put the value is %quote(&product.);
C. %put the value is “&product.”;
D. %put the value is ““&product.””;
C
Item 31
Given the SAS data sets:
WORK.ONE WORK.TWO
X Y SumY
-- -- ----
A 10 36
A 3
A 14
B 9
The following SAS DATA step is submitted:
data WORK.COMBINE;
if _n_=1 then set WORK.TWO;
set WORK.ONE;run;
What data values are stored in data set WORK.COMBINE?
A. An ERROR message is written to the SAS log and the data set WORK.COMBINE is not created.
B.
SumY X Y
---- -- --
36 A 10
C.
SumY X Y
---- -- --
36 A 10
. A 3
. A 14
. B 9
D.
SumY X Y
---- -- --
36 A 10
36 A 3
36 A 14
36 B 9
D
Item 32
The following SAS program is submitted:
data WORK.NEW(bufno=4);
set WORK.OLD(bufno=3);
run;
Why are the BUFNO options used?
A. to reduce memory usage
B. to reduce CPU time usage
C. to reduce the amount of data read
D. to reduce the number of I/O operations
D
Item 33
Given the following program and desired results:
%let Thing1=gift;
%let Thing2=surprise;%let Gift1=book;
%let Gift2=jewelry;
%let Surprise1=dinner;
%let Surprise2=movie;
%let Pick=2;
%let Choice=surprise;
Desired %PUT Results in LOG:
My favorite surprise is a movie
What is the correct %PUT statement that generates the desired results?
A. %put My favorite &Thing&Pick is a &&Choice&Pick;
B. %put My favorite &&Thing&pick is a &&&Choice&Pick;
C. %put My favorite &Choice&pick is a &&Thing&Pick;
D. %put My favorite &&Choice&pick is a &&&Thing&Pick;
B
Item 34
Given the SAS dataset WORK.ONE
Name Salary
----- ------
Hans 200
Maria 205
Jose 310
Ariel 523
The following SAS program is submitted:
proc sql;
[_insert_select_clause_]
from WORK.ONE
;
quit;
The following output is desired:
Salary Bonus
------ -----
200 20
205 20.5
310 31
523 52.3
Which SQL procedure clause completes the program and generates the desired output?
A. select Salary Bonus as Salary*.10 as BonusB. select Salary Bonus=Salary*.10 ‘Bonus'
C. select Salary, Salary*.10 label=‘Bonus'
D. select Salary, Salary*.10 column=“Bonus”
C
Item 35
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 SAS system option?
A. It allows updates in place.
B. It tracks and recycles free space.
C. It allows a permanently stored SAS data set to be replaced.
D. It allows users to access the same SAS data set concurrently.
B
Item 36
Which statement is true for Data step HASH objects?
A. The key component must be numeric.
B. The data component may consist of numeric and character values.
C. The HASH object is created in one step and referenced in another.
D. The HASH object must be smaller than 2 to the 8th power bytes.
B
Item 37
Given the SAS data sets:
WORK.CLASS1 WORK.CLASS2
Name Course Name Class
------ ------ ------- -------
Lauren MATH1 Smith MATH2
Patel MATH1 Farmer MATH2
Chang MATH1 Patel MATH2
Chang MATH3 Hillier MATH2
The following SAS program is submitted:
proc sql;
select Name
from WORK.CLASS1
[_insert_set_operator_]select Name
from WORK.CLASS2
;
quit;
The following output is desired:
Name
------
Chang
Chang
Lauren
Which SQL set operator completes the program and generates the desired output?
A. intersect corr
B. except all
C. intersect all
D. left except
B
Item 38
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. Result is 0
B. Result is 1
C. Result is 10 gt 5
D. Result is true
B
Item 39
The following SAS program is submitted:
%let Mv=shoes;
%macro PRODUCT(Mv=bicycles);
%let Mv=clothes;
%mend;
%PRODUCT(Mv=tents)
%put Mv is &Mv;What is written to the SAS log?
A. Mv is bicycles
B. Mv is clothes
C. Mv is shoes
D. Mv is tents
C
Item 40
Which of the following SAS System options can aid in benchmarking?
A. BUFSIZE= and BUFNO=
B. FULLSTIMER
C. IOBLOCKSIZE=
D. SYSTIMER
B
Item 41
Given the following macro program:
%macro MAKEPGM(NEWNAME, SETNAME, PRINT);
data &NEWNAME;
set &SETNAME;
run;
%if &PRINT=YES %then %do;
proc print data=&NEWNAME.(obs=10);
run ;
%end;
%mend;
Which option would provide feedback in the log about the parameter values passed into this macro when invoked?
A. MPRINT
B. MDEBUG
C. MLOGIC
D. MPARAM
C
Item 42
The NOTSORTED option on the BY statement cannot be used with which other statement or option?
A. SET
B. MERGE
C. IF FIRST.by-variable
D. BY GROUPFORMAT by-variable
BItem 43
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) as Average
from WORK.ONE
[either__insert_SQL_where_clause_]
group by Rep
[_or_ _insert_SQL_having_clause_]
;
quit;
The following output is desired:
Rep Average
----- -------
SMITH 400
Which SQL clause completes the program and generates the desired output?
A. where calculated Average > (select avg(Cost) from WORK.ONE)
B. having Average > (select avg(Cost) from WORK.ONE)
C. having avg(Cost) < (select avg(Cost) from WORK.ONE)
D. where avg(Cost) > (select avg(Cost) from WORK.ONE)
B
Item 44
Which dictionary table provides information on each occurrence of the variable named LastName?
A. DICTIONARY.TABLES
B. DICTIONARY.COLUMNS
C. DICTIONARY.MEMBERS
D. DICTIONARY.VARIABLES
B
Item 45To create a list of unique Customer_Id values from the customer data set, which of the following techniques can be
used?
technique 1: proc SORT with NODUPKEY and OUT=
technique 2: data step with IF FIRST.Customer_Id=1
technique 3: proc SQL with the SELECT DISTINCT statement
A. only technique 1
B. techniques 1 and 2
C. techniques 1 and 3
D. techniques 1, 2, or 3
C
Item 46
Given the SAS data sets:
WORK.CLASS1 WORK.CLASS2
Name Course Name Class
------ ------ ------ -----
Lauren MATH1 Smith MATH2
Patel MATH1 Farmer MATH2
Chang MATH1 Patel MATH2
Hillier MATH2
The following SAS program is submitted:
proc sql;
select Name
from WORK.CLASS1
[_insert_set_operator_]
select Name
from WORK.CLASS2
;
quit;
The following output is desired:
Name
------
Chang
Lauren
Which SQL set operator completes the program and generates the desired output?
A. intersect corr
B. except
C. intersectD. left except
B
Item 47
The following SAS program is submitted:
%macro execute;
[_insert_statement_here_]
proc print data=SASUSER.HOUSES;
run;
%end;
%mend execute;
%execute
Which statement completes the program so that the PROC PRINT step executes on Thursday?
A. if &sysday = Thursday then %do;
B. %if &sysday = Thursday %then %do;
C. %if “&sysday” = Thursday %then %do;
D. %if &sysday = “Thursday” %then %do;
B
Item 48
Given the following program and data:
data WORK.BDAYINFO;
infile datalines;
input Name $ Birthday : mmddyy10.;
datalines;
Alan 11/15/1950
Barb 08/23/1966
Carl 09/01/1963
;
run;
%let Want=23AUG1966;
proc print data=WORK.BDAYINFO;
[_insert_statement_]
run;
What is the WHERE statement that successfully completes the PROC PRINT and selects the observation for Barb?
A. where Birthday=&Want;
B. where Birthday=“&Want”;
C. where Birthday=“&Want”d;
D. where Birthday=‘&Want’d;
CItem 49
Which macro statement would remove the macro variable Mv_Info from the symbol table?
A. %mdelete &Mv_Info;
B. %symerase Mv_Info;
C. %symdel &Mv_Info;
D. %symdel Mv_Info;
D
Item 50
The table WORK.PILOTS contains the following data:
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
A query was constructed to display the pilot salary means at each level of Jobcode and the difference to the overall
mean salary:
Jobcode Average Difference
---------- ----------- ---------
PT1 60000 -15000
PT2 85000 10000
PT3 100000 25000
Which select statement could NOT have produced this output?
A.
select
Jobcode,
avg(Salary) as Average,
calculated Average - Overall as difference
from
WORK.PILOTS,
(select avg(Salary) as Overall from WORK.PILOTS)
group by jobcode
;
B.
select
Jobcode,
avg(Salary) as Average,(select avg(Salary) from WORK.PILOTS) as Overall,
calculated Average - Overall as Difference
from WORK.PILOTS
group by 1
;
C.
select
Jobcode,
Average,
Average-Overall as Difference
from
(select Jobcode, avg(Salary) as Average
from WORK.PILOTS
group by 1),
(select avg(Salary) as Overall
from WORK.PILOTS)
;
D.
select
Jobcode,
avg(Salary) as Average,
calculated Average-(select avg(Salary) from WORK.PILOTS)
as Difference
from WORK.PILOTS
group by 1
;
B
Item 51
The SAS data set WORK.TEMP is indexed on the variable Id:
Id Amount
-- ------
P 52
P 45
A 13
A 56
R 34
R 12
R 78
The following SAS program is submitted:
proc print data=WORK.TEMP;
[_insert_BY_statement_]run;
Which BY statement completes the program, creates a listing report that is grouped by Id, and completes without
errors?
A. by Id;
B. by Id grouped;
C. by Id descending;
D. by descending Id;
A
Item 52
To create a dataset with unique values of a given varible using a data step and the FIRST. and LAST. variables, it is
assumed that the input dataset is:
A. sorted on that variable.
B. indexed by that variable.
C. naturally in order.
D. any of the above A, B, or C
D
Item 53
The SASFILE statement requests that a SAS data set be opened and loaded into memory:
A. one page at a time.
B. one variable at a time.
C. one observation at a time.
D. in its entirety, if possible.
D
Item 54
The following SAS program is submitted:
%let Name1=Shoes;
%let Name2=Clothes;
%let Root=name;
%let Suffix=2;
%put &&&Root&Suffix;
What is written to the SAS log?
A. &Name2
B. Clothes
C. &&&Root&Suffix
D. WARNING: Apparent symbolic reference ROOT2 not resolved.
B
Item 55Given the SAS data sets:
WORK.ONE WORK.TWO
Year Qtr Budget Year Qtr Sales
---- --- ------ ---- --- -----
2001 3 500 2001 4 300
2001 4 400 2002 1 600
2003 1 350
The following SAS program is submitted:
proc sql;
select
TWO.*, budget
from
WORK.ONE
[_insert_join_operator_]
WORK.TWO
on ONE.Year=TWO.Year
;
quit;
The following output is desired:
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. left join
B. right join
C. full join
D. outer join
C
Item 56
The SAS data set WORK.ADDRESSES contains the email addresses of The XYZ Corporation's customers in a
variable named Email_Address. The following DATA step is submitted:
data _null_;
set WORK.ADDRESSES;
[_insert_statement_]
put “filename mail email ‘“ Email_Address ”’; ”;
put “data _null_;”;put “ file mail;”;
put “ put ‘Thank you for your continued’;”;
put “ put ‘support of The XYZ Corporation.’;”;
put “ put ‘We appreciate your patronage.’;”;
put “ put ‘Sincerely,’;” ;
put “ put ‘The XYZ Corporation’;”;
put “run;” ;
run;
Which statement completes the program and creates a SAS program file?
A. infile “c:\email.sas”;
B. output “c:\email.sas”;
C. file “c:\email.sas”;
D. None of the above.
C
Item 57
Which of the following is true about the COMPRESS=YES data set option?
A. It uses the Ross Data Compression method to compress numeric data.
B. It is most effective with character data that contains repeated characters.
C. It is most effective with numeric data that represents large numeric values.
D. It is most effective with character data that contains patterns, rather than simple repetitions.
B
Item 58
Given the SAS dataset WORK.ONE:
Salary
------
200
205
.
523
The following SAS program is submitted:
proc sql;
select *
from WORK.ONE
[_insert_where_clause_]
;
quit;
The following output is desired:
Salary------
200
205
523
Which WHERE expression completes the program and generates the desired output?
A. where Salary is not .
B. where Salary ne missing
C. where Salary ne null
D. where Salary is not missing
D
*Item 59
The SAS data set WORK.TEST has an index on the variable Id and the following SAS program is submitted.
data WORK.TEST;
set WORK.TEST (keep=Id Var_1 Var_2 rename=(Id=Id_Code));
Total=sum(Var_1, Var_2);
run;
Which describes the result of submitting the SAS program?
A. The index on Id is deleted.
B. The index on Id is updated as an index on Id_Code.
C. The index on Id is deleted and an index on Id_Code is created.
D. The index on Id is recreated as an index on Id_Code.
A
Item 60
Given the data set SASHELP.CLASS:
Name Age
------- ---
Mary 15
Philip 16
Robert 12
Ronald 15
The following SAS program is submitted:
%macro MP_ONE(pname=means);
proc &pname data=SASHELP.CLASS;
run;
%mend;
%MP_ONE(print)
%MP_ONE()Which PROC steps execute successfully?
A. PROC MEANS only
B. PROC PRINT only
C. PROC MEANS and PROC PRINT
D. No PROC steps execute successfully
A
Item 61
In a data step merge, the BY variables in all data sets must have the same:
A. name.
B. name and type.
C. name and length.
D. name, type, and length.
B
Item 62
Given the following macro program and invocation:
%macro MAKEPGM(NEWNAME, SETNAME);
data &NEWNAME;
set &SETNAME;
run;
%put ---> inside macro &NEWNAME &SETNAME;
%mend;
%MAKEPGM(WORK.NEW, SASHELP.CLASS)
%put ---> outside macro &NEWNAME &SETNAME;
Which of these choices shows the correct %PUT statement output if the program is submitted at the beginning of a
new SAS session? Note that other lines may be written to the SAS log by the program but only the %PUT output is
shown here.
A. ---> inside macro WORK.NEW SASHELP.CLASS
---> outside invocation WORK.NEW SASHELP.CLASS
B. ---> inside macro WORK.NEW SASHELP.CLASS
---> outside invocation &NEWNAME &SETNAME
C. ---> inside macro &NEWNAME &SETNAME
---> outside invocation WORK.NEW SASHELP.CLASS
D. ---> inside macro &NEWNAME &SETNAME
---> outside invocation &NEWNAME &SETNAME
B
Item 63
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 to produce a report containing four variables?
A. var %COLS1 %COLS2;
B. var %COLS1-%COLS2;
C. var %COLS1 Weight Height;
D. var Weight Height %COLS1;
DSAS® Certification:
Advanced Programming Practice Exam
Certification Practice Exam
Select the best answer for each question and click Score My Practice Exam.
1. Given the following SAS data sets One and Two:
The following SAS program is submitted:
proc sql;
select one.*, sales
from one, two
where one.year=two.year and
one.qtr=two.qtr;
quit;
Which of the following reports is generated?
You should read the introductory material before you take this practice exam.
One
Year Qtr Budget
2001 3 500
2001 4 400
2002 1 700
Two
Year Qtr Sales
2001 4 300
2002 1 600
a. Year Qtr Budget Sales
2001 3 500 300
2002 1 700 600
b.
Year Qtr Budget Sales
2001 4 400 300
2002 1 700 600
c. Year Qtr Budget Sales
2001 3 500 .
2001 4 400 300
2002 1 700 600
d.
Year Qtr Budget Sales
2001 3 500 300
2001 4 400 3002. Given the following SAS data sets One and Two:
The following SAS program is submitted:
proc sql;
select one.*, sales
from one right join two
on one.year=two.year;
quit;
Which one of the following reports is generated?
3. Which one of the following programs is syntactically correct?
2002 1 700 300
2001 3 500 600
2001 4 400 600
2002 1 700 600
One
Year Qtr Budget
2001 3 500
2001 4 400
2002 1 700
Two
Year Qtr Sales
2001 4 300
2002 1 600
a. Year Qtr Budget Sales
2001 3 500 .
b.
Year Qtr Budget Sales
2001 4 400 300
2002 1 700 600
c. Year Qtr Budget Sales
2001 3 500 .
2001 4 400 300
2002 1 700 600
d.
Year Qtr Budget Sales
2001 3 500 300
2001 4 400 300
2002 1 700 600
a. proc sql;
create table forecast as
select a.*, b.sales
from actual a, budget b
where a.dept=b.dept and4. Given the following SAS data sets One and Two:
The following SAS program is submitted:
proc sql;
select *
from one
except
select *
from two;
quit;
Which of the following reports is generated?
a.month=b.month;
quit;
b. proc sql;
create table forecast as
select a.* b.sales
from actual as a, budget as b
where a.dept=b.dept and
a.month=b.month;
quit;
c. proc sql;
create table forecast as
select a.*, b.sales
from actual as a, budget as b
where dept=dept and
month=month;
quit;
d. proc sql;
create table forecast as
select a.*, b.sales
from actual, budget
where a.dept=b.dept and
a.month=b.month;
quit;
One
Num Var1
1 A
1 A
2 B
3 C
Two
Num Var2
1 A
4 Y
4 Z
a. Num
2 3
b.
Num Var1
2 B5. Given the following SAS data sets One and Two:
The following SAS program is submitted:
proc sql;
select *
from one
intersect all
select *
from two;
quit;
Which of the following reports is generated?
3 C
c. Num Var1
1 A
2 B
3 C
d.
Num Var2
1 A
4 Y
4 Z
One
Num Var1
1 A
1 A
2 B
3 C
Two
Num Var2
1 A
4 Y
4 Z
a. Num
1
b.
Num
1 2
c. Num Var1
1 A
d.
Num Var1
1 A
1 A6. Given the following SAS data sets One and Two:
The following SAS program is submitted:
[Show More]
