DATA Step, Macro, Functions and more

Risk Control Incident report

Reply
Occasional Contributor
Posts: 8

Risk Control Incident report

Risk Control Incident report
 

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

Risk idRisk Name
10id therft
20fraud

 

Controls

Control numberRisk id
c110
c210
c310
c420
c520

 

Incidents

incident nofreqamountRisk id
115010
215010
325010
451020
511020

 

 

Require Output Report

Risk tableRisk TableControl tableincident tableincident table
Risk idRisk NameControlfreq (sum)amount(sum)
10id therftc14150
  c2
  c3
20fraudc4620
  c5
Super Contributor
Posts: 252

Re: Risk Control Incident report

[ Edited ]
Posted in reply to rehman_panjwani

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? 

Occasional Contributor
Posts: 8

Re: Risk Control Incident report

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 

 

SAS Output

 
 
 
List Report

Risk Table Control Table Incident Table Risk ID Risk Name Control Freq (sum) Amount (sum)
10ID Theftc1150
  c2150
  c3250
10  4150
20Fraudc4510
  c5110
20  620
 
Requirement : 
risk_idrisk_namecontrol_numberfreqamount
10ID Theftc14150
  c2  
  c3  
20Fraudc4620
  c5  

 

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. 

Ask a Question
Discussion stats
  • 2 replies
  • 131 views
  • 0 likes
  • 2 in conversation