Solved
Contributor
Posts: 27

# Self Join Issue

DATA TEST2;

INPUT KIDID \$ CANDY \$;

DATALINES;

K1 YELLOW

K1 RED

K2 RED

K2 BLUE

K3 WHITE

K3 RED

K3 YELLOW

;

RUN;

In above data step i want only those kid ids which has yellow and red candies only. For eg output should be  k1   k3.  How can i code it.

Accepted Solutions
Solution
‎03-17-2013 10:14 AM
Posts: 5,539

## Re: Self Join Issue

The problem is a set theory statement that translates almost directly into SQL :

proc sql;

select kidid from test2 where candy="YELLOW"

intersect

select kidid from test2 where candy="RED";

quit;

which is simpler and easier to understand than the self join equivalent :

proc sql;

select a.kidid from

test2 as a inner join test2 as b on a.kidid=b.kidid

where a.candy="YELLOW" and b.candy="RED";

quit;

PG

PG

All Replies
Solution
‎03-17-2013 10:14 AM
Posts: 5,539

## Re: Self Join Issue

The problem is a set theory statement that translates almost directly into SQL :

proc sql;

select kidid from test2 where candy="YELLOW"

intersect

select kidid from test2 where candy="RED";

quit;

which is simpler and easier to understand than the self join equivalent :

proc sql;

select a.kidid from

test2 as a inner join test2 as b on a.kidid=b.kidid

where a.candy="YELLOW" and b.candy="RED";

quit;

PG

PG
Contributor
Posts: 27

PROC Star
Posts: 8,165

## Re: Self Join Issue

: I hadn't realized that you had reached the 'Master' level.  Congratulations and very well deserved!

But isn't it about time that we got to see your real picture?  Somehow, I don't think you're quite as old as the individual shown in your current icon (i.e., 421 years old )

Contributor
Posts: 27

## Re: Self Join Issue

By the way Mr Arthur, how much time (on an average) does it take for an individual to achieve 'Master' level ???

PROC Star
Posts: 8,165

## Re: Self Join Issue

roghibuptaecb: Since only 10 people have reached that stage, I don't think providing an "average" time will be very reliable.  However, I think all 10 have done it in under 2 years.  It depends upon the combination of how much one posts and how often those posts are marked as being either "correct" or "helpful"

Posts: 5,539

## Re: Self Join Issue

Thank You! Mystery is part of my charm :smileyblush:! It is also key to my unhindered participation to the Forum.

PG
PROC Star
Posts: 8,165

## Re: Self Join Issue

: Totally understood and I, for one, will respect your preference.  I just couldn't resist doing a picture search on the icon (i.e.: 4 results - TinEye )

Super Contributor
Posts: 1,636

## Re: Self Join Issue

Hi Art,

100 percent agree with you. I WANT to see real PG too!

PROC Star
Posts: 8,165

## Re: Self Join Issue

: Given the choice, I would prefer to see his continued posts!

Super Contributor
Posts: 1,636

## Re: Self Join Issue

Of course, if we have to choose one!

Posts: 3,167

## Re: Self Join Issue

Another alternative is to use sum();

proc sql;

select kidid from test2 group by kidid

having sum(candy="YELLOW") * sum(candy="RED")>0 ;quit;

Haikuo

BTW, CONGRATULATIONS to PG for joining the gang of 'Master', which is, like Art mentioned, very well deserved and long overdue.

Posts: 5,539

## Re: Self Join Issue

Clever! Or : having max(candy="YELLOW") AND max(candy="RED");

I wish SAS/SQL offered logical summary functions ANY and ALL, so we could write

having ANY(candy="YELLOW") AND ANY(candy="RED");

that wouldn't rely on the internal values of TRUE and FALSE.

PG

PG
🔒 This topic is solved and locked.