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;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.