04-10-2017 08:25 AM
a week ago
Here I mentioned 3 tables. Risk, Control and incidents, each incident is mapped with Risk and and each control is mapped with Risk. the output which i require is also mention below. Can any body please provide the code to obtain the required output report.
|Risk id||Risk Name|
|Control number||Risk id|
|incident no||freq||amount||Risk id|
Require Output Report
|Risk table||Risk Table||Control table||incident table||incident table|
|Risk id||Risk Name||Control||freq (sum)||amount(sum)|
04-10-2017 07:54 PM - edited 04-10-2017 07:55 PM
I've come close, but putting the totals on the first line for a risk_id group means jumping through too many hoops for my proc report skills. I could do it with some tricky data steps, but it starts getting too complicated.
data risk; do risk_id = 10, 20; risk_name = ifc(risk_id = 10, 'ID Theft', 'Fraud'); output; end; run; data controls; do control_number = 'c1', 'c2', 'c3', 'c4', 'c5'; risk_id = ifn(control_number le 'c3', 10, 20); output; end; run; data incidents; infile cards dsd dlm=','; attrib incident_no freq amount risk_id length=8; input incident_no freq amount risk_id; cards; 1,1,50,10 2,1,50,10 3,2,50,10 4,5,10,20 5,1,10,20 ; run; data want; merge incidents(in=in_incidents) risk controls; by risk_id; if in_incidents; run; proc report data=want nowd; cols ('Risk Table' risk_id risk_name) ('Control Table' control_number) ('Incident Table' freq amount); define risk_id / group 'Risk ID'; define risk_name / group 'Risk Name'; define control_number / display 'Control'; define freq / sum 'Freq (sum)'; define amount / sum 'Amount (sum)'; break after risk_id / summarize; run;
Can you explain the significance of the control number?
04-11-2017 02:38 AM
Thank you for your efforts. below is your out put in which Freq and amount is showing against each Control No.. requirement is like against Risk ID 10 the freq will be 4 and Amount will be 150
Actually it is the Operational Risk Report. There is also other columns in control table with control number like control descreption and nature of control etc.
Requirement to show the risk id, risk name, control number, control descreption, freq and amount is that to show the strenth of controls for mitigating the risk.