02-03-2015 11:29 AM
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.
/* Import the SQL raw dataset */
proc import datafile= 'E:\SAS_BI\CFAD\AP\Data\Rules.xlsx'
/* SAS Dataset */
policy_number_1 = substr(application_number,verify(application_number,'0'));
date = datepart(rule_result_date);
format date $DATE9.;
date = input(CDT,DATETIME18.);
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;
proc sort data=SIRA_NEW nodupkey;
by policy_number rule_name CDT;
/* Merge both datasets using 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;
Proc Freq DATA= Allign;
02-04-2015 04:53 AM
Thanks for replying really appreciate it.
Apologies, the date variable which was created for sira_new can be marked as invalid, as this produces just an additional numeric variable which is not required.
Originally in excel, the 'rules' spreadsheet contains the 'CDT' variable which was displayed in the following format 'dd/mm/yyyy hh:mm:ss' . This was subsequently formatted on excel to a 'short date' format to output these values in the form 'dd/mm/yyyy'.
As you can see the 'CDT' variable portrays itself as a date variable when run on SAS. I'm not sure whether setting the 'CDT' variable as 'short date' on excel would make it text or numeric. I want to keep the formats for both 'CDT' (sira) and 'date'(SAS) in the form ddmmmyyyy.
Would I need to refer back and add conditions to the date variable I added for sira_new or would I need to do something else?
02-06-2015 03:56 AM
Like excel, SAS can format a datetime value as just the date without creating a new variable via the datepart() function.
Just apply the format DTDATE9. and only the date is shown.
There is no built-in format DTDDMMYY. provided with SAS but you could build one with PROC FORMAT.
When comparing a datetime value with a date constant don't aoply the datepart() function to the value (performance reasons) - just extent the constant in a range test like
where datetime_variable between "&dconst:0:0"dt and "&dateconst:23:59:59"dt