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
[Show More]
.png)
.png)
