BookmarkSubscribeRSS Feed
rabishaw
Calcite | Level 5

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

6 REPLIES 6
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

rabishaw
Calcite | Level 5

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

PeterClemmensen
Tourmaline | Level 20

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;
BrunoMueller
SAS Super FREQ

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

rabishaw
Calcite | Level 5
Thank you for your reply. I will try the way mention.


sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 1256 views
  • 2 likes
  • 4 in conversation