Self Join Issue

Accepted Solution Solved
Reply
Contributor
Posts: 27
Accepted Solution

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
Respected Advisor
Posts: 4,919

Re: Self Join Issue

Posted in reply to rohitguptaecb

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

View solution in original post


All Replies
Solution
‎03-17-2013 10:14 AM
Respected Advisor
Posts: 4,919

Re: Self Join Issue

Posted in reply to rohitguptaecb

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

Re: Self Join Issue

Thanks Master for your wonderful reply.... I got the concept ...

PROC Star
Posts: 7,467

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 ??? Smiley Happy

PROC Star
Posts: 7,467

Re: Self Join Issue

Posted in reply to rohitguptaecb

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"

Respected Advisor
Posts: 4,919

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: 7,467

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: 7,467

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!

Respected Advisor
Posts: 3,156

Re: Self Join Issue

Posted in reply to rohitguptaecb

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. 

Respected Advisor
Posts: 4,919

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 12 replies
  • 385 views
  • 9 likes
  • 5 in conversation