03-03-2014 11:15 PM
What is wrong with this sas code?
proc sql; select * from ball04
I get this error ERROR: Unresolved reference to table/correlation name ball04.
03-04-2014 07:02 AM
I think Proc SQL is expecting aliases to be declared
from ball04 b4
from ball06 b6
03-04-2014 08:05 AM
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?
03-04-2014 09:29 AM
Since you want the intersection why not just take the records from one or the other table?
where exists (select * from ball06 where ball04.player=ball06.player)
03-04-2014 10:29 AM
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.
03-04-2014 01:30 PM
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
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.