Help using Base SAS procedures

How to show matched and unmatched obs in proc sql

Reply
Frequent Contributor
Posts: 122

How to show matched and unmatched obs in proc sql

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.

Regular Contributor
Posts: 191

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

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

Ask a Question
Discussion stats
  • 1 reply
  • 658 views
  • 0 likes
  • 2 in conversation