records in one table but not in another table

[kajal_30]

I have 4 tables as 

table - 1 

ID

1

2

3

4

table- 2 

ID 

2

3

4

5

table - 3

ID

1

2

4

6

so need a report like records in one but not in 2nd and not in 3rd

Output table  

ID col1 col2 col3 

1     y      n          y

2    y      y           y 

3    y      y           n

4    y      y           y

5    n      y           n

6    n      n           y

[Tom · Accepted solution]

Just use a simple MERGE.  You can use the IN= datsaet option to name a variable that indicates if a particular dataset has contributed to the resulting observation.  But that variable will be temporary.  So just assign the value to another variable that will be permanent.

first let's convert your listings into actual datasets:

data table1 ;
  input ID @@;
cards;
1 2 3 4
;

data table2;
  input ID @@;
cards;
2 3 4 5
;

data table3;
  input ID @@;
cards;
1 2 4 6
;

Now just merge and save the IN= variable values:

data want;
  merge table1(in=in1) table2(in=in2) table3(in=in3);
  by id;
  col1=in1;
  col2=in2;
  col3=in3;
run;

Result:

Obs    ID    col1    col2    col3

 1      1      1       0       1
 2      2      1       1       1
 3      3      1       1       0
 4      4      1       1       1
 5      5      0       1       0
 6      6      0       0       1

If you would like COL1 to COL3 to print as Y/N or YES/NO then just define a format and attach it to the variables.  Demonstration:

proc format ;
value yn 1='Y' 0='N';
value yesno 1='Yes' 0='No';
run;

data want;
  merge table1(in=in1) table2(in=in2) table3(in=in3);
  by id;
  col1=in1;
  col2=in2;
  col3=in3;
  format col2 yn. col3 yesno.;
run;

Obs    ID    col1    col2    col3

 1      1      1      N      Yes
 2      2      1      Y      Yes
 3      3      1      Y      No
 4      4      1      Y      Yes
 5      5      0      Y      No
 6      6      0      N      Yes

[kajal_30]

Thank you @Tom