Hi All,
I need your help on one scenario based on merge two data sets.
Suppose I have two data sets where no of records present 50000 in first data set and 80000 records in second data set. I have to join the data sets by using keys Cust_No,Mem_No and Acct_No. Once I use innre join I will get output of matched values..how will I get records which are not matching??
Please help me and let me knoq in case if you need any other information
I would try to be a bit clearer with your question. If you have decided that inner join is right for your secnario, why are you now worried about those records which do not match?
Above is a graphic which illustrates the various joins, choose one appropriate to your scenario.
Thank you for your reply. My report needed unmatched records also for analysis purpose. Is there any other ways?? Please help me
This small example should get you going
data a;
input id name$;
datalines;
1 x
2 y
3 z
;
data b;
input id sal;
datalines;
2 100
4 400
5 500
;
proc sort data = a; by id; run;
proc sort data = b; by id; run;
/* Get non matches (id = 2 is the only match)*/
data nonMatch;
merge a (in=ina) b (in=inb);
by id;
if not ina or not inb;
run;
Hi
Extending on @PeterClemmensen example you can combine the inA and inB variables so that you know the join type and do whatever you need.
data a;
input id name$;
datalines;
1 x
2 y
3 z
5 a
;
data b;
input id sal;
datalines;
2 100
4 400
5 500
;
proc sort data = a; by id; run;
proc sort data = b; by id; run;
/* Get non matches (id = 2 is the only match)*/
data fulljoin;
merge a (in=ina) b (in=inb);
by id;
length joinType $ 2;
joinType = cats(ina, inb);
run;
You can also use SQL to create the join type varibale:
proc sql;
create table fullJoin_SQL as
select
coalesce(a.id, b.id) as id
, name
, sal
, cats( missing(a.id) = 0, missing(b.id) = 0) as joinType length=2
from
a
full outer join
b
on a.id = b.id
;
quit;
Bruno
Sorry, your still not explaining what the issue is. The graphic I provided gives you a visual representation of how to get various types of data out from joining, you just select which one meets your needs and use that. From what you have said its likely that outer join is what you want.
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 25. 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.