Contributor
Posts: 42

Using Enterprise Guide 6.1 Date Calculations using Query Builder

I'm trying to write a query based upon two dates (Date1 and Date2) to determine if Date 2 is within 30 days of Date1 in Query Builder.

What is the best what to write this query?

Super User
Posts: 13,583

Re: Using Enterprise Guide 6.1 Date Calculations using Query Builder

If the variables are actually SAS date values, which is a good idea with any date value, then the condition can be expressed as: abs(date1-date2) le 30 (if exactly 30 days is acceptable, otherwise < 30)

Regular Contributor
Posts: 233

Re: Using Enterprise Guide 6.1 Date Calculations using Query Builder

data have;
input date1 date2;
format date1 mmddyy10. date2 mmddyy10.;
informat date1 mmddyy10. date2 mmddyy10.;
datalines;
2/12/2014  3/12/2014
1/2/2014   3/2/2014
;
run;

data want;
set have;
length flag \$60.;
if date2-date1 le 30 then flag='date 2 is with in 30 days of date 1';
else flag='date 2 is with NOT in 30 days of date 1';
run;

Contributor
Posts: 42

Re: Using Enterprise Guide 6.1 Date Calculations using Query Builder

I tried the following:

1. Using the INTCK Function I chose the two dates to calculate the amount of days between 9See below).

INTCK("DAY", t2.EffectiveDateOfCession,t1.AccidentDate)

2. Ran the query and got the following results:

EffCess              AccDate        EffCessAccDateDays

04/05/2013        06/11/13        67

3. Ran a query (see below).

t1.EffCessAccDate_Days = 30

This query will display all records with t1.EffCessAccDate_Days = 30

Is this another good solution?

Am I missing something?

Posts: 3,215

Re: Using Enterprise Guide 6.1 Date Calculations using Query Builder

is equal is not the same as le less or equal. Is there a language interpretation difference within?

---->-- ja karman --<-----
Discussion stats
• 4 replies
• 865 views
• 0 likes
• 4 in conversation