topic Re: SQL JOIN - one column twice in New SAS User

SQL JOIN - one column twice

PM23 — Tue, 17 Mar 2020 09:13:26 GMT

Hi guys,

I have two tables and I need to get info from one table to the second one, so I used JOIN. But I need to add index from one column from table #2 joined on two columns in table #1 (e.g.: I need to mark apples and pears as a fruit, fruit index is info from table #2, apple index is in column A in table #1 and pear index is in column B in table #1). Is it possible to do it? And is it possible to do it in one step?

Thank you.

Re: SQL JOIN - one column twice

Kurt_Bremser — Tue, 17 Mar 2020 09:20:18 GMT

Please show us your input data, preferably in data steps with datalines, so we can easily use it for testing.

Also show the intended result.

Re: SQL JOIN - one column twice

PM23 — Tue, 17 Mar 2020 09:41:44 GMT

Let's say I have two tables:

First one - columns Apple_name, Pear_name (empty, if it is apple and vice versa), Weight

Second one - columns Fruit_index, Name

So I tried:

proc sql;
	create table test as
		select a.*, b.fruit_index
			from First_table a left join Second_table b 
				on a.Apple_name=b.Name and a.Pear_name=b.Name;
quit;

But it seems that just one part joined.

Intended result:
First row - Apple_name: Golden Smith, Pear_name:., Weight: 300, Fruit_index: Apple
Second row - Apple_name:., Pear_name: Gren Anjou Weight: 280, Fruit_index: Pear
Third row - Apple_name:., Pear_name:., Weight: 250, Fruit Index:.

Re: SQL JOIN - one column twice

Kurt_Bremser — Tue, 17 Mar 2020 09:59:43 GMT

Just replace the "and" in your condition with "or":

data have1;
infile datalines dlm=',' dsd truncover;
input apple_name :$20. pear_name :$20. weight;
datalines;
Golden Smith,,300
,Gren Anjou,280
,,250
;

data have2;
infile datalines dlm=',' dsd truncover;
input fruit_index :$10. fruit_name :$20.;
datalines;
Apple,Golden Smith
Pear,Gren Anjou
;

proc sql;
create table want as
select a.*, b.fruit_index
from have1 a left join have2 b
on a.apple_name = b.fruit_name or a.pear_name = b.fruit_name;
quit;

Re: SQL JOIN - one column twice

PM23 — Tue, 17 Mar 2020 10:36:37 GMT

It was that simple! Thank you very much.