topic Re: Finding a unmatching text in two datasets? in SAS Programming

Finding a unmatching text in two datasets?

ybz12003 — Tue, 18 Jun 2024 15:58:01 GMT

Hello,

I have two datasets, A and B. A contains 56 codes, and B has 53 codes. Dataset B is coming from A. I would like to find the 3 unmatching codes that B doesn't have. My code is listed below. But the result I got was still 56 obs, not the 3 unmatching codes. Please help me find where I went wrong.

proc sql; 
   create table Code_unMatch as
	   select DISTINCT a.code      
	   from Master_Code as a
   left join code_check as b    
	   on a.code=b.OBR_Code
	   order by a.code;
quit;

Re: Finding a unmatching text in two datasets?

ballardw — Tue, 18 Jun 2024 16:05:40 GMT

See if this helps:

Except is designed to get unmatched from one set in another.

proc sql; 
   create table Code_unMatch as
	   select DISTINCT code      
	   from Master_Code
      except 
	   select distinct obr_code as code
	          from code_check
      ;
quit;

If you do a LEFT(or Right) join without a WHERE clause to reduce the results you always get all the records from the Left(or right) source.

The join approach would look like:

proc sql; 
   create table Code_unMatch as
	   select DISTINCT a.code      
	   from Master_Code as a
   left join code_check as b    
	   on a.code=b.OBR_Code
      where not missing(b.obr_code)
	   order by a.code
   ;
quit;

Re: Finding a unmatching text in two datasets?

PaigeMiller — Tue, 18 Jun 2024 16:07:50 GMT

Try this:

proc sql; 
   create table Code_unMatch as
	   select DISTINCT a.code,b.OBR_Code      
	   from Master_Code as a
   full join code_check as b    
	   on a.code=b.OBR_Code
	   order by a.code;
quit;

You should find the unmatching text this way.