topic Re: Keeping specific observations in a data set in SAS Programming

Keeping specific observations in a data set

space_dam — Fri, 19 Nov 2021 20:30:11 GMT

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

Re: Keeping specific observations in a data set

PeterClemmensen — Fri, 19 Nov 2021 20:44:42 GMT

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;

Re: Keeping specific observations in a data set

EyalGonen — Sun, 21 Nov 2021 08:41:05 GMT

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;

Re: Keeping specific observations in a data set

Patrick — Sun, 21 Nov 2021 13:20:48 GMT

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;

Re: Keeping specific observations in a data set

space_dam — Tue, 23 Nov 2021 02:31:18 GMT

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