Calculating Duration in Days within EG4.1

[deleted_user]

Help...how can one calculate the number of days between a start date (contained within a field in a table) and "today" within EG?

[ChrisHemedinger]

Using the query builder, you can add a calculated column to your table. In the expression for the new column, use the DATDIF function. Example:

DATDIF(TABLE.STARTDATE, TODAY(), 'ACT/ACT')

where TABLE.STARTDATE is the column that contains the start date you refer to.

Chris

[deleted_user]

Thanks for the prompt reply...

I followed your example above, but unfortunately the new calcuated column did not return any values.

The format for the start date is DATEAMPM22.0

Could this be an issue?

[Olivier]

Yes, the format indication explains it all : your date variable is not a "simple" date, but a datetime.
The DATEDIF function only deals with dates, not datetimes.

You should use the following formula :

TODAY() - DATEPART(yourDateColumn)

and this will return duration in days directly. Or, to derive from Chris' answer :

DATDIF(DATEPART(TABLE.STARTDATE), TODAY(), 'ACT/ACT')

[deleted_user]

Thanks again...it worked.

A related question...how to determine duration of days between a start and stop date, in DATEAMPM22.0 Format.

I have tried based on the 2 expressions above, but the values returned do not make sense. Far too large a number

[Olivier]

Then again, it is a problem of dates and datetimes.
SAS dates are stored in number of days since January 1st, 1960. So substracting one date from another gives durations in days without needing functions.
SAS datetimes (and most date columns imported into SAS Guide from Oracle, DB2, Excel) are stored in number of seconds since midnight on January 1st, 1960. So substracting one datetime from another will give delays expressed in seconds (that should be the result that you currently get).
The DATEPART function is transform a datetime into a date, by removing the time part and computing the right number of days from 01/01/1960.

So your delay computations should look like :
DATEPART(column1) - DATEPART(column2)

The last question is to guess if a given column is date or datetime : check its format. If the format is something like DATETIME., DATEAMPM., then it is datetime. If the format is DATE., DDMMYY., MMYY., YEAR., then it is a SAS date.