turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- BI
- /
- Enterprise Guide
- /
- Randomized Values

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

02-05-2015 12:01 PM

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