There are two potential problems. You are joining table B to table A using a LEFT JOIN. This means you will get all records in table A even if there are no matching rows in table B. This also means that you will not get rows in table B that do NOT exist in table A. This is fine if this is what you want. For example, table A does not have a row where "state_code = 55 and co_code = 87 and year=1980". But, there is a corresponding row in table B. But, because you are left joining B to A, the resulting table will not have a row where "state_code = 55 and co_code = 87 and year=1980". Your second problem is probably why you are not getting the results you expect. Your two tables have inconsistent formats for variables in table A vs table B. In particular, the year variable in table B has a YEARw format whereas the same variable in table A is formatted BEST12. The table B format is interpreting the year variable as a date value. Try this: proc sql; create table sample_result2 as select a.*, b.edu, b.mhincome, b.mage from tmp2.sample_a as a left join tmp3.sample_b as b on put(a.year, 4.0) = put(b.year, year4.) and a.state_code=b.state_code and a.co_code=b.co_code; quit;
... View more
