Question on in-line views?

[Syntas_error]

While doing practice exams for the Advanced programmer certification I encountered the following question:

------------------

This question will ask you to provide a segment of missing code.

The following  SAS program is submitted:

proc sql;

select a.name, a.sal, a.dept, b.maxsal

from EMP a, 

<insert code here>

where a.dept=b.maxsal

What segment of code will successfully run an in-line view?

A)

 ( create dept, max(sal) as maxsal
from EMP
group by dept) b

B)

create ( dept, max(sal) as maxsal
from EMP
group by dept) b

C)

(select dept, max(sal) as maxsal
from EMP
group by dept) b

D)

select ( dept, max(sal) as maxsal
from EMP
group by dept) b

-----------------------------

My intuition here says C, since the SELECT statement always seems to be made inside the parenthesis. But the correct answer

seems to be D. Am I missing something and in that case what?

[FreelanceReinh · Accepted solution]

Hi @Syntas_error,

Don't let typos in prep guides (or wherever D was stated to be the correct answer) confuse you. Of course, only C is correct. You can test it with a simple example (e.g. a single obs. with all variables 0).

[Syntas_error]

"sigh"

I know for a fact that question is part of the actual exam, there's a very realy chance I will have to "memorize" a number of these typos which feels incredibly backwards ^^

[PGStats]

where a.dept=b.maxsal

?? The authors of these questions are very creative indeed 

[Syntas_error]

So only the inner query (that is to say the in-line view) executes when the outer query fails?

[FreelanceReinh]

---

@Syntas_error wrote:

So only the inner query (that is to say the in-line view) executes when the outer query fails?

---

If you asked me, I'd rather say: The inline view can be thought of as a virtual table which is created "on the fly" and then immediately used in the implicit join that constitutes the outer query. The result is the same as if that table had been created physically as a named SAS dataset in a preliminary PROC SQL step (using the code of the inline view).

If the outer query fails because of, say, a non-existing variable or table or a syntax error, this will be noticed during compilation already, so the inner query will not be executed in this case.

[DMIN]

C is correct for sure. The answers given are not always correct sometimes, i know this when preparing for the SAS certification exams.