Advanced SAS Test 2

Question 1

Given the non-indexed SAS data set TEMP:
TEMP
X Y
P 52
P 45
A 13
A 56
R 34
The following SAS program is submitted:

Proc print data=temp;

run;

Which BY statement completes the program, creates a listing report that is grouped by X and completes without errors?

A. By X grouped;
B. By X;
C. By X notsorted;
D. By descending X

Answer 1

C. By X notsorted

Question 2

Which of the following statement(s) in the DATASETS procedure alters the name of a SAS data set stored in a SAS data library?

A. RENAME statement only
B. CHANGE statement only
C. MODIFY and RENAME statements
D. MODIFY and CHANGE statements

Answer 2

Correct Answer B : The CHANGE Statement
Renames one or more SAS files in the same SAS library.

CHANGE statement changes names by the order that the old-names occur in the directory listing, not in the order that you list the changes in the CHANGE statement.
If the old-name SAS file does not exist in the SAS library, PROC DATASETS stops processing the RUN group containing the CHANGE statement and issues an error message. To override this behavior, use the NOWARN option in the PROC DATASETS statement.
If you change the name of a data set that has an index, the index continues to correspond to the data set.

The RENAME Statement renames variables in the SAS data set specified in the MODIFY statement.

The CHANGE statement changes names by the order that the old-names occur in the directory listing, not in the order that you list the changes in the CHANGE statement.

proc datasets lib=…
change alpha=omega

Question 3

data set: SASHELP.CLASS
NAME AGE
Mary 15
Philip 16
Robert 12
The following SAS program is submitted

%let value = Philip;
proc print data =sashelp.class;

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);

Answer 3

A. where upcase(name)=upcase(“&value”)

Tested

Question 4

options mprint;
%macro test(parm);
proc &parm data = sashelp.prdsale;
run;
%mend;
%test(print)

What is the result of the MPRINT options?

A. It writes macro execution messages to the SAS log
B. It echos the text sent to the SAS compiler

Answer 4

B. It echos the text sent to the SAS compiler

No explanation. Macro successfully runs proc print procedure.

Question 5

At the start of a new SAS session; the following program is submitted:

%macro one;
data _null_;
call symput('proc','measn);
run;
proc &proc data=sashelp.class;
run;
%mend;
%one()

What is the result?

A. The program fails to execute because “proc” is a reserved word.
B. The macro variable “proc” is stored in the global symbol table

Answer 5

B. The macro variable “proc” is stored in the global symbol table

The proc means procedure is successfully completed

Question 6

The following SAS program is submitted:

%let value=9;
%let add=5;
%let newval=%eval(&value/&add);

What is the value of the macro variable NEWVAL?

A. 1.8
B. 2
C. 1
D. Null

Answer 6

C. 1

EVAL(character) any numeric value other than 0 returns true (1) and a value of 0 is false (0).
Returns number when it is an integer: %eval(7) is 7!
Fails when number is rational %eval(10.8) leads to log error message :
ERROR: A character operand was found in the %EVAL function

Question 7

Which of the following is true about the COMPRESS=YES data set option?

A. It is most effective with character data that contains repeated characters
B. It is most effective with characters that contain patterns
C. It is most effective with numeric data

Answer 7

A. It is most effective with character data that contains repeated characters

Question 8

data new;

do i=1,2,3
nextfile=compress('March' || |);
infile abc filevar=nextfile
end=eof;
do until (eof);
input dept $sales;
end;run;

What is the purpose of the FILEVAR=option on the INFILE statement?

A. It names a variable NEXFILE, whose value is a SAS file reference
B. It names a variable NEXFILE, whose change in value caused the infile statement to open a new input file

Answer 8

B. It names a variable NEXFILE, whose change in value caused the infile statement to open a new input file

Question 9

The following SAS program is submitted:

data new(bufsize = 6144 bufno = 4);
set old;
run;

What is the difference between the usage of BUFFSIZE= and BUFNO= options?

Answer 9

BUFFSIZE= specifies the size of the output buffer in bytes
BUFNO= specifies the number of output buffers

Question 10

The SAS data set ONE contains the variables X,Y,Z and W. 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. new, X, Y, and COL1
B. new, X, and COL1 only
C. new, Y, and COL1 only
D. new and X only

Answer 10

B. new, X, and COL1 only

The BY variables themselves aren’t transposed. The variables need to be sorted before running PROC TRANSPOSE. You can sort the variables with PROC SORT.

VAR - [Transpose Column] It lists the actual data that needs to be transposed. If you do not include a VAR statement, the procedure will transpose all numeric variables that are not included in a BY statement or a ID statement. If you want to transpose a character variable, a VAR statement is required.

Question 11

Which SAS integrity constraint type ensures that a specific set or range of values are the only values in a variable?

A. check
B. unique
C. not null
D. primary key

Answer 11

A. check

Question 12

The following SAS program is submitted:

%let test=one;
%let one=two;
%let two=three;
%let three=last;
%put what displays ;
&&&&&test;

What is the written to the SAS log?

A. what displays is three
B. what displays is two
C. what displays is one
D. what displays is last

Answer 12

B. what displays is two

Question 13

What is an advantage of using a hash object in a SAS DATA step?

A. The hash object key values can be multiple numeric and character data values
B. The hash object does not require unique keys

Answer 13

A. The hash object key values can be multiple numeric and character data values

Question 14

What is generated as a result of submitting the RANUNI function with a seed of 123?

A. A consistent sequence of random numbers with each program execution.
B. A random number between 0 and 123.

Answer 14

A. A consistent sequence of random numbers with each program execution.

Question 15

Which one of the following statements completes the above and resolves dept to
“For research&development”?

A. %let dept = %nrstr(For research&development);
B. %let dept = %str(For research&development);

Answer 15

A. %let dept = %nrstr(For research&development);

Explaination: %NRSTR also masks the following characters: &; %

Question 16

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

A. SIZE=
B. BUFNO=
C. BUFSIZE=
D. PAGESIZE=

Answer 16

C. BUFSIZE= Data Set Option

Explaination: Specifies the size of a permanent buffer page for an output SAS data set.
Valid in: DATA step and PROC steps
Category: Data Set Control
Restriction: Use with output data sets only.

Details : 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.
To change the page size, use a DATA step to copy the data set and either specify a new page or use the SAS default. To reset the page size to the default value in your operating environment, use BUFSIZE=0

Question 17

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?

A. I/O usage only

B. CPU usage only

C. I/O and CPU usage

D. temporary disk usage

Answer 17

Correct Answer : D temporary disk usage

TAGSORT stores only the BY variables and the observation numbers in temporary files. The BY variables and the observation numbers are called tags. At the completion of the sorting process, PROC SORT uses the tags to retrieve records from the input data set in sorted order.

Note: The utility file created is much smaller than it would be if the TAGSORT option were not specified.
Restriction: The TAGSORT option is not compatible with the OVERWRITE option.
Interaction: The TAGSORT option is not supported by the multi-threaded sort.
Tip: When the total length of BY variables is small compared with the record length, TAGSORT reduces temporary disk usage considerably. However, processing time might be much higher.

Using the TAGSORT Option
You can also use the TAGSORT option 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 completion of the sorting process, PROC SORT uses the tags to retrieve records from the input data set in sorted order.

Question 18

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)
%houses(dsn = ranch)
%houses(sub = TWOSTORY)
Which one of the following is the value of the automatic macro variable SYSLAST?

1. work.ranch
2. work.houses
3. WORK.RANCH
4. WORK.HOUSES

Answer 18

4. WORK.HOUSES

SYSLAST the name of the most recently created SAS data set, in the form LIBREF.NAME. This value is always stored in ALL capital letters.

Question 19

How many statements does the program below contain?

proc sql;
 select grapes,oranges,
 grapes + oranges as sumsales
 from sales.produce
 order by sumsales;

A. 2

B. 3

C. 4

D. 5

Answer 19

A. 2

There are two statements, the PROC SQL statement and the SELECT statement. The SELECT statement contains three clauses.

Question 20

Complete the following PROC SQL query to select the columns Address and SqFeet from the table List.Size
and to select Price from the table List.Price. (Only the Address column appears in both tables.)
proc sql;
_____________
from list.size,list.price;

A. select address,sqfeet,price

B. select size.address,sqfeet,price

C. select price.address,sqfeet,price

D. either 2 or 3

Answer 20

A. select size.address,sqfeet,price

The SELECT clause lists the columns from both tables to be queried. You must use a prefix with the Address column because it appears in both tables. The prefix specifies the table from which you want the column to be read.

Question 21

Question 14 : The variable attributes of SAS data sets ONE and TWO are shown below:
ONE 
# Variable Type Len Pos 
2 sales Num 8 8 
1 year Num 8 0

TWO
# Variable Type Len Pos
2 budget Num 8 8
3 sales Char 8 16
1 year Num 8 0

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. No messages are written to the SAS log.

B. ERROR and WARNING messages are written to the SAS log.

C. Data set THREE is created with two variables and 50 observations.

D. Data set THREE is created with three variables and 100 observations.

Answer 21

B. ERROR and WARNING messages are written to the SAS log.

Any variables that have the same name in multiple data sets in the merge statement must also have the same type.

Question 22

Question 15 : Which of the clauses below correctly sorts rows by the values of the columns Price and SqFeet?

A. order price, sqfeet

B. order by price,sqfeet

C. sort by price sqfeet

D. sort price sqfeet

Answer 22

B. order by price,sqfeet

Question 23

Question 16 : Which clause below specifies that the two tables Produce and Hardware be queried?
Both tables are located in a library to which the libref Sales has been assigned.

A. select sales.produce sales.hardware

B. from sales.produce sales.hardware

C. from sales.produce,sales.hardware

D. where sales.produce, sales.hardware

Answer 23

C. from sales.produce,sales.hardware

Question 24

Given the following SAS statement:
 %let idcode = Prod567;
 Which one of the following statements stores the value 567 in the macro variable CODENUM?

A.  
%let codenum = substr(&idcode,length(&idcode)-2);

B.  
%let codenum = substr(&idcode,length(&idcode)-3);

C.  
%let codenum = %substr(&idcode,%length(&idcode)-2);

D.  
%let codenum = %substr(&idcode,%length(&idcode)-3);

Answer 24

C.  
%let codenum = %substr(&idcode,%length(&idcode)-2);

Retrieve the substring from the 5’th position

Example:

a=’KIDNAP’;
substr(a,1,3)=’CAT’;
put a;
CATNAP

b=a;
substr(b,4)=’TY’;
put b;
CATTY

Question 25

What happens if you use a GROUP BY clause in a PROC SQL step without a summary function?

A. The step does not execute.

B. The first numeric column is summed by default.

C. The GROUP BY clause is changed to an ORDER BY clause.

D. The step executes but does not group or sort data.

Answer 25

C. The GROUP BY clause is changed to an ORDER BY clause.

PROC SQL calculates the aggregate function separately for each group. When you do not use an aggregate function, PROC SQL treats the GROUP BY clause as if it were an ORDER BY clause, and any aggregate functions are applied to the entire table.

The following query uses the SUM function to list the total population of each continent. The GROUP BY clause groups the countries by continent, and the ORDER BY clause puts the continents in alphabetical order:
 select Continent, sum(Population)
 from sql.countries
 group by Continent
 order by Continent;

The GROUP BY clause is used in queries that include one or more summary functions. If you specify a GROUP BY clause in a query that does not contain a summary function, your clause is changed to an ORDER BY clause.

Question 26

Which statement is true regarding the use of the PROC SQL step to query data that is stored in two or more tables?

A. When you join multiple tables, the tables must contain a common column.

B. You must specify the table from which you want each column to be read.

C. The tables that are being joined must be from the same type of data source.

D. If two tables that are being joined contain a same-named column, then you must specify the table from which you want the column to be read.

Answer 26

D. If two tables that are being joined contain a same-named column, then you must specify the table from which you want the column to be read.

When multiple tables, views, or query-expressions are listed in the FROM clause, they are processed to form one table. The resulting table contains data from each contributing table. These queries are referred to as joins.
Conceptually, when two tables are specified, each row of table A is matched with all the rows of table B to produce an internal or intermediate table. The number of rows in the intermediate table (Cartesian product) is equal to the product of the number of rows in each of the source tables. The intermediate table becomes the input to the rest of the query in which some of its rows can be eliminated by the WHERE clause or summarized by a summary function.
A common type of join is an equijoin, in which the values from a column in the first table must equal the values of a column in the second table.

If you are joining two tables that contain a same-named column, then you must use a prefix to specify the table(s) from which you want the column to be read. Remember that if you join tables that don’t contain columns that have matching data values, you can produce a huge amount of output. Be sure to specify a WHERE clause to select only the rows that you want.

Question 27

Which clause in the following program is incorrect?
proc sql;
select sex,mean(weight) as avgweight
from company.employees company.health
where employees.id=health.id
group by sex;

A. SELECT

B. FROM

C. WHERE

D. GROUP BY

Answer 27

B. FROM

The table names that are specified in the FROM clause must be separated by commas. Note that you can specify columns in the WHERE clause that are not specified in the SELECT clause.