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!
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;
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;
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;
Thank you!
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.
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!
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.