It is not clear to me what your logic is but your example makes it look like you want to flag the LAST observation for a department except when it is also the last observation for the ID.
data have ;
input id datetime :$16. department :$10. count flag ;
cards;
1 01-01-2020.10:00 aaa 1 1
1 02-01-2020.10:00 bbb 2 1
1 03-01-2020.10:00 ccc 3 1
1 04-01-2020.10:00 aaa 4 0
1 05-01-2020.10:00 aaa 5 1
1 06-01-2020.10:00 ccc 6 0
2 01-01-2020.10:00 bbb 1 0
2 02-01-2020.10:00 bbb 2 0
2 03-01-2020.10:00 bbb 3 0
2 04-01-2020.10:00 bbb 4 0
2 05-01-2020.10:00 bbb 5 0
3 01-01-2020.10:00 aaa 1 1
3 02-01-2020.10:00 ddd 2 0
3 03-01-2020.10:00 ddd 3 0
3 04-01-2020.10:00 ddd 4 1
3 05-01-2020.10:00 aaa 5 0
;
data want;
set have ;
by id department notsorted;
want=last.department and not last.id;
run;
proc print;
run;
proc compare data=want;
var flag;
with want;
run;
The COMPARE Procedure
Comparisons of variables in WORK.WANT
(Method=EXACT)
Data Set Summary
Dataset Created Modified NVar NObs
WORK.WANT 30DEC20:10:38:27 30DEC20:10:38:27 6 16
NOTE: No unequal values were found. All values compared are exactly equal.
... View more
