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;
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;
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;
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.