BookmarkSubscribeRSS Feed
zscott1
Calcite | Level 5

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.

6 REPLIES 6
RichardinOz
Quartz | Level 8

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

Tom
Super User Tom
Super User

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

Tom
Super User Tom
Super User

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)

;

bentleyj1
Quartz | Level 8

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.

PGStats
Opal | Level 21

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
zscott1
Calcite | Level 5

Thank you everyone.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 629 views
  • 1 like
  • 5 in conversation