DATA Step, Macro, Functions and more

help with a natural join

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;
WHEN t1.condition = 'YES', THEN input(t2.number, 8.2)
else 0 end ) as column1

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:



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 as b 

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
  • 5 in conversation