topic Re: Joins in SAS Programming

Joins

sasenthusiast07 — Wed, 28 Jul 2021 23:51:22 GMT

Good evening all,

I have an question in inner join ,

I have performed inner join like this

proc sql;

create table combine as

select distinct

n.apple,

p.vegges

from 

fruits as n

inner join 

vegetables as p

on 

n.key=p.key;

quit;

Now in vegges column I have carrots,beans,brocoli,beets--I like my data only to have carrot and beans , how will I apply this condition here.

Thank you

Re: Joins

jimbarbour — Wed, 28 Jul 2021 23:36:47 GMT

You probably just need a WHERE clause just before the semi-colon.

WHERE vegge IN (carrot, beans)

However, remember that if you're doing an Inner join, only items found in both tables will be present in the results set. I'm not sure that carrot or beans will be found in an Inner join with fruits.

Jim

Re: Joins

sasenthusiast07 — Wed, 28 Jul 2021 23:46:12 GMT

Thanks for the reply.

Appreciate it.

How will I address this issue if not inner join

I have 2 tables A and B

A has Vehicle,Bus,Train

B has Vehicle,road,car

In B table I need only Toyota , camry from car variable

I can join these tables by vehicle as an common variable

How should I approach this condition?

Re: Joins

Reeza — Wed, 28 Jul 2021 23:54:18 GMT

The question is, what do you want to happen if that value is not in both tables? What do you want as the output? INNER join only works if it's both tables, if it's one and not the other then you would need to use a LEFT/RIGHT JOIN. Personally, I almost never use an INNER join, I use right/left instead that way if missing values are present they would have missing values and it's an obvious error that's easier to notice. If you use an inner join and accidentally drop a category that's much harder to know.