Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- BI
- /
- Visual Analytics
- /
- How do I calculate difference between two datetime...

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

05-14-2017 11:32 AM

In VA 6.3 I am trying to calculate the difference between 2 datetime stamps - but only when both are not missing. Currently, I am getting the missing date as Jan 1 1960, and a very large # of days for my calculation which I don't want. I want to NOT display the Missing value as January 1 1960 12:00:00 AM in the PREV ETR D/T column and NOT have a result for the calculation if one of the dates is missing in my data source.

These are my calculated items and calculations so far for the example above.

PREV ETR D/T column:

Parse('PREV ETR'n, 'ANYDTDTM19.')

DIFF_ETR_CHANGED_TIME_PREV_ETR column:

TreatAs(_Number_, ( TreatAs(_Number_, 'TIME OF ETR CHANGE BY OP/SVCMAN'n) - TreatAs(_Number_, 'PREV ETR D/T'n) ))

I have tried to put an IF...THEN..ELSE around the calculation and only have it executed if the PREV ETR D/T is not missing - but I can't seem to get that to work.

Any help is greatly appreciated.

Thanks!

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

05-14-2017 12:02 PM

I'm not familiar with SAS VA but, from the snapshot you posted, it looks like the datetimes you are considering as null or missing, actually have a value of 0. Have you tried your if then logic checking whether the value is null, missing and/or 0?

Art, CEO, AnalystFinder.com

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

05-14-2017 02:49 PM

Art

Thanks. They definitely are Missing when viewed in SAS VA Data Explorer. Also, when I view my raw data source in SQL developer - they say NULL. I am forcing them to NULL with a SQL CASE statement in the Oracle view that is the source for the SAS VA LASR table.

I rebuilt some of the calculations part of the report from scratch (originally I just changed the data source of an existing report) and now I seem to get the result I am looking for. I get a - (dash) when the value is missing and in the calculation it also gives me a - since I assume it can't do the calculation of x-y if one of the values is missing.

So, it seems I may have something not quiet right in my current version of the report.

I will keep looking as I don't really want to rebuild it all from scratch again.

Thanks again.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

05-14-2017 03:26 PM

SAS typically stores datetimes as the number of seconds from January 1, 1960. In Base SAS you would use DATEPART to extract the date protion of the variable and then subtract the two variables. You likely need something similar in VA.

I would use COALESCE in SAS VA to test the missing and assign it to the value you expect or want.