topic PROC SQL how to return all mismatched records in New SAS User

PROC SQL how to return all mismatched records

cosmid — Tue, 11 May 2021 01:28:21 GMT

If I have the following two datasets:

data set ONE:

ID X

1 a

2 .

3 c

6 a

data set TWO:

ID Y

1 a

2 b

5 d

6 e

Here's the output I am looking for:

ID X Y

2 . b

3 c .

5 . d

6 a e

Is there a way to do this in PROC SQL?

Re: PROC SQL how to return all mismatched records

Reeza — Tue, 11 May 2021 02:01:43 GMT

So if X & Y are the same you delete the record? In that case this would work, assuming your input data is sorted by ID.

data want;
merge one two;
by ID;
if x = y then delete;
run;

proc sql;

create table want as

select coalesce(t1.ID, t2.ID) as ID, X, Y

from ONE as t1

full join TWO as t2

on t1.id = t2.id

where x ne y;

quit;

Re: PROC SQL how to return all mismatched records

Ksharp — Tue, 11 May 2021 12:27:08 GMT

data ONE;
input ID X $;
cards;
1   a
2   .
3   c
6   a
;
 

data TWO;
input ID Y $;
cards;
1   a
2   b
5   d
6   e
;


proc sql;
create table want as
select coalesce(a.id,b.id) as id,x,y
 from 
 (select * from one except select * from two) as a
 full join
 (select * from two except select * from one) as b
 on a.id=b.id;
quit;

Re: PROC SQL how to return all mismatched records

cosmid — Tue, 11 May 2021 15:25:07 GMT

Wow, that datastep is so clean compared to a Proc SQL. I needed Proc SQL for my case because with Proc SQL it will not print anything to the output window if the dataset is empty.

Thank you for the help!

And I really need to read more about coalesce and full joins!

Re: PROC SQL how to return all mismatched records

cosmid — Tue, 11 May 2021 15:26:44 GMT

Thank you Ksharp! both your code and Reeza's worked. I would have accepted both as a solution but it only allowed me to pick one so I just picked one that replied earlier hopefully you are okay with it. I really appreciate your help on this! I learned from both of your codes! Thanks again!