Prc SQL Set operators

Reply
New Contributor
Posts: 4

Prc SQL Set operators

I have three year data  of soccer games. They all have similar columns and I need to create a report of teams who played in all three tournaments. I have columns name Team, Player name, Average player PPG, year. I am getting a NOTE: No rows were selected. in log where as I can see some common rows in all tables. I created these data sets from select statements and group clause. Any hint I can get why I am not able to join all the data with the Set Operators.

select *

from tab1

intersect

select *

from tab2

intersect

select *

from tab3

;

PROC Star
Posts: 7,468

Re: Prc SQL Set operators

Posted in reply to ssengar25

Given data like that shown below, don't you really only need:

data tab1;

  input team x;

  cards;

1 1

3 2

5 3

;

data tab2;

  input team x;

  cards;

2 6

3 7

4 8

;

data tab3;

  input team x;

  cards;

3 6

6 7

7 8

;

proc sql;

  create table want as

    select team

      from tab1

    intersect

    select team

      from tab2

    intersect

    select team

      from tab3

  ;

quit;

New Contributor
Posts: 4

Re: Prc SQL Set operators

thanks Arthur... I need 4 columns in my output but intercept requires the entire row to be the same in both tables to return the row right. my entire rows are not same in all the tables. I think I need to use some sub query for that. Trying but there yet.

PROC Star
Posts: 7,468

Re: Prc SQL Set operators

Posted in reply to ssengar25

Sounds like you want to join the 3 tables.  e.g.:

data tab1;

  input team name $ x1 x2 x3;

  cards;

1 John 1 1 1

1 Tom 1 1 1

3 Joe 3 3 3

3 Sam 3 3 3

5 Harry 5 5 5

;

data tab2;

  input team name $ y1 y2 y3;

  cards;

2 John 2 2 2

3 Rick 3 3 3

3 Joe 3 3 3

4 Howard 2 2 2

;

data tab3;

  input team name $ z1 z2 z3;

  cards;

3 Rick 3 3 3

3 Edwardo 3 3 3

6 George 6 6 6

7 Jerry 7 7 7

;

proc sql;

  create table want as

    select distinct *

      from tab1 (drop=name),

           tab2 (drop=name),

           tab3 (drop=name)

        where tab1.team=tab2.team and

              tab2.team=tab3.team

  ;

quit;

New Contributor
Posts: 4

Re: Prc SQL Set operators

I have to use Set operators in order to get my answers..I got a hint that I need to use nested intersect and sub queries.

PROC Star
Posts: 7,468

Re: Prc SQL Set operators

Posted in reply to ssengar25

Sounds like this is a classroom project.  If not, provide some example have and want data.  Using intersect, in this case, makes the problem more complex than it has to be.

New Contributor
Posts: 4

Re: Prc SQL Set operators

Thanks Arthur I fugured it out. In order to get common teams name I used intersect for all three tables on TEAM variable. I used INLINE , subquery then UNION operator. Its was very big code but finally I got the output.

Ask a Question
Discussion stats
  • 6 replies
  • 291 views
  • 0 likes
  • 2 in conversation