DATA Step, Macro, Functions and more

Selecting observations based on closest records of days

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 6
Accepted Solution

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,023

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;

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

drop found;
run;



View solution in original post


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

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;

if not found and abs(gap) gt 30 then do;
 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,
Your insight helped.
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 239 views
  • 1 like
  • 2 in conversation