topic Re: Identifying ids with a certain missing value in SAS Programming

Identifying ids with a certain missing value

SarahW13 — Thu, 21 Jun 2018 00:25:31 GMT

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!

Re: Identifying ids with a certain missing value

novinosrin — Thu, 21 Jun 2018 00:37:46 GMT

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;

Re: Identifying ids with a certain missing value

novinosrin — Thu, 21 Jun 2018 00:42:43 GMT

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;

Re: Identifying ids with a certain missing value

SarahW13 — Thu, 21 Jun 2018 00:47:55 GMT

Thank you!

Re: Identifying ids with a certain missing value

mkeintz — Thu, 21 Jun 2018 04:09:21 GMT

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.

Re: Identifying ids with a certain missing value

SarahW13 — Thu, 21 Jun 2018 03:08:40 GMT

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!

Re: Identifying ids with a certain missing value

Ksharp — Thu, 21 Jun 2018 13:56:49 GMT

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;