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.
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
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
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)
;
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.
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
Thank you everyone.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.