Hi,
I'm just trying to order the 'Adverse %' column descending wise but have had no success. I've tried using versions of the order function on the 'Adverse %' statement but this has still not outputted the desired result.
The code for this report is outlined below;
/* Assigning Libname */
libname SIRA 'E:\SAS_BI\CFAD\DATA';
Data SAS_Extract;
Set SIRA.SIRA_SCORES;
policy_number_1 = substr(policy_number,verify(policy_number,'0'));
run;
Proc sql;
create table RULEHIT as
select distinct policy_number,
rule_name,
case when action_status in ('Fraud ','Inconsistency','Suspect') then 1 else 0 end as Adverse
from SAS_EXTRACT
run;
roc report data= RULEHIT split="~" style(header)=[vjust=b] nowd headline headskip;
title1 "Fraud Rates For Most Common SIRA Rule Hits";
column rule_name n adverse adverse=pct;
define rule_name / group 'Rule Name';
define n / 'N';
define adverse / analysis sum 'Adverse';
define Pct / analysis mean format = percent9.2 'Adverse %';
run;
There is a also a screenshot attached to show the result output.
Would really appreciate the help,
Thanks.
Hi:
You are creating a grouped report. That means your ADVERSE column is a summary column (summarized from your individual detail rows). So you cannot ask for ADVERSE to be summarized and then to set the order in the same step. Generally, this means you have to make 2 passes thru the data. There is an example of this in my paper "Creating Complex Reports" -- see example 3 on page 11. And I believe I have posted the example on the forum in answer to a previous posting.
https://communities.sas.com/message/16737#16737
and
https://communities.sas.com/message/50676#50676
cynthia
Hi,
I've tried implementing the following code, but have received an error regarding the size of the data. I have attached the error below and have subsequently selected 'no'. Just wandering because of this, this could explain why the proc report step hadn't run hence why the 'Rulehits' was the final output?
I've also tried to group by the rule name (which was originally intended) in proc report , which has eliminated the data capacity error but still hasn't ordered the 'Adverse %' column. As below the 'Adverse' column had already been ordered in the proc sql stage, so am a bit confused as to why it hasn't ordered in the proc report stage by default.
Thanks,
/* Assigning Libname */
libname SIRA 'E:\SAS_BI\CFAD\DATA';
Data SAS_Extract;
Set SIRA.SIRA_SCORES;
policy_number_1 = substr(policy_number,verify(policy_number,'0'));
run;
Proc sql;
create table RULEHIT as
select distinct policy_number,
rule_name,
case when action_status in ('Fraud ','Inconsistency','Suspect') then 1 else 0 end as Adverse
from SAS_EXTRACT
run;
proc sql;
create table RULEHITS as
Select RH.Policy_Number, Adverse, rule_name
from RULEHIT RH
group by RH.Policy_Number
order by Adverse descending;
quit;
proc report data= RULEHITS split="~" style(header)=[vjust=b] nowd headline headskip;
title1 "Fraud Rates For Most Common SIRA Rule Hits";
column rule_name n Adverse Adverse=pct;
define rule_name / order order=data 'Rule Name';
define n /'N';
define Adverse / analysis Sum 'Adverse';
define Pct / analysis mean format = percent9.2 'Adverse %';
rbreak after / Summarize;
Compute after;
rule_name = 'Total';
endcomp;
run;
For your scenario , This could be done easily by SQL .
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.