Hello,   I'm trying to get a certain output through a join.   Code for creating Input datasets: data rank; infile datalines; input rmg $4. acc; datalines; 123 1 124 2 125 3 126 4 127 5 ; run; data rank1; infile datalines; input rmg $4. acc; datalines; 123 1 124 2 125 3 123 7 124 8 125 9 136 10 138 11 ; run; What I'm looking to do is get all the data from table rank and get only that data from table rank1 where rank.rmg=rank1.rmg but rank.acc is not in rank1.acc to avoid duplication. So for eg, for rmg 123 in rank, the code should include 123 from rank, but in addition should also pull 123  from rank1 where acc is not 1.   So this is the output that I'm looking to get: rmg acc 123 1 123 7 124 2 124 8 125 3 125 9 126 4 127 5   When I try this code: proc sql; create table new as select rank.*, rank1.rmg, rank1.acc from rank left join rank1 on rank.rmg=rank1.rmg where rank1.acc not in (select distinct acc from rank); quit; I get this output: rmg acc 123 1 123 1 124 2 124 2 125 3 125 3 126 4 127 5   Then when I try this code: proc sql; create table new as select rank.*, rank1.rmg as rmg1, rank1.acc as acc1 from rank left join rank1 on rank.rmg=rank1.rmg where rank1.acc not in (select distinct acc from rank); quit; I get this output: rmg acc rmg1 acc1 123 1 123 7 124 2 124 8 125 3 125 9 126 4     127 5       What should I be changing to get the desired output? Appreciate any suggestions. Thank you! ... View more