How to show matched and unmatched obs in proc sql

SeanZ — Tue, 15 Oct 2013 04:10:43 GMT

Hi, I use proc sql procedure to merge two dataset A and B. I wonder in the default merging process, all the observations in the merged dataset will be only matched observations or they include unmatched ones? Also, how to identify whether the unmatched ones are from A or B. I want to a new variable to show this information (like variable merge with values 1-A, 2-B, or 3-both A & B).

Thank you.

Re: How to show matched and unmatched obs in proc sql

FredrikE — Tue, 15 Oct 2013 06:19:02 GMT

The type of join condition descides how the "merge" is done. Using full join selects all rows from each tabe.

This example might work?

data a;
length a a1 $10;
input a a1;
datalines;
two hands
tree hands
;
run;

data b;
length b b1 $10;
input b b1;
datalines;
one leg
two legs
;
run;

proc sql;
create table c as
select coalesce(a.a, b.b) as c
,a.a1
,b.b1
,ifc(missing(a.a),'N','J') as ina
,ifc(missing(b.b),'N','J') as inb
from a
full join b
on a.a = b.b
;
quit;

//Fredrik

topic How to show matched and unmatched obs in proc sql in SAS Procedures

How to show matched and unmatched obs in proc sql

Re: How to show matched and unmatched obs in proc sql