I had a similar approach as @Patrick with ranking but ended up using a nested SQL instead:
proc sql;
select have.*,
case
when lenderscore is not null then 'Yes'
when No = 1 then 'Yes'
else 'No'
end as Lead_Arranger
from have
left join
(select facilityid,
lender,
count(*) as No,
case
when lenderRole = "Administrative Agent" then 3
when LeadArrangerCredit = 'Yes' then 2
when AgentCredit = 'Yes' then 1
else 0 end as lenderScore
from have
group by facilityid
having calculated lenderScore = max(calculated lenderScore)) as score
on have.facilityid = score.facilityid and
have.lender = score.lender
;
quit;
Edit: this code works if a lender only can appear once per FacilityId
... View more
