Hello dear SAS community.
I need help with the following issue.
There are 2 datasets named "one" and "two" with 696 and 695 observations respectively and same variable "id".
How to write out this one observation which is not present in "two" dataset?
For example with the following syntax id=x.
Many thanks in advance.
proc sql;
select a.id
from one as a left join two as b on a.id=b.id
where missing(b.id);
quit;
As often happens with SAS, there are lots of ways to approach this. PROC COMPARE comes to mind. It could also be done with PROC SQL or a DATA step.
What code you have you tried? Are you comfortable with SQL or would your prefer a DATA step approach?
@Zakharkou wrote:
I tried several times with proc compare, but that did not give the desired result.
If you use an ID statement and the LISTOBS option, PROC COMPARE will tell which observations are missing.
data one ;
set sashelp.class ;
run ;
data two ;
set sashelp.class ;
if _n_=3 then delete ;
run ;
proc compare base=one compare=two listobs ;
id name ;
run ;
Returns:
The COMPARE Procedure Comparison of WORK.ONE with WORK.TWO (Method=EXACT) Data Set Summary Dataset Created Modified NVar NObs WORK.ONE 24JAN23:15:47:23 24JAN23:15:47:23 5 19 WORK.TWO 24JAN23:15:47:23 24JAN23:15:47:23 5 18 Variables Summary Number of Variables in Common: 5. Number of ID Variables: 1. Comparison Results for Observations Observation 3 in WORK.ONE not found in WORK.TWO: Name=Barbara.
proc sql;
select a.id
from one as a left join two as b on a.id=b.id
where missing(b.id);
quit;
proc sort data=one;
by id;
run;
proc sort data=two;
by id;
run;
data
both
only_one
only_two
;
merge
one (in=one keep=id)
two (in=two keep=id)
;
by id;
if one and two
then output both;
else if one
then output only_one;
else output only_two;
run;
proc sql;
create table want as
select * from one where id not in (select id from two);
quit;
@Zakharkou wrote:
Hello dear SAS community.
I need help with the following issue.
There are 2 datasets named "one" and "two" with 696 and 695 observations respectively and same variable "id".
How to write out this one observation which is not present in "two" dataset?
For example with the following syntax id=x.
Many thanks in advance.
Thank you for the numerous responses. My problem was successfully solved.
Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.