Hi,
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.
Thanks.
libname SIRA 'E:\SAS_BI\CFAD\DATA';
/* Import the SIRA Extract Raw
Dataset */
proc import datafile= 'E:\SAS_BI\CFAD\AP\Data\Rules2.xlsx'
out= SIRA
dbms= excel
replace;
run;
Data SAS_Extract;
Set SIRA.SIRA_SCORES;
policy_number_1 =
substr(application_number,verify(application_number,'0'));
date = datepart(rule_result_date);
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 nodupkey;
by policy_number
rule_name CDT2 match_key;
run;
/* Merge both datasets using Proc
SQL */
proc sort data= new nodupkey;
by policy_number_1 rule_name date
rule_result_key;
run;
/* Final Table*/
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.CDT2, 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.CDT2 = N.date then 1 else 0 end as Date_Equal
From SIRA S
inner join new N
on S.policy_number =
N.policy_number_1;
run;
For example, the first two rows from this output