SQL equivalent of Merge by if a and not b

[wbsjd]

Hello everyone 

Could you please help me to find the Proc SQL equivalent for the following SAS Data Step? Thanks a lot

data keepc;
merge a b;
by member_i;
if a and not b;
run;

[SASKiwi · Accepted solution]

proc sql;
  create table keep as
  select A.*
  from a as a
  left join
 (select member_i 
  from b) as B
  on a.member_i = b.member_i
  where missing(b.member_i)  
;
quit;

There's a few different ways of doing this and the above is one.

[Kurt_Bremser]

proc sql;
create table keepc as
  select *
  from a
  where a.member_i not in (select member_i from b)
;
quit;

Be aware that sub-selects are notoriously slow.

The fastest method for your task is this:

data keepc;
set a;
if _n_ = 1
then do;
  declare hash b (dataset:"b");
  b.definekey("member_i");
  b.definedone();
end;
if b.check() ne 0;
run;

as it requires no prior sorting.

[Patrick]

---

@wbsjd wrote:

Hello everyone 

Could you please help me to find the Proc SQL equivalent for the following SAS Data Step? Thanks a lot

data keepc;
merge a b;
by member_i;
if a and not b;
run;

---

You've got good answers from very experienced users. I'm a bit astonished though that they haven't picked up on the code you've shared as it's syntactically not correct. The correct code would be:

data keepc;
  merge a(in=a) b(in=b);
  by member_i;
  if a and not b;
run;

If you just want to keep the rows from table a that don't have a matching key in table b then below SQL should work

proc sql;
  select *
  from a
  where not exists
  (select * from b where a.member_i=b.member_i)
  ;
quit;

[ballardw]

Actually without examples of the data sets A and B I would not suggest an SQL "equivalent" because MERGE treats same-named variables that appear in both data sets, other than the by variables, quite differently than an SQL join of any flavor. If you have other variables of the same name to get the the same result with SQL you would need either Coalesce(b.varname,a.varname) as Varname for Numeric variables or Coalescec(b.charvar, a.charvar) as Charvar for each pair of like named variables.

[ballardw]

Actually without examples of the data sets A and B I would not suggest an SQL "equivalent" because MERGE treats same-named variables that appear in both data sets, other than the by variables, quite differently than an SQL join of any flavor. If you have other variables of the same name to get the the same result with SQL you would need either Coalesce(b.varname,a.varname) as Varname for Numeric variables or Coalescec(b.charvar, a.charvar) as Charvar for each pair of like named variables.