topic Re: Percentage Order in ODS and Base Reporting

Percentage Order

ToonKnight — Mon, 16 Feb 2015 17:02:18 GMT

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.

Re: Percentage Order

Cynthia_sas — Tue, 17 Feb 2015 03:09:50 GMT

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

Re: Percentage Order

Ksharp — Tue, 17 Feb 2015 05:24:59 GMT

For your scenario , This could be done easily by SQL .

Re: Percentage Order

ToonKnight — Tue, 24 Feb 2015 15:48:01 GMT

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;