topic Re: what is the best way to compare two fields from two tables in SAS Programming

what is the best way to compare two fields from two tables

Stalk — Tue, 04 Oct 2022 21:56:53 GMT

data t1;
input id1 $3. id2 $5.;
cards;
123 hkg5
689 yukq
567 khnx
;
run;
data t2;
input id1 $3. id2 $5.;
cards;
123 hkg5
569 yukq
567 khnt
;
run;

I want to get mis-matched records from t1 and t2 where the id1 is different in two tables for id2 values, In the above example I just want to retrieve 567 khnt

what is the best way to compare two columns from two tables?

trying to replicate the following query that is working fine in other application..

select t1.custid,t2.custid,t2.id2,ord_date from t2 left join t1 using(id2)where t2.custid != t1.custid

Thank you

Re: what is the best way to compare two fields from two tables

Patrick — Tue, 04 Oct 2022 23:19:31 GMT

@Stalk wrote:

...

I want to get mis-matched records from t1 and t2 where the id1 is different in two tables for id2 values, In the above example I just want to retrieve 567 khnt

Based on the sample data you've provided the only matching row over ID2 that has different values for ID1 is as below:

proc sql;
  select 
    t1.id1,
    t2.id1,
    t2.id2
  from t2 inner join t1
    on t2.id2=t1.id2
  where t2.id1 ne t1.id1
  ;
quit;

What am I missing?

Re: what is the best way to compare two fields from two tables

Stalk — Wed, 05 Oct 2022 12:46:40 GMT

Patrick, you are right. Your query's results is exactly what I am looking for.
My original query took 7 hours to run and there were no records in the original data, so I made up this test data and got messed up. Thank you so much