HOW TO REMOVE ID BASED ON ONE VALUE

[ndamo]

Dear community , 

I need your help. I have a dataset like this 

ID   indication 

1        A

1        B

1        A

1        A

2        A

2        A

2        A

2        A

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

2        A

2        A

2        A

2        A

Thank you 

[PeterClemmensen]

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

[ndamo]

@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

[PeterClemmensen · Accepted solution]

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

[ndamo]

@PeterClemmensen 

It works. Thank you so much !😊