Selecting observations based on closest records of days

Solved
Occasional Contributor
Posts: 6

Selecting observations based on closest records of days

Hi, I have data as follows

data have;

input subject studyday visit trt_changeday ;

datalines;

1 23 1 30

1 36 2 30

1 40 3 30

1 55 4 30

1 78 5 30

2 23 1 35

2 25 2 35

2 44 3 35

3 12 1 24

3 18 2 24

3 30 3 24

3 45 4 24

3 66 5 24

4 2 1 47

4 56 2 47

4 89 3 47

;

run;

data have;

set have;

gap=trt_changeday-studyday;

run;

I want to flag records based on following conditions

1. if studyday is +30 or -30 days  of trt_changeday then flag the record closest to trt_changeday
else ;

2. if studyday is out of range of +/ -30 days of trt_changeday then flag the record of the visit erlier to trt_changeday (Subject 4)

I need final output as below.

 subject studyday visit trt changeday gap flag 1 23 1 30 7 1 36 2 30 -6 1 1 40 3 30 -10 1 55 4 30 -25 1 78 5 30 -48 2 23 1 35 12 2 25 2 35 10 2 44 3 35 -9 1 3 12 1 24 12 3 18 2 24 6 3 30 3 24 -6 1 3 45 4 24 -21 3 66 5 24 -42 4 2 1 47 45 1 4 56 2 47 -9 4 89 3 47 -42

Thanks for the help.

Accepted Solutions
Solution
‎07-04-2016 11:45 AM
Super User
Posts: 10,761

Re: Selecting observations based on closest records of days

It would be better if you could post some more data.

``````data have;
input subject studyday visit trt_changeday ;
datalines;
1 23 1 30
1 36 2 30
1 40 3 30
1 55 4 30
1 78 5 30
2 23 1 35
2 25 2 35
2 44 3 35
3 12 1 24
3 18 2 24
3 30 3 24
3 45 4 24
3 66 5 24
4 2 1 47
4 56 2 47
4 89 3 47
;
run;

data have;
set have;
by subject;
gap=trt_changeday-studyday;

retain found ;
if first.subject then found=.;

if not found and abs(gap) le 30 and gap lt 0 and lag(gap) gt 0 then do;
found=1;flag=1;
end;

found=1;flag=1;
end;

drop found;
run;

``````

All Replies
Solution
‎07-04-2016 11:45 AM
Super User
Posts: 10,761

Re: Selecting observations based on closest records of days

It would be better if you could post some more data.

``````data have;
input subject studyday visit trt_changeday ;
datalines;
1 23 1 30
1 36 2 30
1 40 3 30
1 55 4 30
1 78 5 30
2 23 1 35
2 25 2 35
2 44 3 35
3 12 1 24
3 18 2 24
3 30 3 24
3 45 4 24
3 66 5 24
4 2 1 47
4 56 2 47
4 89 3 47
;
run;

data have;
set have;
by subject;
gap=trt_changeday-studyday;

retain found ;
if first.subject then found=.;

if not found and abs(gap) le 30 and gap lt 0 and lag(gap) gt 0 then do;
found=1;flag=1;
end;

found=1;flag=1;
end;

drop found;
run;

``````
Occasional Contributor
Posts: 6

Re: Selecting observations based on closest records of days

Thanks Ksharp.
I just tweaked this a little and got what I wanted,