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;
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.
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.
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.
@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;
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.
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.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: