Chapter 7: Creating and Managing Views Using PROC SQL

1. PROC SQL views can access data from

A SAS data file.

Another PROC SQL view.

A relational database table.

All of the above

PROC SQL views can access data from a SAS data file, a DATA step view, a PROC SQL view, or a relational database table.

Explanation

PROC SQL views can access data from a SAS data file, a DATA step view, a PROC SQL view, or a relational database table.

2. Which of the following PROC SQL steps enables you to see a description of the view definition?

Proc sql; select * from sasuser.payrollmasterv;

Proc sql; describe view sasuser.payrollmasterv;

Proc sql; list sasuser.payrollmasterv;

Proc sql; contents view=sasuser.payrollmasterv;

The DESCRIBE VIEW statement displays the view definition in the SAS log.

Explanation

The DESCRIBE VIEW statement displays the view definition in the SAS log.

3. Which of the following statements describes an advantage of using a PROC SQL view?

Views often save space, because a view is usually quite small compared with the data that it accesses.

Views prevent users from continually submitting queries to omit unwanted columns or rows.

Views hide complex joins or queries from users.

All of the above

PROC SQL views are useful because they

often save space (a view is usually quite small compared with the data that it accesses)

prevent users from continually submitting queries to omit unwanted columns or rows

hide complex joins or queries from users.

In addition, PROC SQL views

ensure that input data sets are always current, because data is derived from tables at execution time

can be used to shield sensitive or confidential columns from users while enabling the same users to view other columns in the same table.

Explanation

PROC SQL views are useful because they

often save space (a view is usually quite small compared with the data that it accesses)

prevent users from continually submitting queries to omit unwanted columns or rows

hide complex joins or queries from users.

In addition, PROC SQL views

ensure that input data sets are always current, because data is derived from tables at execution time

can be used to shield sensitive or confidential columns from users while enabling the same users to view other columns in the same table.

4. Which of the following programs drops (deletes) a view?

Proc sql; delete sasuser.newview;

Proc sql; drop view sasuser.newview;

Proc sql; erase view sasuser.newview;

Proc sql; remove newview from sasuser;

The DROP VIEW statement drops a view from the specified library.

Explanation

The DROP VIEW statement drops a view from the specified library.

5. Which PROC SQL step creates a view that queries the table Sasuser.Payrollmaster?

Proc sql; insert into sasuser.newview select * from sasuser.payrollmaster;

Proc sql; create sasuser.newview as select * from sasuser.payrollmaster;

Proc sql; create view sasuser.newview as select * from sasuser.payrollmaster;

Proc sql; select * from sasuser.payrollmaster into view sasuser.newview;

You use the CREATE VIEW statement to create a view. The keywords CREATE VIEW are followed by the name of the view and the keyword AS.

Explanation

You use the CREATE VIEW statement to create a view. The keywords CREATE VIEW are followed by the name of the view and the keyword AS.

6. When you are working with PROC SQL views, it is best to

Avoid using an ORDER BY clause in a view.

Avoid creating views that are based on tables whose structure might change.

All of the above

When you are working with PROC SQL views, it is best to

avoid using an ORDER BY clause in a view. If you specify an ORDER BY clause, the data must be sorted each time the view is referenced.

avoid creating views that are based on tables whose structure might change. A view is no longer valid when it references a nonexistent column.

specify a one-level name in the FROM clause if the view resides in the same SAS data library as the contributing table(s). Using a one-level name in the FROM clause prevents you from having to change the view if you assign a different libref to the SAS data library that contains the view and its contributing table or tables.

Explanation

When you are working with PROC SQL views, it is best to

avoid using an ORDER BY clause in a view. If you specify an ORDER BY clause, the data must be sorted each time the view is referenced.

avoid creating views that are based on tables whose structure might change. A view is no longer valid when it references a nonexistent column.

specify a one-level name in the FROM clause if the view resides in the same SAS data library as the contributing table(s). Using a one-level name in the FROM clause prevents you from having to change the view if you assign a different libref to the SAS data library that contains the view and its contributing table or tables.

7. Which PROC SQL step correctly references the view Data.Empview?

Proc sql; select * from data.empview;

Proc sql; select * from view data.empview;

Proc sql; select view * from data.empview;

Proc sql; select * from data where view='empview';

A view can be used in a PROC SQL step just as you would use an actual SAS table.

Explanation

A view can be used in a PROC SQL step just as you would use an actual SAS table.

8. Which of the following PROC SQL steps correctly embeds a LIBNAME statement with a view definition?

The USING clause enables you to embed a LIBNAME statement in your view definition. The USING clause must be the last clause in the CREATE VIEW statement.

Explanation

The USING clause enables you to embed a LIBNAME statement in your view definition. The USING clause must be the last clause in the CREATE VIEW statement.

9. Which of the following statements is false regarding a PROC SQL view?

A view cannot be used in a join.

A view accesses the most current underlying data.

A view follows the same naming conventions as a table.

A view can be used in SAS programs in place of an actual SAS data file.

A PROC SQL view accesses the most current underlying data and can be joined with tables or other views. In addition, a PROC SQL view can

be used in SAS programs in place of an actual SAS data file

be derived from one or more tables, PROC SQL views, or DATA step views.

Explanation

A PROC SQL view accesses the most current underlying data and can be joined with tables or other views. In addition, a PROC SQL view can

be used in SAS programs in place of an actual SAS data file

be derived from one or more tables, PROC SQL views, or DATA step views.

10. You can update the data underlying PROC SQL view using the INSERT, DELETE, and UPDATE statements under which of the following conditions:

The view is joined or linked to another table.

The view contains a subquery.

The view contains a WHERE clause.

All of the above

You can update a PROC SQL view provided that the view does not join or link to another table, the view does not have a subquery, or you try to update a derived column. You can update a view that contains a WHERE clause. The WHERE clause can be in the UPDATE clause or in the view. You cannot update a view that contains any other clause such as an ORDER BY or a HAVING clause.

Explanation

You can update a PROC SQL view provided that the view does not join or link to another table, the view does not have a subquery, or you try to update a derived column. You can update a view that contains a WHERE clause. The WHERE clause can be in the UPDATE clause or in the view. You cannot update a view that contains any other clause such as an ORDER BY or a HAVING clause.

Chapter 7: Creating And Managing Views Using Proc SQL