How does this work for you? It is extensible: throw as much data at it as you like, and it should still work.
data have;
infile cards dsd dlm=',' firstobs=2 missover;
attrib agent_name length=$ 20;
attrib action_type length=$ 4;
attrib risk_level subject_to_review length=$ 1;
input agent_name
action_type
risk_level;
select(risk_level);
when('1', '2', '3') recode_risk_level = risk_level;
when('0') recode_risk_level = '4';
otherwise recode_risk_level = '5';
end;
random = ranuni(225465114); /* My 30-year-old UK phone number as seed */
id = monotonic(); /* Create a unique incremented ID per observation */
cards;
Agent_name,action_type,risk_level,subject_to_review
Albert,1000,1,Y
Albert,1000,1,Y
Albert,1000,1,
Albert,1000,1,
Albert,2100,2,Y
Albert,2100,2,Y
Albert,2100,2,Y
Albert,2100,2,
Albert,3230,0,
Albert,2500,3,
Albert,2500,3,
Albert,2500,3,
Albert,2500,3,
Albert,2500,3,
Albert,5200,,
Barb,1000,1,Y
Barb,1000,1,Y
Barb,2100,2,Y
Barb,2100,2,Y
Barb,2100,2,Y
Barb,3230,0,
Cathy,1000,1,Y
Cathy,2100,2,Y
Cathy,2100,2,Y
Cathy,3230,0,Y
Cathy,5200,,Y
;
run;
proc sort data=have noequals;
by agent_name recode_risk_level random;
run;
data subject_to_review;
set have;
by agent_name recode_risk_level;
retain count output_count;
if first.agent_name then
output_count = 1;
if first.recode_risk_level
then count = 1;
else count + 1;
if ( output_count > 5
or recode_risk_level = '1' and count > 3
or recode_risk_level = '2' and count > 2) then
delete;
output_count + 1;
keep id;
run;
proc sql undo_policy=none magic=102;
create table want as
select have.agent_name,
have.action_type,
have.risk_level,
ifc(str.id is not null, 'Y', ' ') as subject_to_review length=1
from have
left join subject_to_review as str
on have.id = str.id
order by have.id;
quit;
... View more
