Exploring, 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?

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

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!

 

 

 

 

 


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

Accepted Solutions
Solution
‎08-05-2017 09:34 PM
Super User
Posts: 19,063

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. 

 

 

View solution in original post


All Replies
PROC Star
Posts: 7,432

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.

 

 

Solution
‎08-05-2017 09:34 PM
Super User
Posts: 19,063

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. 

 

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 335 views
  • 0 likes
  • 3 in conversation