Are you saying that you want LEAD1009 8/31/2007 output 2 times?
Once because it is within 84 days of 7/11/2007 and has a higher pb?
Once because it is within 84 days of 9/21/2007 and has a higher pb?
If so, then this works:
data have;
input Record_id :$8. Collected_date :mmddyy10. Pb_result Days ;
format collected_date yymmddn8.;
datalines;
LEAD1000 9/14/2007 9 .
LEAD1005 4/30/2007 5 .
LEAD1005 1/22/2008 7 267
LEAD1007 3/9/2007 6 .
LEAD1009 7/11/2007 17 .
LEAD1009 8/31/2007 22 51
LEAD1009 9/21/2007 10 21
LEAD1009 2/22/2008 15 154
LEAD1011 1/22/2007 6 .
LEAD1011 7/9/2007 7 168
LEAD1011 9/17/2007 10 70
LEAD1012 10/22/2007 8 .
run;
data want (drop=nxt_:);
set have ;
by record_id;
/* Compare to predecessor */
if pb_result>lag(pb_result) and first.record_id=0 and days<=84 then output;
/* ReCompare, this time to successor */
merge have (keep=record_id)
have (firstobs=2 keep=pb_result days rename=(pb_result=nxt_pb days=nxt_days));
if pb_result>nxt_pb and last.record_id=0 and nxt_days<=84 then output;
run;
Notes;
Comparing to predessor is pretty straightforward, but one caveat In the IF statement make sure that the "pb_result>lag(pb_result) condition precedes the other conditions. Because all the conditions are connected by "and", sas is smart enough to stop checking once a failed condition is detected. But since we always want the lag queue (this queue have size 1) updated, it should be the first condition.
SAS has no lead functino. To the compare to successor uses the MERGE statement (notice no accompanying BY statement), where the data set is merged with itself, but the second HAVE has "firstobs=2" meaning that it reads one observation ahead of the first have. The "rename=" parameters is needed to avoid two records trying to populate the same variable.
... View more