BookmarkSubscribeRSS Feed
ToonKnight
Calcite | Level 5

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;


Allign.JPG
4 REPLIES 4
ballardw
Super User

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?

ToonKnight
Calcite | Level 5

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.

Peter_C
Rhodochrosite | Level 12

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

ShashikanthRai
Calcite | Level 5

Format DTDATE9. is nice one. Thank you.

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
  • 4 replies
  • 1945 views
  • 1 like
  • 4 in conversation