topic How to compare 4 columns in two tables ? in SAS Programming

How to compare 4 columns in two tables ?

MILKYLOVE — Wed, 06 Dec 2023 14:33:25 GMT

Hello,

I have two tables with identical columns A, B, C, D, E, F.

T1 has 2000 values

T2 has 5300 values

These two tables have 4 primary keys: A,C,D,F.

So I want to create a new table and take values from T2 where A,C,D,F are not present in T1.

How do I proceed please?

I have tried proc sql 'except', but it is comparing all columns in both tables

Re: How to compare 4 columns in two tables ?

yabwon — Wed, 06 Dec 2023 14:55:26 GMT

data T1 T2;
input A B C D E F;
if mod(_N_,2) then output T1;
output T2;
cards;
1 2 3 4 5 6
2 3 4 5 6 7
3 4 5 6 7 8
4 5 6 7 8 9
5 6 7 8 9 0
;
run;


proc sql;
select A,C,D,F from T2
except
select A,C,D,F from T1
;
quit;

Re: How to compare 4 columns in two tables ?

MILKYLOVE — Wed, 06 Dec 2023 14:59:39 GMT

Hi,

I need all the columns A,B,C,D,E,F in the output table. But I need to compare only the four primary keys A,C,D,F.

Re: How to compare 4 columns in two tables ?

yabwon — Wed, 06 Dec 2023 15:01:59 GMT

proc sql;
select * from T2
natural inner join
(
select A,C,D,F from T2
except
select A,C,D,F from T1
)
;
quit;