topic Re: Risk Control Incident report in SAS Programming

Risk Control Incident report

rehman_panjwani — Mon, 10 Apr 2017 12:25:29 GMT

Risk Control Incident report

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

Risk id	Risk Name
10	id therft
20	fraud

Controls

Control number	Risk id
c1	10
c2	10
c3	10
c4	20
c5	20

Incidents

incident no	freq	amount	Risk id
1	1	50	10
2	1	50	10
3	2	50	10
4	5	10	20
5	1	10	20

Require Output Report

Risk table	Risk Table	Control table	incident table	incident table
Risk id	Risk Name	Control	freq (sum)	amount(sum)
10	id therft	c1	4	150
		c2
		c3
20	fraud	c4	6	20
		c5

Re: Risk Control Incident report

LaurieF — Mon, 10 Apr 2017 23:55:41 GMT

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?

Re: Risk Control Incident report

rehman_panjwani — Tue, 11 Apr 2017 06:38:57 GMT

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)

10	ID Theft	c1	1	50
		c2	1	50
		c3	2	50
10			4	150
20	Fraud	c4	5	10
		c5	1	10
20			6	20

Requirement :

risk_id	risk_name	control_number	freq	amount
10	ID Theft	c1	4	150
		c2
		c3
20	Fraud	c4	6	20
		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.