topic Re: HOW TO REMOVE ID BASED ON ONE VALUE in SAS Programming

HOW TO REMOVE ID BASED ON ONE VALUE

ndamo — Thu, 08 Sep 2022 08:27:40 GMT

Dear community ,

I need your help. I have a dataset like this

ID indication

1 A

1 B

1 A

2 A

I would like to remove ID were indication = B like the output below:

2 A

Thank you

Re: HOW TO REMOVE ID BASED ON ONE VALUE

PeterClemmensen — Thu, 08 Sep 2022 09:33:49 GMT

data have;
input ID indication $;
datalines;
1 A
1 B
1 A
1 A
2 A
2 A
2 A
2 A
;

proc sql;
   create table want as
   select * from have
   group by ID
   having(sum(indication = 'B') = 0)
   ;
quit;

Result:

ID  indication
2   A
2   A
2   A
2   A

Re: HOW TO REMOVE ID BASED ON ONE VALUE

ndamo — Thu, 08 Sep 2022 09:24:09 GMT

@PeterClemmensen

Thank you but it did not work . Indeed my column Indication have the value A, B ,C, D , E .

The program you proposed removed all observation even if ID not equal to B.

For exemple here , the ID 1 and 3 should be deleted and keep ID 2 and 4.

ID	Indication
1	A
1	B
1	A
1	B
2	C
2	C
2	C
2	C
3	A
3	B
3	C
3	D
4	A
4	C
4	D
4	E

Re: HOW TO REMOVE ID BASED ON ONE VALUE

PeterClemmensen — Thu, 08 Sep 2022 09:33:27 GMT

Here is how I interpret your needs:

You want to remove the entire ID group if one or more observations within that group has indication = 'B'. Correct?

That is exactly what my code does, both in the previous example and in this one 🙂

See the posted results as well.

data have;
input ID Indication $;
datalines;
1 A
1 B
1 A
1 B
2 C
2 C
2 C
2 C
3 A
3 B
3 C
3 D
4 A
4 C
4 D
4 E
;

proc sql;
   create table want as
   select * from have
   group by ID
   having(sum(indication = 'B') = 0)
   ;
quit;

Result:

ID  Indication
2   C
2   C
2   C
2   C
4   D
4   C
4   A
4   E

Re: HOW TO REMOVE ID BASED ON ONE VALUE

ndamo — Thu, 08 Sep 2022 10:02:34 GMT

@PeterClemmensen

It works. Thank you so much !😊