records in one table but not in another table

kajal_30 — Mon, 03 Jun 2024 19:49:50 GMT

I have 4 tables as

table - 1

table- 2

table - 3

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

Re: records in one table but not in another table

Tom — Mon, 03 Jun 2024 20:02:27 GMT

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

Re: records in one table but not in another table

kajal_30 — Fri, 21 Jun 2024 16:02:55 GMT

Thank you @Tom

topic records in one table but not in another table in SAS Programming

records in one table but not in another table

Re: records in one table but not in another table

Re: records in one table but not in another table