topic Re: What is wrong w this SQL code? in SAS Programming

What is wrong w this SQL code?

zscott1 — Tue, 04 Mar 2014 04:15:05 GMT

What is wrong with this sas code?

proc sql; select * from ball04

intersect all

select *

from ball06

where ball04.player=ball06.player;

quit;

I get this error ERROR: Unresolved reference to table/correlation name ball04.

Re: What is wrong w this SQL code?

RichardinOz — Tue, 04 Mar 2014 12:02:46 GMT

I think Proc SQL is expecting aliases to be declared

proc sql;

select *

from ball04 b4

intersect all

select *

from ball06 b6

where b4.player=b6.player;

quit;

Richard

Re: What is wrong w this SQL code?

Tom — Tue, 04 Mar 2014 13:05:00 GMT

You are asking it to take the common rows from two sets of records with the INTERSECT keyword. In that case the use of a where clause to also require that the subset of the variables to match is redundant.

Perhaps you want an inner join instead?

select *

from ball04

inner join

ball06

on ball04.player=ball06.player

Re: What is wrong w this SQL code?

Tom — Tue, 04 Mar 2014 14:29:56 GMT

Since you want the intersection why not just take the records from one or the other table?

select *

from ball04

where exists (select * from ball06 where ball04.player=ball06.player)

;

Re: What is wrong w this SQL code?

bentleyj1 — Tue, 04 Mar 2014 15:29:40 GMT

I'm not very familiar with SAS's Intersect operator but from your syntax it 'joins' two stand-alone Select queries, like a Union does. Neither query has any knowledge of the other--the query with the table aliased as ball06 has no knowledge of the query with the table aliased as ball04 so your Where critieria will not work. That's what the error message is saying.

Maybe Tom's second answer will give you the results you want.

Re: What is wrong w this SQL code?

PGStats — Tue, 04 Mar 2014 18:30:29 GMT

Here is what is wrong with your SQL query:

INTERSECT (as well as UNION and EXCEPT) set operators take queries as operands.

QUERY1 intersect QUERY2

finds the intersection of unique rows between QUERY1 and QUERY2. In your case, QUERY2 is

select * from ball06 where ball04.player=ball06.player

which is not a valid query since ball04 is referred to in your WHERE clause but not mentioned in your FROM clause.

If what you want are the players that are present in both tables, you should simply ask for:

select player from ball04

intersect

select player from ball06

However, if you want to extract more information from ball04 or ball06, you should INNER JOIN the tables ON ball04.player=ball06.player, as was suggested by Tom.

Re: What is wrong w this SQL code?

zscott1 — Wed, 05 Mar 2014 17:30:38 GMT

Thank you everyone.