BookmarkSubscribeRSS Feed
mrdlau
Obsidian | Level 7

I'm trying to do a natural join but it seems that the tables aren't joining as I want.

proc sql;
select
(case 
WHEN t1.condition = 'YES', THEN input(t2.number, 8.2)
else 0 end ) as column1
FROM
t1
NATURAL LEFT OUTER JOIN t2 t2;
quit;

both my t1 and t2 tables have a 'condition' column with the same data type, so I expected them to pair together.  however, when I run both tables side by side, t2.column is all empty as if it is not joining.

 

6 REPLIES 6
LinusH
Tourmaline | Level 20
Start with select * from both tables and then perhaps you can find a pattern.
Data never sleeps
RW9
Diamond | Level 26 RW9
Diamond | Level 26

To be honest, I don't think I have ever used "natural" in any join ever.  Consult:

Visual_SQL_JOINS_orig.jpg

 

You should be able to get your data as you want using one of those.

aanan1417
Quartz | Level 8

thanks a ton

ballardw
Super User

Considering the intent of Natural to "guess" what should match I find the use of Natural Left outer join to be "unnatural" if allowed a pun.

 

If you mean you are getting records that don't match at all don't be surprised. I tested your code with two data sets that had one variable name in common and no values for that variable in common:

 

proc sql;
   create table work.junk as
   select a.*, b.*
   from sashelp.class as a
   natural left outer join
   sashelp.baseball as b 
   ;
quit; 

The result is all of the data from the A set and missing values for all of the variables from the B set.

 

 

I suggest either specifying your variables you explicitly expect to have some match with "left join on t1.var = t2.var" or similar or just use Natural.

aanan1417
Quartz | Level 8

thanks a ton

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 6 replies
  • 14699 views
  • 3 likes
  • 6 in conversation