02-05-2015 12:01 PM
I have attached a sample screenshot of the output generated from my final table. The issue arises from the 'date' variable and 'CDT2' variable which both represent dates in numerical form. I have a previous thread which is linked to this discussion, but I have now resolved that previous issue by converting both dates in numerical form, so it's worth a look for more information. The only other difference is that I have renamed the 'CDT' variable as 'CDT2'.
From the screenshot, the numbers highlighted in red should match together and the numbers highlighted in blue should also match. My code seems to have a flaw where it randomizes the order of both date values. For this example, the 'Date Equal' column should all be 1 , as you can see there are 2 rows displaying 0. The 'rule_result_key' variable and 'match_key' variable act as identifiers where I have highlighted 2 cases.
The purple section represents equality whilst the green section represents difference, but I believe the values for these two variables have been randomized with the two values above(row above), similar to the issue with the date values mentioned previously.
I'm not too sure what coding would be required to address this problem. I assume the 4 variables in question to be looked at are the 'date' , 'CDT2', 'rule_result_key' and 'match_key' variables.
I have outlined the code below for further assistance, any help would be greatly appreciated.
libname SIRA 'E:\SAS_BI\CFAD\DATA';
/* Import the SIRA Extract Raw
proc import datafile= 'E:\SAS_BI\CFAD\AP\Data\Rules2.xlsx'
date = datepart(rule_result_date);
create table new as
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;
proc sort data=SIRA nodupkey;
rule_name CDT2 match_key;
/* Merge both datasets using Proc
proc sort data= new nodupkey;
by policy_number_1 rule_name date
/* Final Table*/
Create Table Allign as
N.policy_number_1, S.policy_number, N.rule_name as SAS_RN, S.rule_name as SIRA_RN, N.date, S.CDT2, N.rule_result_key,
Case when S.rule_name = N.rule_name then 1 else 0 end as Rule_Equal,
case when S.CDT2 = N.date then 1 else 0 end as Date_Equal
From SIRA S
inner join new N
on S.policy_number =
For example, the first two rows from this output