topic Re: Not common ids in SAS Programming

Not common ids

SASPhile — Wed, 08 Dec 2010 14:33:27 GMT

Two datasets A and B have Id's.How to find the id's that are NOT matching between them.

I could use a merge:
if A and NOT B then Output A1;
if NOT A and B then output B1;

Then combine A1 and B1 and get distinct ids.
Is there any other way?

Re: Not common ids

ieva — Wed, 08 Dec 2010 14:52:15 GMT

You can also simply combile both of thes IF statements in one, so no need to merge afterwards:

if (a and not b) or ( b and not a);

Re: Not common ids

SPR — Wed, 08 Dec 2010 19:12:58 GMT

Hello SASPhile,

You can use even more simple logic: if NOT (a and b); like this:
[pre]
data a;
x=1; output;
x=2; output;
run;
data b;
x=3; output;
x=2; output;
run;
proc sort data=a;
by x;
run;
proc sort data=b;
by x;
run;
data dif;
merge a (in=a) b(in=b);
if not (a and b);
by x;
run;
[/pre]
Sincerely,
SPR

Re: Not common ids

SASPhile — Wed, 08 Dec 2010 19:28:45 GMT

Thanks SPR!

Re: Not common ids

SASPhile — Wed, 08 Dec 2010 19:28:54 GMT

Thanks Ieva

Re: Not common ids

Ksharp — Fri, 10 Dec 2010 07:46:19 GMT

Yes.
There is another way.Try to use proc sql + except .

[pre]
data temp;
set sashelp.class;
where sex eq 'F';
run;
proc sql;
select *
from sashelp.class as a
where a.sex not in (
select b.sex
from temp as b
) ;quit;
[/pre]

But it is low efficient for the sub-query.

Ksharp Message was edited by: Ksharp