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;
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;
@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;
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)
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: