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.
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.
thanks a ton
Please post the log from your code, that comma after 'YES' does not seem right at all.
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.
thanks a ton
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.