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-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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