Order of operations.
The usage of FIRST/LAST is logically incorrect because groups don't align. The groups only include specific records in that group so you need to change the order. First get the records that meet the condition and then flag the last operator.
Do you need the records flagged or selected? Selected out is actually easier and can be done in one step. If you want it flagged it's a slightly longer process though a merge would work as well - ideally you have a unique identifier for each row as well to make it simpler.
proc sort data=results out=res2;
by id test vdt vdtm;
run;
data res3;
set res2;
*filter only records of interest;
where (vdt<dt or vdtm < dtm);
by id test vdt vdtm;
*keep last record;
if last.test;
run;
@sri1 wrote:
Hi ,
I have the following data
data results;
input id $ test $ dt date9. dtm datetime20. vdt date9. vdtm datetime20. ;
cards;
001 MAT 15APR2019 15APR2019:10:05:00 05MAR2019 05MAR2019:14:05:00
001 MAT 15APR2019 15APR2019:10:05:00 15APR2019 15APR2019:09:05:00
001 MAT 15APR2019 15APR2019:10:05:00 17APR2019 17APR2019:08:05:00
002 SSP 15APR2019 15APR2019:10:05:00 03FEB2019 03FEB2019:11:05:00
002 SSP 15APR2019 15APR2019:10:05:00 19APR2019 19APR2019:07:05:00
003 PPE 15APR2019 15APR2019:10:05:00 05MAR2019 05MAR2019:14:05:00
003 PPE 15APR2019 15APR2019:10:05:00 15APR2019 15APR2019:09:05:00
;
run;
I would like to flag latest records (highlighted ones) where vdt< dt or vdtm <dtm and I am using the following code but I am not getting intended result.
proc sort data=results out=res2;
by id test vdt vdtm ;
run;
data res3;
set res2;
by id test vdt vdtm;
retain flag;
if last.test then flag=0;
if flag=0 and (. <vdt<dt or . < vdtm < dtm) then do;
slfl="Y";
flag=1;
end;
run;
Please let me know how to fix the above.
Thanks
