BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
confused_saser
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

@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

5 REPLIES 5
ballardw
Super User

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
Obsidian | Level 7

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
Super User

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
Opal | Level 21

@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
Obsidian | Level 7

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!

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
  • 5 replies
  • 1738 views
  • 1 like
  • 4 in conversation