topic Re: How to see what didn't merge? in SAS Programming

How to see what didn't merge?

confused_saser — Wed, 17 May 2017 16:00:17 GMT

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

Re: How to see what didn't merge?

ballardw — Wed, 17 May 2017 16:11:49 GMT

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?

Re: How to see what didn't merge?

confused_saser — Wed, 17 May 2017 16:52:07 GMT

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

Re: How to see what didn't merge?

Reeza — Wed, 17 May 2017 19:50:37 GMT

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.

Re: How to see what didn't merge?

Patrick — Wed, 17 May 2017 21:25:46 GMT

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

Re: How to see what didn't merge?

confused_saser — Thu, 18 May 2017 02:02:16 GMT

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!