BookmarkSubscribeRSS Feed
ToonKnight
Calcite | Level 5

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 


Randomized Variable Values.JPG

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 0 replies
  • 738 views
  • 0 likes
  • 1 in conversation