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
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
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
Thank you @Tom
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.