DATA Step, Macro, Functions and more

Identifying ids with a certain missing value

Accepted Solution Solved
Reply
Contributor
Posts: 23
Accepted Solution

Identifying ids with a certain missing value

Hello!

 

I have a question about how to identify individuals that are missing a certain value.

 

Below is my dataset:

 

id       class

1        A

1        B

1        C

2        A

2        B

3        B

3        C

 

In this dataset, id 3 is the only one that does not have any class value of A. I would like to identify all individuals that do not have a class value of A. Can anyone guide me how to do this?

 

Thanks!

 


Accepted Solutions
Solution
a month ago
PROC Star
Posts: 1,805

Re: Identifying ids with a certain missing value

Posted in reply to novinosrin

More cleaner

 

data want;
set have;
by id;
retain _class 'A' found;
if first.id then found=.;
if class=_class then found=1;
if last.id and not found;
drop _class found;
run;

View solution in original post


All Replies
PROC Star
Posts: 1,805

Re: Identifying ids with a certain missing value

data have;
input ID    class $;
cards;
1        A
1        B
1        C
2        A
2        B
3        B
3        C
;
run;

data want;
set have;
by id;
retain _class found;
if first.id then found=.;
_class='A';
if class=_class then found=1;
if last.id and not found;
run;
Solution
a month ago
PROC Star
Posts: 1,805

Re: Identifying ids with a certain missing value

Posted in reply to novinosrin

More cleaner

 

data want;
set have;
by id;
retain _class 'A' found;
if first.id then found=.;
if class=_class then found=1;
if last.id and not found;
drop _class found;
run;
Contributor
Posts: 23

Re: Identifying ids with a certain missing value

Posted in reply to novinosrin

Thank you!

Trusted Advisor
Posts: 1,337

Re: Identifying ids with a certain missing value

[ Edited ]
Posted in reply to novinosrin

If each id has exactly zero or one instance of class='A' then a compact way to do this is:

 

data want;
  merge have (where=(class='A') in=afound)  have;
  by id;
  flag=afound;
run;

 

Edited additional note.   This program  will also works for ID's with multiple class A records.

Contributor
Posts: 23

Re: Identifying ids with a certain missing value

Thank you, mkeintz! In my dataset, there are many ids with multiple instances of class='A' but that's helpful code to know for the future. Thanks!

Super User
Posts: 10,778

Re: Identifying ids with a certain missing value

data have;
input id       class $;
cards;
1        A
1        B
1        C
2        A
2        B
3        B
3        C
;
run;
proc sql;
select *
 from have
  group by id
   having sum(class='A')=0;
quit;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 134 views
  • 4 likes
  • 4 in conversation