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. ... View more

Hi, I'm generating a report using proc report to output data split by month. The issue that I'm currently having is sorting the months across in order. There are 2 attachments, one to represent the code and the other which conveys the output. For the output, my initial assumption is that this has something to do with the first row where there are no values. I have tried to remedy this but unfortunately to no avail. Would really appreciate any help on the matter if possible. Thanks ... View more

Hi, I'm just trying to compare against certain columns from two datasets. One of these is derived from SQL database extraction and the other is a SAS dataset. The issue has been with the date variables. The 'rule_result_date' variable from the SAS dataset has an original format of ddmmmyy:hh:mm:ss, whilst the 'CDT' variable has a format of ddmmmyyyy from the SQL excel dataset. With the code below, I have managed to format the 'rule_result_variable' (which has been renamed as Date) into ddmmmyyyy. However the issue arises in the section when creating the final table 'Allign'. I am trying to compare both dates (i.e Date and CDT) by adding a new column 'Date_Equal' to show which dates are equal, denoted by either a '1' for equivalence or '0'  for non-equivalence. However the 'Date_Equal' column is just returning all zeroes, despite the fact by visual observation I can see the dates are clearly equal. Would really appreciate some help on the matter. I have attached a screenshot of the 'Allign' table output to demonstrate the problem. Thank you. /* Import the SQL raw dataset */ proc import datafile= 'E:\SAS_BI\CFAD\AP\Data\Rules.xlsx' out= SIRA dbms= excel replace; run; /* SAS Dataset */ Data SAS_Extract; Set SIRA.SIRA_SCORES; policy_number_1 = substr(application_number,verify(application_number,'0')); date = datepart(rule_result_date); format date $DATE9.; run; data SIRA_NEW; set SIRA; date = input(CDT,DATETIME18.); run; proc sql; create table new as select distinct SE.policy_number_1, SE.rule_name, SE.date, SE.rule_result_key from SAS_Extract SE where date between '01JAN2014'D and '31MAR2014'D order by policy_number_1; run; proc sort data=SIRA_NEW  nodupkey; by policy_number rule_name CDT; run; /* Merge both datasets using Proc SQL */ Proc sql; Create Table Allign as Select distinct N.policy_number_1, S.policy_number, N.rule_name as SAS_RN, S.rule_name as SIRA_RN, N.date, S.CDT, N.rule_result_key, S.match_Key, Case when S.rule_name = N.rule_name then 1 else 0 end as Rule_Equal, case when S.CDT = N.date then 1 else 0 end as Date_Equal From SIRA_NEW  S inner join new N on S.policy_number = N.policy_number_1; run; Proc Freq DATA= Allign; Tables SAS_RN*Date_Equal; run; ... View more