Unique rows between two files

[art297]

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