How to see what didn't merge?

[confused_saser]

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

[ballardw]

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?

[confused_saser]

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

[Reeza]

That's essentially the opposite of your join 🙂

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.

[Patrick · Accepted solution]

@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;

[confused_saser]

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!