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
Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!
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.
Ready to level-up your skills? Choose your own adventure.