What is wrong w this SQL code?

Reply
Contributor
Posts: 30

What is wrong w this SQL code?

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.

Super Contributor
Posts: 644

Re: What is wrong w this SQL code?

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

Super User
Super User
Posts: 7,077

Re: What is wrong w this SQL code?

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

Super User
Super User
Posts: 7,077

Re: What is wrong w this SQL code?

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)

;

Contributor
Posts: 69

Re: What is wrong w this SQL code?

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.

Respected Advisor
Posts: 4,934

Re: What is wrong w this SQL code?

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.

PG

PG
Contributor
Posts: 30

Re: What is wrong w this SQL code?

Thank you everyone.

Ask a Question
Discussion stats
  • 6 replies
  • 302 views
  • 1 like
  • 5 in conversation