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
PROC Star

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?

Check out the Boston Area SAS Users Group (BASUG) video archives: https://www.basug.org/videos.
Zakharkou
Calcite | Level 5
I tried several times with proc compare, but that did not give the desired result.
Quentin
PROC Star

@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.
Check out the Boston Area SAS Users Group (BASUG) video archives: https://www.basug.org/videos.
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 2024

Innovate_SAS_Blue.png

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

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.

Get the $99 certification deal.jpg

 

 

Back in the Classroom!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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