DATA Step, Macro, Functions and more

Joining

Reply
Occasional Contributor
Posts: 5

Joining

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

Super User
Super User
Posts: 7,970

Re: Joining

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?  

Capture.PNG

Above is a graphic which illustrates the various joins, choose one appropriate to your scenario.

Occasional Contributor
Posts: 5

Re: Joining

Thank you for your reply. My report needed unmatched records also for analysis purpose. Is there any other ways?? Please help me

PROC Star
Posts: 755

Re: Joining

[ Edited ]

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;
SAS Super FREQ
Posts: 708

Re: Joining

Hi

 

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;

Bruno

Super User
Super User
Posts: 7,970

Re: Joining

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.

Occasional Contributor
Posts: 5

Re: Joining

Thank you for your reply. I will try the way mention.


Ask a Question
Discussion stats
  • 6 replies
  • 171 views
  • 2 likes
  • 4 in conversation