topic Re: Query using PROC SQL in SAS Programming

Query using PROC SQL

ThongNguyen — Mon, 23 Jul 2012 22:51:28 GMT

I've just started preparing for the SAS Advanced Certification and come through the first chapter of the SAS Prep Guide. Below is one of the chapter 1 quizzes and I sort of disagree with the answer given in the book. I picked 'd' because all columns are table-unique except for 'Address', so there is no ambiguous reference if we use 'price.address' to read column 'Address'. To make sure, I tried running the code on some other databases and results are the same for 'b' and 'd', no errors were found.

Does anybody have any ideas on the choice of a correct answer to this question? Thanks.

Thong

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; _______________ where size.address =price.address; from list.size,list.price;

select address,sqfeet,price
select size.address,sqfeet,price
select price.address,sqfeet,price
either b or c

Correct answer: b

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.

Re: Query using PROC SQL

ArtC — Mon, 23 Jul 2012 23:01:49 GMT

Are you sure about the SQL step code? The semi-colon after the WHERE clause will cause the FROM to be separated from the WHERE.

Re: Query using PROC SQL

Dorota_Jarosz — Mon, 23 Jul 2012 23:20:22 GMT

Answer d is 'either b or c' but c is impossible, because there is no table price. So d is not the right one.

Answer a is incorrect, because address exists on two tables, so the selection is not unique.

We are left with b.

Message was edited by: Dorota Jarosz Oops! Table price does exist. Sorry.

Re: Query using PROC SQL

ArtC — Mon, 23 Jul 2012 23:37:33 GMT

If we can assume that the SQL step was actually stated something like:

proc sql; _______________

from list.size,list.price

where size.address =price.address;

I would have also selected D.

Re: Query using PROC SQL

Dorota_Jarosz — Tue, 24 Jul 2012 00:03:47 GMT

Art: You are right, I misread the problem. Table price does exist. In this case b and c are equivalent, so d is the correct answer.

Re: Query using PROC SQL

Haikuo — Tue, 24 Jul 2012 01:41:18 GMT

Am I missing something here? "

Address and SqFeet from the table List.Size", while c: select price.address,sqfeet,price

So that would leave 'b' as only correct answer?

Haikuo

Re: Query using PROC SQL

ThongNguyen — Tue, 24 Jul 2012 02:59:52 GMT

: I just copied and pasted. And yes, I picked 'd' as well, assuming there were no errors in with semicolons and clause order in the question, not in the choices. This is an answer in the set of SAS solutions that I disagree with.

Re: Query using PROC SQL

ThongNguyen — Tue, 24 Jul 2012 03:13:51 GMT

: We have column 'Address' in table 'price' as well, so 'c' is also correct.

Re: Query using PROC SQL

Haikuo — Tue, 24 Jul 2012 03:24:25 GMT

Yes, I am aware of that. However, from I can see from your problem request, let me repeat:

select the columns Address and SqFeet from the table List.Size

So even table 'price' does have included variable 'address', but to comply with the request, variable 'address' has to be from table 'size', therefore 'c' is NOT correct.

Still Confused.

Haikuo

Re: Query using PROC SQL

ThongNguyen — Tue, 24 Jul 2012 03:32:17 GMT

It think literally it does not force you to use 'Size' table to reference to the 'Address' column in the SELECT clause .

Re: Query using PROC SQL

TomKari — Tue, 24 Jul 2012 03:36:05 GMT

What a muddle!

I think you're all correct, but in different ways. I do agree that b and c are equivalent, but only because of the WHERE clause. Because the question lists b as the right answer, and the justification is the need to distinguish address because it is in both tables, I'm speculating that the sample SQL statement was pulled in from a different question, and the person drafting the question didn't pay attention to the WHERE clause.

Because the question specifically requests ADDRESS from SIZE, I think that's what they wanted.

Clearly, either the question or the proposed answer needs to be reworked.

Tom

Re: Query using PROC SQL

Amir — Tue, 24 Jul 2012 09:50:31 GMT

Further, just because two columns have the same name on different tables it doesn't necessarily mean they hold the same data. E.g., one Address might be a contact address, and the other Address might be an invoice address, so it would be important to follow the spec (or question in this case) to ensure the correct column / data is used.

Regards,

Amir.

Re: Query using PROC SQL

ThongNguyen — Tue, 24 Jul 2012 15:44:10 GMT

: It's not necessary that same-named columns in both tables contain same data, but same type of data. If no matchings were found, outputs would have no records.

Re: Query using PROC SQL

Dorota_Jarosz — Tue, 24 Jul 2012 21:19:50 GMT

It is a thin line: what they WANT versus what gives a correct outcome. They stated:

"...to select the columns Address and SqFeet from the table List.Size and to select Price from the table List.Price."

Maybe they subscribe to the "do always what your boss wants and how he wants it" philosophy? If this is so, I would not like to work for them.