Except for "Administrative Agent" the LenderRole terms in your spreadsheet don't match the terms in your narrative.
To make below code work you need to amend the terms in the informat so that they match your data (terms in the format must be uppercase, data can be mixed case).
data have;
infile datalines dsd truncover;
input Facilityid Lender:$40. LenderRole:$20. (LeadArrangerCredit AgentCredit) (:$3.);
datalines;
1,Deutsche Bank,Arranger,Yes,No
1,Goldman,Admin Agent,No,No
1,JP Morgan,Participant,No,No
1,Citi,Administrative Agent,No,No
2,Morgan Stanley,Manadated Lead arranger,No,No
2,Wells Fargo,Bookrunner,No,No
2,UBS,Bookrunner,No,No
2,Credit Suisse,Bookrunner,Yes,No
3,Deutsche Bank,Administrative Agent,No,Yes
4,JP Morgan,Bookrunner,No,No
4,Goldman,Bookrunner,No,No
4,Citi,Bookrunner,No,No
4,UBS,Bookrunner,No,Yes
;
proc format;
invalue lead_arr_hier (upcase)
'ADMINISTRATIVE AGENT' = 1
'LEADARRANGERCREDIT' = 2
'AGENTCREDIT' = 3
other = 999
;
quit;
data inter;
set have;
lead_order=input(LenderRole,lead_arr_hier.);
run;
proc sort data=inter;
by Facilityid lead_order;
run;
data want;
set inter;
by Facilityid lead_order;
if first.Facilityid then
do;
if last.Facilityid or lead_order ne 999 then lead_arranger = 1;
end;
run;
... View more
