: Hopefully, someone more versed in SQL, than me, can show you how this code SHOULD be written. However, the following two sets of code do the two tasks that you asked about. The good news is that, while probably doing it in a more round about way than necessary, both will work regardless of whether there are any many-to-many situations:
/*create two test files*/
data file_a;
input geid goc x y z;
cards;
1 1 1 2 3
2 1 1 2 3
3 1 1 2 3
4 1 1 2 3
5 1 1 2 3
;
data file_b;
input geid goc a b c;
cards;
1 1 4 5 6
2 2 4 5 6
7 1 4 5 6
8 1 4 5 6
9 1 4 5 6
;
proc sql;
create table work.met_condition as
select * from
(select geid from work.file_a
except
select geid from work.file_b)
union
(select geid from work.file_b
except
select geid from work.file_a)
;
create table work.all_combined as
select * from work.file_a
union
select * from work.file_b
;
create table work.file_discrepancies (drop=b_:) as
select *
from work.met_condition a,
work.all_combined (rename=(geid=b_geid)) b
where a.geid=b.b_geid
;
quit;
proc sql;
create table work.met_condition as
select * from
(select geid,goc from work.file_a
except
select geid,goc from work.file_b)
union
(select geid,goc from work.file_b
except
select geid,goc from work.file_a)
;
create table work.all_combined as
select * from work.file_a
union
select * from work.file_b
;
create table work.file_discrepancies2 (drop=b_:) as
select *
from work.met_condition a,
work.all_combined (rename=(geid=b_geid
goc=b_goc)) b
where a.geid=b.b_geid and
a.goc=b.b_goc
;
quit;
