BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Zakharkou
Calcite | Level 5

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.

1 ACCEPTED SOLUTION

Accepted Solutions
PaigeMiller
Diamond | Level 26
proc sql;
    select a.id 
    from one as a left join two as b on a.id=b.id
    where missing(b.id);
quit;
--
Paige Miller

View solution in original post

7 REPLIES 7
Quentin
Super User

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?

The Boston Area SAS Users Group is hosting free webinars!
Next webinar will be in January 2025. Until then, check out our archives: https://www.basug.org/videos. And be sure to subscribe to our our email list.
Zakharkou
Calcite | Level 5
I tried several times with proc compare, but that did not give the desired result.
Quentin
Super User

@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.
The Boston Area SAS Users Group is hosting free webinars!
Next webinar will be in January 2025. Until then, check out our archives: https://www.basug.org/videos. And be sure to subscribe to our our email list.
PaigeMiller
Diamond | Level 26
proc sql;
    select a.id 
    from one as a left join two as b on a.id=b.id
    where missing(b.id);
quit;
--
Paige Miller
Kurt_Bremser
Super User
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;
Reeza
Super User
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.


 

Zakharkou
Calcite | Level 5

Thank you for the numerous responses. My problem was successfully solved.

SAS Innovate 2025: Register Now

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!

What is Bayesian Analysis?

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 7 replies
  • 1077 views
  • 8 likes
  • 5 in conversation