Alright, i got your idea. Thx, that works in my situation. Now, i understand what you meant the pattern. I will run into other format of the task ids. Hope this does not bother you too much. I will have to deal with another situation that have to handle different format of ids. For example, the main id will be either 4 digits or 8 digits, and this will be followed by another 4 digits that identify the status of required to redo the task. e.g. for task 1234, required redo it then the full task id will be 1234R001, for task 1234S001 (8 digits main id) required redo, full id is 1234S001R001. Again, i want to count the tasks done, if worker 1 works on 1234 and 1234R001, this counts as 1 task done. if worker 1 works 1234S001 and 1234S001R001, this also counts as 1. if worker 2 only works on 1234R001, this counts as 1. I will still have problems to count worker 2. What will be the idea to handle this? Sorry to bother you a lot. data test;
informat id task_id $25. redo_task;
input id task_id redo_task;
datalines;
1 1234 0
1 1234R001 1
1 1234S001 0
1 1234S001R002 1
1 3456 0
1 9685 0
1 3648 0
2 1234S001R001 1
2 1234R001 1
2 5678 0
;
RUN;
proc sql;
create table out as
select *,
count(distinct case when not redo_task then task_id end) as done
from test
group by id
;
quit;
... View more