topic Date Format in SAS Enterprise Guide

Date Format

ToonKnight — Tue, 03 Feb 2015 16:29:08 GMT

Hi,

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.

Thank you.

/* Import the SQL raw dataset */
proc import datafile= 'E:\SAS_BI\CFAD\AP\Data\Rules.xlsx'
out= SIRA
dbms= excel
replace;
run;

/* SAS Dataset */

Data SAS_Extract;
Set SIRA.SIRA_SCORES;
policy_number_1 = substr(application_number,verify(application_number,'0'));
date = datepart(rule_result_date);
format date $DATE9.;
run;

data SIRA_NEW;
set SIRA;
date = input(CDT,DATETIME18.);
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_NEW nodupkey;
by policy_number rule_name CDT;
run;

/* Merge both datasets using Proc SQL */

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;
run;

Proc Freq DATA= Allign;
Tables SAS_RN*Date_Equal;
run;

Re: Date Format

ballardw — Tue, 03 Feb 2015 17:27:50 GMT

Why are you using CDT from Sira_new instead of the DATE variable you created? CDT is either text or a numeric that looks like a datetime and would not be the date?

Re: Date Format

ToonKnight — Wed, 04 Feb 2015 09:53:34 GMT

Hi,

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?

Thank you.

Re: Date Format

Peter_C — Fri, 06 Feb 2015 08:56:35 GMT

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

Re: Date Format

ShashikanthRai — Fri, 06 Feb 2015 12:50:47 GMT

Format DTDATE9. is nice one. Thank you.