Advance SAS Certification Quiz

1. Which of the following will cause PROC SQL to list rows that have no data in the address column?

Where address is missing

Where address not exists

Where address is null

Both a and c

To list rows that have no data (that is, missing data), you can use either of these other conditional operators: IS MISSING or IS NULL. The NOT EXISTS operator is used specifically with a subquery, and resolves to true if the subquery returns no values to the outer query.

Explanation

To list rows that have no data (that is, missing data), you can use either of these other conditional operators: IS MISSING or IS NULL. The NOT EXISTS operator is used specifically with a subquery, and resolves to true if the subquery returns no values to the outer query.

2. Consider the following PROC SQL query:

proc sql;
select lastname, firstname,
total, since from charity.donors where not exists
(select lastname
from charity.current where donors.lastname =
current.lastname);

The query references two tables:

• Charity.Donors lists name and contact information for all donors who have made contributions since the charity was founded. The table also contains these two columns: Total, which shows the total dollars given by each donor, and Since, which stores the first year in which each donor gave money.

• Charity.Current lists the names of all donors who have made contributions in the
current year, and the total dollars each has given this year (YearTotal). Assume that the values of LastName are unique in both tables.
The output of this query displays

All donors whose rows do not contain any missing values.

All donors who made a contribution in the current year.

All donors who did not make a contribution in the current year.

Ll donors whose current year's donation in Charity.Current has not yet been added to Total in Charity.Donors.

In this PROC SQL query, the outer query uses the operator NOT EXISTS with a correlated subquery. The outer query selects all rows from Charity.Donors whose names do not appear in Charity.Current. In other words, this PROC SQL query output lists all donors who did not make a contribution in the current year.

Explanation

In this PROC SQL query, the outer query uses the operator NOT EXISTS with a correlated subquery. The outer query selects all rows from Charity.Donors whose names do not appear in Charity.Current. In other words, this PROC SQL query output lists all donors who did not make a contribution in the current year.

3. You are creating a PROC SQL query that will list all employees who have spent (or overspent) their allotted 120 hours of vacation for the current year. The hours that each employee used are stored in the existing column Spent. Your query defines a new column, Balance, to calculate each employee's balance of vacation hours. Which query will produce the report that you want?

Proc sql;select name, spent,120-spent as calculated Balancerom Company.Absenceswhere balance <= 0;

Proc sql;select name, spent,120-spent as Balancefrom Company.Absenceswhere calculated balance <= 0;

Proc sql;select name, spent,120-spent as Balance from Company.Absences where balance <= 0;

Proc sql;select name, spent,120-spent as calculated Balance from Company.Absenceswhere calculated balance <= 0;

When a WHERE clause references a new column that was defined in the SELECT clause, the WHERE clause must specify the keyword CALCULATED before the column name.

Explanation

When a WHERE clause references a new column that was defined in the SELECT clause, the WHERE clause must specify the keyword CALCULATED before the column name.

4. By definition, a noncorrelated subquery is a nested query that

Returns a single value to the outer query.

Contains at least one summary function.

Executes independently of the outer query.

Requires only a single value to be passed to it by the outer query.

A noncorrelated subquery is a nested query that executes independently of the outer query. The outer query passes no values to the subquery.

Explanation

A noncorrelated subquery is a nested query that executes independently of the outer query. The outer query passes no values to the subquery.

5. You are writing a PROC SQL query that will display the names of all library cardholders who work as volunteers for the library, and the number of books each volunteer currently has checked out. You will use one or both of the following tables: List.Circulation lists the name and contact information for all library cardholders, and the number of books that each cardholder currently has checked out.
Library.Volunteers lists the name and contact information for all library volunteers.
Assume that the values of Name are unique in both tables. Which query will produce your report?

Proc sql;select name, checkedoutfrom library.circulation where * in(select * from library.volunteers);

Proc sql;select name, checkedoutfrom library.circulation where name in(select namefrom library.volunteers);

Proc sql; select namefrom library.volunteers where name, checkedout in(select name, checkedout from library.circulation);

Proc sql;select name, checkedoutfrom library.circulation where name in(select name from library.volunteers;);

Your PROC SQL query needs to use data from both tables. The outer query reads the name and number of books checked out from Library.Circulation. The multiple-value noncorrelated subquery selects the names of volunteers from Library.Volunteers and passes these names back to the outer query. The outer query then selects data for only the volunteers whose names match names returned by the subquery. The subquery is indented under the outer query's WHERE clause, is enclosed in parentheses, and does not require a semicolon inside the closing parenthesis.

Explanation

Your PROC SQL query needs to use data from both tables. The outer query reads the name and number of books checked out from Library.Circulation. The multiple-value noncorrelated subquery selects the names of volunteers from Library.Volunteers and passes these names back to the outer query. The outer query then selects data for only the volunteers whose names match names returned by the subquery. The subquery is indented under the outer query's WHERE clause, is enclosed in parentheses, and does not require a semicolon inside the closing parenthesis.

6. Consider this PROC SQL query:
proc sql;
select flightnumber,
count(*) as Flights, avg(boarded) label="Average Boarded" format=3.
from sasuser.internationalflights group by flightnumber
having avg(boarded) > 150;
The table Sasuser.Internationalflights contains 201 rows, 7 unique values of FlightNumber, 115 unique values of Boarded, and 4 different flight numbers that have an average value of Boarded that is greater than 150. How many rows of output will the query generate?

150

7

4

1

To determine how PROC SQL calculates and displays output from summary functions, consider the key factors. This PROC SQL query has a GROUP BY clause, and it does not specify any columns that are outside of summary functions. Therefore, PROC SQL calculates and displays the summary function for each group. There are 7 unique values of FlightNumber, but the HAVING clause specifies only the flights that have an average number of boarded passengers greater than 150. Because 4 of the 7 flight numbers meet this condition, the output will contain 4 rows.

Explanation

To determine how PROC SQL calculates and displays output from summary functions, consider the key factors. This PROC SQL query has a GROUP BY clause, and it does not specify any columns that are outside of summary functions. Therefore, PROC SQL calculates and displays the summary function for each group. There are 7 unique values of FlightNumber, but the HAVING clause specifies only the flights that have an average number of boarded passengers greater than 150. Because 4 of the 7 flight numbers meet this condition, the output will contain 4 rows.

7. Which statement about the following PROC SQL query is false?

proc sql; validate
select name label='Country', rate label='Literacy Rate'
from world.literacy where 'Asia' =
(select continent
from world.continents where literacy.name =
continents.country)
order by 2;

The query syntax is not valid.

The outer query must pass values to the subquery before the subquery can return values to the outer query.

After the query is submitted, the SAS log will indicate whether the query has valid syntax.

The syntax in this PROC SQL query is valid, so the first statement is false. The query contains a correlated subquery, so the second statement is true. The VALIDATE keyword is used after the PROC SQL statement, so the third statement is true. And the last statement correctly indicates that the VALIDATE keyword causes the SAS log to display a special message if the query syntax is valid, or standard error messages if the syntax is not valid.

Explanation

The syntax in this PROC SQL query is valid, so the first statement is false. The query contains a correlated subquery, so the second statement is true. The VALIDATE keyword is used after the PROC SQL statement, so the third statement is true. And the last statement correctly indicates that the VALIDATE keyword causes the SAS log to display a special message if the query syntax is valid, or standard error messages if the syntax is not valid.

8. A public library has several categories of books. Each book in the library is assigned to only one category. The table Library.Inventory contains one row for each book in the library. The Checkouts column indicates the number of times that each book has been checked out.
You want to display only the categories that have an average circulation (number of checkouts) that is less than 2500.

Does the following PROC SQL query produce the results that you want?

proc sql;
title 'Categories with Average Circulation'; title2 'Less Than 2500';
select category,
avg(checkouts) as AvgCheckouts
from library.inventory having avg(checkouts) < 2500 order by 1;

No. This query will not run because a HAVING clause cannot contain a summary function.

No. This query will not run because the HAVING clause must include the CALCULATED keyword before the summary function.

No. Because there is no GROUP BY clause, the HAVING clause treats the entire table as one group.

Yes

PROC SQL can execute this query, but the query will not produce the results that you want. If you omit the GROUP BY clause in a query that contains a HAVING clause, then the HAVING clause and any summary functions treat the entire table as one group. Without a GROUP BY clause, the HAVING clause in this example calculates the average circulation for the table as a whole (all books in the library), not for each group (each category of books). The output contains either all the rows in the table (if the average circulation for the entire table is less than 2500) or none of the rows in the table (if the average circulation for the entire table is greater than 2500).

Explanation

PROC SQL can execute this query, but the query will not produce the results that you want. If you omit the GROUP BY clause in a query that contains a HAVING clause, then the HAVING clause and any summary functions treat the entire table as one group. Without a GROUP BY clause, the HAVING clause in this example calculates the average circulation for the table as a whole (all books in the library), not for each group (each category of books). The output contains either all the rows in the table (if the average circulation for the entire table is less than 2500) or none of the rows in the table (if the average circulation for the entire table is greater than 2500).

9. Which statement about data remerging is true?

When PROC SQL remerges data, it combines data from two tables.

By using data remerging, PROC SQL can avoid making two passes through the data.

When PROC SQL remerges data, it displays a related message in the SAS log.

PROC SQL does not attempt to remerge data unless a subquery is used.

not-available-via-ai

Explanation

not-available-via-ai

10. Which PROC SQL query will remove duplicate values of MemberType from the query output, so that only the unique values are listed?

Proc sql nodup; select membertypefrom sasuser.frequentflyers;

Proc sql;select distinct(membertype)as MemberTypefrom sasuser.frequentflyers;

Proc sql;select unique membertypefrom sasuser.frequentflyers group by member type;

Proc sql;select distinct membertypefrom sasuser.frequentflyers;

to remove duplicate values from the PROC SQL output, you specify the DISTINCT keyword before the column name in the SELECT clause.

Explanation

to remove duplicate values from the PROC SQL output, you specify the DISTINCT keyword before the column name in the SELECT clause.