Biderectional Vlookup - flag in the same table - Sas

Question

I need to do this:

table 1:

    ID   Cod.
    1     20
    2     102
    4     30
    7     10
    9     201
    10    305

table 2:

    ID    Cod.
    1     20
    2     50
    3     15
    4     30
    5     25
    7     10
    10    300

Now, I got a table like this with an outer join:

   ID    Cod.    ID1    Cod1.
    1     20      1      20
    2     50      .      .
    .      .      2      102
    3     15      .      .
    4     30      4      30
    5     25      .      .
    7     10      7      10
    .     .       9      201
    10    300     .      .
    .     .       10     305

Now I want to add a flag that tell me if the ID have common values, so:

   ID    Cod.    ID1    Cod1.   FLag_ID  Flag_cod:
    1     20      1      20      0         0
    2     50      .      .       0         1
    .      .      2      102     0         1 
    3     15      .      .       1         1
    4     30      4      30      0         0
    5     25      .      .       1         1
    7     10      7      10      0         0
    .     .       9      201     1         1
    10    300     .      .       0         1
    .     .       10     305     0         1

I would like to know how can I get the flag_ID, specifically to cover the cases of ID = 2 or ID=10.

Thank you

Answer 1

You can group by a coalescence of id in order to count and compare details.

Example

data table1;
  input id code @@; datalines;
1 20 2 102 4 30 7 10 9 201 10 305
;

data table2;
  input id code @@; datalines;
1 20 2 50 3 15 4 30 5 25 7 10 10 300
;

proc sql;
  create table got as
  select 
    table2.id, table2.code
  , table1.id as id1, table1.code as code1 
  , case 
      when count(table1.id) = 1 and count(table2.id) = 1 then 0 else 1 
    end as flag_id
  , case
      when table1.code - table2.code ne 0 then 1 else 0
    end as flag_code
  from 
    table1
  full join 
    table2
  on 
    table2.id=table1.id and table2.code=table1.code
  group by 
    coalesce(table2.id,table1.id)
  ;

You might also want to look into

• Proc COMPARE with BY