Remove all rows of one observation based on another observation condition

[serena13lee]

Hi, I have a dataset that contains patients, visit and values. My confusion lies in the fact the data is ordered. I would like to be able to remove all the patients if one of the visits is "unscheduled" or is missing "second". This may have been asked before so feel free to direct me to a posted solution.

Have: 

ID   visit                    value 

1    first                      0

1    second                0

2    first                      0

2    unscheduled       0

3    first                      0

Want:

ID   visit                    value 

1    first                      0

1    second                0

My pseudo attempt would be: if missing("second") or contains("unscheduled") delete ID. 

[serena13lee]

data have;
input ID visit $ value;
datalines;
ID visit value
1 first 0
1 second 0
2 first 0
2 unscheduled 0
3 first 0
;

[novinosrin · Accepted solution]

data have;
input ID   visit   :$12.                 value ;
cards;
1    first                      0

1    second                0

2    first                      0

2    unscheduled       0

3    first                      0
;

proc sql;
create table want as
select *
from have
group by id
having not (max(visit='unscheduled')=1 or max(visit = 'second')=0);
quit;

[serena13lee]

This is exactly what I was looking for. Thanks!

[mkeintz]

If the data are sorted by id:

data have;
 input id visit :$11. value;
datalines;
1    first                      0
1    second                0
2    first                      0
2    unscheduled       0
3    first                      0
run;
data want;
  merge have (where=(visit='second') in=second_present)
		have (where=(visit='unscheduled') in=unscheduled_present)
        have;
  by id;
  if second_present=1 and unscheduled_present=0;
run;