BookmarkSubscribeRSS Feed
rehman_panjwani
Calcite | Level 5
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
2 REPLIES 2
LaurieF
Barite | Level 11

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? 

rehman_panjwani
Calcite | Level 5

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. 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 839 views
  • 0 likes
  • 2 in conversation