Exploring, modeling, predicting and reporting with SAS Visual Analytics and SAS Visual Statistics

How do I calculate difference between two datetimestamps only when both are NOT Missing?

Reply
New Contributor
Posts: 2

How do I calculate difference between two datetimestamps only when both are NOT Missing?

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.

 

2017-05-14_10-37-35.jpg

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!

 

 

 

 

 

Attachment
Esteemed Advisor
Posts: 6,907

Re: How do I calculate difference between two datetimestamps only when both are NOT Missing?

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

 

New Contributor
Posts: 2

Re: How do I calculate difference between two datetimestamps only when both are NOT Missing?

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.

 

 

Grand Advisor
Posts: 16,393

Re: How do I calculate difference between two datetimestamps only when both are NOT Missing?

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. 

 

 

Post a Question
Discussion Stats
  • 3 replies
  • 81 views
  • 0 likes
  • 3 in conversation