BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
PM23
Calcite | Level 5

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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;

View solution in original post

4 REPLIES 4
Kurt_Bremser
Super User

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.

PM23
Calcite | Level 5

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:.

Kurt_Bremser
Super User

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;
PM23
Calcite | Level 5

It was that simple! Thank you very much.

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 4 replies
  • 978 views
  • 0 likes
  • 2 in conversation