topic Re: help with a natural join in SAS Programming

help with a natural join

mrdlau — Mon, 11 Jun 2018 06:03:13 GMT

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.

Re: help with a natural join

LinusH — Mon, 11 Jun 2018 06:20:41 GMT

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

Re: help with a natural join

RW9 — Mon, 11 Jun 2018 07:40:27 GMT

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.

Re: help with a natural join

Kurt_Bremser — Mon, 11 Jun 2018 07:51:06 GMT

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

Re: help with a natural join

ballardw — Sun, 25 Apr 2021 20:30:46 GMT

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.

Re: help with a natural join

aanan1417 — Sat, 24 Apr 2021 09:15:07 GMT

thanks a ton

Re: help with a natural join

aanan1417 — Sat, 24 Apr 2021 09:16:01 GMT

thanks a ton