DATA Step, Macro, Functions and more

help with a natural join

Reply
Contributor
Posts: 43

help with a natural join

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.

 

Super User
Posts: 5,852

Re: help with a natural join

Start with select * from both tables and then perhaps you can find a pattern.
Data never sleeps
Super User
Super User
Posts: 9,416

Re: help with a natural join

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.

Super User
Posts: 9,890

Re: help with a natural join

Please post the log from your code, that comma after 'YES' does not seem right at all.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super User
Posts: 13,321

Re: help with a natural join

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

 

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.

Ask a Question
Discussion stats
  • 4 replies
  • 105 views
  • 0 likes
  • 5 in conversation