如何使用 Proc SQL 查找仅存在于一个表中而不存在于另一个表中的所有记录？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8946593/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I use Proc SQL to find all the records that only exist in one table but not the other?
提问by Jay Corbett
I'm trying to do this in Enterprise Guide, with a task, otherwise I would just use a data step.
我正在尝试在企业指南中执行此操作，并执行一项任务，否则我将只使用数据步骤。
In a data step, this would be:
在数据步骤中，这将是：
data names;
input name $;
datalines;
John
Mary
Sally
Fred
Paul
;
run;
data check;
input name $;
datalines;
Mary
Fred
;
Proc sort data=names; by name; run;
Proc sort data=check; by name; run;
Data work.not_in_check;
merge names(in=n) check(in=c);
by name;
if n and not c;
run;
回答by itzy
Here's one way. There are surely many others.
这是一种方法。肯定还有很多其他的。
proc sql;
create table not_in_check as
select name
from names
where name not in (select name from check);
quit;
回答by Longfish
Another slight variation is:
另一个细微的变化是：
proc sql;
create table not_in_check as select
a.* from names as a left join
check as b on
a.name=b.name
where b.name is null;
quit;
回答by user2239912
The following method is pretty simple way to get records present in one table and not in the other.
下面的方法是一种非常简单的方法来获取一个表中存在的记录而不是另一个表中的记录。
Created table new with records containing sex = M, the result after the query will be records with sex = F.
创建新表，记录包含sex = M，查询后的结果将是sex = F的记录。
Example:
例子：
data new;
set sashelp.class;
where sex = 'M';
run;
proc sql;
create table new1 as
select * from sashelp.class
except all
select * from new;
quit;
Will put the code to test on my actual datasets which are of around 100k obs and update the result.
将把代码放在我大约 100k obs 的实际数据集上进行测试并更新结果。
P.S: I know the question has been asked answered and forgotten,I was looking for a way to do the above and couldn't find a direct answer any where. So, adding so that it may come handy. :)
PS：我知道问题已被回答并被遗忘，我正在寻找一种方法来执行上述操作，但在任何地方都找不到直接答案。因此，添加以便它可以派上用场。:)
My first answer also. :)
我的第一个回答也是。:)
回答by user2781854
proc sql;
create table inNamesNotIncheck
as
select *
from names n
where not exists
(select name
from check c
where n.name=c.name);
quit;
