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 |
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?
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 |
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 |
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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.