topic Re: SQL equivalent of Merge by if a and not b in SAS Programming

SQL equivalent of Merge by if a and not b

wbsjd — Fri, 09 Sep 2022 00:30:06 GMT

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;

Re: SQL equivalent of Merge by if a and not b

SASKiwi — Fri, 09 Sep 2022 02:37:43 GMT

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.

Re: SQL equivalent of Merge by if a and not b

Kurt_Bremser — Fri, 09 Sep 2022 04:42:21 GMT

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.

Re: SQL equivalent of Merge by if a and not b

Patrick — Fri, 09 Sep 2022 07:56:17 GMT

@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;

Re: SQL equivalent of Merge by if a and not b

ballardw — Fri, 09 Sep 2022 10:14:08 GMT

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.

Re: SQL equivalent of Merge by if a and not b

ballardw — Fri, 09 Sep 2022 10:14:23 GMT