topic Re: Proc SQL - unmatch? in SAS Programming

Proc SQL - unmatch?

ybz12003 — Fri, 26 Oct 2018 15:14:46 GMT

Hello:

After I use sort and nodup function in dataset, I am trying to use Proc SQL in the one step to get the same result. However, the code didn't work. Any idea how to fix it? Thanks.

Dataset final step is like:

data UnMatch;
     merge test1_format (in=ina) test2 (in=inb);
     by id;
     if not ina or not inb;
run;

Then I am trying to use Proc SQL outer union corresponding to get the same result. It didn't work. Did I use the wrong join?

proc sql;
   create table UnMatch as
   select DISTINCT a.id, a.site      
   from test1 as a
   outer union corresponding
   select test2 as b    
   on a.id=b.id
   order by b.id;
quit;

Re: Proc SQL - unmatch?

novinosrin — Fri, 26 Oct 2018 15:21:14 GMT

How did the use of match option based on keys arise in set operators(outer union in your case)?

I think you should be looking at using joins rather than set operators imho

"Then I am trying to use Proc SQL outer union corresponding to get the same result. It didn't work. Did I use the wrong join?

proc sql;
   create table UnMatch as
   select DISTINCT a.id, a.site      
   from test1 as a
   outer union corresponding
   select test2 as b    
   on a.id=b.id
   order by b.id;
quit;

Re: Proc SQL - unmatch?

FreelanceReinh — Fri, 26 Oct 2018 18:31:45 GMT

@ybz12003 wrote:

Then I am trying to use Proc SQL outer union corresponding to get the same result. It didn't work. Did I use the wrong join?

Yes, you did. I think you mean a full join (also known as full outer join). Try this:

proc sql;
create table UnMatch as
select coalesce(a.id, b.id) as id,
       coalesce(a.site, b.site) as site
from test1 a full join test2 b    
on a.id=b.id
where a.id=. | b.id=.;
quit;

Re: Proc SQL - unmatch?

PGStats — Fri, 26 Oct 2018 20:06:07 GMT

As the documentation says:

There is no keyword in PROC SQL that returns unique rows from the first 
and second table, but not rows that occur in both. Here is one way that you 
can simulate this operation: 

(query1 except query2) 
union 
(query2 except query1)