04-18-2017 04:17 AM
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
04-18-2017 04:26 AM
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.
04-18-2017 04:38 AM - edited 04-18-2017 04:38 AM
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;
04-18-2017 04:58 AM
Extending on @draycut 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;
04-18-2017 04:56 AM
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.