How to see what didn't merge?

Accepted Solution Solved
Reply
Contributor
Posts: 37
Accepted Solution

How to see what didn't merge?

I just completed a left join using proc sql, however I would like to see what did NOT merge.

 

here is the code I used:

 

proc sql;
	create table conc_merge as
	select* from conc_tx_base as x left join conc_dados as y
	on x.mrn=y.mrn;
run;

How can I see what didn't merge from Y (conc_dados)?

 

If it can be spit out in another dataset or table that would be awesome.

 

Thank you


Accepted Solutions
Solution
‎05-17-2017 10:02 PM
Respected Advisor
Posts: 3,837

Re: How to see what didn't merge?

[ Edited ]

@confused_saser

You can't create multiple output tables with SQL so you would have to implement multiple SQL queries with different join conditions in order to get what you want.

 

You could use a SAS Data step merge instead as below (not tested):

proc sql noprint;
  select name into :keep_list separated by ' '
  from dictionary.columns
  where libname='WORK' and memname='CONC_DADOS'
  ;
quit;

data 
  conc_merge 
  conc_dados_noMatch (keep=&keep_list)
  ;
  merge conc_tx_base (in=in_base) conc_dados (in=in_dados);
  by mrn;
  if in_base then output conc_merge;
  else if not in_base and in_dados then output conc_dados_noMatch;
run;

View solution in original post


All Replies
Grand Advisor
Posts: 10,223

Re: How to see what didn't merge?

Please clarify "did not merge". Do you mean records in conc_tx_base that did not find any matches in conc_dados?

Or do you mean records in conc_dados that did not find a match in conc_tx_base?

Contributor
Posts: 37

Re: How to see what didn't merge?

I mean records in conc_dados that did not match up with any MRN in conc_tx_base

 

there were 98 records in conc_dados-I want to see how many of these 98 DID NOT match up with an MRN in the conc_tx_base

Grand Advisor
Posts: 17,396

Re: How to see what didn't merge?

That's essentially the opposite of your join Smiley Happy

So try a different join that would meet those conditions 

or do a full join and use a CASE statement to identify the matches/non matches.

Solution
‎05-17-2017 10:02 PM
Respected Advisor
Posts: 3,837

Re: How to see what didn't merge?

[ Edited ]

@confused_saser

You can't create multiple output tables with SQL so you would have to implement multiple SQL queries with different join conditions in order to get what you want.

 

You could use a SAS Data step merge instead as below (not tested):

proc sql noprint;
  select name into :keep_list separated by ' '
  from dictionary.columns
  where libname='WORK' and memname='CONC_DADOS'
  ;
quit;

data 
  conc_merge 
  conc_dados_noMatch (keep=&keep_list)
  ;
  merge conc_tx_base (in=in_base) conc_dados (in=in_dados);
  by mrn;
  if in_base then output conc_merge;
  else if not in_base and in_dados then output conc_dados_noMatch;
run;
Contributor
Posts: 37

Re: How to see what didn't merge?

thanks @Patrick! I'm not sure I understand the proc sql part of your answer, but yes that is essentially what I ended up doing! I did a data step merge to output the records from conc_dados that did not merge with conc_tx_base and then I did a proc sql left join to merge the two datasets.

 

thank you!

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 129 views
  • 1 like
  • 4 in conversation