topic Re: Question on in-line views? in New SAS User

Question on in-line views?

Syntas_error — Fri, 26 Apr 2019 20:30:41 GMT

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?

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

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

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

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?

Re: Question on in-line views?

FreelanceReinh — Fri, 26 Apr 2019 21:11:02 GMT

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

Re: Question on in-line views?

PGStats — Sat, 27 Apr 2019 04:19:47 GMT

where a.dept=b.maxsal

?? The authors of these questions are very creative indeed

Re: Question on in-line views?

Syntas_error — Sat, 27 Apr 2019 06:00:51 GMT

"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 ^^

Re: Question on in-line views?

Syntas_error — Sat, 27 Apr 2019 06:02:04 GMT

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

Re: Question on in-line views?

FreelanceReinh — Mon, 29 Apr 2019 14:24:41 GMT

@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.

Re: Question on in-line views?

DMIN — Fri, 03 May 2019 18:37:25 GMT

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