topic Re: Joining in SAS Programming

Joining

rabishaw — Tue, 18 Apr 2017 08:17:06 GMT

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

Re: Joining

RW9 — Tue, 18 Apr 2017 08:26:59 GMT

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.

Re: Joining

rabishaw — Tue, 18 Apr 2017 08:29:57 GMT

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

Re: Joining

PeterClemmensen — Tue, 18 Apr 2017 08:38:46 GMT

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;

Re: Joining

RW9 — Tue, 18 Apr 2017 08:56:50 GMT

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.

Re: Joining

BrunoMueller — Tue, 18 Apr 2017 08:58:35 GMT

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

Re: Joining

rabishaw — Tue, 18 Apr 2017 08:59:16 GMT

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