Keeping specific observations in a data set

[space_dam]

I'm trying to create a data set from an original data set. But I only want to keep data for participants that had all 3 visits and who were not taking any medication at the first visit (visit=1). Meds: 1= taking medication; 0= not taking medication. 

Here is an example of the data (called exercise) for reference.

ID  visit  meds   time

1    1        1         5

1    2        1         4

2    1        0         6

2    2        1         8

2    3        1         5

3    1        1         9

4    1        0         3

4    2        0         4

4    3        0         3

5    1        1         3

5    2        0         8

5    3        0         5

6    1        1         7

6    2        1         9

7    1        0         6

7    2        0         5

7    3        0         8

[PeterClemmensen]

Try this

data have;
input ID visit meds time;
datalines;
1 1 1 5
1 2 1 4
2 1 0 6
2 2 1 8
2 3 1 5
3 1 1 9
4 1 0 3
4 2 0 4
4 3 0 3
5 1 1 3
5 2 0 8
5 3 0 5
6 1 1 7
6 2 1 9
7 1 0 6
7 2 0 5
7 3 0 8
;

data want(drop = f1 f2);
   do until (last.id);
      set have;
      by id;
      if first.id and meds = 0 then f1 = 1;
      if last.id and visit = 3 then f2 = 1;
   end;
   
   do until (last.id);
      set have;
      by id;
      if f1 and f2 then output;
   end;
run;

[EyalGonen]

Here's an idea:

data have;
input ID visit meds time;
datalines;
1 1 1 5
1 2 1 4
2 1 0 6
2 2 1 8
2 3 1 5
3 1 1 9
4 1 0 3
4 2 0 4
4 3 0 3
5 1 1 3
5 2 0 8
5 3 0 5
6 1 1 7
6 2 1 9
7 1 0 6
7 2 0 5
7 3 0 8
;

/* assumes data is sorted by id and visit. If not, sort it first */
data pre_want;
      set have;
      by id;
	  keep id;
	  retain fmeds numvisits;
      if first.id then do;
	  	numvisits = 0;
		if  visit = 1 then fmeds = meds;
	  end;
	  numvisits + 1;
      if last.id and numvisits = 3 and fmeds = 0 then output;	  
run;

proc sql;
	create table want as
	select * from have where id in (select id from pre_want)
	;
quit;

[space_dam]

Haven't learned proc sql yet. Is there a way to do that part of the code in the data step instead? 

[Patrick]

Here a SQL only option.

proc sql;
  create table want as
  select 
    l.*
  from 
    have l
    inner join
    (select id from have where visit=1 and meds=0) r
    on l.id=r.id
  group by l.id
  having count(*)=3
  order by l.id, l.visit
  ;
quit;