how to delete a record depending on specific variable

[hjjijkkl]

I have a big data and I want to keep records where the term variable is either 0 or missing for that specific ID. For example in the table below, ID 2 has term=1 so, I want to delete the whole record for ID 2 from the data. How can I do that?

D	term
1	0
1
2	1
2
2
3	.
3

This is the outcome I want to have 

D	term
1	0
1
3	.
3

[PaigeMiller]

---

@hjjijkkl wrote:

I have a big data and I want to keep records where the term variable is either 0 or missing for that specific ID. For example in the table below, ID 2 has term=1 so, I want to delete the whole record for ID 2 from the data. How can I do that?

---

So, I think your explanation needs clarification. In the first sentence, you want to keep records where term is either 0 or missing. Does that mean you want to keep just those records are either 0 or missing; or does that mean you want to keep the entire ID if you get a 0 or missing? Or does that mean something else?

In your example where ID 2 has term=1, you want to delete it. What if there are mixed 0s and missings and 1s for term for an ID?

[Reeza · Accepted solution]

---

@hjjijkkl wrote:

I have a big data and I want to keep records where the term variable is either 0 or missing for that specific ID. For example in the table below, ID 2 has term=1 so, I want to delete the whole record for ID 2 from the data. How can I do that?

D	term
1	0
1
2	1
2
2
3	.
3

This is the outcome I want to have 

D	term
1	0
1
3	.
3

---

proc sql;
create table want as
select * from have
where D NOT IN (select distinct D from have where term=1);
quit;

[novinosrin]

Hi @hjjijkkl  I just love these fun questions-

data have ;
  infile cards truncover ;
  input ID $	term ;
cards ;
1	0
1	
2	1
2	
2	
3	.
3	
;

proc sql;
 create table want as
 select *
 from have
 group by id
 having not max(term) ;
quit;

proc print noobs ; run ;

ID	term
1	0
1	.
3	.
3	.

[Reeza]

Breaks if you have no 1 in the data so not a really robust solution but meets minimal requirements 😉