help with a natural join

[mrdlau]

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.

[LinusH]

Start with select * from both tables and then perhaps you can find a pattern.

[RW9]

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

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

[aanan1417]

thanks a ton

[Kurt_Bremser]

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

[ballardw]

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]

thanks a ton