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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: